Solved

Oracle query problem

Posted on 2012-03-25
14
676 Views
Last Modified: 2012-08-13
I created this oracle query using VS 2008.

In first query, I want all records in gprecl that have a match in gpprob_recl.
It does what I want when I run it in a sql stored procedure (accessing oracle tables).
Completes in seconds.

SELECT        GPCOMP1.GPRECL.TRAN_ID, GPCOMP1.GPPROB_RECL.TRAN_ID AS EXPR1, GPCOMP1.GPPROB_RECL.PROBLEM_ID
FROM            GPCOMP1.GPRECL, GPCOMP1.GPPROB_RECL
WHERE        GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_ID

In second query, I want all records in gprecl that dont have a match in  gpprob_recl.
This query runs, but runs forever (8+ minutes) before giving me any data.

SELECT        GPCOMP1.GPRECL.TRAN_ID, GPCOMP1.GPPROB_RECL.TRAN_ID AS EXPR1, GPCOMP1.GPPROB_RECL.PROBLEM_ID
FROM            GPCOMP1.GPRECL, GPCOMP1.GPPROB_RECL
WHERE        GPCOMP1.GPRECL.TRAN_ID <> GPCOMP1.GPPROB_RECL.TRAN_ID

What am I doing wrong in the second query ?
Just changed the = to <>.
Why isnt this query running in seconds like the first query.
0
Comment
Question by:thayduck
  • 6
  • 3
  • 2
  • +2
14 Comments
 

Expert Comment

by:expert9
Comment Utility
well first of all the first query you are matching identicals taking join on both ids in table  "GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_ID"

in the second query you are doing GPCOMP1.GPRECL.TRAN_ID <> GPCOMP1.GPPROB_RECL.TRAN_ID. the join condition is missing and it is taking cartesian.....

try this

SELECT        GPCOMP1.GPRECL.TRAN_ID, GPCOMP1.GPPROB_RECL.TRAN_ID AS EXPR1, GPCOMP1.GPPROB_RECL.PROBLEM_ID
            FROM            GPCOMP1.GPRECL LEFT OUTER JOIN GPCOMP1.GPPROB_RECL
                              ON GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_ID
                              and GPCOMP1.GPRECL.TRAN_ID <> GPCOMP1.GPPROB_RECL.TRAN_ID
0
 
LVL 23

Expert Comment

by:David
Comment Utility
Part of the performance hit is you switched from a index scan (if the fields were indexed) to full table -- any comparison such as NE, LT, GT takes a full table hit.
0
 

Author Comment

by:thayduck
Comment Utility
Tried this way but get error:

OLE DB provider "OraOLEDB.Oracle" for linked server "GPNFE" returned message "ORA-00942: table or view does not exist".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT        R.TRAN_ID, GR.TRAN_ID AS EXPR1, GR.PROBLEM_ID
            FROM            GPCOMP1.GPRECL R, LEFT OUTER JOIN GPCOMP1.GPPROB_RECL GR
                              ON R.TRAN_ID <> GR.TRAN_ID" for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "GPNFE".

Tried this way but get error:

OLE DB provider "OraOLEDB.Oracle" for linked server "GPNFE" returned message "ORA-00905: missing keyword".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT        GPCOMP1.GPRECL.TRAN_ID, GPCOMP1.GPPROB_RECL.TRAN_ID AS EXPR1, GPCOMP1.GPPROB_RECL.PROBLEM_ID
FROM            GPCOMP1.GPRECL, LEFT OUTER JOIN GPCOMP1.GPPROB_RECL
WHERE        GPCOMP1.GPRECL.TRAN_ID <> GPCOMP1.GPPROB_RECL.TRAN_ID " for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "GPNFE".
0
 
LVL 23

Expert Comment

by:David
Comment Utility
942:  you do not have select permission on the table or view attempted.  This has to be granted by the object owner.
0
 

Author Comment

by:thayduck
Comment Utility
I do have select permission.

I start getting these errors when I tried the suggestion (from expert9) to put in LEFT OUTER JOIN.

If I remove LEFT OUTER JOIN from query , it runs, but runs way to long.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Are you sure your queries are valid?

Check out the following test case.  I think it should mirror what you are doing.

Check the results of the second query.

Are you sure you want the following from the second query:          2          1 a

If there is no match, why are you returning a value from tab2 (your GPPROB_RECL)?

If not, please post sample data and expected results.

The results from the two queries below:

   TRAN_ID    TRAN_ID P
---------- ---------- -
         1          1 a


   TRAN_ID    TRAN_ID P
---------- ---------- -
         2          1 a

Open in new window


drop table tab1 purge;
create table tab1(tran_id number);

drop table tab2 purge;
create table tab2(tran_id number,problem_id char(1));

insert into tab1 values(1);
insert into tab1 values(2);

insert into tab2 values(1,'a');
commit

SELECT        t1.TRAN_ID, t2.TRAN_ID, t2.PROBLEM_ID
FROM            tab1 t1, tab2 t2
WHERE        t1.TRAN_ID = t2.TRAN_ID
/

SELECT        t1.TRAN_ID, t2.TRAN_ID, t2.PROBLEM_ID
FROM            tab1 t1, tab2 t2
WHERE        t1.TRAN_ID <> t2.TRAN_ID
/

Open in new window

0
 

Expert Comment

