Join syntax? [2 SELECTS in FROM]

Toad for Oracle
1

join syntax? [2 SELECTS in FROM]

2

"jc_name2" (not converted) is not the same as a.jc_name2 (which is converted to "A"."JC_NAME2" internally).

At 03-29-2011 07:04, you wrote:

Hi folks

I have this SQL statement and wish to join jc_name2 from table a with
JC_NAME from table b.
The part without join works.

I tried several things (sadly little try+error). At the moment I have
ORA-00904: "A"."JC_NAME2": invalid identifier

SELECT *
FROM ( SELECT COUNT (action),
action,
SUBSTR (MESSAGE, INSTR (MESSAGE,
'=',
1,
2)

AS "jc_name2"
FROM audit_trail
WHERE TO_CHAR (from_event_time, 'MM.YYYY') =
TO_CHAR (ADD_MONTHS (SYSDATE, -2), 'MM.YYYY')
AND table_name = 'user_jc'
AND MESSAGE LIKE '%SunOS_Sadm%'
GROUP BY action,
SUBSTR (MESSAGE, INSTR (MESSAGE,
'=',
1,
2)

  • 1)) a,
    ( SELECT COUNT (a.user_id), a.jc_name, b.description
    FROM user_jc a, job_code b
    WHERE a.jc_name = b.jc_name AND a.jc_name LIKE '%SunOS_Sadm%'
    GROUP BY a.jc_name, b.description) b
    WHERE a.jc_name2 = b.JC_NAME;

cheers Sven

3

Upper and lower case.

In your first subSelect you name the column AS “jc_name2”. Putting the
column name in double quotes tells Oracle to respect the case of the
column name, so the column name is jc_name2.

In your final Where clause you refer to a.jc_name2 without quotes.
Oracle uppercases column (and table) names in this case, so it’s looking
for a column in your first subSelect called JC_NAME2. And there is no
such column.

I suggest taking the quotes off of the AS clause.

Nate Schroeder
Enterprise Services - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167
314-694-2592

4

Sven,

Try removing the double quotes “ from the jc_name2 alias in the AS
statement. Or adding them in the join.
image001.jpeg

5

Sven,

Try removing the double quotes “ from the jc_name2 alias in the AS
statement. Or adding them in the join.
image001.jpeg

6

I tried several things (sadly little try+error). At the moment

I have ORA-00904: "A"."JC_NAME2": invalid identifier

Don’t put your alias in double quotes.

Change: AS “jc_name2” to: AS jc_name2

A friendly suggestion for your own future sanity:

You might want to try being a little more unique in your use of aliases.
You’ve got 2 different objects aliased as A and 2 different objects
aliased as B.

That could make troubleshooting other issues a bit confusing if the individual
doing the troubleshooting looses track of which object is in scope at which
point.

Roger S.

7

I think you have to use

a.“jc_name2” = b.JC_NAME

instead of

a.jc_name2 = b.JC_NAME (what you have)

8

On Wed, Mar 30, 2011 at 8:27 PM, wrote:

Sven,
Try removing the double quotes “ from the jc_name2 alias in the AS statement. Or adding them in the join.

thanks. That works.

cheers Sven