# Join syntax? \[2 SELECTS in FROM\] **URL:** \ **Category:** Toad for Oracle & DevOps Toolkit\ **Created:** [March 30, 2011, 1:24pm UTC](https://forums.toadworld.com/t/join-syntax-2-selects-in-from/10348 "2011-03-30T13:24:46Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\ **Post date:** [March 30, 2011, 1:24pm UTC](https://forums.toadworld.com/t/join-syntax-2-selects-in-from/10348/1 "2011-03-30T13:24:46Z")
join syntax? [2 SELECTS in FROM] ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\ **Post date:** [March 30, 2011, 1:24pm UTC](https://forums.toadworld.com/t/join-syntax-2-selects-in-from/10348/2 "2011-03-30T13:24:47Z")
"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) > > - > 1. > > 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 ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\ **Post date:** [March 30, 2011, 1:24pm UTC](https://forums.toadworld.com/t/join-syntax-2-selects-in-from/10348/3 "2011-03-30T13:24:47Z")
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 ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\ **Post date:** [March 30, 2011, 1:27pm UTC](https://forums.toadworld.com/t/join-syntax-2-selects-in-from/10348/4 "2011-03-30T13:27:39Z")
Sven, Try removing the double quotes “ from the jc\_name2 alias in the AS statement. Or adding them in the join. ![image001.jpeg](https://global.discourse-cdn.com/quest/original/1X/278be512a68050c664416c8c1dd90a338bc191af.jpeg) ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\ **Post date:** [March 30, 2011, 1:27pm UTC](https://forums.toadworld.com/t/join-syntax-2-selects-in-from/10348/5 "2011-03-30T13:27:39Z")
Sven, Try removing the double quotes “ from the jc\_name2 alias in the AS statement. Or adding them in the join. ![image001.jpeg](https://global.discourse-cdn.com/quest/original/1X/e8288ef7bf69daaab6959f752b25ca292478b911.jpeg) ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\ **Post date:** [March 30, 2011, 2:25pm UTC](https://forums.toadworld.com/t/join-syntax-2-selects-in-from/10348/6 "2011-03-30T14:25:42Z")
> > 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. ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\ **Post date:** [March 30, 2011, 2:40pm UTC](https://forums.toadworld.com/t/join-syntax-2-selects-in-from/10348/7 "2011-03-30T14:40:16Z")
I think you have to use a.“jc\_name2” = b.JC\_NAME instead of a.jc\_name2 = b.JC\_NAME (what you have) ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\ **Post date:** [March 31, 2011, 6:14am UTC](https://forums.toadworld.com/t/join-syntax-2-selects-in-from/10348/8 "2011-03-31T06:14:20Z")
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