Solved

Case WHEN in SQL SELECT Statement.

Posted on 2012-03-26
9
539 Views
Last Modified: 2012-03-26
I am trying to achieve the following, is this possible....

select A.NAME
     , A.MAINID
     , SA.SalePrice as Price
     , case when SA.Name = 'somename' AND Unitcost > 0
            then Case SELECT Result from View1 name WHERE A.MAINID = X
            else Case SELECT Result from View2 name WHERE A.MAINID = X AS NewResult

Thanks,
Dean
0
Comment
Question by:deanlee17
  • 4
  • 4
9 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 37765496
Hi Dean,

Each item in the CASE statement must be a single, scalar item.

If the results of the sub-select statements do that, wrapping the sub-queries with parentheses should work for you.

select A.NAME
     , A.MAINID
     , SA.SalePrice as Price
     , case when SA.Name = 'somename' AND Unitcost > 0
            then Case (SELECT Result from View1 name WHERE A.MAINID = X)
            else Case (SELECT Result from View2 name WHERE A.MAINID = X) 
       end AS NewResult

Open in new window


Good Luck,
0
 

Author Comment

by:deanlee17
ID: 37765525
Hi thanks for the reply,

Ok here is my complete code, I am getting the error message...

Error in WHERE clause near 'ELSE'.
Unable to parse query text.

=================================

SELECT     idMESTEC, MESDATE, LOCATION, ALLOCATION, OWNERSHIPTYPE, PARTNBR, VERSION, PARTDESCRIPTION, QTY, TRANSTYPE, REASONCODE, REASONDESC,
                      TRANSCOMMENT, GRNNBR, SERIALNBR, CURRENCY, UNITCOST, TOTALBASELINECOST, COMPARTNBR,

            Case when OWNERSHIPTYPE = "VMI Thales" AND UNITCOST> 0
            then Case (SELECT Price  from [MesTransData].[dbo].[stocksale] WHERE [MesTransData].[dbo].[stocksale].[PartNumber] =  PARTNBR)
            else Case (SELECT V1Price from [MesTransData].[dbo].[tblV1Price] WHERE [MesTransData].[dbo].[tblV1Price].[V1Item]  = PARTNBR) AS NewResult


FROM         dbo.tblMestecData
WHERE     (MESDATE > '2012-01-29') AND (MESDATE < '2012-02-25')

=================================

Thanks,
Dean
0
 
LVL 45

Expert Comment

by:Kdo
ID: 37765549
Hi Dean,

There's no 'end' statement on the CASE clause.  :)

The last line of the case statement should be:


  MesTransData].[dbo].[tblV1Price].[V1Item]  = PARTNBR) end AS NewResult


Just for the record, if you've got a lot of data in the table, you'll probably get better performance if you join views 1 and 2 to the base query, and select the item in the case statement based on the joined result instead of sub-select in a cases statement.


Kent
0
 

Author Comment

by:deanlee17
ID: 37765633
I think i almost understand lol.

See print screen, am I on the right track?

Thanks
print-screen.docx
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 3

Expert Comment

by:Lynn Huff
ID: 37765683
Try this:

SELECT     idMESTEC, MESDATE, LOCATION, ALLOCATION, OWNERSHIPTYPE, PARTNBR, VERSION, PARTDESCRIPTION, QTY, TRANSTYPE, REASONCODE, REASONDESC,
                      TRANSCOMMENT, GRNNBR, SERIALNBR, CURRENCY, UNITCOST, TOTALBASELINECOST, COMPARTNBR,

            Case
                  WHEN OWNERSHIPTYPE = "VMI Thales" AND UNITCOST> 0
                  THEN (SELECT Price  from [MesTransData].[dbo].[stocksale]
                             WHERE [MesTransData].[dbo].[stocksale].[PartNumber] =  PARTNBR)
                  ELSE (SELECT V1Price from [MesTransData].[dbo].[tblV1Price]
                            WHERE [MesTransData].[dbo].[tblV1Price].[V1Item]  = PARTNBR)
                  END AS NewResult


FROM         dbo.tblMestecData
WHERE     (MESDATE > '2012-01-29') AND (MESDATE < '2012-02-25')
0
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 37765714
Hi Dean,

You're very close.  The CASE statement needs just a bit of work though.  Remember that CASE chooses a value from multiple possible values.  :)

CASE when ownershiptype = "VMI Thales" AND unitcost > 0
     then Price
     else V1Price
END as NewResult

Open in new window



And you probably want to do outer (LEFT) joins to tblMestercData.

Kent
0
 

Author Comment

by:deanlee17
ID: 37765764
Hi ML-Huff I think to try and keep this running as quickly as possible I will continue with Kent's suggestion for now.

Hi Kent,

Even tho im using...

 CASE WHEN OWNERSHIPTYPE = "VMI Thales" AND UNITCOST > 0 THEN Price ELSE V1Price END AS NewResult

I get the error: Invalid column name "VMI THALES", even tho its not a column name, its data in a column?
0
 
LVL 45

Expert Comment

by:Kdo
ID: 37765796
Oops.  Use single quotes around strings.  Double quote indicate that the contents are an object name

 CASE WHEN OWNERSHIPTYPE = 'VMI Thales' AND UNITCOST > 0 THEN Price ELSE V1Price END AS NewResult

Open in new window



Kent
0
 

Author Comment

by:deanlee17
ID: 37765830
Hi Kent,

That worked a charm. Thanks for all your help.

Was also useful that you suggested a quicker way of constructing this, im always interested in query optimisation and speed.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Read about achieving the basic levels of HRIS security in the workplace.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

861 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now