Skip to Content

Optional prompt and case when

Hi Experts,

How to select the 0MOVETYPE also from below query

SELECT "0PLANT", "0MATERIAL",

       (case when sum(case when "0MOVETYPE" = 602 then 1 else 0 end) > 0

             then max(case when "0MOVETYPE" = 602 then "0PSTNG_DATE" end)

             when sum(case when "0MOVETYPE" = 601 then 1 else 0 end) > 0

             then max(case when "0MOVETYPE" = 601 then "0PSTNG_DATE" end)

             when sum(case when "0MOVETYPE" = 102 then 1 else 0 end) > 0

             then max(case when "0MOVETYPE" = 102 then "0PSTNG_DATE" end)

             when sum(case when "0MOVETYPE" = 101 then 1 else 0 end) > 0

             then max(case when "0MOVETYPE" = 101 then "0PSTNG_DATE" end)

        end) as Last_Sales_Date

FROM "NM1"."PUBLIC"."IZDSO_DMR"  Table__1

Where Table__1."0CALMONTH"  <=@Prompt('Enter YYYYMM','N',,Mono,Constrained,Not_Persistent,,User:-1,(CAST((Concat(CAST((YEAR(Table__1."0PSTNG_DATE")) AS VARCHAR) ,CAST((MONTH(Table__1."0PSTNG_DATE")) AS VARCHAR))) As INTEGER)),Optional)

Group By "0MATERIAL", "0PLANT"

optional promt also not giving any value if i do not give any input.

Please help on this.

Thanks,

G Sampath Kumar

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jan 29, 2014 at 09:32 AM

    HI

    If you have access to DB, please check the data is available for the 0MOVETYPE values (601,602,101,102) with 0CALMONTH less than 0PSTNG_DATE

    0MOVETYPE have different values (601,602,101,102) maintained one of  the below table

    "NM1".

    "PUBLIC".

    "IZDSO_DMR"

    Check whether DB have the  0MOVETYPE values (601,602,101,102) or not if DB have the values, then check

    0CALMONTH less than 0PSTNG_DATE have the check 0MOVETYPE values (601,602,101,102) have or not

    Add comment
    10|10000 characters needed characters exceeded