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;
Problems when using or with the +
-
- 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?
Thanks.
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 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;
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:
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 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;
-
- 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 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;
-
- Information
-
Who is online
Users browsing this forum: Bing [Bot] and 1 guest