Solved

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

Posted on 2008-09-30
5
3,095 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
Comment Utility
were you able to execute the query by itself (not creating the view)
0
 
LVL 23

Accepted Solution

by:
paquicuba earned 300 total points
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
Great job!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now