Getting Error sql0338n

[tejasmore]

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 anyone please help me, as how these subqueries can be replaced without change in resultset?

Thanks,
Tejas.



--------------------------------------------------------------------------------
Select
.
.
.
FROM join_is JID
LEFT OUTER JOIN AD_PH ph
ON JID.h_num=ph.h_ num
AND JID.ph_ts=ph.ts
LEFT OUTER JOIN AD_COM cmd
ON ph.cmd_cd = cmd.cmd_cd
AND cmd.ts =
(
SELECT max(ts)
FROM AD_COM cmd_sub
WHERE cmd_sub.v_frm <= @start
AND cmd_sub.cmd_cd = ph.cmd_cd
) LEFT OUTER JOIN AD_PS ps
ON JID.p_num=ps.p_ num
AND JID.ps_ts=ps.ts
LEFT OUTER JOIN PSM
ON JID.p_num=PSM.p _num
LEFT OUTER JOIN AD_FORM af
ON JID.h_num = af.h_num
AND af.ts =
(
SELECT max(ts)
FROM AD_FORM af_sub
WHERE af_sub.v_frm <= @start
AND af_sub.h_num = JID.h_num
) LEFT OUTER JOIN AD_FC afc
ON JID.h_num = afc.h_num
AND afc.ts =
(
SELECT max(ts)
FROM AD_FC afc_sub
WHERE afc_sub.v_frm <= @start
AND afc_sub.h_num = JID.h_num
),
AD_PCR pcr
LEFT OUTER JOIN
(
SELECT sub_p.ps_num AS m_ps_num, max(f_pr_dt) AS match_f_pr_dt
FROM AD_PCR sub_p, PSM ps_ids
WHERE sub_p.ps_num = ps_ids.ps_num
AND sub_p.ts =
(SELECT MAX(ts) FROM AD_PCR sub_p2 WHERE sub_p2.p_num = sub_p.p_num AND sub_p2.v_frm < @end)
AND sub_p.ad_ty_ind != 'D'
AND sub_p.td_st_ind != 'C'
GROUP BY sub_p.ps_num
HAVING MAX(sub_p.f_pr_ dt) <= @start
) match ON match.m_ps_num = pcr.ps_num
LEFT OUTER JOIN #efp efp ON efp.p_num = pcr.p_num
LEFT OUTER JOIN AD_BD bd
ON bd.p_num IN
(
SELECT head_bd.p_num
FROM AD_BD head_bd
WHERE head_bd.bd_num = pcr.bd_num
AND head_bd.ts =
(
SELECT max(ts)
FROM AD_BD head_bd_sub
WHERE head_bd_sub.v_f rm <= @start
AND head_bd_sub.bd_ num = head_bd.bd_num
)
)AND bd.ts =
(
SELECT max(ts)
FROM AD_BD bd_sub
WHERE bd_sub.v_frm <= @start
AND bd.bd_num = bd_sub.bd_num
)
AND bd.pump_dt IS NOT NULL
AND bd.ad_ty_ind != 'D'
AND NOT EXISTS
(
SELECT 1
FROM AD_BD bd_sub2
WHERE bd_sub2.p_num = bd.p_num
AND bd_sub2.ts != bd.ts
AND bd_sub2.ad_ty_i nd != 'D'
AND
(
bd_sub2.pump_dt IS NULL
OR (bd.pump_dt <= bd.v_frm AND bd_sub2.ts > bd.ts)
OR (bd.pump_dt > bd.v_frm AND (bd_sub2.pump_d t > bd.pump_dt OR bd_sub2.v_frm > bd.pump_dt))
)
AND bd_sub2.ts =
(
SELECT max(ts)
FROM AD_BD bd_sub3
WHERE bd_sub3.v_frm <= @start
AND bd_sub3.bd_num = bd_sub2.bd_num
) )
LEFT OUTER JOIN AD_CNT cntr
ON pcr.cnt_num = cntr.cnt_num
AND cntr.ts =
(
SELECT max(ts)
FROM AD_CNT cntr_sub
WHERE cntr_sub.v_frm <= @start
AND cntr_sub.cnt_nu m = pcr.cnt_num
)

[cburnett]

You might want to try the following approach:

