Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need Help With CASE Statement (Newbie)

Status
Not open for further replies.
TanmedIT

TanmedIT

IS-IT--Management
Nov 6, 2000
53
0
0
US
I am trying to figure out how to use these CASE statements.

I have a Stored Procedure that is capturing some incorrect data.

I need to to filter out base on 2 criteria:

If USCATVLS_1 = "Latitude" then USCATVLS_2 = "Hum. Stem"
else
If USCATVLS_1 = "RHS" then USCATVLS_2 = "Rad. Stem"
else
IV00101.USCATVLS_2 in ('Elbow','Femur','Glenoid','Great Toe','Hum. Stem','Lesser Toe','PLATE','Plug','Prime Pin','Prm Screw','Prox Screw','Rad. Stem','RES. HEAD','Staple','SubTalar','Surg. Pack','Talar','Wedge')

I tried to make a CASE statement, but I dont know how to use the list of values at the end, it only wants me to use one.

Any Help?

I am using SQL 2005
 
Sort by date Sort by votes
Code: 
SELECT ...
      FROM ...
WHERE (USCATVLS_2 IN (CASE WHEN USCATVLS_1 = "Latitude" 
                                THEN "Hum. Stem"
                           WHEN USCATVLS_1 = "Latitude" 
                                THEN "Rad. Stem"
                      ELSE 'Elbow','Femur','Glenoid',
                           'Great Toe','Hum. Stem',
                           'Lesser Toe','PLATE',
                           'Plug','Prime Pin','Prm Screw',
                           'Prox Screw','Rad. Stem',
                           'RES. HEAD','Staple','SubTalar',
                           'Surg. Pack','Talar','Wedge'
                      END)
NOT TESTED

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP
 
Upvote 0 Downvote
I GET THIS ERROR:

Msg 102, Level 15, State 1, Line 29
Incorrect syntax near ','.

HERE IS MY QUERY:

SELECT
SOP10200.SOPTYPE, SOP10100.VOIDSTTS,SOP10100.SOPNUMBE, IV00101.ITEMNMBR, RM00101.CUSTCLAS, SOP10100.SALSTERR, SOP10106.USRDAT01, SOP10100.CUSTNMBR, IV00101.USCATVLS_1, IV00101.USCATVLS_2, IV00101.ITMCLSCD, SOP10200.QUANTITY, SOP10200.QTYTOINV, IV00101.ITMGEDSC
FROM
(((TOR.dbo.SOP10200 SOP10200 with(nolock) RIGHT OUTER JOIN TOR.dbo.IV00101 IV00101 with(nolock) ON SOP10200.ITEMNMBR=IV00101.ITEMNMBR) INNER JOIN TOR.dbo.SOP10100 SOP10100 with(nolock) ON (SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND (SOP10200.SOPNUMBE=SOP10100.SOPNUMBE)) INNER JOIN TOR.dbo.SOP10106 SOP10106 with(nolock) ON (SOP10100.SOPTYPE=SOP10106.SOPTYPE) AND (SOP10100.SOPNUMBE=SOP10106.SOPNUMBE)) INNER JOIN TOR.dbo.RM00101 RM00101 with(nolock) ON SOP10100.CUSTNMBR=RM00101.CUSTNMBR

WHERE
SOP10100.VOIDSTTS=0 and NOT (SOP10100.SOPNUMBE LIKE 'INVREP%' OR SOP10100.SOPNUMBE LIKE 'REP%') AND NOT (SOP10200.ITEMNMBR='FEDEX CHG' OR SOP10200.ITEMNMBR='FREIGHT 0' OR SOP10200.ITEMNMBR='FREIGHT 100' OR SOP10200.ITEMNMBR='FREIGHT 50' OR SOP10200.ITEMNMBR='INST FEE 0' OR SOP10200.ITEMNMBR='INST FEE 100' OR SOP10200.ITEMNMBR='INST FEE 50') AND RM00101.CUSTCLAS<>'DIST' AND (RM00101.SALSTERR='CANADA' OR RM00101.SALSTERR='MTN PLAINS' OR RM00101.SALSTERR='NORTH CENTRAL' OR RM00101.SALSTERR='NORTH EAST' OR RM00101.SALSTERR='NORTHEAST' OR RM00101.SALSTERR='SOUTH CENTRAL' OR RM00101.SALSTERR='SOUTHEAST' OR RM00101.SALSTERR='WEST') AND (SOP10200.SOPTYPE=2 OR SOP10200.SOPTYPE=3 OR SOP10200.SOPTYPE=4) AND IV00101.ITMCLSCD<>'INSTRU' AND
(USCATVLS_2 IN (CASE WHEN USCATVLS_1 = "Latitude"
THEN "Hum. Stem"
WHEN USCATVLS_1 = "RHS" THEN "Rad. Stem"
ELSE ('Elbow','Femur','Glenoid','Great Toe','Hum. Stem','Lesser Toe','PLATE','Plug','Prime Pin','Prm Screw','Prox Screw','Rad. Stem','RES. HEAD','Staple','SubTalar','Surg. Pack','Talar','Wedge')
END)
and
IV00101.USCATVLS_1 in ('Latitude','RHS')
 
Upvote 0 Downvote
THE ERROR IS ON THIS LINE (PROBABLY THE FIRST ","):

ELSE ('Elbow','Femur','Glenoid','Great Toe','Hum. Stem','Lesser Toe','PLATE','Plug','Prime Pin','Prm Screw','Prox Screw','Rad. Stem','RES
 
Upvote 0 Downvote
Ouch. A little formatting would work wonders here.

Meanwhile, you've got double quotes in your case statement.

< M!ke >
[small]Don't believe everything you think.[/small]
 
Upvote 0 Downvote
still no go.. getting same error even when I replace the double quotes with singles
 
Upvote 0 Downvote
You can use the [&#91;]code] and [&#91;]/code] tags to start and stop a code block:

Code: 
SELECT 
   SOP10200.SOPTYPE
   , SOP10100.VOIDSTTS
   , SOP10100.SOPNUMBE
   , IV00101.ITEMNMBR
   , RM00101.CUSTCLAS
   , SOP10100.SALSTERR
   , SOP10106.USRDAT01
   , SOP10100.CUSTNMBR
   , IV00101.USCATVLS_1
   , IV00101.USCATVLS_2
   , IV00101.ITMCLSCD
   , SOP10200.QUANTITY
   , SOP10200.QTYTOINV
   , IV00101.ITMGEDSC
FROM   
   (((TOR.dbo.SOP10200 SOP10200 with(nolock) 
   RIGHT OUTER JOIN TOR.dbo.IV00101 IV00101 with(nolock) 
      ON SOP10200.ITEMNMBR=IV00101.ITEMNMBR) 
   INNER JOIN TOR.dbo.SOP10100 SOP10100 with(nolock) 
      ON (SOP10200.SOPTYPE=SOP10100.SOPTYPE) 
      AND (SOP10200.SOPNUMBE=SOP10100.SOPNUMBE)) 
   INNER JOIN TOR.dbo.SOP10106 SOP10106 with(nolock) 
      ON (SOP10100.SOPTYPE=SOP10106.SOPTYPE) 
      AND (SOP10100.SOPNUMBE=SOP10106.SOPNUMBE)) 
   INNER JOIN TOR.dbo.RM00101 RM00101 with(nolock) 
      ON SOP10100.CUSTNMBR=RM00101.CUSTNMBR
WHERE  
   SOP10100.VOIDSTTS=0 
   and NOT (SOP10100.SOPNUMBE LIKE 'INVREP%' 
      OR SOP10100.SOPNUMBE LIKE 'REP%') 
   AND NOT (SOP10200.ITEMNMBR='FEDEX CHG' 
      OR SOP10200.ITEMNMBR='FREIGHT 0' 
      OR SOP10200.ITEMNMBR='FREIGHT 100' 
      OR SOP10200.ITEMNMBR='FREIGHT 50' 
      OR SOP10200.ITEMNMBR='INST FEE 0' 
      OR SOP10200.ITEMNMBR='INST FEE 100' 
      OR SOP10200.ITEMNMBR='INST FEE 50') 
   AND RM00101.CUSTCLAS<>'DIST' 
   AND (RM00101.SALSTERR='CANADA' 
      OR RM00101.SALSTERR='MTN PLAINS' 
      OR RM00101.SALSTERR='NORTH CENTRAL' 
      OR RM00101.SALSTERR='NORTH EAST' 
      OR RM00101.SALSTERR='NORTHEAST' 
      OR RM00101.SALSTERR='SOUTH CENTRAL' 
      OR RM00101.SALSTERR='SOUTHEAST' 
      OR RM00101.SALSTERR='WEST') 
   AND (SOP10200.SOPTYPE=2 
      OR SOP10200.SOPTYPE=3 
      OR SOP10200.SOPTYPE=4) 
   AND IV00101.ITMCLSCD<>'INSTRU' 
   AND 
      (USCATVLS_2 IN (CASE WHEN USCATVLS_1 = "Latitude" 
          THEN "Hum. Stem"
              WHEN USCATVLS_1 = "RHS" THEN "Rad. Stem"
         ELSE ('Elbow','Femur','Glenoid','Great Toe','Hum. Stem','Lesser Toe','PLATE','Plug','Prime Pin','Prm Screw','Prox Screw','Rad. Stem','RES. HEAD','Staple','SubTalar','Surg. Pack','Talar','Wedge')
      END)
   and IV00101.USCATVLS_1 in ('Latitude','RHS')

< M!ke >
[small]Don't believe everything you think.[/small]
 
Upvote 0 Downvote
What error are you getting?

< M!ke >
[small]Don't believe everything you think.[/small]
 
Upvote 0 Downvote
LNBruno,

Msg 102, Level 15, State 1, Line 29
Incorrect syntax near ','.


here is the line:

ELSE ('Elbow','Femur','Glenoid','Great Toe','Hum. Stem','Lesser Toe','PLATE','Plug','Prime Pin','Prm Screw','Prox Screw','Rad. Stem','RES
 
Upvote 0 Downvote
I'd suggest running this in parts. Execute just the Select with the joins first without the where clause. If that works, then include the first Where criteria and so on until you can be a bit more specific as to where the error occurs.



< M!ke >
[small]Don't believe everything you think.[/small]
 
Upvote 0 Downvote
is your caps lock key broken?

that's one of the ugliest queries i've seen in a long time

the parenthisizing of the joins is the main culprit

you are negating whatever you hoped to achieve with the RIGHT OUTER JOIN by putting a predicate on the IV00101 table into the WHERE clause...

IV00101.USCATVLS_1 in ('Latitude','RHS')

you might as well use a plain INNER JOIN instead

plus, you will notice that USCATVLS_1 will have only one of two values

therefore, the big -- and incorrect -- ELSE list in the CASE is not needed

:)


r937.com | rudy.ca
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Locked
  • Question
Case sensitive request 1
Replies
3
Views
68
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
Create Function Within IF statement. 1
Replies
4
Views
190
mikrom
mikrom
f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
88
f0rg3tfu1
f0rg3tfu1
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
226
Chris Miller
Chris Miller
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
264
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top