By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,380 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

Case statements in MS SQL

P: 2
Dear Folks,

I have an query which something looks like this

select ILTRDJ AS YEAR, DATEPART(MONTH,ILTRDJ) AS MONTH,
FMSTR.IMITM AS CODE,FMSTR.IMSRP6_UDC AS DESCRIPTION, FTRANS.ILDCT [Recieved] =
CASE
WHEN (select FTRANS.ILDCT FROM [TP].[dbo].[F4111Selected] FTRANS
WHERE FMSTR.IMITM = FTRANS.ILITM and FTRANS.ILDCT = 'OV')
THEN 'OV'
ELSE 'OTHERS'
END,

[Issued Job] =
CASE
WHEN (select FTRANS.ILDCT FROM [TP].[dbo].[F4111Selected] FTRANS WHERE FMSTR.IMITM = FTRANS.ILITM and FTRANS.ILDCT = 'IJ')
THEN 'IJ',
ELSE 'OTHERS'
END,
[Inventory Adjustment] =
CASE
WHEN (select FTRANS.ILDCT FROM [TP].[dbo].[F4111Selected] FTRANS
WHERE FMSTR.IMITM = FTRANS.ILITM and FTRANS.ILDCT = 'IA')
THEN 'IA'
ELSE 'OTHERS'
END ,ABS(ILTRQT) AS RECV_QTY,ILUNCS AS UNIT_PRICE
FROM [TP].[dbo].[F4111Selected] FTRANS INNER JOIN dbo.F4101Sel FMSTR
ON FMSTR.IMITM = FTRANS.ILITM
GROUP BY FMSTR.IMITM,FMSTR.IMSRP6_UDC,FTRANS.ILDCT,ILTRDJ,I LTRQT,ILUNCS
ORDER BY ILTRDJ,DATEPART(MONTH,ILTRDJ),FMSTR.IMITM


In the dataset we have an column named document type which has three entries like Issues, Recipts and adjustement.
and we also have another columns like QTY, and Unitprice for these three entries in the same table. My requirment is some thing like I need to segregate these three entries as seperate columns like Recipts, Issues and Adjustment in my target table with there corresponding entries (qty and unitprice)!!!

I have used an case statement in the above mentioned in query whiile at the time of query execution it is giving me something like

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '='.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'THEN'.
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'THEN'.
Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'THEN'.

Pls do help me out to resolve this query as this is an urgent requirement!!!

Kindly feel free to revert back for clarrifications!!!

Thanks and Regards
Arun Menon D
Mar 23 '08 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
Dear Folks,

I have an query which something looks like this

select ILTRDJ AS YEAR, DATEPART(MONTH,ILTRDJ) AS MONTH,
FMSTR.IMITM AS CODE,FMSTR.IMSRP6_UDC AS DESCRIPTION, FTRANS.ILDCT [Recieved] =
CASE
WHEN (select FTRANS.ILDCT FROM [TP].[dbo].[F4111Selected] FTRANS
WHERE FMSTR.IMITM = FTRANS.ILITM and FTRANS.ILDCT = 'OV')
THEN 'OV'
ELSE 'OTHERS'
END,

[Issued Job] =
CASE
WHEN (select FTRANS.ILDCT FROM [TP].[dbo].[F4111Selected] FTRANS WHERE FMSTR.IMITM = FTRANS.ILITM and FTRANS.ILDCT = 'IJ')
THEN 'IJ',
ELSE 'OTHERS'
END,
[Inventory Adjustment] =
CASE
WHEN (select FTRANS.ILDCT FROM [TP].[dbo].[F4111Selected] FTRANS
WHERE FMSTR.IMITM = FTRANS.ILITM and FTRANS.ILDCT = 'IA')
THEN 'IA'
ELSE 'OTHERS'
END ,ABS(ILTRQT) AS RECV_QTY,ILUNCS AS UNIT_PRICE
FROM [TP].[dbo].[F4111Selected] FTRANS INNER JOIN dbo.F4101Sel FMSTR
ON FMSTR.IMITM = FTRANS.ILITM
GROUP BY FMSTR.IMITM,FMSTR.IMSRP6_UDC,FTRANS.ILDCT,ILTRDJ,I LTRQT,ILUNCS
ORDER BY ILTRDJ,DATEPART(MONTH,ILTRDJ),FMSTR.IMITM


In the dataset we have an column named document type which has three entries like Issues, Recipts and adjustement.
and we also have another columns like QTY, and Unitprice for these three entries in the same table. My requirment is some thing like I need to segregate these three entries as seperate columns like Recipts, Issues and Adjustment in my target table with there corresponding entries (qty and unitprice)!!!

I have used an case statement in the above mentioned in query whiile at the time of query execution it is giving me something like

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '='.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'THEN'.
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'THEN'.
Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'THEN'.

Pls do help me out to resolve this query as this is an urgent requirement!!!

Kindly feel free to revert back for clarrifications!!!

Thanks and Regards
Arun Menon D

I don't think you still need subquery.

Try following this pattern:

Expand|Select|Wrap|Line Numbers
  1. [Recieved] = 
  2. CASE
  3. WHEN FTRANS.ILDCT = 'OV'
  4. THEN 'OV'
  5. ELSE 'OTHERS'
  6. END,
  7.  
  8. [Issued Job] =  
  9. CASE
  10. WHEN FTRANS.ILDCT = 'IJ'
  11. THEN 'IJ',
  12. ELSE 'OTHERS'
  13. END,
  14. [Inventory Adjustment] = 
  15. CASE 
  16. WHEN FTRANS.ILDCT = 'IA'
  17. THEN 'IA'
  18. ELSE 'OTHERS'
This portion "FMSTR.IMITM = FTRANS.ILITM" has been taken care of by your JOIN.

-- CK
Mar 24 '08 #2

P: 2
Hi,

Thanks for your reply and it worked well!!!!

Regards
Arun Menon D


I don't think you still need subquery.

Try following this pattern:

Expand|Select|Wrap|Line Numbers
  1. [Recieved] = 
  2. CASE
  3. WHEN FTRANS.ILDCT = 'OV'
  4. THEN 'OV'
  5. ELSE 'OTHERS'
  6. END,
  7.  
  8. [Issued Job] =  
  9. CASE
  10. WHEN FTRANS.ILDCT = 'IJ'
  11. THEN 'IJ',
  12. ELSE 'OTHERS'
  13. END,
  14. [Inventory Adjustment] = 
  15. CASE 
  16. WHEN FTRANS.ILDCT = 'IA'
  17. THEN 'IA'
  18. ELSE 'OTHERS'
This portion "FMSTR.IMITM = FTRANS.ILITM" has been taken care of by your JOIN.

-- CK
Mar 24 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.