Problems when using or with the +

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
flebrao
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 116
Joined: Mon, 10 Apr 2006 4:46 pm
Location: Santa Bárbara D'Oeste
Sua vida É o que você É!!!!

Personal I have the select below, but I need to make the lines that contains or and the (commented lines), but because I'm using the + on my WHERE it does not accept or, would anyone know another way to set up this select?

Select all

 
SELECT SFX01.ID_SOLICITACAO 
      ,SFX01.ID_STATUS 
      ,SFX01.DT_SOLICITACAO 
      ,SFX01.DT_APROVACAO 
      ,SFX01.DT_SUPER_APROVACAO 
      ,SFX01.DT_não_APROVACAO 
      ,SFX01.DT_não_SUPER_APROVACAO 
      ,SFX01.DT_EMISSAO 
      ,SFX01.DT_não_EMISSAO 
      ,SFX01.DT_REEMISSAO 
      ,SFX01.DT_CANCELAMENTO 
      ,SFX01.ID_APROVADOR 
      ,SFX01.ID_SUPER_APROVADOR 
      ,SFX01.ID_EMISSOR 
      ,SFX01.PARTY_ID 
      ,SFX01.PARTY_SITE_BILL_ID 
      ,SFX01.PARTY_SITE_SHIP_ID 
      ,SFX01.ID_MASTER_GRUPO 
      ,SFX14.CD_MASTER_GRUPO 
      ,SFX01.ID_GRUPO 
      ,SFX01.CD_REPOSICAO 
      ,SFX01.BATCH_SOURCE_ID 
      ,SFX01.CUST_TRX_TYPE_ID 
      ,SFX01.TERM_ID 
      ,SFX01.TX_DEVOLUCAO_MOTIVO 
      ,SFX01.ID_NFF_ORIGINAL 
      ,SFX01.CD_IMG_SOLICITANTE 
      ,SFX01.CD_IMG_APROVADOR 
      ,SFX01.CD_IMG_DIRETORIA 
      ,SFX01.CD_IMG_EMISSOR 
      ,SFX01.CD_IMG_APROVADOR_CONTABIL 
      ,SFX01.CREATED_BY 
      ,SFX01.CREATION_DATE 
      ,SFX01.LAST_UPDATED_BY 
      ,SFX01.LAST_UPDATE_DATE 
      ,SFX01.ID_NFF 
  FROM R_XXSFX_014 SFX14 
      ,R_XXSFX_001 SFX01 
 WHERE SFX14.ID_MASTER_GRUPO(+) = SFX01.ID_MASTER_GRUPO 
  AND SFX01.ID_STATUS = NVL(30,SFX01.ID_STATUS) 
   AND SFX01.CREATED_BY = NVL(null,SFX01.CREATED_BY) 
   AND SFX14.ID_MASTER_GRUPO = NVL(null, SFX14.ID_MASTER_GRUPO) 
   AND SFX01.ID_SOLICITACAO LIKE '%'||UPPER(REPLACE(TRIM(null),' ','%'))||'%' 
   AND NVL(SFX01.DT_EMISSAO, TRUNC(SYSDATE)) >= NVL(null,TO_DATE('19800101','RRRRMMDD')) 
   AND NVL(SFX01.DT_EMISSAO, TRUNC(SYSDATE)) <= NVL(null,SYSDATE) 
   AND EXISTS (SELECT NULL 
                 FROM DUAL 
				    WHERE SFX01.BATCH_SOURCE_ID IS NULL 
			         AND null IS NULL 
					 UNION ALL 
      			SELECT NULL 
                 FROM RA_BATCH_SOURCES_ALL RBSA2 
					      ,RA_BATCH_SOURCES_ALL RBSA 
					WHERE RBSA2.ATTRIBUTE1 = NVL(null,RBSA2.ATTRIBUTE1) 
					  AND RBSA2.BATCH_SOURCE_ID = TO_NUMBER(RBSA.GLOBAL_ATTRIBUTE1) 
					  AND RBSA.BATCH_SOURCE_ID = SFX01.BATCH_SOURCE_ID 
					  AND SFX01.BATCH_SOURCE_ID IS NOT NULL 
              ) 
  --OR SFX01.ID_EMISSOR = NVL(10453,SFX01.ID_EMISSOR) 
  --AND SFX01.ID_STATUS = 50 
