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.
thayduckProgrammer AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
thayduckProgrammer AnalystAuthor 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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

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
thayduckProgrammer AnalystAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thayduckProgrammer AnalystAuthor 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
thayduckProgrammer AnalystAuthor 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
thayduckProgrammer AnalystAuthor 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
thayduckProgrammer AnalystAuthor Commented:
Thanks for everyones help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.