CODEWITHSUNDEEP
sqloracle-databaseoracle11gsubquery
Pedro Ivo
Pedro Ivo

Reputation: 13

Use an Alias or table name in Where Clause Subquery in Oracle,

I need to display some fields from other table in my query and I got this error:

ORA-00904: "THIS_"."ID": identificador inválido 00904. 00000 - "%s: invalid identifier"

Here is the Query

SELECT this_."ID" as ID1_47_2_
FROM "ENSAIO_AMOSTRA" this_
inner join "ETAPA_FLUXO_AMOSTRA" efe1_ on  this_."ID" = efe1_."ID_ENSAIO_AMOSTRA"
inner join "ETAPA" et2_ on efe1_."ID_ETAPA"=et2_."ID"
WHERE et2_."ID" in (5) and not (et2_."ID" = 6)
and this_."ID_ENSAIO" = 835
and efe1_."ID" = (
    select y0_ from
    ( SELECT this_0_."ID" as y0_
      FROM "ETAPA_FLUXO_AMOSTRA" this_0_
      WHERE this_0_."ID_ENSAIO_AMOSTRA" = this_."ID"
      ORDER BY this_0_."ID" desc )
)

If I change this_."ID" for a number (an already existing ensaio_amostra), it works.

What do I do?

Upvotes: 1

Views: 215

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

and efe1_."ID" = (
    select y0_ from
    ( SELECT this_0_."ID" as y0_
      FROM "ETAPA_FLUXO_AMOSTRA" this_0_
      WHERE this_0_."ID_ENSAIO_AMOSTRA" = this_."ID"
      ORDER BY this_0_."ID" desc )
)

You don't need the sub-query since you have already joined the tables using INNER JOIN clause:

FROM "ENSAIO_AMOSTRA" this_
inner join "ETAPA_FLUXO_AMOSTRA" efe1_ on  this_."ID" = efe1_."ID_ENSAIO_AMOSTRA"

Just use:

SELECT this_."ID" AS ID1_47_2_
FROM "ENSAIO_AMOSTRA" this_
INNER JOIN "ETAPA_FLUXO_AMOSTRA" efe1_
ON this_."ID" = efe1_."ID_ENSAIO_AMOSTRA"
INNER JOIN "ETAPA" et2_
ON efe1_."ID_ETAPA"   =et2_."ID"
WHERE et2_."ID"      IN (5)
AND NOT (et2_."ID"    = 6)
AND this_."ID_ENSAIO" = 835;

On a side note, ORDER BY in your sub-query is meaningless. Also, if you get more than one row in the sub-query it would anyway fail.

Upvotes: 1

Related Questions