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_frm <= @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_ind != '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_dt > 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_num = pcr.cnt_num
) 3 2952
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;
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.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: sara |
last post by:
Hi
I'm pretty new to Access here (using Access 2000), and appreciate the
help and instruction.
I gave myself 2.5 hours to research online and help and try to get this
one, and I am not getting...
|
by: Rod |
last post by:
I have been working with ASP.NET 1.1 for quite a while now. For some
reason, opening some ASP.NET applications we wrote is producing the
following error message:
"The Web server reported...
|
by: paul |
last post by:
HI! I keep on getting this error and I have tried different things but I am
not sure how to send the expiring date.
The error that I am getting in Firefox 1.5 is
"Error: expires.toGMTString is...
|
by: MSK |
last post by:
Hi,
Continued to my earlier post regaring "Breakpoints are not getting hit"
, I have comeup with more input this time.. Kindly give me some idea.
I am a newbie to .NET, recently I installed...
|
by: MSK |
last post by:
Dear all,
Continued to my earlier mail regarding "Breaking points are not getting
hit in ASP.NET " ,
I got some idea from net. Can anyone give me more idea on this?
Just to remind the issue:...
|
by: Sascha.Moellering |
last post by:
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...
|
by: Manikandan |
last post by:
Hi,
I have a program written in .Net Framework 1.1 using Visual studio
enterprise edition 2003.
I tried compiling the same program in visual c# express edition 2005.
I'm getting following...
|
by: buntyindia |
last post by:
Hi,
I have a very strange problem with my application.
I have developed it using Struts.
I have a TextBox With Some fixed value in it and on Submit iam passing it to another page.
<html:form...
|
by: imaloner |
last post by:
I am posting two threads because I have two different problems, but both have the same background information.
Common Background Information:
I am trying to rebuild code for a working,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new...
| |