Solved

Oracle query problem

Posted on 2012-03-25
14
698 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +2
14 Comments
 

Expert Comment

by:expert9
ID: 37763007
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
ID: 37763312
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
ID: 37763356
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 23

Expert Comment

by:David
ID: 37763371
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
ID: 37763519
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37763959
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
ID: 37764069
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
 
LVL 11

Accepted Solution

by:
yuching earned 100 total points
ID: 37764394
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
ID: 37765394
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
ID: 37765440
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
ID: 37766077
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
ID: 37766328
are you sure you have privileges of the tables you are querying......
0
 

Author Comment

by:thayduck
ID: 37767884
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
ID: 37767895
Thanks for everyones help.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql 2016 data tools breakdown.. 1 28
Oracle Errors 11 52
the way to learn Microsoft BI 13 51
T-SQL: problem comparing datetime 4 51
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

730 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