Home » SQL & PL/SQL » SQL & PL/SQL » HELP IN NOT LIKE / INSTEAD OF NOT LIKE
HELP IN NOT LIKE / INSTEAD OF NOT LIKE [message #302486] Mon, 25 February 2008 21:55 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Dear All,

I have the following sql using NOT LIKE

SELECT DISTINCT b.rno,d.INO,d.IAMT,b.cno,b.eno,b.lic,
a.code,a.dsc
FROM RJOBS a,RHDR b,CCR c,IHDR d,ILINES e
WHERE a.sts = 'I'
AND A.CACC_NO='DT000004'
AND a.rohd_id=b.ID
AND B.MAKE_CODE='T7'
AND b.CARD_ID=c.ID
AND a.ID = e.rojb_id
AND e.inhd_id=d.ID
AND d.sts != 'X'
AND D.INV_NO LIKE '%SAI%'
AND B.ROST_CODE != 'PDI'
and A.eng_dsc NOT LIKE '%SERVICE%'

When I run this script it takes lot of time atleast 30 minutes since the RJOBS table has got more than 500,000 records. Is there anyother method or SQL to get the records in a faster way. Please kindly help.

Re: HELP IN NOT LIKE / INSTEAD OF NOT LIKE [message #302489 is a reply to message #302486] Mon, 25 February 2008 22:03 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
SELECT DISTINCT b.rno,d.INO,d.IAMT,b.cno,b.eno,b.lic,a.code,a.dsc
FROM RJOBS a,RHDR b,CCR c,IHDR d
WHERE a.sts = 'I' 
 AND  A.CACC_NO='DT000004' 
 AND  a.rohd_id=b.ID 
 AND  B.MAKE_CODE='T7' 
 AND  b.CARD_ID=c.ID 
 AND  a.ID,d.ID  in (select e.rojb_id, e.inhd_id from ILINES e)
 AND  d.sts != 'X' 
 AND  D.INV_NO LIKE '%SAI%' 
 AND  B.ROST_CODE != 'PDI' 
 and  A.eng_dsc NOT LIKE '%SERVICE%'
Re: HELP IN NOT LIKE / INSTEAD OF NOT LIKE [message #302520 is a reply to message #302489] Tue, 26 February 2008 00:39 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
hi,

when i use your script i am getting invalid relational operator.
ORA-00920: invalid relational operator
Re: HELP IN NOT LIKE / INSTEAD OF NOT LIKE [message #302525 is a reply to message #302520] Tue, 26 February 2008 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Some brackets are missing. Just fix it.

Regards
Michel
Re: HELP IN NOT LIKE / INSTEAD OF NOT LIKE [message #302526 is a reply to message #302489] Tue, 26 February 2008 00:59 Go to previous message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
anacedent wrote on Tue, 26 February 2008 05:03
SELECT DISTINCT b.rno,d.INO,d.IAMT,b.cno,b.eno,b.lic,a.code,a.dsc
FROM RJOBS a,RHDR b,CCR c,IHDR d
WHERE a.sts = 'I' 
 AND  A.CACC_NO='DT000004' 
 AND  a.rohd_id=b.ID 
 AND  B.MAKE_CODE='T7' 
 AND  b.CARD_ID=c.ID 
 AND  (a.ID,d.ID)  in (select e.rojb_id, e.inhd_id from ILINES e)
 AND  d.sts != 'X' 
 AND  D.INV_NO LIKE '%SAI%' 
 AND  B.ROST_CODE != 'PDI' 
 and  A.eng_dsc NOT LIKE '%SERVICE%'



anacedent forgot to put braces around the a.ID,d.ID

 AND  (a.ID,d.ID)  in (select e.rojb_id, e.inhd_id from ILINES e)


But could you provide us with the EXPLAIN PLAN of your statement?

If you are familiar with SQL_TRACE & TKPROF: output of the latter would be very welcome as well

We could suggest to add indexes, but maybe they're already there as we don't know how the tables & which indexes have been created.


Sorry Michel, I've been spoonfeeding again Embarassed

[Updated on: Tue, 26 February 2008 01:01]

Report message to a moderator

Previous Topic: Combine fields in query
Next Topic: dbms_jobs failed with ORA-06550
Goto Forum:
  


Current Time: Sat Dec 10 18:15:54 CST 2016

Total time taken to generate the page: 0.07217 seconds