Case statements in MS SQL

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • arunmenon
    New Member
    • Mar 2008
    • 2
    #1

    Case statements in MS SQL

    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.IMSR P6_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,FMS TR.IMSRP6_UDC,F TRANS.ILDCT,ILT RDJ,ILTRQT,ILUN CS
    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
    Tags: None
    • ck9663
      Recognized Expert Specialist
      • Jun 2007
      • 2878
      #2
      Originally posted by arunmenon
      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.IMSR P6_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,FMS TR.IMSRP6_UDC,F TRANS.ILDCT,ILT RDJ,ILTRQT,ILUN CS
      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:

      Code:
      [Recieved] = 
CASE
WHEN FTRANS.ILDCT = 'OV'
THEN 'OV'
ELSE 'OTHERS'
END,

[Issued Job] =  
CASE
WHEN FTRANS.ILDCT = 'IJ'
THEN 'IJ',
ELSE 'OTHERS'
END,
[Inventory Adjustment] = 
CASE 
WHEN FTRANS.ILDCT = 'IA'
THEN 'IA'
ELSE 'OTHERS'
      This portion "FMSTR.IMIT M = FTRANS.ILITM" has been taken care of by your JOIN.

      -- CK

        Comment

        • arunmenon
          New Member
          • Mar 2008
          • 2
          #3
          Hi,

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

          Regards
          Arun Menon D


          Originally posted by ck9663
          I don't think you still need subquery.

          Try following this pattern:

          Code:
          [Recieved] = 
CASE
WHEN FTRANS.ILDCT = 'OV'
THEN 'OV'
ELSE 'OTHERS'
END,

[Issued Job] =  
CASE
WHEN FTRANS.ILDCT = 'IJ'
THEN 'IJ',
ELSE 'OTHERS'
END,
[Inventory Adjustment] = 
CASE 
WHEN FTRANS.ILDCT = 'IA'
THEN 'IA'
ELSE 'OTHERS'
          This portion "FMSTR.IMIT M = FTRANS.ILITM" has been taken care of by your JOIN.

          -- CK

            Comment

            Previous template Next
            Working...