Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4155
  • Last Modified:

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

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
lamishael
Asked:
lamishael
1 Solution
 
sventhanCommented:
were you able to execute the query by itself (not creating the view)
0
 
paquicubaCommented:
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
 
MikeOM_DBACommented:

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
 
lamishaelAuthor Commented:
I´m away from the pc running this DB now, but I'll try your solution when I get there.
0
 
lamishaelAuthor Commented:
Great job!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Tackle projects and never again get stuck behind a technical roadblock.
Join Now