lookup parameter proc help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

lookup parameter proc help

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-04 : 09:48:18

Hi,

I am trying to create the below lookup parameter proc which should display values as below so the front end can pick up the values based on the key.

I get the error below

Msg 4145, Level 15, State 1, Procedure splkup_BuySell_AvgCoverRatio, Line 25
An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'. Am I missing a condition? Thanks for your help.

Key Value
Bid Bid
Offer Offer
Both Both

DECLARE @BuyOrSell TABLE
(
[Key] varchar(50),
[Value] varchar(50)
)

--SELECT DISTINCT IsBuy FROM Trade

INSERT INTO @BuyOrSell

SELECT lookup_key = IsBuy,
lookup_val = IsBuy
FROM Trade
WHERE ISBuy = CASE WHEN 1 THEN 'Bid' WHEN 0 THEN 'Offer' ELSE 'ALL' END

SELECT
lookup_key = bs.[Key],
lookup_val = bs.[Value]
FROM
@BuyOrSell bs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 09:51:52

you should have a condition specified for CASE

CASE <some condition> WHEN 1 THEN 'Bid' WHEN 0 THEN 'Offer' ELSE 'ALL' END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 09:52:13

INSERT INTO @BuyOrSell

SELECT lookup_key = IsBuy,
lookup_val = IsBuy
FROM Trade
WHERE ISBuy = CASE [value] WHEN 1 THEN 'Bid' WHEN 0 THEN 'Offer' ELSE 'ALL' END

Madhivanan

Failing to plan is Planning to fail

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-04 : 10:38:29

Question:

IsBuy has value 1 0r 0. I get the below error for the query below. Should I use the convert ?

Msg 245, Level 16, State 1, Procedure splkup_BuySell_AvgCoverRatio, Line 20
Conversion failed when converting the varchar value 'Bid' to data type bit.

DECLARE @BuyOrSell TABLE
(
[Key] varchar(50),
[Value] varchar(50)
)

--SELECT DISTINCT IsBuy FROM Trade

INSERT INTO @BuyOrSell

SELECT
lookup_key = IsBuy,
lookup_val = IsBuy
FROM Trade trd
WHERE IsBuy = CASE [IsBuy] WHEN 1 THEN 'Bid' WHEN 0 THEN 'Offer' ELSE 'ALL' END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 10:51:31

what re you trying to do in WHERE clause? can you explain that?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-04 : 10:59:05

Sure. I want to check for the value of IsBuy. When it is 1 I want to replace with 'Bid' When 0 I want to replace with 'Offer'

I want the final result of the proc to look like so
Key Value
Bid Bid
Offer Offer
Bother Both

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 11:06:42

how Bother became Both?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-04 : 11:20:28

Oops, It is Both, typo error, apologies.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 11:29:55

ok. so whats the relevance of CASE expression in WHERE. what check are you doing there?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-04 : 12:00:15

The reason for CASE is that I want to restrict to three rows values which will be passed to an in-house GUI via the column "Key".

I tried the below query but I get too many rows. I will try other options but any help is appreciated.

SELECT
lookup_key = CASE trd.IsBuy WHEN 1 THEN 'Bid' WHEN 0 THEN 'Offer' ELSE 'ALL' END,
lookup_val = CASE trd.IsBuy WHEN 1 THEN 'Bid' WHEN 0 THEN 'Offer' ELSE 'ALL' END
FROM Trade trd

For example:

DECLARE @SingleOrList TABLE
(
[Key] varchar(50),
[Value] varchar(50)
)

INSERT INTO @SingleOrList VALUES ('All Inquiries','All Inquiries')
INSERT INTO @SingleOrList VALUES ('Single Inquiry','Single Inquiries')
INSERT INTO @SingleOrList VALUES ('Lists','Lists')

SELECT
lookup_key = yn.[Key],
lookup_val = yn.Value
FROM
@SingleOrList yn

gives the below output but I do not have aconditional bit value to check for in a base table so it is simple. The one I am having an issue with is a little tricky.

Key Value
All Inquiries All Inquiries
Single Inquiry Single Inquiries
Lists Lists

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 12:02:55

Whats the tricky one? post the query that you're having problem with not the one which is working fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-04 : 12:11:19

This is what i tried before and I get too much data

DECLARE @BuyOrSell TABLE
(
[Key] varchar(50),
[Value] varchar(50)
)
INSERT INTO @BuyOrSell

SELECT
lookup_key = CASE trd.IsBuy WHEN 1 THEN 'Bid' WHEN 0 THEN 'Offer' ELSE 'ALL' END,
lookup_val = CASE trd.IsBuy WHEN 1 THEN 'Bid' WHEN 0 THEN 'Offer' ELSE 'ALL' END
FROM Trade trd

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 12:18:28

what do you mean by too much data? explain with example..plzz understand we cant see your system neither can we read your mind so unless you tell us with help of data what you're getting and what you expect we cant help you

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-04 : 12:34:28

I mean it continues to print while I would like to restrict to 3 rows of data. I tried doing joins to filter but this did not help.
Key Value
Bid Bid
Offer Offer
Bother Both

Key Value
Bid Bid
Offer Offer
Bother Both
Bid Bid
Offer Offer
Bother Both
Bid Bid
Offer Offer
Bother Both
Bid Bid
Offer Offer
Bother Both
Bid Bid
Offer Offer
Bother Both

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-04 : 12:39:23

I feel dumb. Sorry. I did a DISTINCT and I have the following. Now IsBuy from Trade has only 1 or 0 hence the ELSE case will never happen but I have it there anyways.

SELECT DISTINCT
lookup_key = CASE trd.IsBuy WHEN 1 THEN 'Bid' WHEN 0 THEN 'Offer' ELSE 'BOTH' END,
lookup_val = CASE trd.IsBuy WHEN 1 THEN 'Bid' WHEN 0 THEN 'Offer' ELSE 'BOTH' END
FROM Trade trd

Key Value
Offer Offer
Bid Bid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 12:41:59

Do you think anybody can guess your problem from what you've explained so far?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources