Why does this query not work on Oracle?

Question

Why does this query not work on Oracle?

Asked 7 years, 9 months ago

Viewed 844 times

0

I am translating a query from SQL Server to Oracle and found an error that makes no sense and I am unable to fix.

The error shown 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 (That 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

Query Oracle (Which is in trouble)

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

The alias is between quotes is not ? LEFT JOIN ITEM_DEMANDADO "ITD"

– Diego Souza

2017/01/12 at 12:22
The ITEM table Item by column . NRO_INT_ITEM ?

– Motta

2017/01/12 at 12:25
@Zooboomafoo Depends, if the name does not contain spaces you do not need to use quotes, so it can be ISSO_EH_UM_ALIAS, but it can never be THAT IS AN ALIAS, so you need to put in quotes to be able to use 'THIS IS AN ALIAS'

– Andrey Hartung

2017/01/12 at 12:37
@Motta Yes, he has that column :/

– Andrey Hartung

2017/01/12 at 12:37
There is no missing comma before QTD_UM?

– Jéf Bueno

2017/01/12 at 12:41
Is there any way you can make the table creation script available? Only with the necessary columns, of course. Then we can test, otherwise it gets bad.

– Jéf Bueno

2017/01/12 at 12:44
If you take out the alias the query works ?

– Diego Souza

2017/01/12 at 12:55
@Zooboomafoo No oracle no

– Andrey Hartung

2017/01/12 at 13:07
@jbueno n man, that’s the name of select http://prntscr.com/duoqv0

– Andrey Hartung

2017/01/12 at 13:09
It’s true. I’m not used to not using the AS.

– Jéf Bueno

2017/01/12 at 13:10
@Andreyhartung I don’t know if this is the case or if there would be the possibility to use it, but Oracle Golden Gate allows you to migrate the entire bank in real time and without the need to stop it.

– Guilherme Reis

2017/01/13 at 16:09

Show 6 more comments

1 answer

Browser other questions tagged sql sql-server oracle translating

You are not signed in. Login or sign up in order to post.

by R. Genaro • 36 points · Answer 1 · 2017-01-13T13:45:02+00:00

The problem is in your subquery (QTD_UM).

The table of alias ITM cannot be used in Join condition at that point.

For correction, simply put the Join conditions that use the main query columns in the clause where:

      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 don’t know how the column is populated TAREFAS.DTH_SOME_DATA, but it is necessary that the format used by the contents of this column and the value used in the command nvl are exactly in the format specified in the command to_date.

In this case, the consultation uses a nvl in format yyyy-mm-dd, but achieves the to_date in format yyyy-mm.