SQL Disaster

In keeping with the theme of my blog, I present to you one example of a horrendous SQL query my co-worker sent me. I don't know what kind of super villain created this, but I'd hate to meet him or her in a dark alley. We're hoping it's some sort of program-generated query, like what Microstrategy can produce.

This one has it all. - advanced analytical functions, aggregates, a hint override, order bys, group bys, aliases, nested sub-selects, outer joins, in clauses, between clauses, even a UNION!

Think you've seen worse? Send it to me!

SELECT /*+ ORDERED */
inv.inv_bol, inv.cost_qualifier, NVL(inv.cost_code,' '), inv.invoice_id
,inv.inv_nbr, rcv.dttm, inv.status_code, inv.inv_date, vouch.dttm AS VOUCHER_DTTM
,inv.inv_distance, inv.inv_weight, inv.inv_cube, vouch.ap_pay_date, inv.amount
FROM (SELECT i.inv_bol, c.cost_qualifier, c.cost_code, i.invoice_id
,MAX(i.inv_date) AS INV_DATE
,MAX(i.inv_nbr) AS INV_NBR
,MAX(i.status_code) AS STATUS_CODE
,MAX(i.inv_distance) AS INV_DISTANCE
,MAX(i.inv_weight) AS INV_WEIGHT
,MAX(i.inv_cube) AS INV_CUBE
,SUM(amount) AS AMOUNT
FROM (SELECT inv_bol
,LAST_VALUE(invoice_id) OVER(PARTITION BY inv_bol ORDER BY status_code DESC) INVOICE_ID
,LAST_VALUE(inv_date) OVER(PARTITION BY inv_bol ORDER BY status_code DESC) INV_DATE
,LAST_VALUE(inv_nbr) OVER(PARTITION BY inv_bol ORDER BY status_code DESC) INV_NBR
,LAST_VALUE(status_code) OVER(PARTITION BY inv_bol ORDER BY status_code DESC) STATUS_CODE
,LAST_VALUE(inv_distance) OVER(PARTITION BY inv_bol ORDER BY status_code DESC) INV_DISTANCE
,LAST_VALUE(inv_weight) OVER(PARTITION BY inv_bol ORDER BY status_code DESC) INV_WEIGHT
,LAST_VALUE(inv_cube) OVER(PARTITION BY inv_bol ORDER BY status_code DESC) INV_CUBE
,ROW_NUMBER() OVER(PARTITION BY inv_bol ORDER BY status_code DESC) SEQ
FROM
tm_fp_invoice
,(SELECT DISTINCT PRIMARY_KEY1 BOL
FROM tm_FP_AUDIT_TRAIL
WHERE TABLE_NAME = 'FP_MASTER'
AND DTTM between (select extract_from_tstmp from PM_BI_TMS_DW_CTL) and (select extract_to_tstmp from PM_BI_TMS_DW_CTL)
UNION
SELECT DISTINCT I.INV_BOL BOL
FROM tm_FP_AUDIT_TRAIL A, tm_FP_INVOICE I
WHERE TABLE_NAME = 'FP_INVOICE'
AND A.DTTM between (select extract_from_tstmp from PM_BI_TMS_DW_CTL) and (select extract_to_tstmp from PM_BI_TMS_DW_CTL)
AND A.PRIMARY_KEY1 = I.INVOICE_ID) B
WHERE tm_fp_invoice.inv_bol = b.bol) I
,tm_fp_invoice_cost C
WHERE i.seq = 1
AND i.invoice_id = c.invoice_id
AND c.cost_qualifier <= 6
GROUP BY i.inv_bol,c.cost_qualifier,c.cost_code,i.invoice_id) INV
,(SELECT
i.inv_bol
,a.dttm
,p.ap_pay_date
FROM
tm_fp_audit_trail A
,tm_fp_invoice I
,tm_fp_payment P
WHERE a.table_name = 'FP_INVOICE'
AND a.value_after = '70'
AND a.value_before != '70'
AND a.primary_key1 = TO_CHAR(i.invoice_id)
AND i.status_code IN (70,80)
AND i.invoice_id = p.invoice_id (+)) VOUCH
,(SELECT primary_key1, MAX(dttm) DTTM
FROM tm_fp_audit_trail
WHERE action_code = 'A'
AND table_name = 'FP_INVOICE'
GROUP BY primary_key1) RCV
WHERE inv.inv_bol = vouch.inv_bol (+)
AND inv.invoice_id = rcv.primary_key1 (+)