Solved

ORA-01799: a column may not be outer-joined to a subquery

Posted on 2008-09-30
5
3,266 Views
Last Modified: 2013-12-07
Hi, i am converting this Firebird view to Oracle and I'm getting this error: ORA-01799: a column may not be outer-joined to a subquery. Seems like I should remove the outer-join but I failing to do it. It wasnt me who wrote this but I think the problem is with the last subquery, which is meant to retrieve only the first row of a customer contacts table. Any ideias of how to fix this and make it run on oracle?
CREATE VIEW FB_ORACLE.VIEW_PEDIDOSAIDA(
  PK_PEDIDONOTA,
  FK_EMPRESA,
  FK_CLIFOR,
  FK_OPERNOTA,
  FK_CONDPAGTO,
  FK_TRANSP,
  FK_TRANSPREDESP,
  FK_PEDIDONOTAIMPOSTO,
  NUMERO,
  DESDOBRAMENTOIPI,
  TOTALPRODUTO,
  TOTALSERVICO,
  TOTALDESCONTOITEM,
  TOTALGERAL,
  PERCDESCONTO,
  PERCENCARGOFINAN,
  FK_ENDENTREGA,
  FK_ENDCOMERCIAL,
  TIPOFRETE,
  VLRFRETE,
  VLRSEGURO,
  VLROUTRASDESP,
  DATAGERACAO,
  HORAGERACAO,
  PESOLIQUIDO,
  PESOBRUTO,
  FK_INDICE,
  SEUPEDIDO,
  VLRFIXOINDICE,
  OBSPEDIDO,
  FK_PEDIDOSAIDASTATUS,
  FK_TABPRECO,
  DATAVALIDADE,
  ENDERECO_COBRANCA,
  NUMERO_COBRANCA,
  COMPLEMENTO_COBRANCA,
  BAIRRO_COBRANCA,
  CEP_COBRANCA,
  MUNICIPIO_COBRANCA,
  ESTADO_COBRANCA,
  NOME_CONTATO,
  RAMAL_CONTATO,
  FONE_CONTATO,
  FK_CONTATOCLIFOR)
AS
SELECT PN.PK_PEDIDONOTA,
       PN.FK_EMPRESA,
       PN.FK_CLIFOR,
       PN.FK_OPERNOTA,
       PN.FK_CONDPAGTO,
       PN.FK_TRANSP,
       PN.FK_TRANSPREDESP,
       PN.FK_PEDIDONOTAIMPOSTO,
       PN.NUMERO,
       PN.DESDOBRAMENTOIPI,
       PN.TOTALPRODUTO,
       PN.TOTALSERVICO,
       PN.TOTALDESCONTOITEM,
       PN.TOTALGERAL,
       PN.PERCDESCONTO,
       PN.PERCENCARGOFINAN,
       PS.FK_ENDENTREGA,
       PN.FK_ENDCOMERCIAL,
       PN.TIPOFRETE,
       PN.VLRFRETE,
       PN.VLRSEGURO,
       PN.VLROUTRASDESP,
       PN.DATAGERACAO,
       PN.HORAGERACAO,
       PN.PESOLIQUIDO,
       PN.PESOBRUTO,
       P.FK_INDICE,
       P.SEUPEDIDO,
       P.VLRFIXOINDICE,
       P.OBSPEDIDO,
       PS.FK_PEDIDOSAIDASTATUS,
       PS.FK_TABPRECO,
       PS.DATAVALIDADE,
       AECF.ENDERECO,
       AECF.NUMERO,
       AECF.COMPLEMENTO,
       AECF.BAIRRO,
       AECF.CEP,
       AM.MUNICIPIO,
       AECF.FK_UF,
       ACCF.NOME,
       ACCF.RAMAL,
       ACCF.FONE,
       PS.FK_CONTATOCLIFOR
  FROM FB_ORACLE.PEDIDONOTA PN
       INNER JOIN
       FB_ORACLE.PEDIDO P ON (P.PK_PEDIDONOTA = PN.PK_PEDIDONOTA)
       INNER JOIN
       FB_ORACLE.PEDIDOSAIDA PS ON (PS.PK_PEDIDONOTA = P.PK_PEDIDONOTA)
       LEFT JOIN
       FB_ORACLE.ALLENDCLIFOR AECF ON (AECF.FK_CLIFOR = PN.FK_CLIFOR) AND (AECF.ENDDEFAULT = '1' AND AECF.COBRANCA = '1')
       LEFT JOIN
       FB_ORACLE.ALLMUNICIPIO AM ON (AM.PK_MUNICIPIO = AECF.FK_MUNICIPIO)
       LEFT JOIN
       FB_ORACLE.ALLCONTATOCLIFOR ACCF ON (ACCF.FK_CLIFOR = PN.FK_CLIFOR) AND (ACCF.PADRAO = '1' AND
       
       /* PROBLEM SHOULD BE HERE */
       ACCF.PK_CONTATOCLIFOR = (SELECT PK_CONTATOCLIFOR FROM FB_ORACLE.ALLCONTATOCLIFOR ALLCCF WHERE ROWNUM = 1 AND ALLCCF.PADRAO = '1' AND ALLCCF.FK_CLIFOR = PN.FK_CLIFOR)
);

Open in new window

0
Comment
Question by:lamishael
5 Comments
 
LVL 18

Expert Comment

by:sventhan
ID: 22606143
were you able to execute the query by itself (not creating the view)
0
 
LVL 23

Accepted Solution

by:
paquicuba earned 300 total points
ID: 22606182
Try this:

