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?

[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.

Kent OlsenDBACommented:
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
Kent OlsenDBACommented:
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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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
Kent OlsenDBACommented:
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

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
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 OlsenDBACommented:
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
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
Query Syntax

From novice to tech pro — start learning today.