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?

Improve company productivity with a Business Account.Sign Up

x
 
Kent OlsenConnect With a Mentor Data 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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
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.