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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

eszaqCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.