How to define table aliases

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,

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.

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

Select allOpen in new window

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

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

sorry I missed this earlier

>>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
/

Select allOpen in new window

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

Select allOpen in new window

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.

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".

I thank all of you for your help.

The project has now taken a different direction.