by:expert9
Comment Utility
SELECT        GPCOMP1.GPRECL.TRAN_ID, GPCOMP1.GPPROB_RECL.TRAN_ID AS EXPR1, GPCOMP1.GPPROB_RECL.PROBLEM_ID
            FROM            GPCOMP1.GPRECL LEFT OUTER JOIN GPCOMP1.GPPROB_RECL
                              ON GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_ID
                              where GPCOMP1.GPRECL.TRAN_ID <> GPCOMP1.GPPROB_RECL.TRAN_ID


and should be where.... try again
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 11

Accepted Solution

by:
yuching earned 100 total points
Comment Utility
Hi,

try this

a. Using left outer join to select records found in GPCOMP1.GPRECL
  but not in GPCOMP1.GPPROB_RECL

SELECT   GPCOMP1.GPRECL.TRAN_ID, GPCOMP1.GPPROB_RECL.TRAN_ID AS EXPR1, GPCOMP1.GPPROB_RECL.PROBLEM_ID
FROM   GPCOMP1.GPRECL
LEFT OUTER JOIN GPCOMP1.GPPROB_RECL ON
         GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_ID
WHERE        GPCOMP1.GPPROB_RECL.TRAN_ID IS NULL


b. using NOT EXISTS, alternatively you can also try below provided total records in GPCOMP1.GPRECL is less

-- fields for GRPCOMP1_GPPROB_RECL always null because no matching
SELECT   GPCOMP1.GPRECL.TRAN_ID, NULL AS EXPR1, NULL AS PROBLEM_ID
FROM   GPCOMP1.GPRECL
WHERE NOT EXISTS (
      SELECT 1 FROM GPCOMP1.GPPROB_RECL
      WHERE GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_ID
)
0
 

Author Comment

by:thayduck
Comment Utility
Expert9:

Tried your last sample but now get this error,

Msg 208, Level 16, State 1, Line 1
Invalid object name 'GPCOMP1.GPRECL'.

SET @sql= 'SELECT        GPCOMP1.GPRECL.TRAN_ID, GPCOMP1.GPPROB_RECL.TRAN_ID AS EXPR1, GPCOMP1.GPPROB_RECL.PROBLEM_ID
            FROM            GPCOMP1.GPRECL LEFT OUTER JOIN GPCOMP1.GPPROB_RECL
                              ON GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_ID
                              where GPCOMP1.GPRECL.TRAN_ID <> GPCOMP1.GPPROB_RECL.TRAN_ID'
                             
                               --Execute oracle query      EXEC (@sql)


also got this error:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'LEFT'.

When trying this  (put comma in from statement)


 SET @sql= 'SELECT        GPCOMP1.GPRECL.TRAN_ID, GPCOMP1.GPPROB_RECL.TRAN_ID AS EXPR1, GPCOMP1.GPPROB_RECL.PROBLEM_ID
            FROM            GPCOMP1.GPRECL, LEFT OUTER JOIN GPCOMP1.GPPROB_RECL
                              ON GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_ID
                              where GPCOMP1.GPRECL.TRAN_ID <> GPCOMP1.GPPROB_RECL.TRAN_ID'
                             
                               --Execute oracle query and populate temp table
      EXEC (@sql)
0
 

Author Comment

by:thayduck
Comment Utility
yuching:

Get this error

Msg 208, Level 16, State 1, Line 1
Invalid object name 'GPCOMP1.GPRECL'.
 
When trying this

   SET @sql=  'SELECT   GPCOMP1.GPRECL.TRAN_ID, GPCOMP1.GPPROB_RECL.TRAN_ID AS EXPR1, GPCOMP1.GPPROB_RECL.PROBLEM_ID
FROM   GPCOMP1.GPRECL
LEFT OUTER JOIN GPCOMP1.GPPROB_RECL ON
         GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_ID
WHERE        GPCOMP1.GPPROB_RECL.TRAN_ID IS NULL'

 EXEC (@sql)



Get this error

Msg 208, Level 16, State 1, Line 1
Invalid object name 'GPCOMP1.GPRECL'.

When trying this

SET @sql= 'SELECT   GPCOMP1.GPRECL.TRAN_ID, NULL AS EXPR1, NULL AS PROBLEM_ID
FROM   GPCOMP1.GPRECL
WHERE NOT EXISTS (
      SELECT 1 FROM GPCOMP1.GPPROB_RECL
      WHERE GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_ID)'
     
     
  EXEC (@sql)
0
 
LVL 11

Expert Comment

by:yuching
Comment Utility
invalid object name??? They are the same tables you listed in the question.

Can you please check are you able to make a select statement from

1. select * from GPCOMP1.GPRECL
2. select * from GPCOMP1.GPPROB_RECL
0
 

Expert Comment

by:expert9
Comment Utility
are you sure you have privileges of the tables you are querying......
0
 

Author Comment

by:thayduck
Comment Utility
expert9:

This ran (my mistake before) (also changed what i was selecting)
but gave me no records.

SET @sql= 'SELECT GPCOMP1.GPRECL.CUSTNO as Payer, SUBSTR(GPCOMP1.GPRECL.INVNO, 1, 10) AS InvoiceNo
            FROM            GPCOMP1.GPRECL LEFT OUTER JOIN GPCOMP1.GPPROB_RECL
                              ON GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_ID
                              where GPCOMP1.GPRECL.TRAN_ID <> GPCOMP1.GPPROB_RECL.TRAN_ID'




Yuching:

Reran your code (my mistake before)
and it worked. It gave me the the records I wanted
0
 

Author Closing Comment

by:thayduck
Comment Utility
Thanks for everyones help.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now