October 12, 2010 at 2:22 pm
I am trying to execute the following statement.
SELECT
CSP.Provider_Address1,
CSP.Provider_Address2,
CSP.Provider_City,
CSP.Provider_State,
CSP.Provider_Zip,
CSP.Provider_County,
CSP.Provider_Email,
CSP.Provider_Web_Addr
FROM dbo.CMD_Staging_Providers CSP
INNER JOIN dbo.Providers P
ON P.Provider_NPI = CSP.Provider_NPI#
AND P.Provider_Tax_ID = CSP.Provider_TAXID
WHERE CSP.Has_Errors = 0 AND
CSP.Provider_Address1 NOT IN ( SELECT Street FROM dbo.Provider_Addresses )
AND CSP.Provider_Address2 NOT IN ( SELECT Street_2 FROM dbo.Provider_Addresses )
AND CSP.Provider_City NOT IN ( SELECT City FROM dbo.Provider_Addresses )
AND CSP.Provider_Zip NOT IN ( SELECT Zip_Code FROM dbo.Provider_Addresses )
AND CSP.Provider_Phone NOT IN ( SELECT Phone FROM dbo.Provider_Addresses )
AND (
SELECT S.State_ID FROM dbo.States S INNER JOIN dbo.CMD_Staging_Providers CSP
ON S.State_Abbr = CSP.Provider_State
WHERE S.State_ID NOT IN ( SELECT State_ID FROM dbo.Provider_Addresses ))
And getting the error given below :
An expression of non-boolean type specified in a context where a condition is expected, near 'SELECT'.
please help
October 12, 2010 at 2:54 pm
your synax is missing some condition here:
SELECT
CSP.Provider_Address1,
CSP.Provider_Address2,
CSP.Provider_City,
CSP.Provider_State,
CSP.Provider_Zip,
CSP.Provider_County,
CSP.Provider_Email,
CSP.Provider_Web_Addr
FROM dbo.CMD_Staging_Providers CSP
INNER JOIN dbo.Providers P
ON P.Provider_NPI = CSP.Provider_NPI#
AND P.Provider_Tax_ID = CSP.Provider_TAXID
WHERE CSP.Has_Errors = 0 AND
CSP.Provider_Address1 NOT IN ( SELECT Street FROM dbo.Provider_Addresses )
AND CSP.Provider_Address2 NOT IN ( SELECT Street_2 FROM dbo.Provider_Addresses )
AND CSP.Provider_City NOT IN ( SELECT City FROM dbo.Provider_Addresses )
AND CSP.Provider_Zip NOT IN ( SELECT Zip_Code FROM dbo.Provider_Addresses )
AND CSP.Provider_Phone NOT IN ( SELECT Phone FROM dbo.Provider_Addresses )
AND (
SELECT S.State_ID FROM dbo.States S INNER JOIN dbo.CMD_Staging_Providers CSP
ON S.State_Abbr = CSP.Provider_State
WHERE S.State_ID NOT IN ( SELECT State_ID FROM dbo.Provider_Addresses ))
see bold area
Thanks [/font]
October 12, 2010 at 2:55 pm
Comment out the last four lines
AND (
SELECT S.State_ID FROM dbo.States S INNER JOIN dbo.CMD_Staging_Providers CSP
ON S.State_Abbr = CSP.Provider_State
WHERE S.State_ID NOT IN ( SELECT State_ID FROM dbo.Provider_Addresses ))
then run the statement.
If this part is supposed to follow the same kind of format as the other filters, then it should read something like
AND State_ID NOT IN (
SELECT S.State_ID FROM dbo.States S INNER JOIN dbo.CMD_Staging_Providers CSP
ON S.State_Abbr = CSP.Provider_State
WHERE S.State_ID NOT IN ( SELECT State_ID FROM dbo.Provider_Addresses ))
With dbo.CMD_Staging_Providers already in your FROM list, you could add dbo.States on an INNER JOIN and simplify the exclusion like this:
...FROM dbo.CMD_Staging_Providers CSP
INNER JOIN dbo.States S ON S.State_Abbr = CSP.Provider_State
...
WHERE
...
AND S.State_ID NOT IN ( SELECT State_ID FROM dbo.Provider_Addresses )
Are you sure this is what you want to do? It looks like what you're trying to do is exclude duplicate rows, but your exclusion will be much weirder in scope because each element is treated in isolation from the others. I reckon what you want is more like this:
...
FROM dbo.CMD_Staging_Providers CSP
INNER JOIN dbo.Providers P ON P.Provider_NPI = CSP.Provider_NPI# AND P.Provider_Tax_ID = CSP.Provider_TAXID
LEFT JOIN dbo.Provider_Addresses pa
ON CSP.Provider_Address1 = p.Street
AND CSP.Provider_Address2 = p.Street_2
AND CSP.Provider_City = p.City
AND CSP.Provider_Zip = p.Zip_Code
AND CSP.Provider_Phone = p.Phone
WHERE CSP.Has_Errors = 0
AND p.Street IS NULL -- better still, pk of dbo.Provider_Addresses
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 14, 2010 at 8:29 am
This worked. thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply