[lianite]

Wrote this at my last job. Surprisingly, this is one of the simpler queries I wrote. Nevertheless, the database itself is a nightmare to work with.

select
   ORDER_NO,
   SKU_NO,
   LINE_NBR,
   case
      when substr(WOD, 1, instr(WOD, '\"', 1, 1) - 1 ) like 'FOLLOW WOD ON LINE%' then substr(WOD, 20, instr(WOD, '\"', 1, 1) - 20 )
      else ''
   end as WOD,
   STATUS_CODE,
   case
      when substr(NEEDBY, 1, 1) = '\"' then ''
      else NEEDBY
   end as NEEDBY,
   QTY,
   CUST_NAME,
   case
      when CUST_TYPE = 'TEXT' then 'TEXT'
      when CUST_TYPE = 'LSR' then 'LSR'
      when CUST_TYPE = 'NO CHARGE' then 'EMB'
      when CUST_TYPE = 'NO CUSTOM' then ''
      else substr(CUST_TYPE, 1, instr(CUST_TYPE, '\"', 1, 1) - 1)
   end as CUST_TYPE,
   BATCH_ID,
   case
      when CUST_TYPE in ('TEXT', 'NO CUSTOM') then '99999999'
      else substr(LOGO_ID_1, 1, decode(instr(LOGO_ID_1, '\"', 1, 1), 0, 8, instr(LOGO_ID_1, '\"', 1, 1) - 1))
   end as LOGO_ID_1,
   substr(LOGO_ID_2, 1, decode(instr(LOGO_ID_2, '\"', 1, 1), 0, 8, instr(LOGO_ID_2, '\"', 1, 1) - 1)) as LOGO_ID_2,
   substr(LOGO_ID_3, 1, decode(instr(LOGO_ID_3, '\"', 1, 1), 0, 8, instr(LOGO_ID_3, '\"', 1, 1) - 1)) as LOGO_ID_3,
   substr(LOGO_ID_4, 1, decode(instr(LOGO_ID_4, '\"', 1, 1), 0, 8, instr(LOGO_ID_4, '\"', 1, 1) - 1)) as LOGO_ID_4,
   substr(LOGO_ID_5, 1, decode(instr(LOGO_ID_5, '\"', 1, 1), 0, 8, instr(LOGO_ID_5, '\"', 1, 1) - 1)) as LOGO_ID_5,
   substr(LOGO_ID_6, 1, decode(instr(LOGO_ID_6, '\"', 1, 1), 0, 8, instr(LOGO_ID_6, '\"', 1, 1) - 1)) as LOGO_ID_6,
   substr(LOGO_ID_7, 1, decode(instr(LOGO_ID_7, '\"', 1, 1), 0, 8, instr(LOGO_ID_7, '\"', 1, 1) - 1)) as LOGO_ID_7,
   substr(LOGO_ID_8, 1, decode(instr(LOGO_ID_8, '\"', 1, 1), 0, 8, instr(LOGO_ID_8, '\"', 1, 1) - 1)) as LOGO_ID_8,
   substr(LOGO_ID_9, 1, decode(instr(LOGO_ID_9, '\"', 1, 1), 0, 8, instr(LOGO_ID_9, '\"', 1, 1) - 1)) as LOGO_ID_9,
   substr(LOGO_ID_10, 1, decode(instr(LOGO_ID_10, '\"', 1, 1), 0, 8, instr(LOGO_ID_10, '\"', 1, 1) - 1)) as LOGO_ID_10,
   TXT,
   case
      when upper(substr(WOD, 1, 4)) = 'HOLD' then 1
      else 0
   end as HLD
