April 15, 2013 at 7:51 am
I am looking for the right way to determine a missing service from all similar client accounts. We have six different regions with multiple clients. Only One region has this specific service for all of its regional clients. This service is mandatory, so all client accounts must have/show this service. Each client account has a "Portfolio" containing all the offered services.
What I want to do is to run a query to find all portfolios for Region 1 where this specific service is missing for this one region. Since all Portfolios across the six regions are set up the same way a simple WHERE NOT EXIST statement will return thousands of records that are irrelevant since they don't contain this service already.
The Code I am trying to use is as follows:
SELECT dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT INNER JOIN
dbo.SERVICE_JOIN ON dbo.ACCOUNT.PORT_ROW_ID =
dbo.SERVICE_JOIN.PORTFOLIO_ID INNER JOIN
dbo.SERVICES ON dbo.SERVICE_JOIN.SERVICE_ID =
dbo.SERVICES.ROW_ID INNER JOIN
dbo.CONTACT_JOIN ON dbo.SERVICE_JOIN.SERVICE_ID =
dbo.CONTACT_JOIN.SERVICE_ID INNER JOIN
dbo.CONTACTS ON dbo.CONTACT_JOIN.CONTACT_ID =
dbo.CONTACTS.ROW_ID FULL OUTER JOIN
dbo.NOTES ON dbo.SERVICES.SERVICE_TYPE =
dbo.NOTES.SERVICE_TYPE AND
dbo.ACCOUNT.ACC_ROW_ID = dbo.NOTES.ACCOUNT_ID
WHERE EXISTS
(SELECT dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT WHERE dbo.CLIENT.REGION = 'R1')
WHERE NOT EXISTS
(SELECT dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT
WHERE dbo.DM_SERVICES.SERVICE = 'MISSING_SERVICE')
My reasoning is that I am trying to find the portfolios where the service is missing and trying to restrict the search to the one region so we may add them back in. I have included the full code containing the joins for multiple tables where the combined informaton for the accounts are which is correct. My issue seems to begin after the where clause.
My error message is this:
"Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'WHERE'."
Line 13 ia actually the WHERE NOT EXIST statement.
Any tips and suggestions would be greatly appreciated.
James
April 15, 2013 at 7:55 am
your outer query has Two WHERe statmeents; the second one should be an AND, i think:
SELECT
dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT
INNER JOIN dbo.SERVICE_JOIN
ON dbo.ACCOUNT.PORT_ROW_ID = dbo.SERVICE_JOIN.PORTFOLIO_ID
INNER JOIN dbo.SERVICES
ON dbo.SERVICE_JOIN.SERVICE_ID = dbo.SERVICES.ROW_ID
INNER JOIN dbo.CONTACT_JOIN
ON dbo.SERVICE_JOIN.SERVICE_ID = dbo.CONTACT_JOIN.SERVICE_ID
INNER JOIN dbo.CONTACTS
ON dbo.CONTACT_JOIN.CONTACT_ID = dbo.CONTACTS.ROW_ID
FULL OUTER JOIN dbo.NOTES
ON dbo.SERVICES.SERVICE_TYPE = dbo.NOTES.SERVICE_TYPE
AND dbo.ACCOUNT.ACC_ROW_ID = dbo.NOTES.ACCOUNT_ID
WHERE EXISTS (SELECT
dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT
WHERE dbo.CLIENT.REGION = 'R1')
AND NOT EXISTS (SELECT
dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT
WHERE dbo.DM_SERVICES.SERVICE = 'MISSING_SERVICE')
also, you may have modified an existing query, but as it stands, if this is returning just the one column dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT there is no reason to have lall those other joins...they just slow down the query, and you only need the WHERe...AND
Lowell
April 15, 2013 at 10:15 am
Thanks Lowell for the quick reply. But when I altered the code as you presented it the query returned nothing. I would be satisfied if that were the case but I know that there are account portfolios missing this service.
Best Regards,
April 15, 2013 at 10:33 am
It is difficult to provide correct answers to questions when we can't see what you see.
Please read the first article I reference below in my signature block. It will walk you through the things you need to post and how to post them to get the best answers quickly and get tested code in return.
When asking for sample data, we know that you can't post actual company data but we do expect you to be able to put together sample data that mirros the type of data in your production systems.
April 15, 2013 at 10:49 am
jbulldog (4/15/2013)
Thanks Lowell for the quick reply. But when I altered the code as you presented it the query returned nothing. I would be satisfied if that were the case but I know that there are account portfolios missing this service.Best Regards,
yeah the pseudo code you posted makes it hard to really see any issues.
use the IF code tags, and paste the real query you are testing with , at a minimum.
The suggestion about DDL and sample data would allow any of the volunteers here to give you a working, tested solution, instead of guesses.
you also might consider using CTE's to organize the data , but it really depends on your query.
Lowell
April 16, 2013 at 6:34 am
Hey Lowell this the actual code I ran(below), the only changes were to the table and column names. What was returned was the PORTFOLIO column and 0 rows. As I said before I know there are some portfolios with this missing service. Or to rephrase it there are some portfolios that should have this service but it is now missing. Reducing the number of joins to just three tables necessary seemed to increase the length of the query time immensely.
SELECT
dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT
INNER JOIN dbo.SERVICE_JOIN
ON dbo.ACCOUNT.PORT_ROW_ID = dbo.SERVICE_JOIN.PORTFOLIO_ID
INNER JOIN dbo.SERVICES
ON dbo.SERVICE_JOIN.SERVICE_ID = dbo.SERVICES.ROW_ID
INNER JOIN dbo.CONTACT_JOIN
ON dbo.SERVICE_JOIN.SERVICE_ID = dbo.CONTACT_JOIN.SERVICE_ID
INNER JOIN dbo.CONTACTS
ON dbo.CONTACT_JOIN.CONTACT_ID = dbo.CONTACTS.ROW_ID
FULL OUTER JOIN dbo.NOTES
ON dbo.SERVICES.SERVICE_TYPE = dbo.NOTES.SERVICE_TYPE
AND dbo.ACCOUNT.ACC_ROW_ID = dbo.NOTES.ACCOUNT_ID
WHERE EXISTS (SELECT
dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT
WHERE dbo.CLIENT.REGION = 'R1')
AND NOT EXISTS (SELECT
dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT
WHERE dbo.DM_SERVICES.SERVICE = 'MISSING_SERVICE')
Now removing the NOT from the AND NOT EXISTS statement actually returns the same results. 0 rows.
Is it possible this is the wrong path to a solution?
How do you query to find a missing record that should be present with a criteria to filter the selection then?
April 16, 2013 at 7:38 am
really?
to me, the WHERE just does not look right:
I see two huge issues:
first, each EXISTS seem to reference THREE tables, and there's no joins...i don't even think it should work:
WHERE EXISTS (SELECT
dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT
WHERE dbo.CLIENT.REGION = 'R1')
that's dbo.ACCOUNT from the inner query (maybe?) and dbo.CLIENT.REGION from the outer query, with no criteria to join the two within the EXISTs.,
the same thing with the second EXISTS :
SELECT
dbo.ACCOUNT.PORTFOLIO
FROM dbo.ACCOUNT
WHERE dbo.DM_SERVICES.SERVICE = 'MISSING_SERVICE')
where is dbo.DM_SERVICES joined to dbo.ACCOUNT?
both of those end up being a cross join, whihch i assume makes it slow, and probably never filters much data.
The other thing is, for an exists, i always select a constant, and not a column name for clarity.
i would select a constant in my exists, instead of the column from the outer query;
something like this looks a little better to me.
SELECT
acct.PORTFOLIO
FROM dbo.ACCOUNT acct
INNER JOIN dbo.SERVICE_JOIN
ON acct.PORT_ROW_ID = dbo.SERVICE_JOIN.PORTFOLIO_ID
INNER JOIN dbo.SERVICES
ON dbo.SERVICE_JOIN.SERVICE_ID = dbo.SERVICES.ROW_ID
INNER JOIN dbo.CONTACT_JOIN
ON dbo.SERVICE_JOIN.SERVICE_ID = dbo.CONTACT_JOIN.SERVICE_ID
INNER JOIN dbo.CONTACTS
ON dbo.CONTACT_JOIN.CONTACT_ID = dbo.CONTACTS.ROW_ID
FULL OUTER JOIN dbo.NOTES
ON dbo.SERVICES.SERVICE_TYPE = dbo.NOTES.SERVICE_TYPE
AND acct.ACC_ROW_ID = dbo.NOTES.ACCOUNT_ID
--testing if related data exists or not
WHERE EXISTS (SELECT
1
FROM dbo.CLIENT cl
WHERE cl.REGION = 'R1'
AND cl.?PORTFOLIO? = acct.PORTFOLIO)
--testing if related data exists or not
AND NOT EXISTS (SELECT
1
FROM dbo.DM_SERVICES sv
WHERE sv.SERVICE = 'MISSING_SERVICE'
AND sv.?PORTFOLIO? = acct.PORTFOLIO)
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply