August 2, 2015 at 6:09 pm
Hi All
We have upgraded our MS SQL server from 2008 to 2014 and one of the SP isn't working. Our SQL guy has left and I'm trying to work out the issue. The SP seems to using an old method for Left Outer Join, (=*) and i'm trying to rewrite it, however unsuccesfully.
Hoping someone can help me, I have included the snippet of the code
------------------------------------------
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
------------------------------------------
Thank you all very much for help.
Cheers, Jason
August 2, 2015 at 7:28 pm
The old notation =* was used for a right outer join, not for a left outer join (that was *=); maybe that's the cause of your difficulties?
Tom
August 2, 2015 at 9:17 pm
Hi Tom
To be honest, probably not 🙂
I come from a sysadmin back ground, not SQL. I know enough to get me through basic troublehshooting, but I'm having issues rewriting this code to work under MS SQL 2014.
Any help would be much appriecated.
Cheers, Jason
August 2, 2015 at 9:52 pm
Thank you all for looking a the post, got some help from a user and re-wrtten the code.
In case anyone was interested, here is the fixed version
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)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply