May 8, 2016 at 10:22 pm
Hi,
We have upgraded from SQL 2008 to SQL 2014, and there part of a stored procedure that isn't working, it is using an old join that isn't supported and I'm having some problems re-writing it and would love some help please.
Old code that is currently working on SQL 2008 is
**********************
INSERT #operator_new (operatorCode, storeId, personId, storeCode, password, name, mnemonic, accessGroup,
active, loggedOn, language, country, dialect, changePassword, isInShift)
SELECT person.logon AS operatorCode,
@storeId as storeId,
person.Id as personId,
busidentity.description2 as storeCode,
person.password,
person.familyName AS name,
person.firstName AS mnemonic,
person.accessGroup,
1 as active,
0 as loggedOn, -- this will be replaced with the old value
person.languageCode AS language,
person.countryCode AS country,
NULL AS dialect,
person.changePassword,
isInShift = CASE WHEN timePeriod.actualEndTime IS NULL AND timePeriod.actualStartTime IS NOT NULL
THEN 1
ELSE 0
END
FROM person, busIdentity, accessGroup, accessGroupProperty, timePeriod
WHERE person.accessGroup = accessGroup.id
AND accessGroup.id = accessGroupProperty.accessGroupID
AND accessGroupProperty.accessPropertyId = 1 -- access property is AccessLevel
AND accessGroupProperty.content = 9 -- AccessLevel is Administrator
AND Person.status = 0 -- person is active
AND busIdentity.id = (Select bus.id from BusIdentity bus, Context ctx
Where ctx.currentValue = bus.description2 AND ctx.subject = 'HeadOffice'
AND ctx.parameter = 'Store')
AND person.logon not in (select operatorCode from #Operator_new)
AND timePeriod.busIdentityId = @storeId
AND timePeriod.personId =* person.id
AND timePeriod.actualEndTime IS NULL
**********************
I have tried using the following but no luck, I don't get an errors but I return no results.
**********************
INSERT #operator_new (operatorCode, storeId, personId, storeCode, password, name, mnemonic, accessGroup,active, loggedOn, language, country, dialect, changePassword, isInShift)
SELECT p.logon AS operatorCode,
@storeId as storeId,
p.Id as personId,
bi.description2 as storeCode,
p.password,
p.familyName AS name,
p.firstName AS mnemonic,
p.accessGroup,
1 as active,
0 as loggedOn, -- this will be replaced with the old value
p.languageCode AS language,
p.countryCode AS country,
NULL AS dialect,
p.changePassword,
isInShift = CASE WHEN tp.actualEndTime IS NULL AND tp.actualStartTime IS NOT NULL
THEN 1
ELSE 0
END
FROM person p
JOIN accessGroup ag ON p.accessGroup = ag.id
JOIN accessGroupProperty agp ON ag.id = agp.accessGroupID
JOIN timePeriod tp ON p.id = tp.personid
JOIN BusIdentity bi ON tp.busIdentityId = bi.Id
JOIN Context ctx ON ctx.currentValue = bi.description2
WHERE
agp.accessPropertyId = 1 -- access property is AccessLevel
AND agp.content = 9 -- AccessLevel is Administrator
AND p.status = 0 -- person is active
AND tp.busIdentityId = @storeId
AND tp.actualEndTime IS NULL
AND ctx.subject = 'HeadOffice'
AND ctx.parameter = 'Store'
AND NOT EXISTS (SELECT 1 FROM #Operator_new opn WHERE p.logon = opn.operatorCode)
**********************
Any help would be much appreciated.
Kind Regards
Jason
May 9, 2016 at 2:57 am
I suspect this line is your problem
AND timePeriod.personId =* person.id
You need to make the join to timePeriod a LEFT JOIN
May 9, 2016 at 4:02 am
Hi Ian
Thanks for that, I was what I thought I was doing :-).
I saw that =* is an OLD SQL statement no longer supported, when I tried to use just a left outer join I couldn't seem to re-write that line I had to add the table joins, I have managed to get it run but no results, I'm expecting to see 1 result back.
Thanks again
Jason
May 9, 2016 at 4:34 am
Yes, you have to add all the table joins, but you've specified all of them as inner join (inner is implied if nothing else is specified), the join between timeperiod and person needs to be a LEFT OUTER JOIN, not the [INNER] JOIN that you have specified.
That said, the other filters against the TimePeriod table would have made it an inner join anyway, as they filter out the NULL values that would result from a left join
AND timePeriod.busIdentityId = @storeId
So I expect the problem is probably more subtle.
Try building up the query bit by bit and comparing the results against the original query, you should be able to see where things change.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 9, 2016 at 4:34 am
The replacement script you posted doesn't have a left join. Does this work?
INSERT #operator_new (operatorCode, storeId, personId, storeCode, password, name, mnemonic, accessGroup,active, loggedOn, language, country, dialect, changePassword, isInShift)
SELECT p.logon AS operatorCode,
@storeId as storeId,
p.Id as personId,
bi.description2 as storeCode,
p.password,
p.familyName AS name,
p.firstName AS mnemonic,
p.accessGroup,
1 as active,
0 as loggedOn, -- this will be replaced with the old value
p.languageCode AS language,
p.countryCode AS country,
NULL AS dialect,
p.changePassword,
isInShift = CASE WHEN tp.actualEndTime IS NULL AND tp.actualStartTime IS NOT NULL
THEN 1
ELSE 0
END
FROM person p
JOIN accessGroup ag ON p.accessGroup = ag.id
JOIN accessGroupProperty agp ON ag.id = agp.accessGroupID
LEFT JOIN timePeriod tp ON p.id = tp.personid
JOIN BusIdentity bi ON tp.busIdentityId = bi.Id
JOIN Context ctx ON ctx.currentValue = bi.description2
WHERE
agp.accessPropertyId = 1 -- access property is AccessLevel
AND agp.content = 9 -- AccessLevel is Administrator
AND p.status = 0 -- person is active
AND tp.busIdentityId = @storeId
AND tp.actualEndTime IS NULL
AND ctx.subject = 'HeadOffice'
AND ctx.parameter = 'Store'
AND NOT EXISTS (SELECT 1 FROM #Operator_new opn WHERE p.logon = opn.operatorCode)
May 9, 2016 at 6:28 am
Thanks for the help Gail and Ten Centuries. Ten Centuries, that snippet didn't work I still get a result with no records.
I did try and break the code down, I also tried to adding in a LEFT JOIN
*********************
FROM person p
JOIN accessGroup ag ON p.accessGroup = ag.id
JOIN accessGroupProperty agp ON ag.id = agp.accessGroupID
JOIN Context ctx ON ctx.currentValue = busidentity.descritpion2
LEFT JOIN timePeriod tp ON tp.personId = p.id
AND tp.busIdentityId = @storeId
AND tp.actualEndTime IS NULL
WHERE
agp.accessPropertyId = 1 -- access property is AccessLevel
AND agp.content = 9 -- AccessLevel is Administrator
AND p.status = 0 -- person is active
AND ctx.subject = 'HeadOffice'
AND ctx.parameter = 'Store'
AND NOT EXISTS (SELECT 1 FROM #Operator_new opn WHERE p.logon = opn.operatorCode)
*********************
But I now get errors on the line "JOIN Context ctx ON ctx.currentValue = busidentity.descritpion2"
Any extra help would be greatly appreciated, also if anyone knows of any good & clear join articles that would be helpful too.
Cheers, Jason
May 9, 2016 at 6:33 am
specific error is probably helpful; i suspect it's the column name is misspelled due to copy paste?
or is it because the table busidentity is not in the join criteria on that line yet? Ians' query had the table, but what you pasted does not.
busidentity..descritpion2 is probably busidentity.description2
Lowell
May 9, 2016 at 6:36 am
Sorry, Error is "The multi-part identifier "busidentity.descritpion2" could not be bound."
May 9, 2016 at 6:39 am
support 37266 (5/9/2016)
Sorry, Error is "The multi-part identifier "busidentity.descritpion2" could not be bound."
Probably because it's spelt wrong.
From your earlier query:
JOIN Context ctx ON ctx.currentValue = bi.description2
but also because there's no table called busidentity anywhere in your from clause in the latest query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 9, 2016 at 6:46 am
Thanks Gail, yes sorry I had it spelt right in the query, but when I added
FROM person p, busidentity bi
It doesn't seem to allow me to add two tables doing it this way.
May 9, 2016 at 6:55 am
Where's the join?
FROM <table> [LEFT|RIGHT|INNER] JOIN <table 2> ON <join condition>
[LEFT|RIGHT|INNER] JOIN <table 3> ON .... repeat for the rest of the tables in your query.
So,
FROM person p INNER JOIN busidentity bi ON <join condition>
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 9, 2016 at 1:48 pm
It sounds like you're trying to mix the obsolete join syntax and the current join syntax.
Use INNER JOINs and OUTER JOINs as appropriate and eliminate the old join syntax of FROM x, y WHERE x.column_id = y.column_id completely.
The syntax Gail posted is the right way to do it.
May 9, 2016 at 11:55 pm
I appreciate all your help guys, simply I just don't get it... I have got a 2 query windows open side by side comparing the statements and I believe I have them joining correctly and using the old =* script workings but the new one simply returns no results.
I have the old code as
FROM person, busIdentity, accessGroup, accessGroupProperty, timePeriod
WHERE person.accessGroup = accessGroup.id
AND accessGroup.id = accessGroupProperty.accessGroupID
AND accessGroupProperty.accessPropertyId = 1 -- access property is AccessLevel
AND accessGroupProperty.content = 9 -- AccessLevel is Administrator
AND Person.status = 0 -- person is active
AND busIdentity.id = (Select bus.id from BusIdentity bus, Context ctx
Where ctx.currentValue = bus.description2 AND ctx.subject = 'HeadOffice'
AND ctx.parameter = 'Store')
AND person.logon not in (select operatorCode from #Operator_new)
AND timePeriod.busIdentityId = '110007'
AND timePeriod.personId =* person.id
AND timePeriod.actualEndTime IS NULL
And the new code as
FROM person p
INNER JOIN accessGroup ag ON p.accessGroup = ag.id
INNER JOIN accessGroupProperty agp ON ag.id = agp.accessGroupID
LEFT JOIN timePeriod tp ON tp.personid = p.id
INNER JOIN BusIdentity bi ON tp.busIdentityId = bi.Id
INNER JOIN Context ctx ON ctx.currentValue = bi.description2
WHERE
agp.accessPropertyId = 1 -- access property is AccessLevel
AND agp.content = 9 -- AccessLevel is Administrator
AND p.status = 0 -- person is active
AND tp.busIdentityId = '110007'
AND tp.actualEndTime IS NULL
AND ctx.subject = 'HeadOffice'
AND ctx.parameter = 'Store'
AND NOT EXISTS (SELECT 1 FROM #Operator_new opn WHERE p.logon = opn.operatorCode)
From my understanding the INNER JOIN should replace the WHERE causes. What have I done wrong.
Cheers, Jason
May 10, 2016 at 1:50 am
The 2 queries are not equivalent. For example, you have included this join in the new code,
INNER JOIN BusIdentity bi ON tp.busIdentityId = bi.Id
but I can see no such relationship between the bi and tp tables in the original query.
What should the relationship be between these 2 tables?
May 10, 2016 at 5:15 pm
Hi All
Thank you for your guidance, after some more digging and some more help the modified code that worked was:
FROM person p
JOIN Context AS ctx
ON ctx.subject = 'HeadOffice'
AND ctx.parameter = 'Store'
JOIN busIdentity bi
ON bi.description2 = ctx.currentValue
JOIN accessGroup
ON accessGroup.id = p.accessGroup
JOIN accessGroupProperty
ON accessGroupProperty.accessGroupID = accessGroup.id
AND accessGroupProperty.accessPropertyId = 1 -- access property is AccessLevel
AND accessGroupProperty.content = 9 -- AccessLevel is Administrator
LEFT OUTER JOIN timePeriod tp
ON tp.personId = p.id
AND tp.busIdentityId = '110007'
AND tp.actualEndTime IS NULL
WHERE p.status = 0 -- person is active
AND p.logon not in (select operatorCode from #Operator_new)
The issue was the miss joining on the "Context" and BuisIdentity tables, I clearly had it wrong in my head and hence the wrong SQL syntax.
Cheers Jason
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply