[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Case WHEN in SQL SELECT Statement.

Posted on 2012-03-26
9
Medium Priority
?
551 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 46

Expert Comment

by:Kent Olsen
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 46

Expert Comment

by:Kent Olsen
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

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
 
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 46

Accepted Solution

by:
Kent Olsen earned 2000 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 46

Expert Comment

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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

591 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