Sa***************@gmail.com wrote:
Hi,
the following SQL-Statement does not compile, I receive SQL0338N as an
error. The erroneous parts of the statement are the left-outer-Joins,
but I don't know how to solve the problem, any hints?
Thanks in advance,
Sascha
[snip horribly formatted query]
First of all, here's a properly formatted version:
SELECT
'LST' AS solve_name,
0 AS scenario,
NULL AS korrektur_kz,
MAX(CF.eventcontractid),
SUM(CF.eventdate * CF.CLI) / SUM(CF.CLI),
SUM(CF.CLI) * (-1),
SUM(CF.CFS) * (-1),
SUM(CF.DDUR) * (-1),
SUM(CF.DBETA) * (-1),
SUM(CF.ZDF * CF.CLI) / SUM(CF.CLI),
SUM(CF.FXB * CF.CLI) / SUM(CF.CLI),
SUM(CF.FXC * CF.CLI) / SUM(CF.CLI),
CASE CF.eventkind
WHEN 'IP' THEN 'IP'
WHEN 'CL' THEN 'CL'
ELSE 'PP'
END AS eventkind,
CF.eventcurrency,
CF.eventcontingency,
CA.refnodeid_kab,
CA.refnodeid_liq,
FD.fs_uteil,
FD.fs_proda,
FD.prod_cluster_nr,
CA.cusip_code AS Descriptor_GADB,
CASE WHEN CLI < 0
THEN '-'
ELSE '+'
END AS cashflow_sign,
FD.bilanz_kz,
FD.akt_pass_kz,
FD.fs_kusys,
FD.dv_sys_l,
FD.fb_datum,
FD.buch_kz_kwg,
CA.fixed_variable,
CA.contract_type,
CASE WHEN FD.kuend_dat_glaeub IS NOT NULL OR FD.kuend_dat_schuld IS
NOT NULL
THEN 1
ELSE 0
END AS kuendrecht_kz,
FD.ssrn_praefix,
FD.fs_kugru,
FD.status_flag,
CASE WHEN FD.portfolio_name = '5010'
THEN 1
ELSE 0
END AS refi_5010_kz,
FD.int_deal_kz,
FD.gp_bayern_kz,
FD.grandfather_kz,
FD.GKM_KZ,
'STD' AS tbs1_name,
COALESCE(tb1.tbs_start, (
SELECT MAX(tbs_start)
FROM LIZSYSABA.VWZL0372TBS_AGG
WHERE tbs_name = 'STD')
) AS tbs1_start,
COALESCE(tb1.tbs_end, (
SELECT MAX(tbs_end)
FROM LIZSYSABA.VWZL0372TBS_AGG
WHERE tbs_name = 'STD')
) AS tbs1_end,
'180D' AS tbs2_name,
COALESCE(tb2.tbs_start, (
SELECT MAX(tbs_start)
FROM LIZSYSABA.VWZL0372TBS_AGG
WHERE tbs_name = '180D')
) AS tbs2_start,
COALESCE(tb2.tbs_end, (
SELECT MAX(tbs_end)
FROM LIZSYSABA.VWZL0372TBS_AGG
WHERE tbs_name = '180D')
) AS tbs2_end
FROM
LIZSYSA.FRE_CASHFLOW_SEQUENCE_KAB AS CF,
LIZSYSABA.VWZL0362CTR_ALL AS CA,
LIZSYSA.FREE_DEFINED_ATTRIBUTES AS FD
left outer join lateral (
SELECT *
FROM LIZSYSABA.VWZL0372TBS_AGG
WHERE tbs_name = 'STD'
) AS TB1
on TB1.tbs_date = cast(CF.eventdate as date)
left outer join lateral (
SELECT *
FROM LIZSYSABA.VWZL0372TBS_AGG
WHERE tbs_name = '180D'
) AS TB2
on TB2.tbs_date = cast(CF.EVENTDATE as date)
WHERE
CF.ID >= 900000000 and CF.ID < 900000020
and EXISTS (
SELECT '1'
FROM LIZSYSA.FRE_CASHFLOW_SEQUENCE_LIQ AS CFL
WHERE CFL.eventcontract_id = CF.eventcontract_id
)
AND CA.contract_id = CF.eventcontractid
AND CA.fda_contract_id=FD.fda_contract_id
Problems with the above:
* Aggregate functions used (MAX() and SUM()) with non-aggregated
columns and yet there's no GROUP BY clause
* You've got a NULL without an explicit CAST() in the third column (as
NULL can fit any datatype DB2 will complain about it because it doesn't
know what type the column should be)
I must admit I'm not sure what the problem is with the ON clause. They
look okay to me (if a little strange - I don't think I've ever had to
use CAST() in a JOIN).
Anyway, there're also several other stylistic annoyances to correct
while we're at it:
* BETWEEN predicate instead of two inequalities
* Converting the WHERE-based SQL-83 style joins to SQL-92 style INNER
JOINs
* Converting the EXISTS predicate into an INNER JOIN
* Converting all sub-selects into CTEs
* Getting rid of the redundant LATERAL keyword
* Change multiplications by -1 into unary negation operators
So, here's a quick attempt at correcting the query. It's still pretty
ugly - especially with all those expressions in the GROUP BY and I'm
not sure whether it'll work or not but it should give you a base to
work from:
WITH
TB1 AS (
SELECT TBS_START, TBS_END, TBS_DATE
FROM LIZSYSABA.VWZL0372TBS_AGG
WHERE TBS_NAME = 'STD'
),
TB2 AS (
SELECT TBS_START, TBS_END, TBS_DATE
FROM LIZSYSABA.VWZL0372TBS_AGG
WHERE TBS_NAME = '180D'
),
MAX1 AS (
SELECT MAX(TBS_START) AS TBS_START, MAX(TBS_END) AS TBS_END
FROM LIZSYSABA.VWZL0372TBS_AGG
WHERE TBS_NAME = 'STD'
),
MAX2 AS (
SELECT MAX(TBS_START) AS TBS_START, MAX(TBS_END) AS TBS_END
FROM LIZSYSABA.VWZL0372TBS_AGG
WHERE TBS_NAME = '180D'
)
SELECT
'LST' AS SOLVE_NAME,
0 AS SCENARIO,
CAST(NULL AS INTEGER) AS KORREKTUR_KZ,
MAX(CF.EVENTCONTRACTID),
-SUM(CF.EVENTDATE * CF.CLI) / SUM(CF.CLI),
SUM(CF.CLI),
-SUM(CF.CFS),
-SUM(CF.DDUR),
-SUM(CF.DBETA),
SUM(CF.ZDF * CF.CLI) / SUM(CF.CLI),
SUM(CF.FXB * CF.CLI) / SUM(CF.CLI),
SUM(CF.FXC * CF.CLI) / SUM(CF.CLI),
CASE WHEN CF.EVENTKIND IN ('IP', 'CL')
THEN CF.EVENTKIND
ELSE 'PP'
END AS EVENTKIND,
CF.EVENTCURRENCY,
CF.EVENTCONTINGENCY,
CA.REFNODEID_KAB,
CA.REFNODEID_LIQ,
FD.FS_UTEIL,
FD.FS_PRODA,
FD.PROD_CLUSTER_NR,
CA.CUSIP_CODE AS DESCRIPTOR_GADB,
CASE WHEN CLI < 0
THEN '-'
ELSE '+'
END AS CASHFLOW_SIGN,
FD.BILANZ_KZ,
FD.AKT_PASS_KZ,
FD.FS_KUSYS,
FD.DV_SYS_L,
FD.FB_DATUM,
FD.BUCH_KZ_KWG,
CA.FIXED_VARIABLE,
CA.CONTRACT_TYPE,
CASE WHEN FD.KUEND_DAT_GLAEUB IS NOT NULL OR FD.KUEND_DAT_SCHULD IS
NOT NULL
THEN 1
ELSE 0
END AS KUENDRECHT_KZ,
FD.SSRN_PRAEFIX,
FD.FS_KUGRU,
FD.STATUS_FLAG,
CASE WHEN FD.PORTFOLIO_NAME = '5010'
THEN 1
ELSE 0
END AS REFI_5010_KZ,
FD.INT_DEAL_KZ,
FD.GP_BAYERN_KZ,
FD.GRANDFATHER_KZ,
FD.GKM_KZ,
'STD' AS TBS1_NAME,
COALESCE(TB1.TBS_START, MAX1.TBS_START) AS TBS1_START,
COALESCE(TB1.TBS_END, MAX1.TBS_END) AS TBS1_END,
'180D' AS TBS2_NAME,
COALESCE(TB2.TBS_START, MAX2.TBS_START) AS TBS2_START,
COALESCE(TB2.TBS_END, MAX2.TBS_END) AS TBS2_END
FROM
LIZSYSA.FRE_CASHFLOW_SEQUENCE_KAB AS CF
INNER JOIN LIZSYSA.FRE_CASHFLOW_SEQUENCE_LIQ AS CFL
ON CFL.EVENTCONTRACT_ID = CF.EVENTCONTRACT_ID
INNER JOIN LIZSYSABA.VWZL0362CTR_ALL AS CA
ON CA.CONTRACT_ID = CF.EVENTCONTRACTID
INNER JOIN LIZSYSA.FREE_DEFINED_ATTRIBUTES AS FD
ON CA.FDA_CONTRACT_ID = FD.FDA_CONTRACT_ID
LEFT OUTER JOIN TB1
ON TB1.TBS_DATE = CAST(CF.EVENTDATE AS DATE)
LEFT OUTER JOIN TB2
ON TB2.TBS_DATE = CAST(CF.EVENTDATE AS DATE)
WHERE
CF.ID BETWEEN 900000000 AND 900000019
GROUP BY
CASE WHEN CF.EVENTKIND IN ('IP', 'CL')
THEN CF.EVENTKIND
ELSE 'PP'
END,
CF.EVENTCURRENCY,
CF.EVENTCONTINGENCY,
CA.REFNODEID_KAB,
CA.REFNODEID_LIQ,
FD.FS_UTEIL,
FD.FS_PRODA,
FD.PROD_CLUSTER_NR,
CA.CUSIP_CODE AS DESCRIPTOR_GADB,
CASE WHEN CLI < 0
THEN '-'
ELSE '+'
END,
FD.BILANZ_KZ,
FD.AKT_PASS_KZ,
FD.FS_KUSYS,
FD.DV_SYS_L,
FD.FB_DATUM,
FD.BUCH_KZ_KWG,
CA.FIXED_VARIABLE,
CA.CONTRACT_TYPE,
CASE WHEN FD.KUEND_DAT_GLAEUB IS NOT NULL OR FD.KUEND_DAT_SCHULD IS
NOT NULL
THEN 1
ELSE 0
END,
FD.SSRN_PRAEFIX,
FD.FS_KUGRU,
FD.STATUS_FLAG,
CASE WHEN FD.PORTFOLIO_NAME = '5010'
THEN 1
ELSE 0
END,
FD.INT_DEAL_KZ,
FD.GP_BAYERN_KZ,
FD.GRANDFATHER_KZ,
FD.GKM_KZ,
COALESCE(TB1.TBS_START, MAX1.TBS_START),
COALESCE(TB1.TBS_END, MAX1.TBS_END),
COALESCE(TB2.TBS_START, MAX2.TBS_START),
COALESCE(TB2.TBS_END, MAX2.TBS_END)
--