ORDER BY SFX01.ID_SOLICITACAO DESC; 
Thanks.
tom
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 29
Joined: Sun, 11 Jul 2004 8:45 pm
Location: Guarulhos - SP

The case is that OR does not work with Outer Join. In this case type, what I have ever seen is to put the part of the select that has the Outer Join in a subselect and on top of this apply another SELECT, then putting the conditions involved in OR. I believe that your SELECT would look like this:

Select all

 
select select_principal.* from 
( 
SELECT SFX01.ID_SOLICITACAO 
      ,SFX01.ID_STATUS 
      ,SFX01.DT_SOLICITACAO 
      ,SFX01.DT_APROVACAO 
      ,SFX01.DT_SUPER_APROVACAO 
      ,SFX01.DT_não_APROVACAO 
      ,SFX01.DT_não_SUPER_APROVACAO 
      ,SFX01.DT_EMISSAO 
      ,SFX01.DT_não_EMISSAO 
      ,SFX01.DT_REEMISSAO 
      ,SFX01.DT_CANCELAMENTO 
      ,SFX01.ID_APROVADOR 
      ,SFX01.ID_SUPER_APROVADOR 
      ,SFX01.ID_EMISSOR 
      ,SFX01.PARTY_ID 
      ,SFX01.PARTY_SITE_BILL_ID 
      ,SFX01.PARTY_SITE_SHIP_ID 
      ,SFX01.ID_MASTER_GRUPO 
      ,SFX14.CD_MASTER_GRUPO 
      ,SFX01.ID_GRUPO 
      ,SFX01.CD_REPOSICAO 
      ,SFX01.BATCH_SOURCE_ID 
      ,SFX01.CUST_TRX_TYPE_ID 
      ,SFX01.TERM_ID 
      ,SFX01.TX_DEVOLUCAO_MOTIVO 
      ,SFX01.ID_NFF_ORIGINAL 
      ,SFX01.CD_IMG_SOLICITANTE 
      ,SFX01.CD_IMG_APROVADOR 
      ,SFX01.CD_IMG_DIRETORIA 
      ,SFX01.CD_IMG_EMISSOR 
      ,SFX01.CD_IMG_APROVADOR_CONTABIL 
      ,SFX01.CREATED_BY 
      ,SFX01.CREATION_DATE 
      ,SFX01.LAST_UPDATED_BY 
      ,SFX01.LAST_UPDATE_DATE 
      ,SFX01.ID_NFF 
  FROM R_XXSFX_014 SFX14 
      ,R_XXSFX_001 SFX01 
 WHERE SFX14.ID_MASTER_GRUPO(+) = SFX01.ID_MASTER_GRUPO 
  AND SFX01.ID_STATUS = NVL(30,SFX01.ID_STATUS) 
   AND SFX01.CREATED_BY = NVL(null,SFX01.CREATED_BY) 
   AND SFX14.ID_MASTER_GRUPO = NVL(null, SFX14.ID_MASTER_GRUPO) 
   AND SFX01.ID_SOLICITACAO LIKE '%'||UPPER(REPLACE(TRIM(null),' ','%'))||'%' 
   AND NVL(SFX01.DT_EMISSAO, TRUNC(SYSDATE)) >= NVL(null,TO_DATE('19800101','RRRRMMDD')) 
   AND NVL(SFX01.DT_EMISSAO, TRUNC(SYSDATE)) <= NVL(null,SYSDATE) 
) select_principal 
 
   where EXISTS (SELECT NULL 
                 FROM DUAL 
                WHERE select_principal.BATCH_SOURCE_ID IS NULL 
                  AND null IS NULL 
                UNION ALL 
               SELECT NULL 
                 FROM RA_BATCH_SOURCES_ALL RBSA2 
                     ,RA_BATCH_SOURCES_ALL RBSA 
               WHERE RBSA2.ATTRIBUTE1 = NVL(null,RBSA2.ATTRIBUTE1) 
                 AND RBSA2.BATCH_SOURCE_ID = TO_NUMBER(RBSA.GLOBAL_ATTRIBUTE1) 
                 AND RBSA.BATCH_SOURCE_ID = select_principal.BATCH_SOURCE_ID 
                 AND select_principal.BATCH_SOURCE_ID IS NOT NULL 
              ) 
  OR select_principal.ID_EMISSOR = NVL(10453,select_principal.ID_EMISSOR) 
  AND select_principal.ID_STATUS = 50 
   
