Solved

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

Posted on 2008-09-30
5
3,357 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

717 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