-
ORA-00904 Invalid column when column exists
Hi I might be missing something obvious here but I'm bashing my head against a wall on this problem.
I have two users A and B, user B has two tables PUB and DETAIL which have SELECT granted to user A and have the following query:
Code:
SELECT
DISTINCT d.adno,d.vno,d.pubno,p.bwnet
FROM B.DETAIL d, B.PUB p
WHERE TO_DATE(d.pubdate,'dd/mm/yy') <= TO_DATE('01-NOV-2004','dd/mm/yy')
AND TO_DATE(d.pubdate,'dd/mm/yy') <= TO_DATE('12-NOV-2004','dd/mm/yy')
AND d.billflag = 'Y'
AND d.adno NOT IN
(SELECT DISTINCT adno
FROM B.DETAIL
WHERE TO_DATE(pubdate,'dd/mm/yy') <= TO_DATE('01-NOV-2004','dd/mm/yy')
AND TO_DATE(pubdate,'dd/mm/yy') <= TO_DATE('12-NOV-2004','dd/mm/yy')
AND (lastbillflag = '1' OR lastbillflag = '999'))
AND d.adno = p.adno
AND d.vno = p.vno
AND d.pubno = p.pubno;
When I run this code as user B (the object owner) it works fine, but when I run it as user A I get
Code:
SELECT DISTINCT d.adno,d.vno,d.pubno,p.bwnet
*
ERROR at line 1:
ORA-00904: invalid column name
Any help would be appreciated
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
Forgot to mention this is 8.1.7.4 on Solaris
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
I know it sounds stupid, but you can describe those tables from user A cant you?
also
Code:
WHERE TO_DATE(d.pubdate,'dd/mm/yy') <= TO_DATE('01-NOV-2004','dd/mm/yy')
AND TO_DATE(d.pubdate,'dd/mm/yy') <= TO_DATE('12-NOV-2004','dd/mm/yy')
Whats the point of the second and?
it it passes the first one, no need to test for the second one and it
Last edited by davey23uk; 11-17-2004 at 01:47 PM.
-
Hi,
yes I can describe the tables.
Appreciate your comment about the crap code (it's from one of our developers) but at present I'm more concerned about why the statement fails when the columns are availiable.
Regards
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
Hi Jim, since the geatest minds on the internet haven't come up with a solution in two days, I'll have go:
- we are talking about a straight query NOT a proc (grants via roles and all that)?
- where does it start to go wrong if you take it apart?
1)
SELECT * FROM B.DETAIL
SELECT * FROM B.PUB
2)
SELECT * FROM B.DETAIL d, B.PUB p
WHERE d.adno = p.adno
AND d.vno = p.vno
AND d.pubno = p.pubno;
3)
orginal query without the NOT IN (SELECT . . . ) clause
-
Hi Dapi,
Sorry about the delay in responding....
The problem seems to lie with the B.PUB table, if I attempt any sort of select including a count(*) I get the same error. but only if I run the queries as user A
This is a straight forward SQL*Plus query and all the grants are direct.
Regards
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
Hmmmmmmm...... baffling.
I'd look next at the data dict for oddities: are the grants really there? does user A have an object called PUB? what does B.PUB look like?
Raise a TAR?
-
Thanks Dapi but I've finally found the problem.
On closer inspection of that table I discovered it has a function based index based on a function in schema A. Directly granting execute to that function to user B now allows user A to run the query.
Can't believe I didn't spot it sooner, D'oh!
Regards
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|