fahVB
asked on
where clause
I've this working query and would like to modify it based on these two conditions, is this do- able?
select xntapplications.nProdId in ('2689', '2690', '2692', '2693', '2694', '2695', '2696', '2697', '2703') only for BranchID=4,5, 24
and select xntapplications.nProdId (2694, 2695 ) regardless of BranchID, in other words for all branches
select xntapplications.nProdId in ('2689', '2690', '2692', '2693', '2694', '2695', '2696', '2697', '2703') only for BranchID=4,5, 24
and select xntapplications.nProdId (2694, 2695 ) regardless of BranchID, in other words for all branches
SELECT xntApplications.nAppId as AppID,
dtAppRcvd as AppRecv_Date,
xntapplications.mLoanAmt as Loan_Amount,
xntMGApplications.nProdId,
xntapplications.dtPrelim as RESPA_Date
FROM xntStatusCodes (nolock),
xntApplications (nolock) left outer join xntCustomers (nolock) on xntApplications.nPrimCustId = xntCustomers.nCustId
left outer join xntProducts (nolock) on xntApplications.nProdId = xntProducts.nProdId
left outer join (xntInvPersonnel (nolock) left outer join xntUsers on xntInvPersonnel.cDataEntry = xntUsers.cUserId)
on xntApplications.nAppId = xntInvPersonnel.nAppId
left outer join xntLocations (nolock) on xntApplications.nBranchId = xntLocations.nLocId
left outer join xntapcustomers on xntapcustomers.nAppid = xntApplications.nAppid
left outer join xntMGApplications on xntMGApplications.nAppid = xntApplications.nAppid
left outer join xntRealEstate on xntRealEstate.nAppid = xntApplications.nAppid
left outer join xntPymts on xntPymts.nAppId = xntApplications.nAppId
WHERE xntApplications.cStatus = xntStatusCodes.cSubStatus
and xntapplications.cDecision not in ('d','p')
and xntapplications.cStatus not in ('4005','4006','4010','8000','8010','8020','8030','9005','9006','9007','9008','9999')
and xntapplications.nProdId in ('2689', '2690', '2692', '2693', '2694', '2695', '2696', '2697', '2703')
and xntApplications.nBranchId in ('4','5','24')
and xntapplications.cBooked IS NULL
Kevin Cross
Yes, you can do that using an OR condition.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I had a copy and paste issue in mind where I forgot to delete the '2694' and '2695' from the first condition -- it will work the same, just redundant. Should look like http:#a35515005
here it is
(
(BranchID in (4,5, 24) and xntapplications.nProdId in ('2689', '2690', '2692', '2693', '2694', '2695', '2696', '2697', '2703'))
or
xntapplications.nProdId in ('2694', '2695')
)
(
(BranchID in (4,5, 24) and xntapplications.nProdId in ('2689', '2690', '2692', '2693', '2694', '2695', '2696', '2697', '2703'))
or
xntapplications.nProdId in ('2694', '2695')
)
ASKER
ewangoya, this error is occuring
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near '*'.
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near '*'.
ASKER
mwvisa1 and sarabhai,
it is creating too many dups...
it is creating too many dups...
Sorry the '*' should not be there
SELECT xntApplications.nAppId as AppID,
dtAppRcvd as AppRecv_Date,
xntapplications.mLoanAmt as Loan_Amount,
xntMGApplications.nProdId,
xntapplications.dtPrelim as RESPA_Date
FROM xntStatusCodes (nolock),
xntApplications (nolock) left outer join xntCustomers (nolock) on xntApplications.nPrimCustId = xntCustomers.nCustId
left outer join xntProducts (nolock) on xntApplications.nProdId = xntProducts.nProdId
left outer join (xntInvPersonnel (nolock) left outer join xntUsers on xntInvPersonnel.cDataEntry = xntUsers.cUserId)
on xntApplications.nAppId = xntInvPersonnel.nAppId
left outer join xntLocations (nolock) on xntApplications.nBranchId = xntLocations.nLocId
left outer join xntapcustomers on xntapcustomers.nAppid = xntApplications.nAppid
left outer join xntMGApplications on xntMGApplications.nAppid = xntApplications.nAppid
left outer join xntRealEstate on xntRealEstate.nAppid = xntApplications.nAppid
left outer join xntPymts on xntPymts.nAppId = xntApplications.nAppId
WHERE xntApplications.cStatus = xntStatusCodes.cSubStatus
and xntapplications.cDecision not in ('d','p')
and xntapplications.cStatus not in ('4005','4006','4010','8000','8010','8020','8030','9005','9006','9007','9008','9999')
and ( (xntapplications.nProdId in ('2689', '2690', '2692', '2693', '2694', '2695', '2696', '2697', '2703')
and (xntApplications.nBranchId in ('4','5','24') ))
OR (xntapplications.nProdId (2694, 2695 ) ))
and xntapplications.cBooked IS NULLASKER
ewangoya
Msg 4145, Level 15, State 1, Line 22
An expression of non-boolean type specified in a context where a condition is expected, near ')'.
Msg 4145, Level 15, State 1, Line 22
An expression of non-boolean type specified in a context where a condition is expected, near ')'.
ASKER
Thank you