We help IT Professionals succeed at work.

How to define table aliases

Dovberman
Dovberman asked
on
325 Views
Last Modified: 2014-08-22
select letter FROM TableA  
     where letter =  
      (select letter from TableA z,
          TableC y, TableB a, TableA w, TableD a    
          where y.Col2 = a.Col2  
          and z.Col1 = y.Col1  
          and letter_type = 15) and letter_type = 15
          and x.Col2 = a.Col2
          and x.Col1 = w.Col1
        and rownum < 6)

Error: ORA-00904  "w.Col1 " :  invalid identifier

Evidently I have incorrectly defined table aliases.
What have I missed?

Thanks
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Steve WalesSenior Database Administrator
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
johnsoneSenior Oracle DBA
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks,

I am modifying a 3 page sql statement in a legacy application.
What I am showing is a snippet that needs to be fixed.

where should the I add a left parenthesis  ?

Please Use a 'XX' to show where it should go.

select letter FROM TableA  
      where letter =  
       (select letter from TableA z,
          TableC y, TableB a, TableA w, TableD a    
          where y.Col2 = a.Col2  
           and z.Col1 = y.Col1  
          and letter_type = 15) and letter_type = 15
          and x.Col2 = a.Col2
          and x.Col1 = w.Col1
        and rownum < 6)

Where should the 'in' be used to replace the '=' ?

Please use Please Use a 'IN' to show where it should go.
 
Thanks,
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
I have no idea what the intent of the query is, but given what you posted, I would guess what you are looking for is this:

select letter FROM TableA  
      where letter in  
       (select letter from TableA z,
          TableC y, TableB a, TableA w, TableD a    
          where y.Col2 = a.Col2  
           and z.Col1 = y.Col1  
          and letter_type = 15
          and x.Col2 = a.Col2
          and x.Col1 = w.Col1
        and rownum < 6)

But that is just a guess.  Having LETTER_TYPE=15 twice doesn't seem correct.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
If the above solution doesn't point you in the right direction, we'll need more of the select that at least has matching parans.

It is possible the w.Col1 is actually referencing a different table aliased 'w'.

The error is definitely a scope issue.

You cannot use a table alias inside a set of parans outside the set or parans.

Here is an example of both:
SQL> -- not allowed since my_alias is defined in the inner select
SQL> select dummy from
  2  (
  3          select dummy col1 from dual my_alias
  4  )
  5  where my_alias.dummy='X'
  6  /
where my_alias.dummy='X'
      *
ERROR at line 5:
ORA-00904: "MY_ALIAS"."DUMMY": invalid identifier


SQL>
SQL> --This is allowed: trick here since I aliased the inner select with the same alias
SQL> select dummy from
  2  (
  3          select dummy from dual my_alias
  4  ) my_alias
  5  where my_alias.dummy='X'
  6  /

D
-
X

Open in new window

EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
The query is supposed to select all letters of type 15.  

I have no idea why the same table, TableB and TableD  have the same alias 'a'

Amazingly, the query works.

I will try your suggestions tomorrow and send feedback.

Thanks
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
yikes, it's worse than just double a... where is x defined?

sorry I missed this earlier
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>Amazingly, the query works.

Not that amazing.  The tables don't have column names in common so Oracle can figure out what you really mean versus what you ask for...

Change the column name in tab2 to col1 (and in the query) and you'll see the following error:
ORA-00918: column ambiguously defined

drop table tab1 purge;
create table tab1(col1 char(1));

insert into tab1 values('a');

drop table tab2 purge;
create table tab2(col2 char(1));

insert into tab2 values('a');

select a.col1, a.col2 from tab1 a, tab2 a
where a.col1=a.col2
/

Open in new window

PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
My guess is the following (I think x is a mistake and should be z)
SELECT
      letter
FROM TableA z
INNER JOIN TableC y ON  z.Col1 = y.Col1
INNER JOIN TableB b ON  y.Col2 = b.Col2 -- was an a, now b
INNER JOIN TableA w ON  z.Col1 = w.Col1 -- x changed to z
INNER JOIN TableD d ON  z.Col2 = d.Col2 -- was an a, now d; x changed to z
WHERE letter_type = 15
	AND rownum < 6

Open in new window

Author

Commented:
Yes, there were many mistakes.  The sql statement was over complicated.

I used MS Access to build tables and the proper query. MS Access was not available at work.

Selects the Letter Name where the Letter Type is 15.  Joins two parent tables to an intersection table.

SELECT Letters.LTR_NAME  
FROM (Letters
INNER JOIN Work_Item_Letters ON Letters.LTR_ID = Work_Item_Letters.LTR_ID)
INNER JOIN Work_Items ON Work_Item_Letters.WI_ID = Work_Items.WI_ID
WHERE (((Letters.LTR_TYPE)=15));

Thanks for all your help.

Author

Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Dovberman's comment #a40275403
Assisted answer: 100 points for slightwv's comment #a40274660
Assisted answer: 100 points for Steve Wales's comment #a40274663
Assisted answer: 100 points for Sanjoy Chowdhury's comment #a40274672
Assisted answer: 100 points for johnsone's comment #a40274710
Assisted answer: 100 points for PortletPaul's comment #a40275077

for the following reason:

Excellent.  I started just 2 weeks ago. I am finding poor design everywhere in this legacy system. That probably explains why no one has lasted long here.  I have an interview for a different contract this afternoon.  This has happened before when I did not get specific answers during my initial interview. One of the answers when I asked what the application did was, "It moves data from the database to reports".

Author

Commented:
I thank all of you for your help.

The project has now taken a different direction.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.