Home » SQL & PL/SQL » SQL & PL/SQL » Subquery question.....
icon5.gif  Subquery question..... [message #193486] Sun, 17 September 2006 18:29 Go to next message
VAC357
Messages: 21
Registered: June 2006
Location: New York
Junior Member
I'm new to Oracle and have a problem I hope someone can explain.

Why can't I reference a table (i.e. accounts)? I receive an error message when trying to use it in my "select blah" subquery. What am I doing wrong?

Error Message = "accts_sys_id" invalid identifier

Many thanks,

VAC

select ............
FROM employees,
         company,
         accounts,
		( SELECT blah
    FROM blah_rollups
 WHERE blah_tax_id IN ('NP')
 CONNECT BY blah_sys_id_child = PRIOR blah_sys_id_parent
   START WITH blah_sys_id_child = accts_sys_id) DV



[Updated on: Sun, 17 September 2006 19:41]

Report message to a moderator

Re: Subquery question..... [message #193489 is a reply to message #193486] Sun, 17 September 2006 19:30 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I receive an error message
Error? What error?
I don't see any error message.
Re: Subquery question..... [message #193492 is a reply to message #193489] Sun, 17 September 2006 19:42 Go to previous messageGo to next message
VAC357
Messages: 21
Registered: June 2006
Location: New York
Junior Member
"accts_sys_id" invalid identifier is the message, yet I'm using the table in the FROM statement above the subquery
Re: Subquery question..... [message #193493 is a reply to message #193486] Sun, 17 September 2006 19:54 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Oracle error messages contain ORA-?????
Two FROM clauses exits.
Post DESC of all tables involved.
PEBKAC is certain, but details are required to discern what is being done wrong.
You are NOT being charged by the character to post questions.
Why not do CUT & PASTE of the ACTUAL input & response?
Re: Subquery question..... [message #193494 is a reply to message #193493] Sun, 17 September 2006 20:02 Go to previous messageGo to next message
VAC357
Messages: 21
Registered: June 2006
Location: New York
Junior Member
ORA-00904: "accts_SYS_ID": invalid identifier
Re: Subquery question..... [message #193496 is a reply to message #193486] Sun, 17 September 2006 20:08 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
CUT & PASTE of the ACTUAL SQL input & response!
===============================================
SELECT blah
FROM blah_rollups
WHERE blah_tax_id IN ('NP')
CONNECT BY blah_sys_id_child = PRIOR blah_sys_id_parent
START WITH blah_sys_id_child = accts_sys_id) DV
=====================================================
SQL> DESC blah_rollups
---produces what output?

[Updated on: Sun, 17 September 2006 20:09] by Moderator

Report message to a moderator

Re: Subquery question..... [message #193499 is a reply to message #193496] Sun, 17 September 2006 20:24 Go to previous messageGo to next message
VAC357
Messages: 21
Registered: June 2006
Location: New York
Junior Member

SQL> desc blah_rollups
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 blah_SYS_ID                                        NUMBER(12)
 blah_SYS_ID_PARENT                            NUMBER(12)
 blah_SYS_ID_CHILD                             NUMBER(12)
 blah_tax_ID                                       VARCHAR2(2)


query is below:

select ............
FROM employees,
         company,
         accounts,
		( SELECT blah_sys_id_child
    FROM blah_rollups
 WHERE blah_tax_id IN ('NP')
 CONNECT BY blah_sys_id_child = PRIOR blah_sys_id_parent
   START WITH blah_sys_id_child = accts_sys_id) DV

 


Why am I not able to see accts_sys_id in the subquery? I clearly identify the table. I've also looked at other queries similar in design and they work fine.

[Updated on: Mon, 18 September 2006 06:50]

Report message to a moderator

Re: Subquery question..... [message #193500 is a reply to message #193486] Sun, 17 September 2006 20:42 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
> I clearly identify the table.
While you may think that you "clearly identify the table",
neither Oracle nor myself know which table contains "accts_sys_id"; or if it exists at all.
YOYO!
Re: Subquery question..... [message #193589 is a reply to message #193500] Mon, 18 September 2006 06:52 Go to previous messageGo to next message
VAC357
Messages: 21
Registered: June 2006
Location: New York
Junior Member
accts_sys_id is part of accounts. I only typed the part of the query in question. The query works fine if I remove the subquery. Needless to say, thanks for your myoptic comment. Ignorance is truly bliss
Re: Subquery question..... [message #193601 is a reply to message #193589] Mon, 18 September 2006 07:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@Anacedent may be a little acerbic at times, but is generally only saying things that a lot of the rest of us think.

You are being particularly reticent in providing the information that has beend asked for:
Quote:

Post DESC of all tables involved.

The error that you're getting is still uncertain. I personally reckon it's an ORA-00904, but you haven't seen fit to share that information with us.

If I had the actual query, and the table definitions, then I reckon I1 could solve this in about 15 minutes.
As it it, it's been 13 hours so far, and you seem to be trying hard to prevent us from being able to help.

To summarise:
1) Post definitions of the tables involved (Actual CREATE TABLE statements would be best, but I'm not going to hold my breath until they turn up)

2) Post the actual query, unless it's some huge 5 page monstrosity, in which case a summarised version that reproduces the problem would be lovely.

3) Remember that we don't get paid to help you, we're not obliged to help you, and if your entire company folds because you couldn't get this fixed, we won't lose a great deal of sleep - if you won't give us the information we need, we'll just go and answer someone elses questions instead.
Re: Subquery question..... [message #193641 is a reply to message #193601] Mon, 18 September 2006 12:14 Go to previous messageGo to next message
VAC357
Messages: 21
Registered: June 2006
Location: New York
Junior Member
Thank you JRow. I thought I'd provided enough info by posting the location of the error, guess I was wrong. My fault and I apologize. I DO understand the nature and assistance this site provides and am grateful as I'm new to Oracle and often browse this site for tips, etc. This I am appreciative for.

I noticed in a reply he stated you can not have two FROM in a SELECT. I differ in opinion as I've seen it done (someone else code) and work. Perhaps I will examine (when I get the chance) their code a bit more.

I honestly thought by simply posting the 'point of failure' would be simple enough to address. I think scaling down the number of tables and their descriptions (their rather large) would be better, no?

I apologize.

Thank you.

VAC
Re: Subquery question..... [message #193651 is a reply to message #193641] Mon, 18 September 2006 14:22 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
to answer your final question, NO.
to clear up what it means to IDENTIFY the tables - use unique table alias for each and every table in the sql. if a table is in the main query, and again in the subquery, give each a different alias. then prefix each and every column with it's proper table alias, so we can see the problem easier.
also, as stated, paste the EXACT error message received - just copy/paste, that's all.

select ............
FROM employees,
company,
accounts,
( SELECT blah
FROM blah_rollups
WHERE blah_tax_id IN ('NP')
CONNECT BY blah_sys_id_child = PRIOR blah_sys_id_parent
START WITH blah_sys_id_child = accts_sys_id) DV

the problem is that ACCTS_SYS_ID is a column in the ACCOUNTS table - just guessing, since the columns are not prefixed with table names or aliases. the part for blah_rollups is NOT a subquery - it is an in-line view. as such, it follows different rules than subqueries follow. in-line views do not allow you to reference columns from tables in the rest of the query (like you would with a correlated subquery).

since I do not know what columns you ultimately select, or how the 4 tables listed are joined, or even if there are more than 4 tables, I can offer no workarounds. that's why posting the entire query is better than posting a snippet of code.
Re: Subquery question..... [message #193654 is a reply to message #193651] Mon, 18 September 2006 15:19 Go to previous message
VAC357
Messages: 21
Registered: June 2006
Location: New York
Junior Member
shoblock, thanks. Your assumptions/guess were on point. I overlooked the part of 'table alias' and have made the correction(s).

Thanks for the explanation
Previous Topic: what is the difference between 8i and 9i
Next Topic: Nested table or ref.. or what else?
Goto Forum:
  


Current Time: Sun Dec 11 04:17:19 CST 2016

Total time taken to generate the page: 0.04365 seconds