Case Statement - 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

General SQL Server Forums

New to SQL Server Programming

Case Statement

Author

Topic

mrkensr
Starting Member

9 Posts

Posted - 2008-09-24 : 13:38:16

I have this:
Declare @testcount as int
Select ACCOUNT.Account_Id,
ID=
CASE
(select count(*) from HOLDING AS HOLDING
where HOLDING.Eligible_Cash_Management_Fl<>0
AND HOLDING.Account_id=ACCOUNT.Account_ID)

WHEN 0 THEN
'NO_ID'

WHEN 1 THEN
'ONE'
ELSE
'MULTI'
END
From dbo.ACCOUNT AS ACCOUNT

What I want to do is set another variable in "When 0" case
such as
WHEN 0 THEN
'NO_ID'
Set @new_variable=0
When 1 THEN

Havent been able to get this right
Any help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 13:40:08

You cant use case in such case what you want is if else for that

if (select count(*) from HOLDING AS HOLDING
where HOLDING.Eligible_Cash_Management_Fl<>0
AND HOLDING.Account_id=ACCOUNT.Account_ID)=0
set @new_variable=0
else
....

X002548
Not Just a Number

15586 Posts

Posted - 2008-09-24 : 13:43:51

Well you're not setting anything now.

And you can't make an assignment in a case

If you want 2 different values, just use 2 sets

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mrkensr
Starting Member

9 Posts

Posted - 2008-09-24 : 13:55:04

I am setting the value in the Case statement:
Select ACCOUNT.Account_Id,
ID=
CASE
(select count(*) from HOLDING AS HOLDING
where HOLDING.Eligible_Cash_Management_Fl<>0
AND HOLDING.Account_id=ACCOUNT.Account_ID)

WHEN 0 THEN
'NO_ID'

You cant use case in such case what you want is if else for that

if (select count(*) from HOLDING AS HOLDING
where HOLDING.Eligible_Cash_Management_Fl<>0
AND HOLDING.Account_id=ACCOUNT.Account_ID)=0
set @new_variable=0
else
Would okay for setting the new variable, but not setting the value in the case statement
Select ACCOUNT.Account_Id,
ID=
CASE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 14:01:25

quote:
Originally posted by mrkensr

I am setting the value in the Case statement:
Select ACCOUNT.Account_Id,
ID=
CASE
(select count(*) from HOLDING AS HOLDING
where HOLDING.Eligible_Cash_Management_Fl<>0
AND HOLDING.Account_id=ACCOUNT.Account_ID)

WHEN 0 THEN
'NO_ID'

You cant use case in such case what you want is if else for that

if (select count(*) from HOLDING AS HOLDING
where HOLDING.Eligible_Cash_Management_Fl<>0
AND HOLDING.Account_id=ACCOUNT.Account_ID)=0
set @new_variable=0
else
Would okay for setting the new variable, but not setting the value in the case statement
Select ACCOUNT.Account_Id,
ID=
CASE

didnt get what you told there. can you clarify?

mrkensr
Starting Member

9 Posts

Posted - 2008-09-24 : 14:09:16

I was saying that using the if statement is okay for setting the new variable, but I was trying to also set the value in my Case statement.
Basically Im want to both, but its not looking good

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 14:17:21

is this what you want?

Declare @testcount as int

select @testcount=count(*) from HOLDING AS HOLDING 
where HOLDING.Eligible_Cash_Management_Fl<>0
AND HOLDING.Account_id=ACCOUNT.Account_ID

Select ACCOUNT.Account_Id, 
ID=
CASE @testcount
WHEN 0 THEN
'NO_ID'

WHEN 1 THEN
'ONE'
ELSE
'MULTI'
END
From dbo.ACCOUNT AS ACCOUNT

if @testcount=0
set @new_variable=0
else
....

mrkensr
Starting Member

9 Posts

Posted - 2008-09-24 : 15:08:06

No, this would not work...
The " select @testcount " would need to be in the Select Case as the ACCOUNT.Account_ID would change as I went thru the rows.

Such as
Select ACCOUNT.Account_Id,
ID=
CASE(select @testcount=count(*) from HOLDING AS HOLDING
where HOLDING.Eligible_Cash_Management_Fl<>0
AND HOLDING.Account_id=ACCOUNT.Account_ID)
WHEN 0 THEN
'NO_ID'

WHEN 1 THEN
'ONE'
ELSE
'MULTI'
END

From dbo.ACCOUNT AS ACCOUNT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 15:11:15

ok. then what was the probelm? why were you trying to set some other variable in between this?

mrkensr
Starting Member

9 Posts

Posted - 2008-09-24 : 15:28:42

I have another value in my select that I only need to do if the @testcount was >0
Like this
Declare @testcount as int
Select ACCOUNT.Account_Id,
ID=
CASE
(select count(*) from HOLDING AS HOLDING
where HOLDING.Eligible_Cash_Management_Fl<>0
AND HOLDING.Account_id=ACCOUNT.Account_ID)

WHEN 0 THEN
'NO_ID'
WHEN 1 THEN
'ONE'
ELSE
'MULTI'
END,
NAME=

CASE (@testcount)

WHEN 0 THEN
'NONAME'
WHEN 1 THEN
(Select Name from from HOLDING AS HOLDING
where HOLDING.Eligible_Cash_Management_Fl<>0
AND HOLDING.Account_id=ACCOUNT.Account_ID)
ELSE
'MULTINAME'
END
From dbo.ACCOUNT AS ACCOUNT

mrkensr
Starting Member

9 Posts

Posted - 2008-09-25 : 15:15:39

visakh16....Did you give up on me?

X002548
Not Just a Number

15586 Posts

Posted - 2008-09-25 : 16:13:22

Here ya go.

Stop building a rocket ship and keep it simple

Define what you need,

Give some sample data

Then post the expected results

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 01:04:39

quote:
Originally posted by mrkensr

visakh16....Did you give up on me?

you're not helping yourself. Then how can we help you...
You have not specified what your exact problem is till now
You have not provided us any sample data or expected output to illustrate your problem
Please keep in mind that we dont know your system and also cant read your mind to understand what you're trying to do.
Refer the link below which Brett already suggested and try to provide necessary info in reqd format. when we will be able to help you out more easily.

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

mrkensr
Starting Member

9 Posts

Posted - 2008-09-29 : 08:30:51

Im not helping myself???
Ive explained what I have, what im trying to do and why, answered the questions you responded back with.
I think Ive explain each step and why the suggestions ( that didnt answer the questions )wouldnt solve what I was trying to do....
I think you guys do a great service, but you should read the postings before you make comments..
If you look back through the posts for this, it explained what I was trying to do....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 09:48:45

quote:
Originally posted by mrkensr

Im not helping myself???
Ive explained what I have, what im trying to do and why, answered the questions you responded back with.
I think Ive explain each step and why the suggestions ( that didnt answer the questions )wouldnt solve what I was trying to do....
I think you guys do a great service, but you should read the postings before you make comments..
If you look back through the posts for this, it explained what I was trying to do....

from first post till latest, you've just given us a query. each time you speak about variable also which i cant understand. probably if you can provide some sample data and explain what you want giving your reqd output, somebody will be able to help. DId you read link i posted giving instructions on how to post a question?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources