[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

where clause

Posted on 2011-05-03
12
Medium Priority
?
243 Views
Last Modified: 2012-05-11
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.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

Open in new window

0
Comment
Question by:fahVB
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35514992
Yes, you can do that using an OR condition.
0
 
LVL 9

Accepted Solution

by:
sarabhai earned 500 total points
ID: 35515000
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 in ('2694' ,'2695'  )  )
and xntapplications.cBooked IS NULL
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 500 total points
ID: 35515005
...
and
(
(xntapplications.nProdId in ('2689', '2690', '2692', '2693', '2696', '2697', '2703')
and xntApplications.nBranchId in ('4','5','24') )
or
xntapplications.nProdId in ('2694','2695')
)
and
 ...
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 500 total points
ID: 35515008
To be more precise:

xntapplications.nProdId in ('2689', '2690', '2692', '2693', '2694', '2695', '2696', '2697', '2703')
and xntApplications.nBranchId in ('4','5','24')


Would become:

((xntapplications.nProdId in ('2689', '2690', '2692', '2693', '2694', '2695', '2696', '2697', '2703')
and xntApplications.nBranchId in ('4','5','24')) or xntapplications.nProdId in ('2694', '2695'))
0
 
LVL 32

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya earned 500 total points
ID: 35515026
try
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 NULL

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35515045
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
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35515193
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')
)
0
 

Author Comment

by:fahVB
ID: 35515251
ewangoya, this error is occuring
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near '*'.
0
 

Author Comment

by:fahVB
ID: 35515281
mwvisa1 and sarabhai,

it is creating too many dups...
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35515316

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 NULL

Open in new window

0
 

Author Comment

by:fahVB
ID: 35515330
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 ')'.
0
 

Author Closing Comment

by:fahVB
ID: 35515378
Thank you
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Screencast - Getting to Know the Pipeline

834 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