473,324 Members | 2,178 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

SQL0338N-Error

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
4 2141
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: wellhole | last post by:
Is there any way to improve this statement? A is the header table while B has 0 to many records for each record in A. I'm trying to pick only the latest record in B for the associated seq in A. ...
3
by: tejasmore | last post by:
Hi, I am running a following query in DB2 and getting SQL0338n error. This is primarily because, there are subqueries at few places (marked in bold)in the main query, after 'ON' clause. Could...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.