Solved

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

Posted on 2008-09-30
5
3,232 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
selective queries 7 38
Oracle sql query 7 74
make null the repeated levels 2 31
format dd/mm/yyyy parameter 16 31
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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

829 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