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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #329408 is a reply to message #329373] Wed, 25 June 2008 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: SQL Query Rewrite [message #329428 is a reply to message #329404] Wed, 25 June 2008 07:19 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Deleting Problem using PL/SQL
Next Topic: Query Problem
Goto Forum:
  


Current Time: Sun Dec 04 12:21:02 CST 2016

Total time taken to generate the page: 0.09818 seconds