-- using first 3 joins as an example
create table join_is(h_num int not null, ph_ts timestamp not null);
create table ad_ph(h_num int not null, ts timestamp not null, cmd_cd varchar(3) not null);
create table ad_com(cmd_cd varchar(3) not null, ts timestamp not null, v_frm int not null);

insert into join_is values(0, timestamp('2009-01-01 00:00:00'));

insert into join_is values(1, timestamp('2009-01-01 00:00:00'));
insert into join_is values(1, timestamp('2009-01-02 00:00:00'));
insert into ad_ph values(1, timestamp('2009-01-02 00:00:00'), 'CC1');

insert into join_is values(2, timestamp('2009-01-01 00:00:00'));
insert into join_is values(2, timestamp('2009-01-03 00:00:00'));
insert into ad_ph values(2, timestamp('2009-01-03 00:00:00'), 'CC2');
insert into ad_com values('CC2', timestamp('2009-01-02 00:00:00'), 50);
insert into ad_com values('CC2', timestamp('2009-01-03 00:00:00'), 50);
insert into ad_com values('CC2', timestamp('2009-01-04 00:00:00'), 150);
insert into ad_com values('CCX', timestamp('2009-01-04 00:00:00'), 50);

insert into join_is values(3, timestamp('2009-01-01 00:00:00'));
insert into join_is values(3, timestamp('2009-01-04 00:00:00'));
insert into ad_ph values(3, timestamp('2009-01-04 00:00:00'), 'CC3');
insert into ad_com values('CC3', timestamp('2009-01-06 00:00:00'), 150);

-- expected results:
-- 0, NULL, NULL
-- 1, NULL, NULL
-- 1, 'CC1', NULL
-- 2, NULL, NULL
-- 2, 'CC2', 2009-01-03 00:00:00
-- 2, NULL, NULL
-- 3, 'CC3', NULL

-- fails with SQL0338N
SELECT JID.h_num, PH.cmd_cd, CMD.ts
  FROM join_is JID
  LEFT OUTER JOIN
       ad_ph    PH
    ON JID.h_num = PH.h_num
   AND JID.ph_ts = PH.ts
  LEFT OUTER JOIN
       ad_com  CMD
    ON PH.cmd_cd = CMD.cmd_cd
   AND CMD.ts = (SELECT max(ts)
                   FROM ad_com CMD_SUB
                  WHERE CMD_SUB.v_frm <= 100
                    AND CMD_SUB.cmd_cd = PH.cmd_cd);

-- rewritten to left outer join to nested table expression which performs aggregation
SELECT JID.h_num, PH.cmd_cd, CMD.ts
  FROM join_is JID
  LEFT OUTER JOIN
       ad_ph    PH
    ON JID.h_num = PH.h_num
   AND JID.ph_ts = PH.ts
  LEFT OUTER JOIN
       (SELECT cmd_cd,
               max(ts) as ts
          FROM ad_com
         WHERE v_frm <= 100
         GROUP BY cmd_cd) CMD
    ON PH.cmd_cd = CMD.cmd_cd
 ORDER BY 1;

-- similar but uses OLAP function to perform aggregation - potentially more efficient
SELECT JID.h_num, PH.cmd_cd, CMD.ts
  FROM join_is JID
  LEFT OUTER JOIN
       ad_ph    PH
    ON JID.h_num = PH.h_num
   AND JID.ph_ts = PH.ts
  LEFT OUTER JOIN
       (SELECT distinct cmd_cd, max(ts) over(partition by cmd_cd) as ts
          FROM ad_com
         WHERE v_frm <= 100) CMD
    ON PH.cmd_cd = CMD.cmd_cd
 ORDER BY 1;

drop table join_is;
drop table ad_ph;
drop table ad_com;

[tejasmore]

Hi Cburnett,
Can we have some other simple approach than this?
Because the query I gave is just a small part of entire stored procedure; hence if I go ahead with creation of temporary tables / insertion of rows in temporary table; then it'll be an overhead; consuming more time...
Is there any alternative solution for this?


Regards,
Tejas.

[cburnett]

Tejas,
I think you may be reading too much into my example. This is a setup and test to show the effect of the changes rather than a rewrite of your problem.

For example, I'm not proposing any temporary tables; just a modificaiton of the SQL. Look particularly at lines 41-47 (of your original problem). I am proposing that you replace these lines with EITHER lines 56-62 OR lines 72-76, depending on whichever performs better.