from
   (
      select
         x.CAPTURE_ID as ORDER_NO,
         b.PRODUCT_ID as SKU_NO,
         b.LINE_NBR,
         dbms_lob.substr(x.RAWXML, 50,( dbms_lob.instr(x.RAWXML, '\"fxInstructions\"', 1, 1)) + 33 ) as WOD,
         b.STATUS_CODE,
         dbms_lob.substr(x.RAWXML, 5,( dbms_lob.instr(x.RAWXML, '\"fxDeadlineDateStr\"', 1, 1)) + 36 ) as NEEDBY,
         b.QTY_ORDERED as QTY,
         d.BO_NAME as CUST_NAME,
         case
            when dbms_lob.substr(x.RAWXML, 4,( dbms_lob.instr(x.RAWXML, '\"fxProductCharge\"', 1, 1)) + 45 ) = 'TEXT' then 'TEXT'
            when dbms_lob.substr(x.RAWXML, 3,( dbms_lob.instr(x.RAWXML, '\"fxProductCharge\"', 1, 1)) + 45 ) = 'LSR' then 'LSR'
            when dbms_lob.substr(x.RAWXML, 9,( dbms_lob.instr(x.RAWXML, '\"fxProductCharge\"', 1, 1)) + 45 ) = 'NO CHARGE' then 'NO CHARGE'
            when dbms_lob.substr(x.RAWXML, 9,( dbms_lob.instr(x.RAWXML, '\"fxProductCharge\"', 1, 1)) + 45 ) = 'NO CUSTOM' then 'NO CUSTOM'
            else dbms_lob.substr(x.RAWXML,15,( dbms_lob.instr(x.RAWXML,'\"fxCustomizationType\"',1,1)) + 38 )
         end as CUST_TYPE,
         dbms_lob.substr(x.RAWXML,3, ( dbms_lob.instr(x.RAWXML, '\"spProductCharge\"',1,1)) - 8 ) as BATCH_ID,
         case
            when dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxLogoID1\"',1,1)) + 28 ) between '0' and '99999999' then dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxLogoID1\"',1,1)) + 28 )
            else ''
         end as LOGO_ID_1,
         case
            when dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxLogoID2\"',1,1)) + 28 ) between '0' and '99999999' then dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxLogoID2\"',1,1)) + 28 )
            else ''
         end as LOGO_ID_2,
         case
            when dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxLogoID3\"',1,1)) + 28 ) between '0' and '99999999' then dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxLogoID3\"',1,1)) + 28 )
            else ''
         end as LOGO_ID_3,
         case
            when dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxLogoID4\"',1,1)) + 28 ) between '0' and '99999999' then dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxLogoID4\"',1,1)) + 28 )
            else ''
         end as LOGO_ID_4,
         case
            when dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxLogoID5\"',1,1)) + 28 ) between '0' and '99999999' then dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxLogoID5\"',1,1)) + 28 )
            else ''
         end as LOGO_ID_5,
         case
            when dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxLogoID6\"',1,1)) + 28 ) between '0' and '99999999' then dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxLogoID6\"',1,1)) + 28 )
            else ''
         end as LOGO_ID_6,
         case
            when dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxLogoID7\"',1,1)) + 28 ) between '0' and '99999999' then dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxLogoID7\"',1,1)) + 28 )
            else ''
         end as LOGO_ID_7,
         case
            when dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxLogoID8\"',1,1)) + 28 ) between '0' and '99999999' then dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxLogoID8\"',1,1)) + 28 )
            else ''
         end as LOGO_ID_8,
         case
            when dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxLogoID9\"',1,1)) + 28 ) between '0' and '99999999' then dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxLogoID9\"',1,1)) + 28 )
            else ''
         end as LOGO_ID_9,
         case
            when dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxLogoID10\"',1,1)) + 28 ) between '0' and '99999999' then dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxLogoID10\"',1,1)) + 28 )
            else ''
         end as LOGO_ID_10,
         case
            when dbms_lob.substr(x.RAWXML,4,( dbms_lob.instr(x.RAWXML,'\"fxNameFontColor1\"',1,1)) + 36 ) > '0' then 1
            when dbms_lob.substr(x.RAWXML,1,( dbms_lob.instr(x.RAWXML,'\"fxTextLocation1\"',1,1)) + 34 ) <> '\"' then 1
            else 0
         end as TXT
      from
         PS_RO_XML x
         inner join PS_RO_LINE b
         on
            x.CAPTURE_ID = b.CAPTURE_ID and
            x.UID20F = b.UID20
         inner join PS_RO_HEADER a
         on
            a.CAPTURE_ID = x.CAPTURE_ID and
            a.CAPTURE_ID = b.CAPTURE_ID
         inner join PS_RO_BOID c
         on
            c.CAPTURE_ID = x.CAPTURE_ID and
            c.CAPTURE_ID = b.CAPTURE_ID and
            c.CAPTURE_ID = a.CAPTURE_ID and
            c.UID20 = a.UID20F_BILL
         inner join (
            select
               BO_ID,
               BO_NAME
            from
               PS_BO_NAME g
            where
               SYNCDTTM = (
                  select
                     max(SYNCDTTM) as SYNCDTTM
                  from
                     PS_BO_NAME f
                  where
                     f.BO_ID = g.BO_ID
               )
         ) d
         on
            d.BO_ID = c.BO_ID
      where
         CAPTURE_TYPE_CD = 'SO'
         and STATUS_CODE_LINE not in ( '1' )
         and b.STATUS_CODE not in ( '2000', '5300' )
         and a.CAPTURE_ID in (" . $order . ")
         and dbms_lob.getlength(x.RAWXML) <> 0
         and case
                  when dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxSKU\"',1,1)) + 24 ) = b.PRODUCT_ID then 1
                  when dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxSKU\"',1,2)) + 24 ) = b.PRODUCT_ID then 1
                  when dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxSKU\"',1,3)) + 24 ) = b.PRODUCT_ID then 1
                  when dbms_lob.substr(x.RAWXML,8,( dbms_lob.instr(x.RAWXML,'\"fxSKU\"',1,4)) + 24 ) = b.PRODUCT_ID then 1
                  else 0
               end = 1
   )