asked on

Dynamic value assignment Case when else

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

Thanks in Advance

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'

Open in new window

CoyotesIT

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

tigin44

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'

ZURINET

ASKER

Hi tigin44

I am have an error on line

Line 8: Incorrect syntax near '='.

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

additional help will be needed

ASKER CERTIFIED SOLUTION

eszaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ZURINET

ASKER

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

tigin44

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'

eszaq

CASE should go into SELECT clause not into WHERE clause