1 Reply Latest reply: Jan 31, 2012 5:03 PM by Miguel Angel Baeyens de Arce RSS

    Do Loop

    frank volpe

      Trying to code a do loop in my script, but getting a script error.  If the syntax is correct the table label (SalesDetails) will be in italics and the Load statement will be blue.  In this example that is not the case.  If I comment out the do statement, then syntax is correct.  Can you help?  Thanks.

       

      Should be

      Do when sub < 13

      SalesDetails:

      Load

      'N/A' as Week,

       

       

       

      LET sub = 1;

       

       

      Do when sub < 13
      SalesDetails:
      Load
      // Change At Beginning of Year Start
      'N/A' as Week,
      'N/A' as Day,
      'N/A' as WeekDay,
      'N/A' as [Week Ending Date],
      If ($(sub) = 1, date('01/31/' & '$(TwoYearsAgo)','MM/DD/YY'),
      If ($(sub) = 2, date('02/28/' & '$(TwoYearsAgo)','MM/DD/YY'), 
      If ($(sub) = 3, date('03/31/' & '$(TwoYearsAgo)','MM/DD/YY'),
      If ($(sub) = 4, date('04/30/' & '$(TwoYearsAgo)','MM/DD/YY'), 
      If ($(sub) = 5, date('05/31/' & '$(TwoYearsAgo)','MM/DD/YY'),
      If ($(sub) = 6, date('06/30/' & '$(TwoYearsAgo)','MM/DD/YY'), 
      If ($(sub) = 7, date('07/31/' & '$(TwoYearsAgo)','MM/DD/YY'),
      If ($(sub) = 8, date('08/30/' & '$(TwoYearsAgo)','MM/DD/YY'),
      If ($(sub) = 9, date('09/30/' & '$(TwoYearsAgo)','MM/DD/YY'), 
      If ($(sub) = 10, date('10/31/' & '$(TwoYearsAgo)','MM/DD/YY'),
      If ($(sub) = 11, date('11/30/' & '$(TwoYearsAgo)','MM/DD/YY'), 
      If ($(sub) = 12, date('12/31/' & '$(TwoYearsAgo)','MM/DD/YY')))))))))))))  as [Invoice Date],
      '$(TwoYearsAgo)' as Year,
      If ($(sub) = 1, 'Jan',
      If ($(sub) = 2, 'Feb',
      If ($(sub) = 3, 'Mar',
      If ($(sub) = 4, 'Apr',
      If ($(sub) = 5, 'May',
      If ($(sub) = 6, 'Jun',
      If ($(sub) = 7, 'Jul',
      If ($(sub) = 8, 'Aug',
      If ($(sub) = 9, 'Sep',
      If ($(sub) = 10, 'Oct',
      If ($(sub) = 11, 'Nov',
      If ($(sub) = 12, 'Dec', )))))))))))) as Month,
      //  Change At Begging of Year End
      'Rolling' as TYPE,
        null() as [Line Number],
      'N/A' as YearKey,
      'N/A' as YearKey2,
      'N/A' as [Customer&Year],
      'N/A' as [Line Type],
      'N/A' as [Order Number],
      'N/A' as [Invoice Number],
      'N/A' as [Doc Type],
      'N/A' as SDPA8,
      'N/A' as SDMCU,
      SDITM,
      'N/A' as [Business Unit Detail],
      text(SDAN8) as [Bill To Address],
      'N/A' as [Ship To Address],
      SDDGL as [Accounting Date],
      'N/A' as [Ship Date],
          'N/A' as [Promise Date],
      // end of loading Dates
      SDITM as [Short Name], // Link To Item Master
      // SDLITM as [Item Number],
      // SDDSC1 as [Decsription 1],
          'N/A' as [Description 2],
      'N/A' as [Order Type],
      'N/A' as [U/M],
      0 as [Open Order QTY ordered LB],
      0 as [Open Order QTY Shipped LB],
      0 as [Open Order QTY BackOrdered LB],
      0 as [Open Order QTY ordered],
      0 as [Open Order QTY Shipped],
      0 as [Open Order QTY BackOrdered],
      0 as [QTY ordered LB],
      0 as [QTY Shipped LB],
      0 as [QTY ordered],
      0 as [QTY Shipped],
      'N/A' as [Unit Weight],
      'N/A' as [Weight U/M],
      0 as [Sales Price],
      0 as [SALES],
      0 as [Sales Margin],
      0 as [FREIGHT],
      0 as [Open Order Amount],
      0 as [Open Order Amount BackOrdered],
      0 as [Open Order Margin],
      0 as [Open Order FREIGHT],
      0 as [Cost Price],
      0 as [Extended Cost Amount],
      0 as [Open Order Extended Cost Amount],
      'N/A' as [Last Status],
      'N/A' as SDDCTO,
      'N/A' as SDRCD,
      //num(SDSLSM) as [Sales Rep],
      'N/A' as [Sales Rep],
      SDDCTO&'_'&SDDOCO&'_'&SDLNID as SalesKey,
      0 as [Replacement Cost],
      0 as [Replacement Margin],
      0 as [Open Order Replacement Cost],
      0 as [Open Order Replacement Margin],
      0 as [Forecast Qty],
      0 as [Original Forecast],
      0 as [Sales Budget],
      0 as [Margin Budget],
      0 as [Margin $ Budget],
      0 as [QTY Budget],
      'N/A' as [Freight Handling],
      'N/A' as Carrier,
      if(SDUOM = 'KG',(SDSOQS/10000)*2.2046,if(SDUOM = 'LB',SDUORG/10000,0)) as [R QTY Shipped LB],
      if(SDLNTY <> 'F',SDAEXP/100,0) as [R SALES],
      if(SDLNTY <> 'F',(SDAEXP/100) - (SDECST/100) ,0) as [R Sales Margin]
      ;

      SQL SELECT
      SDFY,SDLNTY,SDITM,SDDOCO,SDDOC,SDDCT,SDMCU ,SDFY,SDAN8 ,SDSHAN,SDIVD,SDDGL ,SDLITM,SDDSC1,SDDSC2,SDUOM ,SDSOBK,SDADDJ,
      SDUORG,SDSOQS ,SDITWT,SDWTUM,SDUPRC,SDRSDJ,SDLTTR,SDDCTO,SDRCD,SDAEXP,SDLNID,SDUNCS,SDECST,SDDCTO,SDTRDJ,SDPA8,SDURAT, SDFRTH, SDCARS
      FROM "JDE_PRODUCTION".PRODDTA.F42119
      where SDIVD > 108031 and SDIVD < 109032
      and SDLNTY IN ('A','C','D','E','F','G','H','S','M','N','Q','K','R','Y','YR','Z','FP','SC')
      and SDDCTO IN ('SO','SV','CO','CV','CM','SZ','SD','S1','SR')
      and SDLTTR > '579'
      and SDNXTR > '619'
      and SDSHAN > 500
      ;
      let sub = $(sub) + 1;

      loop