midrange.com code scratchpad
Name:
Craig Richards
Scriptlanguage:
Plain Text
Tabwidth:
4
Date:
03/12/2019 08:55:46 am
IP:
Logged
Description:
SQL which doesn't cause internal error
Code:
  1.       Exec Sql
  2.          Insert into GLOUTQ00 ( RqsText, CrtTime, CrtPgm, TPID )
  3.          With r as (
  4.             Select
  5.                t1.OhOrdr,
  6.                :KeyData.NumLines as NumLines,
  7.                Case When t1.OhComp = 'Y' then '1' else '0' end as ShipComplete,
  8.                t3.OaLine,  t3.OaProd,  t3.OaPnam,  t3.OaCnam,  t3.OaType,  t3.OaPric,  t3.OaFrgh,
  9.                t4.ImId# as ItemID,
  10.                char( date( timestamp_format( char( t3.oaprmd ), 'YYYYMMDD' ) ), ISO ) as DateReq,
  11.                'A' as DateReqType,
  12.                Case When t3.OaType = '8' then Dec( t3.OaAloc/100, 9, 2)
  13.                     when t3.OaType = '9' then Dec( t8.P2Sqmb, 9, 2)
  14.                     else Dec( (t3.OaAloc * t3.OaWide )/10000, 9, 2)
  15.                     end as SQM,
  16.                t9.PdFnwt as finWeight,
  17.                Dec( ( t3.OaAloc/100 ), 5, 2 ) as Length,
  18.                case when ( t3.OaType = '8' or t3.OaType = '9') then 0
  19.                        else Dec( ( t3.OaWide / 100 ), 5, 2 ) end as Width,
  20.                t1.OhPo# as CustRef,
  21.                t4.Imid# as StockCutID
  22.             From OrdrHdr t1
  23.             Join GlEvLine00 t2 on t2.eventID = :EventID
  24.             Join OrdrAllo t3 on t3.OaOrdr = t1.OhOrdr
  25.                             and t3.OaLine = t2.EventLine
  26.             Join InvMast t4 on t4.ImProd = t3.OaProd
  27.                            and t4.ImRoll = t3.OaRoll
  28.                            and t4.ImFcut = t3.OaFcut
  29.                            and t4.ImGcut = t3.OaGcut
  30.             Left Join MdSvch00 t7 on t7.ShOrdr = t1.OhOrdr
  31.             Join IvProd00 t8 on t8.P2Prod = t3.OaProd
  32.             Join PsProd00 t9 on t9.PdProd = t3.OaProd
  33.             Where t1.OhOrdr = :OrderID
  34.          )
  35.          Select JSON_Object(
  36.                'msgType' value :MsgType,
  37.                'orderNumber' value Strip(r.OhOrdr, T),
  38.                'numLines' value r.NumLines,
  39.                'lines' value
  40.                JSON_ArrayAgg(
  41.                   JSON_Object(
  42.                      'lineNumber' value r.OaLine,             'productCode' value Strip(r.OaProd, T),
  43.                      'productName' value Strip(r.OaPnam, T),  'colourName' value Strip(r.OaCnam, T),
  44.                      'itemID' value Strip(r.ItemID, T),       'dateRequired' value r.DateReq,
  45.                      'dateRequiredType' value r.DateReqType,  'weight' value Dec( r.SQM * r.FinWeight, 6, 2),
  46.                      'length' value r.Length,                 'width' value r.Width,
  47.                      'customerRef' value Strip(r.CustRef, T),
  48.                      'salesValue' value Dec( (r.SQM * r.OaPric) + r.OaFrgh, 9, 2 ),
  49.                      'productType' value r.OaType,            'stockCutID' value Strip(r.StockCutID, T)
  50.                      absent on null
  51.                   )
  52.                )
  53.             absent on null
  54.             ), Current_Timestamp, :PgmSts.Program, :ThirdPartyID
  55.          From r
  56.          Group by
  57.             r.OhOrdr,  r.NumLines,  r.ShipComplete
  58.          With NC;
  59.  
© 2004-2019 by midrange.com generated in 0.007s valid xhtml & css