thayduck
asked on
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_I D AS EXPR1, GPCOMP1.GPPROB_RECL.PROBLE M_ID
FROM GPCOMP1.GPRECL, GPCOMP1.GPPROB_RECL
WHERE GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_I D
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_I D AS EXPR1, GPCOMP1.GPPROB_RECL.PROBLE M_ID
FROM GPCOMP1.GPRECL, GPCOMP1.GPPROB_RECL
WHERE GPCOMP1.GPRECL.TRAN_ID <> GPCOMP1.GPPROB_RECL.TRAN_I D
What am I doing wrong in the second query ?
Just changed the = to <>.
Why isnt this query running in seconds like the first query.
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_I
FROM GPCOMP1.GPRECL, GPCOMP1.GPPROB_RECL
WHERE GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_I
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_I
FROM GPCOMP1.GPRECL, GPCOMP1.GPPROB_RECL
WHERE GPCOMP1.GPRECL.TRAN_ID <> GPCOMP1.GPPROB_RECL.TRAN_I
What am I doing wrong in the second query ?
Just changed the = to <>.
Why isnt this query running in seconds like the first query.
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.
ASKER
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_I D AS EXPR1, GPCOMP1.GPPROB_RECL.PROBLE M_ID
FROM GPCOMP1.GPRECL, LEFT OUTER JOIN GPCOMP1.GPPROB_RECL
WHERE GPCOMP1.GPRECL.TRAN_ID <> GPCOMP1.GPPROB_RECL.TRAN_I D " for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "GPNFE".
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_I
FROM GPCOMP1.GPRECL, LEFT OUTER JOIN GPCOMP1.GPPROB_RECL
WHERE GPCOMP1.GPRECL.TRAN_ID <> GPCOMP1.GPPROB_RECL.TRAN_I
942: you do not have select permission on the table or view attempted. This has to be granted by the object owner.
ASKER
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.
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.
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:
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
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
/
SELECT GPCOMP1.GPRECL.TRAN_ID, GPCOMP1.GPPROB_RECL.TRAN_I D AS EXPR1, GPCOMP1.GPPROB_RECL.PROBLE M_ID
FROM GPCOMP1.GPRECL LEFT OUTER JOIN GPCOMP1.GPPROB_RECL
ON GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_I D
where GPCOMP1.GPRECL.TRAN_ID <> GPCOMP1.GPPROB_RECL.TRAN_I D
and should be where.... try again
FROM GPCOMP1.GPRECL LEFT OUTER JOIN GPCOMP1.GPPROB_RECL
ON GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_I
where GPCOMP1.GPRECL.TRAN_ID <> GPCOMP1.GPPROB_RECL.TRAN_I
and should be where.... try again
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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_I D AS EXPR1, GPCOMP1.GPPROB_RECL.PROBLE M_ID
FROM GPCOMP1.GPRECL LEFT OUTER JOIN GPCOMP1.GPPROB_RECL
ON GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_I D
where GPCOMP1.GPRECL.TRAN_ID <> GPCOMP1.GPPROB_RECL.TRAN_I D'
--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_I D AS EXPR1, GPCOMP1.GPPROB_RECL.PROBLE M_ID
FROM GPCOMP1.GPRECL, LEFT OUTER JOIN GPCOMP1.GPPROB_RECL
ON GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_I D
where GPCOMP1.GPRECL.TRAN_ID <> GPCOMP1.GPPROB_RECL.TRAN_I D'
--Execute oracle query and populate temp table
EXEC (@sql)
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_I
FROM GPCOMP1.GPRECL LEFT OUTER JOIN GPCOMP1.GPPROB_RECL
ON GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_I
where GPCOMP1.GPRECL.TRAN_ID <> GPCOMP1.GPPROB_RECL.TRAN_I
--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_I
FROM GPCOMP1.GPRECL, LEFT OUTER JOIN GPCOMP1.GPPROB_RECL
ON GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_I
where GPCOMP1.GPRECL.TRAN_ID <> GPCOMP1.GPPROB_RECL.TRAN_I
--Execute oracle query and populate temp table
EXEC (@sql)
ASKER
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_I D AS EXPR1, GPCOMP1.GPPROB_RECL.PROBLE M_ID
FROM GPCOMP1.GPRECL
LEFT OUTER JOIN GPCOMP1.GPPROB_RECL ON
GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_I D
WHERE GPCOMP1.GPPROB_RECL.TRAN_I D 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_I D)'
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, GPCOMP1.GPPROB_RECL.TRAN_I
FROM GPCOMP1.GPRECL
LEFT OUTER JOIN GPCOMP1.GPPROB_RECL ON
GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_I
WHERE GPCOMP1.GPPROB_RECL.TRAN_I
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_I
EXEC (@sql)
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
Can you please check are you able to make a select statement from
1. select * from GPCOMP1.GPRECL
2. select * from GPCOMP1.GPPROB_RECL
are you sure you have privileges of the tables you are querying......
ASKER
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.INVN O, 1, 10) AS InvoiceNo
FROM GPCOMP1.GPRECL LEFT OUTER JOIN GPCOMP1.GPPROB_RECL
ON GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_I D
where GPCOMP1.GPRECL.TRAN_ID <> GPCOMP1.GPPROB_RECL.TRAN_I D'
Yuching:
Reran your code (my mistake before)
and it worked. It gave me the the records I wanted
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.INVN
FROM GPCOMP1.GPRECL LEFT OUTER JOIN GPCOMP1.GPPROB_RECL
ON GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_I
where GPCOMP1.GPRECL.TRAN_ID <> GPCOMP1.GPPROB_RECL.TRAN_I
Yuching:
Reran your code (my mistake before)
and it worked. It gave me the the records I wanted
ASKER
Thanks for everyones help.
in the second query you are doing GPCOMP1.GPRECL.TRAN_ID <> GPCOMP1.GPPROB_RECL.TRAN_I
try this
SELECT GPCOMP1.GPRECL.TRAN_ID, GPCOMP1.GPPROB_RECL.TRAN_I
FROM GPCOMP1.GPRECL LEFT OUTER JOIN GPCOMP1.GPPROB_RECL
ON GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_I
and GPCOMP1.GPRECL.TRAN_ID <> GPCOMP1.GPPROB_RECL.TRAN_I