Case WHEN in SQL SELECT Statement.

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
deanlee17Asked:
Who is Participating?
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
deanlee17Author Commented:
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
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
deanlee17Author Commented:
I think i almost understand lol.

See print screen, am I on the right track?

Thanks
print-screen.docx
0
 
Lynn HuffIT DirectorCommented:
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
 
deanlee17Author Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
deanlee17Author Commented:
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
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.