Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query Rewrite (Oracle 10g (10.2.0.2))
SQL Query Rewrite [message #329373] |
Wed, 25 June 2008 04:24  |
sujitdba
Messages: 6 Registered: June 2008 Location: United Kingdom
|
Junior Member |
|
|
Hi Experts,
Can anybody help me in rewriting the following query, I also want to get rid of the union all used. The reason for this I am scanning same tables twice and it is taking long time to execute.
Is there any better method or logic of achieving the same.
SELECT a.*
FROM ops$ssarkar.table_1 a,
ops$ssarkar.table_2 b
WHERE a.tran_sponsor = b.tran_sponsor
AND a.reporting_sponsor = b.reporting_sponsor
AND a.card_key = b.card_key
AND a.locn_key = b.locn_key
AND a.swipe_date < b.swipe_date --updates
union all
SELECT a.*
FROM ops$ssarkar.table_1 a,
ops$ssarkar.table_2 b
WHERE a.tran_sponsor = b.tran_sponsor(+)
AND a.reporting_sponsor = b.reporting_sponsor(+)
AND a.card_key = b.card_key(+)
AND a.locn_key = b.locn_key(+)
AND b.rowid is null --inserts
Any help is highly appreciated..
Regards,
Sujit
|
|
|
Re: SQL Query Rewrite [message #329388 is a reply to message #329373] |
Wed, 25 June 2008 05:28   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Check this link. This can be easily achieved using ANSI Sql.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2107296
If I understand your requirement correctly you are trying to do a left outer between table_1 and table_2 on the columns (tran_sponsor , reporting_sponsor, card_key, locn_key)
and apply a filter on top of that where table_1.swipe_date < table_2.swipe_date. Try to apply the same logic using ANSI syntax and if you are stuck come back to us with what you have tried so far and somebody will definitely help you out.
But first and foremost spend some time in reading the forum guidelines on how to format your post also post some test data with insert scripts.
Regards
Raj
|
|
|
Re: SQL Query Rewrite [message #329404 is a reply to message #329388] |
Wed, 25 June 2008 06:10   |
sujitdba
Messages: 6 Registered: June 2008 Location: United Kingdom
|
Junior Member |
|
|
Hi Raj,
Appreciate your response on this, I tried with ANSI SQL but got no luck.
I tried to use the following query but it returns no rows.
My requirement is :
1) I need to identify new rows in table_1 which will be inserted into table_2
2) Need to identify the existing rows to update table table_2 if table_1 swipe_date is less than table_2 swipe date.
SELECT a.*
FROM table_1 a
LEFT OUTER JOIN
table_2 b
ON a.tran_sponsor = b.tran_sponsor
AND a.reporting_sponsor = b.reporting_sponsor
AND a.card_key = b.card_key
AND a.locn_key = b.locn_key
WHERE a.swipe_date < b.swipe_date --updates
Thanks in Advance.
Regards,
Sujit
|
|
|
|
Re: SQL Query Rewrite [message #329428 is a reply to message #329404] |
Wed, 25 June 2008 07:19   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
You are almost there. Create a small test case, run the query and post the output and explain which record(s) you are expecting in the output. Also as I said earlier read the forum guidelines how to format your post. Something like this.
1 with t
2 as
3 (select 1 tran_sponsor, 1 reporting_sponsor, 1 card_key, 1 locn_key,
4 to_date('01.01.2008','dd.mm.yyyy') swipe_date from dual union all
5 select 2 , 2 , 2, 2 , to_date('01.02.2008','dd.mm.yyyy') from dual union all
6 select 3, 3, 3, 3, to_date('01.03.2008','dd.mm.yyyy') from dual
7 ),
8 t1
9 as
10 ( select 1 tran_sponsor, 1 reporting_sponsor, 1 card_key, 1 locn_key,
11 to_date('01.03.2008','dd.mm.yyyy') swipe_date from dual union all
12 select 2 , 2 , 2, 2 , to_date('01.01.2008','dd.mm.yyyy') from dual
13 )
14 select t.* from t left outer join t1
15 on
16 (
17 t.tran_sponsor = t1.tran_sponsor and
18 t.reporting_sponsor = t1.reporting_sponsor and
19 t.card_key = t1.card_key and
20 t.locn_key = t1.locn_key)
21* where t.swipe_date < .... (fill up the blanks)
SQL> /
TRAN_SPONSOR REPORTING_SPONSOR CARD_KEY LOCN_KEY SWIPE_DATE
------------ ----------------- ---------- ---------- ------------------
1 1 1 1 01-JAN-08
3 3 3 3 01-MAR-08
|
|
|
Re: SQL Query Rewrite [message #329439 is a reply to message #329428] |
Wed, 25 June 2008 07:38   |
sujitdba
Messages: 6 Registered: June 2008 Location: United Kingdom
|
Junior Member |
|
|
Hi Raj,
Thanks very much for your response.
I ran the below test query provided by you in which i am getting 1 row as output. Along with this row I am also expecting 3, 3, 3, 3, 01-MAR-2008 row from table t since it is not there in table t1.
SQL> with t
2 as
3 (select 1 tran_sponsor, 1 reporting_sponsor, 1 card_key, 1 locn_key,
4 to_date('01.01.2008','dd.mm.yyyy') swipe_date from dual union all
5 select 2 , 2 , 2, 2 , to_date('01.02.2008','dd.mm.yyyy') from dual union all
6 select 3, 3, 3, 3, to_date('01.03.2008','dd.mm.yyyy') from dual
7 ),
8 t1
9 as
10 ( select 1 tran_sponsor, 1 reporting_sponsor, 1 card_key, 1 locn_key,
11 to_date('01.03.2008','dd.mm.yyyy') swipe_date from dual union all
12 select 2 , 2 , 2, 2 , to_date('01.01.2008','dd.mm.yyyy') from dual
13 )
14 select t.* from t left outer join t1
15 on
16 (
17 t.tran_sponsor = t1.tran_sponsor and
18 t.reporting_sponsor = t1.reporting_sponsor and
19 t.card_key = t1.card_key and
20 t.locn_key = t1.locn_key)
21 where t.swipe_date < t1.swipe_date;
TRAN_SPONSOR REPORTING_SPONSOR CARD_KEY LOCN_KEY SWIPE_DAT
------------ ----------------- ---------- ---------- ---------
1 1 1 1 01-JAN-08
I am sorry if the query was not formatted as per this forum guidelines.
Regards,
Sujit
|
|
|
Re: SQL Query Rewrite [message #329446 is a reply to message #329439] |
Wed, 25 June 2008 08:00   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
You still have not formatted the output. Read the very first link in this forum. It will tell you how to format your post. I will give you a clue how to achieve the expected output. Search for NVL function in oracle. Still if you cannot figure out think again. You will get it.
Regards
Raj
|
|
|
Re: SQL Query Rewrite [message #329456 is a reply to message #329446] |
Wed, 25 June 2008 08:36   |
sujitdba
Messages: 6 Registered: June 2008 Location: United Kingdom
|
Junior Member |
|
|
Hi Raj,
Thanks very much for your timely help.
I think I got your NVL hint to achieve the desired results,
Following is the revised query by whcih I am getting my expected output.
SELECT a.*
FROM ops$ssarkar.table_1 a
LEFT OUTER JOIN
ops$ssarkar.table_2 b
ON (a.tran_sponsor = b.tran_sponsor
AND a.reporting_sponsor = b.reporting_sponsor
AND a.card_key = b.card_key
AND a.locn_key = b.locn_key)
WHERE a.swipe_date < nvl(b.swipe_date,'31-DEC-2099')
Once again thanks for the much apprecaited help.
Regards,
Sujit
|
|
|
Re: SQL Query Rewrite [message #329464 is a reply to message #329456] |
Wed, 25 June 2008 08:47  |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Still you didn't follow the forum guidelines. Again I am reiterating read the very first link in this forum. You will understand how to format it otherwise code is not readable.
Quote: | nvl(b.swipe_date,'31-DEC-2099')
|
It is not a date. You are comparing a date column with a string. You should not do that. Always use to_date function to convert a string to a date. Having said that you don't need to give a high date. It could be re-written as follows
a.swipe_date < nvl(b.swipe_date,a.swipe_date + 1)
Regards
Raj
|
|
|
Goto Forum:
Current Time: Fri Feb 14 18:33:19 CST 2025
|