在Oracle的Where子句子查询中使用别名或表名，

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"

ORA-00904:“THIS _”。“ID”:identificadorinválido00904。00000 - “%s:无效标识符”

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.

如果我为一个数字(已经存在的ensaio_amostra)改变了这个_。“ID”,它就可以了。

What do I do?

我该怎么办?

1 个解决方案

#1

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:

您不需要子查询,因为您已使用INNER JOIN子句加入了表:

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.

另外,您的子查询中的ORDER BY是没有意义的。此外,如果您在子查询中获得多行,则无论如何都会失败。

#1

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