ORDER BY SFX01.ID_SOLICITACAO DESC; 
tom
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 29
Joined: Sun, 11 Jul 2004 8:45 pm
Location: Guarulhos - SP

Sorry, there failed the code ... If the administrator can remove the message that was doubled ...

The case there is that or does not work with Outer Join. In this case type, what I have ever seen is to put the part of the select that has the Outer Join in a subselect and on top of this apply another SELECT, then putting the conditions involved in OR. I believe that your SELECT would look like this:

Select all

 
select select_principal.* from 
( 
SELECT SFX01.ID_SOLICITACAO 
      ,SFX01.ID_STATUS 
      ,SFX01.DT_SOLICITACAO 
      ,SFX01.DT_APROVACAO 
      ,SFX01.DT_SUPER_APROVACAO 
      ,SFX01.DT_não_APROVACAO 
      ,SFX01.DT_não_SUPER_APROVACAO 
      ,SFX01.DT_EMISSAO 
      ,SFX01.DT_não_EMISSAO 
      ,SFX01.DT_REEMISSAO 
      ,SFX01.DT_CANCELAMENTO 
      ,SFX01.ID_APROVADOR 
      ,SFX01.ID_SUPER_APROVADOR 
      ,SFX01.ID_EMISSOR 
      ,SFX01.PARTY_ID 
      ,SFX01.PARTY_SITE_BILL_ID 
      ,SFX01.PARTY_SITE_SHIP_ID 
      ,SFX01.ID_MASTER_GRUPO 
      ,SFX14.CD_MASTER_GRUPO 
      ,SFX01.ID_GRUPO 
      ,SFX01.CD_REPOSICAO 
      ,SFX01.BATCH_SOURCE_ID 
      ,SFX01.CUST_TRX_TYPE_ID 
      ,SFX01.TERM_ID 
      ,SFX01.TX_DEVOLUCAO_MOTIVO 
      ,SFX01.ID_NFF_ORIGINAL 
      ,SFX01.CD_IMG_SOLICITANTE 
      ,SFX01.CD_IMG_APROVADOR 
      ,SFX01.CD_IMG_DIRETORIA 
      ,SFX01.CD_IMG_EMISSOR 
      ,SFX01.CD_IMG_APROVADOR_CONTABIL 
      ,SFX01.CREATED_BY 
      ,SFX01.CREATION_DATE 
      ,SFX01.LAST_UPDATED_BY 
      ,SFX01.LAST_UPDATE_DATE 
      ,SFX01.ID_NFF 
  FROM R_XXSFX_014 SFX14 
      ,R_XXSFX_001 SFX01 
 WHERE SFX14.ID_MASTER_GRUPO(+) = SFX01.ID_MASTER_GRUPO 
  AND SFX01.ID_STATUS = NVL(30,SFX01.ID_STATUS) 
   AND SFX01.CREATED_BY = NVL(null,SFX01.CREATED_BY) 
   AND SFX14.ID_MASTER_GRUPO = NVL(null, SFX14.ID_MASTER_GRUPO) 
   AND SFX01.ID_SOLICITACAO LIKE '%'||UPPER(REPLACE(TRIM(null),' ','%'))||'%' 
   AND NVL(SFX01.DT_EMISSAO, TRUNC(SYSDATE)) >= NVL(null,TO_DATE('19800101','RRRRMMDD')) 
   AND NVL(SFX01.DT_EMISSAO, TRUNC(SYSDATE)) <= NVL(null,SYSDATE) 
) select_principal 
 
   where EXISTS (SELECT NULL 
                 FROM DUAL 
                WHERE select_principal.BATCH_SOURCE_ID IS NULL 
                  AND null IS NULL 
                UNION ALL 
               SELECT NULL 
                 FROM RA_BATCH_SOURCES_ALL RBSA2 
                     ,RA_BATCH_SOURCES_ALL RBSA 
               WHERE RBSA2.ATTRIBUTE1 = NVL(null,RBSA2.ATTRIBUTE1) 
                 AND RBSA2.BATCH_SOURCE_ID = TO_NUMBER(RBSA.GLOBAL_ATTRIBUTE1) 
                 AND RBSA.BATCH_SOURCE_ID = select_principal.BATCH_SOURCE_ID 
                 AND select_principal.BATCH_SOURCE_ID IS NOT NULL 
              ) 
  OR select_principal.ID_EMISSOR = NVL(10453,select_principal.ID_EMISSOR) 
  AND select_principal.ID_STATUS = 50 
   
