Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle query problem

Posted on 2012-03-25
14
Medium Priority
?
756 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 400 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

636 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