?
Solved

Dynamic value assignment Case when  else

Posted on 2008-11-03
7
Medium Priority
?
250 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:ZURINET
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 8

Expert Comment

by:CoyotesIT
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

by:tigin44
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

by:ZURINET
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

additional help will be needed
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Accepted Solution

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

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

Expert Comment

by:tigin44
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

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

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

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…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question