Why this query does not work in Oracle?

Question

Why this query does not work in Oracle?

Navigation

#1 by (1 votes)

0

I'm translating a SQL Server query to Oracle and found an error that does not make sense and I'm not getting it fixed.

The error displayed is as follows:

ORA-00904: "ITM"."NRO_INT_ITEM": identificador inválido
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Erro na linha: 20 Coluna: 42

Query SQL Server (which works)

SELECT    
FORMAT(ISNULL(DAQ.DTH_DEMANDA,'9999-01-01'), 'yyyy-MM') + '-01' DT_MOV                              
, ITM.NRO_INT_ITEM
, ( SELECT COUNT(DISTINCT SQRY_DAQ.NRO_INT_TAREFA) QTD_DEMANDA       
    FROM  TAREFAS SQRY_DAQ
    INNER JOIN ITEM_DEMANDADO SQRY_ITD
        ON SQRY_DAQ.NRO_INT_TAREFA = SQRY_ITD.NRO_INT_TAREFA                                                
        AND SQRY_DAQ.NRO_INT_TAREFA = DAQ.NRO_INT_TAREFA
        AND SQRY_ITD.NRO_INT_ITEM = ITD.NRO_INT_ITEM
        AND SQRY_ITD.NRO_INT_ITEM = ITM.NRO_INT_ITEM) 
QTD_DEMANDA
, 0 QTD_REQUISICAO
, 0 QTD_COMPRA
FROM  ITEM ITM
LEFT JOIN ITEM_DEMANDADO ITD
ON ITD.NRO_INT_ITEM = ITM.NRO_INT_ITEM
LEFT JOIN TAREFAS DAQ
ON DAQ.NRO_INT_TAREFA = ITD.NRO_INT_TAREFA

Oracle Query (What's wrong)

SELECT    
TO_DATE(NVL(DAQ.DTH_SOME_DATA,'9999-01-01'), 'YYYY-MM') + '-01' DT_MOV                              
, ITM.NRO_INT_ITEM
, ( SELECT COUNT(DISTINCT SQRY_DAQ.NRO_INT_TAREFA) QTD_DEMANDA       
FROM   TAREFAS SQRY_DAQ
INNER  JOIN ITEM_DEMANDADO SQRY_ITD
       ON SQRY_DAQ.NRO_INT_TAREFA = SQRY_ITD.NRO_INT_TAREFA                                             
       AND SQRY_DAQ.NRO_INT_TAREFA = DAQ.NRO_INT_TAREFA
       AND SQRY_ITD.NRO_INT_ITEM = ITD.NRO_INT_ITEM
       AND SQRY_ITD.NRO_INT_ITEM = ITM.NRO_INT_ITEM) 
QTD_UM
, 0 QTD_DOIS
, 0 QTD_TRES
FROM  ITEM ITM
LEFT JOIN ITEM_DEMANDADO ITD
    ON ITD.NRO_INT_ITEM = ITM.NRO_INT_ITEM
LEFT JOIN TAREFAS DAQ
    ON DAQ.NRO_INT_TAREFA = ITD.NRO_INT_TAREFA

sql sql-server oracle

asked by anonymous 12.01.2017 / 12:35

1 answer

Limit dynamic fields in jquery Implement 2 search criteria in the query (Wodpress / MySQL)

score 1 · Accepted Answer

The problem is in your subquery (QTD_UM).

The alias table ITM can not be used in the join condition at that point.

For correction, it would be enough to put the conditions of join that use the columns of the main query in the where clause:

      SELECT COUNT(DISTINCT SQRY_DAQ.NRO_INT_TAREFA) QTD_DEMANDA
      FROM   TAREFAS SQRY_DAQ
      INNER JOIN ITEM_DEMANDADO SQRY_ITD  ON SQRY_DAQ.NRO_INT_TAREFA = SQRY_ITD.NRO_INT_TAREFA
      ---------------------------------------------------
      where SQRY_DAQ.NRO_INT_TAREFA = DAQ.NRO_INT_TAREFA
      and   SQRY_ITD.NRO_INT_ITEM = ITD.NRO_INT_ITEM
      and   SQRY_ITD.NRO_INT_ITEM = ITM.NRO_INT_ITEM
      ---------------------------------------------------

Another problem is in the date mask used.

I do not know how the TAREFAS.DTH_SOME_DATA column is populated, but it is necessary that the format used by the contents of this column and the value used in the nvl command are exactly the format specified in the to_date command.

In this case, the query uses nvl in yyyy-mm-dd format, but it does to_date in yyyy-mm format.