CREATE VIEW FB_ORACLE.VIEW_PEDIDOSAIDA(
  PK_PEDIDONOTA,
  FK_EMPRESA,
  FK_CLIFOR,
  FK_OPERNOTA,
  FK_CONDPAGTO,
  FK_TRANSP,
  FK_TRANSPREDESP,
  FK_PEDIDONOTAIMPOSTO,
  NUMERO,
  DESDOBRAMENTOIPI,
  TOTALPRODUTO,
  TOTALSERVICO,
  TOTALDESCONTOITEM,
  TOTALGERAL,
  PERCDESCONTO,
  PERCENCARGOFINAN,
  FK_ENDENTREGA,
  FK_ENDCOMERCIAL,
  TIPOFRETE,
  VLRFRETE,
  VLRSEGURO,
  VLROUTRASDESP,
  DATAGERACAO,
  HORAGERACAO,
  PESOLIQUIDO,
  PESOBRUTO,
  FK_INDICE,
  SEUPEDIDO,
  VLRFIXOINDICE,
  OBSPEDIDO,
  FK_PEDIDOSAIDASTATUS,
  FK_TABPRECO,
  DATAVALIDADE,
  ENDERECO_COBRANCA,
  NUMERO_COBRANCA,
  COMPLEMENTO_COBRANCA,
  BAIRRO_COBRANCA,
  CEP_COBRANCA,
  MUNICIPIO_COBRANCA,
  ESTADO_COBRANCA,
  NOME_CONTATO,
  RAMAL_CONTATO,
  FONE_CONTATO,
  FK_CONTATOCLIFOR)
AS
SELECT PN.PK_PEDIDONOTA,
       PN.FK_EMPRESA,
       PN.FK_CLIFOR,
       PN.FK_OPERNOTA,
       PN.FK_CONDPAGTO,
       PN.FK_TRANSP,
       PN.FK_TRANSPREDESP,
       PN.FK_PEDIDONOTAIMPOSTO,
       PN.NUMERO,
       PN.DESDOBRAMENTOIPI,
       PN.TOTALPRODUTO,
       PN.TOTALSERVICO,
       PN.TOTALDESCONTOITEM,
       PN.TOTALGERAL,
       PN.PERCDESCONTO,
       PN.PERCENCARGOFINAN,
       PS.FK_ENDENTREGA,
       PN.FK_ENDCOMERCIAL,
       PN.TIPOFRETE,
       PN.VLRFRETE,
       PN.VLRSEGURO,
       PN.VLROUTRASDESP,
       PN.DATAGERACAO,
       PN.HORAGERACAO,
       PN.PESOLIQUIDO,
       PN.PESOBRUTO,
       P.FK_INDICE,
       P.SEUPEDIDO,
       P.VLRFIXOINDICE,
       P.OBSPEDIDO,
       PS.FK_PEDIDOSAIDASTATUS,
       PS.FK_TABPRECO,
       PS.DATAVALIDADE,
       AECF.ENDERECO,
       AECF.NUMERO,
       AECF.COMPLEMENTO,
       AECF.BAIRRO,
       AECF.CEP,
       AM.MUNICIPIO,
       AECF.FK_UF,
       ACCF.NOME,
       ACCF.RAMAL,
       ACCF.FONE,
       PS.FK_CONTATOCLIFOR
  FROM FB_ORACLE.PEDIDONOTA PN
       INNER JOIN
       FB_ORACLE.PEDIDO P ON (P.PK_PEDIDONOTA = PN.PK_PEDIDONOTA)
       INNER JOIN
       FB_ORACLE.PEDIDOSAIDA PS ON (PS.PK_PEDIDONOTA = P.PK_PEDIDONOTA)
       LEFT JOIN
       FB_ORACLE.ALLENDCLIFOR AECF ON (AECF.FK_CLIFOR = PN.FK_CLIFOR) AND (AECF.ENDDEFAULT = '1' AND AECF.COBRANCA = '1')
       LEFT JOIN
       FB_ORACLE.ALLMUNICIPIO AM ON (AM.PK_MUNICIPIO = AECF.FK_MUNICIPIO)
       LEFT JOIN
       FB_ORACLE.ALLCONTATOCLIFOR ACCF

ON (ACCF.FK_CLIFOR = PN.FK_CLIFOR)
WHERE ACCF.PADRAO = '1'
AND ACCF.PK_CONTATOCLIFOR = (SELECT PK_CONTATOCLIFOR FROM FB_ORACLE.ALLCONTATOCLIFOR ALLCCF WHERE ROWNUM = 1 AND ALLCCF.PADRAO = '1' AND ALLCCF.FK_CLIFOR = PN.FK_CLIFOR)
/
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 22606249

That piece of code makes no sense, you are comparing a primary key to itself!

You may need to take that comparison away.

-- ETC ---
       LEFT JOIN
       FB_ORACLE.ALLCONTATOCLIFOR ACCF
---       ^ This table is the same as in the sub-query!
          ON (ACCF.FK_CLIFOR = PN.FK_CLIFOR)
         AND (ACCF.PADRAO = '1'
         AND ACCF.PK_CONTATOCLIFOR = (
            SELECT PK_CONTATOCLIFOR
--- - - - - - -     ^ This key is from the same table as the JOIN!
              FROM FB_ORACLE.ALLCONTATOCLIFOR ALLCCF
--- - - - - - -     ^ This table is same table as the JOIN!
             WHERE ROWNUM = 1
               AND ALLCCF.PADRAO = '1'
               AND ALLCCF.FK_CLIFOR = PN.FK_CLIFOR)
);

Open in new window

0
 

Author Comment

by:lamishael
ID: 22607598
I´m away from the pc running this DB now, but I'll try your solution when I get there.
0
 

Author Closing Comment

by:lamishael
ID: 31501552
Great job!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question