CODEWITHSUNDEEP
sql-serversql-server-2005sql-server-2012
Bmize729
Bmize729

Reputation: 1136

SQL 2005 to 2012

I am in the midst of upgrading to SQL Server 2012 from 2005 and came across a syntax error that I need help with. The syntax error is actually in a database that was built in 2000 so the compatibility issue cannot be fixed in the settings. 

    Select      PPB.*,      

    SUBSTRING(STUFF((select license_id as k   --*
    FROM LMACatscan..license AS L 
          WHERE L.archived = 0 
                AND ((L.ppb_id IN (SELECT ppb_id FROM LMACatscan..ppb WHERE ppb_id = PPB.ppb_id)) 
                OR (L.ppb_id_2 IN (SELECT ppb_id FROM LMACatscan..ppb WHERE ppb_id = PPB.ppb_id)))
                FOR XML PATH('')),1,0,''),2,999999)    
                FROM   LMACatscan..ppb AS PPB    
                WHERE  state_code > ''

The results from 2005 are: enter image description here From 20012 you can see the column with no name has that same value for all rows and this is where the problem is: enter image description hereAs always, any help is appreciated!

Upvotes: 1

Views: 81

Answers (1)

ZLK
ZLK

Reputation: 2874

Well, the problem lies here:

((L.ppb_id IN (SELECT ppb_id FROM LMACatscan..ppb WHERE ppb_id = PPB.ppb_id)) 
OR (L.ppb_id_2 IN (SELECT ppb_id FROM LMACatscan..ppb WHERE ppb_id = PPB.ppb_id)))

What it's doing is selecting everything from the license_id column where either the ppb_id or ppb_id_2 is in the PPB table's ppb_id column. Which means you're concatenating every match for every row (even if the ppb_id doesn't exist in the license table, it will still show this on that line)

This should fix it:

SELECT PPB.*,
    SUBSTRING(STUFF((SELECT license_id k
        FROM LMACatscan..license L
        WHERE (L.ppb_id = PPB.ppb_id
        OR L.ppb_id_2 = PPB.ppb_id)
        AND L.Archived = 0
        FOR XML PATH('')),1,0,''),2,999999)
FROM LMACatscan..ppb PPB
WHERE state_code > ''

By the way, the stuff statement is literally doing nothing. You can just remove it if you like. 

SELECT PPB.*,
    SUBSTRING((SELECT license_id k
        FROM license L
        WHERE (L.ppb_id = PPB.ppb_id
        OR L.ppb_id_2 = PPB.ppb_id)
        AND L.Archived = 0
        FOR XML PATH('')),2,99999)
FROM PPB
WHERE state_code > ''

Upvotes: 1

Related Questions