ORDER BY SFX01.ID_SOLICITACAO DESC; 
flebrao
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 116
Joined: Mon, 10 Apr 2006 4:46 pm
Location: Santa Bárbara D'Oeste
Sua vida É o que você É!!!!

Thank you very much for the help ... I did otherwise ... But I ended up using your idea in another Select ... Thank you very much ...

Hugs

Fábio Lebrão
tom
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 29
Joined: Sun, 11 Jul 2004 8:45 pm
Location: Guarulhos - SP

I wanted to know how it did, to learn too.
flebrao
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 116
Joined: Mon, 10 Apr 2006 4:46 pm
Location: Santa Bárbara D'Oeste
Sua vida É o que você É!!!!

Tom sorry for the delay to post ... I was kind of out of time ... but it follows below ...

Select all

SELECT   SFX01.ID_SOLICITACAO 
        ,SFX01.ID_STATUS 
        ,SFX01.DT_SOLICITACAO 
        ,SFX01.DT_APROVACAO 
        ,SFX01.DT_SUPER_APROVACAO 
        ,SFX01.DT_não_APROVACAO 
        ,SFX01.DT_não_SUPER_APROVACAO 
        ,SFX01.DT_EMISSAO 
        ,SFX01.DT_não_EMISSAO 
        ,SFX01.DT_REEMISSAO 
        ,SFX01.DT_CANCELAMENTO 
        ,SFX01.ID_APROVADOR 
        ,SFX01.ID_SUPER_APROVADOR 
        ,SFX01.ID_EMISSOR 
        ,SFX01.PARTY_ID 
        ,SFX01.PARTY_SITE_BILL_ID 
        ,SFX01.PARTY_SITE_SHIP_ID 
        ,SFX01.ID_MASTER_GRUPO 
        ,SFX14.CD_MASTER_GRUPO 
        ,SFX01.ID_GRUPO 
        ,SFX01.CD_REPOSICAO 
        ,SFX01.BATCH_SOURCE_ID 
        ,SFX01.CUST_TRX_TYPE_ID 
        ,SFX01.TERM_ID 
        ,SFX01.TX_DEVOLUCAO_MOTIVO 
        ,SFX01.ID_NFF_ORIGINAL 
        ,SFX01.CD_IMG_SOLICITANTE 
        ,SFX01.CD_IMG_APROVADOR 
        ,SFX01.CD_IMG_DIRETORIA 
        ,SFX01.CD_IMG_EMISSOR 
        ,SFX01.CD_IMG_APROVADOR_CONTABIL 
        ,SFX01.CREATED_BY 
        ,SFX01.CREATION_DATE 
        ,SFX01.LAST_UPDATED_BY 
        ,SFX01.LAST_UPDATE_DATE 
        ,SFX01.ID_NFF 
    FROM R_XXSFX_014 SFX14 
        ,R_XXSFX_001 SFX01 
   WHERE SFX14.ID_MASTER_GRUPO(+) = SFX01.ID_MASTER_GRUPO 
     AND R_XXSFX_005_K.CAN_VIEW(SFX01.ID_STATUS 
                               ,SFX01.CREATED_BY 
                               ,SFX01.ID_APROVADOR 
                               ,SFX01.ID_APROVADOR_CONTABIL 
                               ,SFX01.ID_SUPER_APROVADOR 
                               ,SFX01.ID_EMISSOR 
                               ) = 'TRUE' 
     AND SFX01.ID_STATUS = NVL(P_ID_STATUS, SFX01.ID_STATUS) 
     AND SFX01.CREATED_BY = NVL(P_CREATED_BY, SFX01.CREATED_BY) 
     AND SFX14.ID_MASTER_GRUPO = NVL(P_NATUREZA_OPERACAO ,SFX14.ID_MASTER_GRUPO) 
     AND SFX01.ID_SOLICITACAO LIKE '%' || UPPER(REPLACE(TRIM(P_FILTER), ' ' ,'%')) || '%' 
     AND NVL(TRUNC(SFX01.DT_EMISSAO) ,TRUNC(SYSDATE) >= NVL(P_START_DATE, TO_DATE('19800101' ,'RRRRMMDD')) 
     AND NVL(TRUNC(SFX01.DT_EMISSAO) ,TRUNC(SYSDATE)) <= NVL(P_END_DATE ,SYSDATE) 
     AND EXISTS (SELECT NULL 
                   FROM DUAL 
                  WHERE P_LOCAL_EMISSAO IS NULL 
                 UNION ALL 
                 SELECT NULL 
                   FROM RA_BATCH_SOURCES_ALL RBSA2 
                       ,RA_BATCH_SOURCES_ALL RBSA 
                  WHERE RBSA2.ATTRIBUTE1 = P_LOCAL_EMISSAO 
                    AND RBSA2.BATCH_SOURCE_ID = TO_NUMBER(RBSA.GLOBAL_ATTRIBUTE1) 
                    AND RBSA.BATCH_SOURCE_ID = SFX01.BATCH_SOURCE_ID 
                    AND SFX01.BATCH_SOURCE_ID IS NOT NULL 
                    AND ROWNUM = 1 
                ) 
     AND EXISTS (SELECT NULL 
                   FROM DUAL 
                  WHERE P_EMITENTE IS NULL 
                 UNION ALL 
                 SELECT NULL 
                   FROM R_AR_026 AR026 
                  WHERE SFX01.ID_EMISSOR = AR026.USER_ID 
                    AND SFX01.ID_EMISSOR = TO_NUMBER(P_EMITENTE) 
                    AND ROWNUM = 1 
                ) 
ORDER BY SFX01.ID_SOLICITACAO DESC;
tom
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 29
Joined: Sun, 11 Jul 2004 8:45 pm
Location: Guarulhos - SP

Interesting ... Just out of curiosity, checked the two options performance?
flebrao
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 116
Joined: Mon, 10 Apr 2006 4:46 pm
Location: Santa Bárbara D'Oeste
Sua vida É o que você É!!!!

Tom .. It worked perfectly ....
Post Reply
  • Information
  • Who is online

    Users browsing this forum: Bing [Bot] and 1 guest