2

I have this sql request :

select T1.ID, T2.ID 
from task t1, task t2
join workcase w on W.ID = T1.WORKCASE_ID
where W.ID = 1683964476
and T1.STATE < 501
and T2.STATE = 501

but when I run it, I have this error :

ORA-00904: "T1"."WORKCASE_ID": invalid identifier

What's wrong here ?

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
l0r3nz4cc10
  • 1,237
  • 6
  • 27
  • 50
  • Which RDBMS do you use? Does `Task` table has `WORKCASE_ID` field? – Andrey Gordeev Feb 07 '13 at 11:30
  • What is `from task t1, task t2` supposed to do? There is no join condition between the tables. Are you intending a cross join? How about telling us your table structures and the intention of the query? – Martin Smith Feb 07 '13 at 11:30
  • does `workcase_id` column exists in task – asifsid88 Feb 07 '13 at 11:35
  • I withdraw my suggestion of duplication. Bluefeet has the right answer, and it's nothing to do with double quotemarks. – APC Feb 07 '13 at 12:22

3 Answers3

6

The problem is that you are mixing JOIN syntax. You are using an implicit join (the comma) between T1 and T2 and then you are using explicit JOIN syntax to join to the workcase table.

The JOIN syntax takes precedence over the comma so the alias is not available when you try to use it in the join condition.

If you need to use both, then you would nee to use:

select T1.ID, T2.ID 
from
(
  select T1.ID, T2.ID, T1.WORKCASE_ID
  from task T1, task T2
  where T1.STATE < 501
    and T2.STATE = 501
) t
inner join workcase w 
  on W.ID = T1.WORKCASE_ID
where W.ID = 1683964476

This will allow the implicit join to take place in the subquery and then you will use an explicit join to the workcase table.

Or as @APC states in the comments, this can also be written as a CROSS JOIN:

select T1.ID, T2.ID 
from task T1
cross join task T2
inner join workcase w 
  on W.ID = T1.WORKCASE_ID
where W.ID = 1683964476
  and T1.STATE < 501
  and T2.STATE = 501
Taryn
  • 242,637
  • 56
  • 362
  • 405
  • 3
    This is the correct answer - I need to withdraw my duplicate vote. A simpler solution would be to replace that comma in teh FROM clause with `t1 cross join t2`. It's less typing and it is easier to understand what teh query is actually doing. Plus it keeps all the joins in one syntax. – APC Feb 07 '13 at 12:21
  • @APC you are right, I forgot about using `cross join`, I updated my answer to include that. It was too early. :) – Taryn Feb 07 '13 at 13:42
  • Well I managed to spell "the" as "teh" twice in two sentances. I was typing at lunchtime so I don't even have the "too early" excuse 8-) – APC Feb 07 '13 at 14:45
  • 1
    @APC you have the "I was too busy eating" excuse. – Taryn Feb 07 '13 at 14:46
-1

Invalid Identifier means your table doesn't have that column name(WORKCASE_ID)

Madhav
  • 2,285
  • 3
  • 17
  • 16
-1

what about case sensitivity??

Table has been aliased using "t1" but then referenced as "T1" after that...

Just a suggestion... :)

SQLGuru
  • 1,099
  • 5
  • 14
  • Oracle is only case sensitive if the identifier is in double quotes. That is not teh case in the query but it may be the case when the table was created. – APC Feb 07 '13 at 11:49