CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Pulkit Bhatia
Pulkit Bhatia

Reputation: 127

combining or & AND in Oracle SQL

How can I combine OR & AND in SQL? In below query, I need the conditioned statement daction to be 'Reject' with all values OR daction to be 'Approve' that has 'APPROVALTYPE' as NOT NULL values

Below is my query:

SELECT WorkflowHistory.*,
       Reason.Reason,
       ApprovalType.ApprovalType 
FROM   WorkflowHistory WorkflowHistory,
       Reason Reason,
       ApprovalType ApprovalType
WHERE  UPPER(dDocName) = UPPER('D_1188259')
AND    xDocApproval    = ApprovalType.ApprovalTypeID(+)
AND    daction  like 'Reject'
OR (daction ='Approve'
AND APPROVALTYPE IS NOT NULL);

Please help me.

Upvotes: 0

Views: 105

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Use parentheses:

AND 
(
   daction = 'Reject'
 OR 
   (daction ='Approve' AND ApprovalType.ApprovalType IS NOT NULL)
)

This gives you all approvals that have an approval type plus all rejections.

However, please check your joins. There are three tables, but I don't see joins for all of them. This may be on purpose (desired cross join), but better check this. (It would be better anyhow to use ANSI join syntax to avoid accidental cross joins and enhance readbility.) I suppose neither daction nor dDocName are in table ApprovalType? Otherwise you would break your outer join. When working with more than one table use qualifiers to show where the fields come from - again to avoid errors and enhance readability. Just my 5c :-)

Upvotes: 1

San
San

Reputation: 4538

This way

SELECT WorkflowHistory.*,
Reason.Reason,
ApprovalType.ApprovalType 
FROM WorkflowHistory WorkflowHistory,
Reason Reason,
ApprovalType ApprovalType
WHERE UPPER(dDocName) = UPPER('D_1188259')
AND xDocApproval      = ApprovalType.ApprovalTypeID(+)
AND (daction            like 'Reject'
          OR (daction ='Approve' AND APPROVALTYPE IS NOT NULL));

Upvotes: 2

Related Questions