Meursault wrote on Oct 8
th, 2013 at 11:03am:
Oh, veeeeery nice, a double deep nested subquery with correlation? That's some prime job security there!
So you won't tell, but will you answer if we guess? I'm thinking it's part of a database migration where you are looking for details on a foreign key value (and the items that reference it) that is new to the new database? I'll definitely go back and puzzle over that one after work.
I'm doing a migration now myself but sadly my predecessor didn't use foreign key constraints or normalization much, so the queries I'm analyzing are not as interesting as this

EDIT:
OK, now that's not funny, I think I just heard my brain shriek in pain
Here's another for you to ponder:
Quote: DECLARE KM160F CURSOR WITH HOLD
WITH ROWSET POSITIONING FOR
WITH
RULES
(
I_CLIE
,I_DOMN
,I_RULE
,I_COMPN_RULE
,I_LEVL_RULE
,I_TYPE_SUBJ_RULE
,I_SUBJ_RULE
,F_SUBJ_NEGT
,I_COND_RULE
,I_TYPE_PRED_RULE
,I_PRED_RULE
,F_PRED_NEGT
,F_REFR_COMPN
)
AS
(
SELECT
I_CLIE
,I_DOMN
,RULE.I_RULE
,I_COMPN_RULE
,I_LEVL_RULE
,I_TYPE_SUBJ_RULE
,I_SUBJ_RULE
,F_SUBJ_NEGT
,I_COND_RULE
,I_TYPE_PRED_RULE
,I_PRED_RULE
,F_PRED_NEGT
,F_REFR_COMPN
FROM (
SELECT
I_CLIE
,I_DOMN
,I_RULE
,I_COMPN_RULE
,I_LEVL_RULE
,I_TYPE_SUBJ_RULE
,I_SUBJ_RULE
,F_SUBJ_NEGT
,I_COND_RULE
,I_TYPE_PRED_RULE
,I_PRED_RULE
,F_PRED_NEGT
,F_REFR_COMPN
FROM VKM97160
WHERE I_CLIE = :KM160-I-CLIE
AND I_DOMN = :KM160-I-DOMN
AND I_LEVL_RULE = :KM160-I-LEVL-RULE
AND F_STATS_RECR = :WS390-A
AND :KM160-D-EFFC-STAR BETWEEN D_EFFC_STAR
AND D_EFFC_END
) AS RULE
JOIN
(
SELECT I_RULE
FROM SESSION.TKM97RF
WHERE F_EVAL = :WS390-Y
) AS FILTER
ON FILTER.I_RULE = RULE.I_RULE
)
,RULES_REF
(
I_CLIE
,I_DOMN
,I_RULE
,I_LEVL_RULE
,I_COMPN_RULE
,I_TYPE_SUBJ_RULE
,F_SUBJ_NEGT
,I_SUBJ_RULE
,I_COND_RULE
,I_TYPE_PRED_RULE
,F_PRED_NEGT
,I_PRED_RULE
,F_PRED_REF
)
AS
(
SELECT
RULE.I_CLIE
,RULE.I_DOMN
,RULE.I_RULE
,RULE.I_LEVL_RULE
,RULE.I_COMPN_RULE
,RULE.I_TYPE_SUBJ_RULE
,RULE.F_SUBJ_NEGT
,RULE.I_SUBJ_RULE
,RULE.I_COND_RULE
,RULE.I_TYPE_PRED_RULE
,RULE.F_PRED_NEGT
,RULE.I_PRED_RULE
,CASE WHEN RULE_PRED_COMPN.F_REFR_COMPN
= :WS390-A THEN :WS390-Y
WHEN RULE_PRED_PREM.I_TYPE_FIEL_REFR
> :WS390-20-SP THEN :WS390-Y
ELSE :WS390-N
END CASE
FROM
(
SELECT
I_CLIE
,I_DOMN
,I_RULE
,I_LEVL_RULE
,I_COMPN_RULE
,I_TYPE_SUBJ_RULE
,F_SUBJ_NEGT
,I_SUBJ_RULE
,I_COND_RULE
,I_TYPE_PRED_RULE
,F_PRED_NEGT
,I_PRED_RULE
FROM RULES
WHERE F_REFR_COMPN = :WS390-Y
) AS RULE
LEFT JOIN
(
SELECT
I_CLIE
,I_DOMN
,I_PREM
,I_TYPE_FIEL_REFR
FROM VKM97180
WHERE D_EFFC_STAR <= :KM160-D-EFFC-STAR
AND D_EFFC_END >= :KM160-D-EFFC-STAR
AND F_STATS_RECR = :WS390-A
AND F_STATS_PREM = :WS390-A
AND I_CLIE = :KM160-I-CLIE
AND I_DOMN = :KM160-I-DOMN
) RULE_PRED_PREM
ON RULE.I_TYPE_PRED_RULE = :WS390-P
AND RULE_PRED_PREM.I_PREM = RULE.I_PRED_RULE
LEFT JOIN
(
SELECT
I_CLIE
,I_DOMN
,RULE.I_RULE
,I_COMPN_RULE
,F_REFR_COMPN
FROM (
SELECT
I_CLIE
,I_DOMN
,I_RULE
,I_COMPN_RULE
,F_REFR_COMPN
FROM VKM97160
WHERE D_EFFC_STAR <= :KM160-D-EFFC-STAR
AND D_EFFC_END >= :KM160-D-EFFC-STAR
AND F_STATS_RECR = :WS390-A
AND I_CLIE = :KM160-I-CLIE
AND I_DOMN = :KM160-I-DOMN
) AS RULE
JOIN
(
SELECT I_RULE
FROM SESSION.TKM97RF
WHERE F_EVAL = :WS390-Y
) AS FILTER
ON FILTER.I_RULE = RULE.I_RULE
) RULE_PRED_COMPN
ON RULE.I_TYPE_PRED_RULE = :WS390-C
AND RULE_PRED_COMPN.I_RULE = RULE.I_RULE
AND RULE_PRED_COMPN.I_COMPN_RULE = RULE.I_PRED_RULE
)
,RESULTS
(
I_CLIE
,I_DOMN
,I_RULE
,I_TYPE_RESL
,I_COMPN_RESL
,I_LEVL_RESL
,I_KEY_RESL
,I_PHAS_RESL
,I_REFR
)
AS
(
SELECT
I_CLIE
,I_DOMN
,I_RULE
,I_TYPE_RESL
,I_COMPN_RESL
,I_LEVL_RESL
,I_KEY_RESL
,I_PHAS_RESL
,I_REFR
FROM VKM97510
WHERE (
I_CLIE = :KM510-I-CLIE
AND I_DOMN = :KM510-I-DOMN
AND I_TYPE_RESL IN (:WS390-C
,:WS390-P
)
AND I_KEY_DOMN = :KM510-I-KEY-DOMN
AND I_KEY_RAND = :KM510-I-KEY-RAND
AND F_STATS_RECR = :WS390-A
)
)
SELECT
'WR'
,SUBJ.I_CLIE
,SUBJ.I_DOMN
,SUBJ.I_RULE
,SUBJ.F_SUBJ_NEGT
,SUBJ.I_COMPN_RULE
,SUBJ.I_LEVL_RULE
,SUBJ.I_TYPE_SUBJ_RULE
,SUBJ.I_COND_RULE
,SUBJ.I_KEY_RESL
,SUBJ.I_PHAS_RESL
,SUBJ.I_REFR
,SUBJ.I_SUBJ_RULE
,PRED.I_RULE
,PRED.F_PRED_NEGT
,PRED.I_COMPN_RULE
,PRED.I_LEVL_RULE
,PRED.I_TYPE_PRED_RULE
,PRED.I_COND_RULE
,PRED.I_KEY_RESL
,PRED.I_PHAS_RESL
,PRED.I_REFR
,PRED.I_PRED_RULE
FROM
(
SELECT
RULE.I_CLIE
,RULE.I_DOMN
,RULE.I_RULE
,RULE.I_TYPE_SUBJ_RULE
,RULE.I_COMPN_RULE
,RULE.I_LEVL_RULE
,RESL.I_TYPE_RESL
,RULE.I_COND_RULE
,RULE.F_SUBJ_NEGT
,RESL.I_COMPN_RESL
,RESL.I_KEY_RESL
,RESL.I_PHAS_RESL
,IFNULL(RESL.I_REFR,:WS390-20-SP) AS I_REFR
,RULE.I_SUBJ_RULE
,CASE WHEN F_PRED_REF
= :WS390-N THEN :WS390-GENERIC-REFR
ELSE IFNULL(RESL.I_REFR,:WS390-20-SP)
END AS I_REFR_EFFC
FROM
(
SELECT
I_CLIE
,I_DOMN
,I_RULE
,I_TYPE_SUBJ_RULE
,I_COMPN_RULE
,I_LEVL_RULE
,I_COND_RULE
,F_SUBJ_NEGT
,I_SUBJ_RULE
,F_PRED_REF
FROM RULES_REF
) AS RULE
LEFT JOIN
(
SELECT
I_CLIE
,I_DOMN
,I_RULE
,I_TYPE_RESL
,I_COMPN_RESL
,I_LEVL_RESL
,I_KEY_RESL
,I_PHAS_RESL
,IFNULL(I_REFR,:WS390-20-SP) AS I_REFR
FROM RESULTS
WHERE I_REFR <> :WS390-GENERIC-REFR
) RESL
ON RESL.I_CLIE = RULE.I_CLIE
AND RESL.I_DOMN = RULE.I_DOMN
AND RESL.I_TYPE_RESL = RULE.I_TYPE_SUBJ_RULE
AND RESL.I_COMPN_RESL = RULE.I_SUBJ_RULE
WHERE
(
(
RULE.I_TYPE_SUBJ_RULE = :WS390-C
AND
RESL.I_RULE = RULE.I_RULE
)
OR
RULE.I_TYPE_SUBJ_RULE = :WS390-P
)
) AS SUBJ
FULL OUTER JOIN
(
SELECT
RULE.I_CLIE
,RULE.I_DOMN
,RULE.I_RULE
,RULE.I_TYPE_PRED_RULE
,RULE.I_COMPN_RULE
,RULE.I_LEVL_RULE
,RESL.I_TYPE_RESL
,RULE.I_COND_RULE
,RULE.F_PRED_NEGT
,RESL.I_COMPN_RESL
,RESL.I_KEY_RESL
,RESL.I_PHAS_RESL
,IFNULL(RESL.I_REFR,:WS390-20-SP) AS I_REFR
,RULE.I_PRED_RULE
FROM
(
SELECT
I_CLIE
,I_DOMN
,I_RULE
,I_TYPE_PRED_RULE
,I_COMPN_RULE
,I_LEVL_RULE
,I_COND_RULE
,F_PRED_NEGT
,I_PRED_RULE
,F_PRED_REF
FROM RULES_REF
) AS RULE
LEFT JOIN
(
SELECT
I_CLIE
,I_DOMN
,I_RULE
,I_TYPE_RESL
,I_COMPN_RESL
,I_LEVL_RESL
,I_KEY_RESL
,I_PHAS_RESL
,IFNULL(I_REFR,:WS390-20-SP) AS I_REFR
FROM RESULTS
) RESL
ON
RESL.I_TYPE_RESL = RULE.I_TYPE_PRED_RULE
AND RESL.I_COMPN_RESL = RULE.I_PRED_RULE
WHERE
(
(
RULE.I_TYPE_PRED_RULE = :WS390-C
AND
RESL.I_RULE = RULE.I_RULE
)
OR
(
RULE.I_TYPE_PRED_RULE = :WS390-P
)
)
AND
(
( F_PRED_REF = :WS390-Y
AND
I_REFR <> :WS390-GENERIC-REFR
)
OR
F_PRED_REF = :WS390-N
)
) AS PRED
ON
SUBJ.I_RULE = PRED.I_RULE
AND SUBJ.I_COMPN_RULE = PRED.I_COMPN_RULE
AND SUBJ.I_REFR_EFFC = PRED.I_REFR
UNION ALL
SELECT
'NR'
,SUBJ.I_CLIE
,SUBJ.I_DOMN
,SUBJ.I_RULE
,SUBJ.F_SUBJ_NEGT
,SUBJ.I_COMPN_RULE
,SUBJ.I_LEVL_RULE
,SUBJ.I_TYPE_SUBJ_RULE
,SUBJ.I_COND_RULE
,SUBJ.I_KEY_RESL
,SUBJ.I_PHAS_RESL
,SUBJ.I_REFR
,SUBJ.I_SUBJ_RULE
,PRED.I_RULE
,PRED.F_PRED_NEGT
,PRED.I_COMPN_RULE
,PRED.I_LEVL_RULE
,PRED.I_TYPE_PRED_RULE
,PRED.I_COND_RULE
,PRED.I_KEY_RESL
,PRED.I_PHAS_RESL
,PRED.I_REFR
,PRED.I_PRED_RULE
FROM
(
SELECT
RULE.I_CLIE
,RULE.I_DOMN
,RULE.I_RULE
,RULE.I_TYPE_SUBJ_RULE
,RULE.I_COMPN_RULE
,RULE.I_LEVL_RULE
,RESL.I_TYPE_RESL
,RULE.I_COND_RULE
,RULE.F_SUBJ_NEGT
,RESL.I_COMPN_RESL
,RESL.I_KEY_RESL
,RESL.I_PHAS_RESL
,IFNULL(RESL.I_REFR,:WS390-20-SP) AS I_REFR
,RULE.I_SUBJ_RULE
FROM
(
SELECT
I_CLIE
,I_DOMN
,I_RULE
,I_TYPE_SUBJ_RULE
,I_COMPN_RULE
,I_LEVL_RULE
,I_COND_RULE
,F_SUBJ_NEGT
,I_SUBJ_RULE
FROM RULES
WHERE F_REFR_COMPN <> :WS390-Y
) AS RULE
FULL OUTER JOIN
(
SELECT
I_CLIE
,I_DOMN
,I_RULE
,I_TYPE_RESL
,I_COMPN_RESL
,I_LEVL_RESL
,I_KEY_RESL
,I_PHAS_RESL
,IFNULL(I_REFR,:WS390-20-SP) AS I_REFR
FROM RESULTS
WHERE I_REFR = :WS390-GENERIC-REFR
) RESL
ON
RESL.I_TYPE_RESL = RULE.I_TYPE_SUBJ_RULE
AND RESL.I_COMPN_RESL = RULE.I_SUBJ_RULE
WHERE
(
(
RULE.I_TYPE_SUBJ_RULE = :WS390-C
AND
RESL.I_RULE = RULE.I_RULE
)
OR
(
RULE.I_TYPE_SUBJ_RULE = :WS390-P
)
)
) AS SUBJ
FULL OUTER JOIN
(
SELECT
RULE.I_CLIE
,RULE.I_DOMN
,RULE.I_RULE
,RULE.I_TYPE_PRED_RULE
,RULE.I_COMPN_RULE
,RULE.I_LEVL_RULE
,RESL.I_TYPE_RESL
,RULE.I_COND_RULE
,RULE.F_PRED_NEGT
,RESL.I_COMPN_RESL
,RESL.I_KEY_RESL
,RESL.I_PHAS_RESL
,IFNULL(RESL.I_REFR,:WS390-20-SP) AS I_REFR
,RULE.I_PRED_RULE
FROM
(
SELECT
I_CLIE
,I_DOMN
,I_RULE
,I_TYPE_PRED_RULE
,I_COMPN_RULE
,I_LEVL_RULE
,I_COND_RULE
,F_PRED_NEGT
,I_PRED_RULE
FROM RULES
WHERE F_REFR_COMPN <> :WS390-Y
) AS RULE
FULL OUTER JOIN
(
SELECT
I_CLIE
,I_DOMN
,I_RULE
,I_TYPE_RESL
,I_COMPN_RESL
,I_LEVL_RESL
,I_KEY_RESL
,I_PHAS_RESL
,I_REFR
FROM RESULTS
WHERE I_REFR = :WS390-GENERIC-REFR
) RESL
ON
RESL.I_TYPE_RESL = RULE.I_TYPE_PRED_RULE
AND RESL.I_COMPN_RESL = RULE.I_PRED_RULE
WHERE
(
(
RULE.I_TYPE_PRED_RULE = :WS390-C
AND
RESL.I_RULE = RULE.I_RULE
)
OR
(
RULE.I_TYPE_PRED_RULE = :WS390-P
)
)
) AS PRED
ON
SUBJ.I_RULE = PRED.I_RULE
AND SUBJ.I_COMPN_RULE = PRED.I_COMPN_RULE
OPTIMIZE FOR 1 ROW
WITH UR
Now, if you can reverse engineer an approximate understanding of what those two SQLs are doing, I'd look for a better job. Further, if you can learn to build high-level structured queries in the future for production systems, you'll always have a job.

SQL is an amazing, though often underappreciated and subsequently misused, language. I doubt Turbine's payroll contains anyone on it experienced enough in databases to do serious exploiter research beyond the obvious. Especially since a data duplication bug pretty much screams race condition or terrible database SQL.