Solved

# Dynamic value assignment Case when  else

Posted on 2008-11-03
Medium Priority
250 Views
Hi all

I need to dynamically assing the value of level in the code below

That is , if the total count of ArtikelNumber is greater than 1
then set the level to 3

``````select Col1, Col2,level
from Table
where (Table.level = ((select  Count(*) as ArtikelNumber
from Table
Where Col1 = 101
and Col2 = 00150
and(Table.Gueltigab <= getdate() and Table.Gueltigbis >= getdate())
group by Col1) > 1) then 3

and col3 = 'cash'
``````
0
Question by:ZURINET
• 2
• 2
• 2
• +1

LVL 8

Expert Comment

ID: 22870467
Using a CASE statement you would be able to determine this

i.e.

CASE SELECT COUNT(*) FROM ArtickelNumber WHERE ....
WHEN > 1 THEN @LEVEL = 3

Something like that.

Take a look here:

http://msdn.microsoft.com/en-us/library/ms181765.aspx
0

LVL 26

Expert Comment

ID: 22870579
hope this helps
select Col1, Col2,level
from Table
where Table.level = CASE (select  Count(*) as ArtikelNumber
from Table
Where Col1 = 101
and Col2 = 00150
and(Table.Gueltigab <= getdate() and Table.Gueltigbis >= getdate())
group by Col1)
WHEN = 1  THEN 2
WHEN > 1  THEN 3
..........
ELSE 1 END

and col3 = 'cash'
0

Author Comment

ID: 22870704
Hi tigin44

I am have an error on line

Line 8: Incorrect syntax near '='.

that is between group by Col1) WHEN = 1  THEN 2

0

LVL 8

Accepted Solution

eszaq earned 2000 total points
ID: 22870853
Don' know anything about your data and do not have database "handy" at the moment to test the query. So, I am not sure if you are over complicating or I am over simplifying.
Anyway, try this:

SELECT Col1, Col2, 'new_level' =
CASE
WHEN count(*) >1 THEN 3
ELSE count(*)
END
FROM Table
WHERE       Col1 = 101
and Col2 = 00150
and col3 = 'cash'
and Table.Gueltigab <= getdate()
and Table.Gueltigbis >= getdate()
GROUP BY 1,2,3
HAVING count(*) >1
0

Author Comment

ID: 22871209
Hi all thanks non of the code seems to work :-)
0

LVL 26

Expert Comment

ID: 22914167
sory... I put operators that causes the error...
try this..

select Col1, Col2,level
from Table
where Table.level = CASE (select  Count(*) as ArtikelNumber
from Table
Where Col1 = 101
and Col2 = 00150
and(Table.Gueltigab <= getdate() and Table.Gueltigbis >= getdate())
group by Col1)
WHEN  1  THEN 2
WHEN  1  THEN 3
..........
ELSE 1 END

and col3 = 'cash'
0

LVL 8

Expert Comment

ID: 22918856
CASE should go into SELECT clause not into WHERE clause
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed â€¦
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from â€¦
Screencast - Getting to Know the Pipeline
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery procedâ€¦
###### Suggested Courses
Course of the Month15 days, 16 hours left to enroll