[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

How to define table aliases

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
0
Dovberman
Asked:
Dovberman
  • 5
  • 3
  • 3
  • +3
5 Solutions
 
slightwv (䄆 Netminder) Commented:
You have an orphaned ')':
and letter_type = 15)

This closes the (SELECT where you define the alias 'w'. Making the reference outside the ')', non existent.
0
 
Steve WalesSenior Database AdministratorCommented:
From an initial scan, your query is syntactically incorrect.

You have a single left parenthesis and 2 right parentheses.

The reference to w.col1 is outside / after the first right paren, so I'm guessing there's a scope issue in there somewhere.
0
 
Sanjoy ChowdhuryCommented:
You have aliased same name as a for two table TableB and TableD, also intsead of = in the master query, replace that with in. In addition to the extra ).
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
johnsoneSenior Oracle DBACommented:
You also have:

where letter =  
      (select
....
and rownum < 6)

You cannot have equality with 5 rows.  It would have to be IN not =.
0
 
DovbermanAuthor 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,
0
 
johnsoneSenior Oracle DBACommented:
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.
0
 
slightwv (䄆 Netminder) 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

0
 
PortletPaulCommented:
unwanted repetition = errorsee this online syntax checker

But once that syntax error is fixed you get this:
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 x.Col2 = a.Col2
			AND x.Col1 = w.Col1
			AND rownum < 6
		)
	/* but 5 letters cannot be equal to one letter */

Open in new window

Why not just go directly to the letters? like this:
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

Open in new window

0
 
DovbermanAuthor 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
0
 
PortletPaulCommented:
yikes, it's worse than just double a... where is x defined?

sorry I missed this earlier
0
 
slightwv (䄆 Netminder) 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

0
 
PortletPaulCommented:
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

0
 
DovbermanAuthor 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.
0
 
DovbermanAuthor 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".
0
 
DovbermanAuthor Commented:
I thank all of you for your help.

The project has now taken a different direction.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now