• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 765
  • Last Modified:

Oracle query problem

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
thayduck
Asked:
thayduck
  • 6
  • 3
  • 2
  • +2
1 Solution
 
expert9Commented:
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
 
DavidSenior Oracle Database AdministratorCommented:
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
 
thayduckAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
DavidSenior Oracle Database AdministratorCommented:
942:  you do not have select permission on the table or view attempted.  This has to be granted by the object owner.
0
 
thayduckAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
expert9Commented:
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
 
yuchingCommented:
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
 
thayduckAuthor Commented:
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
 
thayduckAuthor Commented:
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
 
yuchingCommented:
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
 
expert9Commented:
are you sure you have privileges of the tables you are querying......
0
 
thayduckAuthor Commented:
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
 
thayduckAuthor Commented:
Thanks for everyones help.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 6
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now