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

ZURINETAsked:
Who is Participating?
 
eszaqConnect With a Mentor Commented:
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
 
CoyotesITCommented:
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
 
tigin44Commented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
ZURINETAuthor Commented:
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
0
 
ZURINETAuthor Commented:
Hi all thanks non of the code seems to work :-)
0
 
tigin44Commented:
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
 
eszaqCommented:
CASE should go into SELECT clause not into WHERE clause
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.