By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,017 Members | 951 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,017 IT Pros & Developers. It's quick & easy.

SQL0338N-Error

P: n/a
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

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

Jul 23 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Most likely correlation in the ON clause, but I don't have the time to
spend on formatting this query into something readable.

Repost with a nicely indented format and I may see what's wrong.
Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jul 23 '07 #2

P: n/a
My repost:

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
On 23 Jul., 17:10, Serge Rielau <srie...@ca.ibm.comwrote:
Repost with a nicely indented format and I may see what's wrong.


Jul 23 '07 #3

P: n/a
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)

--

Jul 23 '07 #4

P: n/a
Cause of SQL0338N is as followings.
(Another isuues already pointed out by Dave Hughes)
You can't reference columns of tables delimited by comma in ON clause.
LATERAL allows reference to columns of previous tables inside sub-
query. It doesn't influence to ON clause.

Abbreviated syntax extracted from "SQL Reference Vol1" is.....
from-claus:
FROM table-reference [,table-reference ...]

table-reference:
table-name | view-name | ... | joined-table

joined-table:
table-reference [INNER | outer] JOIN table-reference ON join-condition
Please see this simple example:
------------------------- Commands Entered -------------------------
SELECT e.empno
, d.deptno, d.mgrno
, mgr.firstnme, mgr.lastname
FROM Department d
, Employee e
LEFT OUTER JOIN
Employee mgr
ON mgr.empno = d.mgrno
WHERE deptno BETWEEN 'E01' AND 'E11'
AND e.workdept = d.deptno;
--------------------------------------------------------------------
SQL0338N An ON clause associated with a JOIN operator or in a MERGE
statement
is not valid. SQLSTATE=42972

------------------------- Commands Entered -------------------------
SELECT e.empno
, d.deptno, d.mgrno
, mgr.firstnme, mgr.lastname
FROM Department d
INNER JOIN
Employee e
ON e.workdept = d.deptno
LEFT OUTER JOIN
Employee mgr
ON mgr.empno = d.mgrno
WHERE deptno BETWEEN 'E01' AND 'E11';
--------------------------------------------------------------------

EMPNO DEPTNO MGRNO FIRSTNME LASTNAME
------ ------ ------ ------------ ---------------
000050 E01 000050 JOHN GEYER
000090 E11 000090 EILEEN HENDERSON
000280 E11 000090 EILEEN HENDERSON
000290 E11 000090 EILEEN HENDERSON
000300 E11 000090 EILEEN HENDERSON
000310 E11 000090 EILEEN HENDERSON

6 record(s) selected.

Jul 25 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.