What am I Missing ? Converting Legacy Code

Question

Post reply

What am I Missing ? Converting Legacy Code

homebrew01

SSC Guru

Points: 55548
More actions
March 10, 2014 at 2:21 pm

#396396

We have some legacy code with old style *= LEFT JOINs that I need to convert. I'm getting error: multi-part identifier "mm.transtype_id" could not be bound.
Thoughts ?
Old Code
SELECTmm.MEMMT_ID,
mm.MEM_ID,
mmp.MEMMP_salutation,
mmp.MEMMP_nickName,
mmp.MEMMP_firstName,
mmp.MEMMP_MI,
mmp.MEMMP_lastName,
mmp.MEMMP_company,
mmp.MEMMP_title,
mmp.MEMMP_address1,
mmp.MEMMP_address2,
mmp.MEMMP_address3,
mmp.MEMMP_city,
mmp.STA_ID,
mmp.MEMMP_zipCode,
mmp.MEMMP_country,
mmp.MEMMP_homePhone,
mmp.MEMMP_bizphone,
mmp.MEMMP_bizPhoneExt,
mmp.MEMMP_fax,
mmp.MEMMP_cellPhone,
mmp.MEMMP_email1,
mmp.MEMMP_source,
mmp.MEMMP_refOther,
(select PROM_CODE from promotion where PROM_ID = mm.PROM_ID),
mm.MEMMT_cmpgn,
mm.MEMMT_premium,
mm.MEMMT_gateway
FROMMember_Meeting mm, Member_MeetingProfile mmp, Transtype tt, Member_MeetingStatus mms, Meeting m
WHEREm.mt_id = mm.mt_id
ANDmm.memmt_id = @memmtid
ANDmmp.memmt_id = @memmtid
ANDmm.transtype_id *= tt.transtype_id
ANDmm.MEMMS_ID *= mms.MEMMS_ID
New Code
SELECTmm.MEMMT_ID,
mm.MEM_ID,
mmp.MEMMP_salutation,
mmp.MEMMP_nickName,
mmp.MEMMP_firstName,
mmp.MEMMP_MI,
mmp.MEMMP_lastName,
mmp.MEMMP_company,
mmp.MEMMP_title,
mmp.MEMMP_address1,
mmp.MEMMP_address2,
mmp.MEMMP_address3,
mmp.MEMMP_city,
mmp.STA_ID,
mmp.MEMMP_zipCode,
mmp.MEMMP_country,
mmp.MEMMP_homePhone,
mmp.MEMMP_bizphone,
mmp.MEMMP_bizPhoneExt,
mmp.MEMMP_fax,
mmp.MEMMP_cellPhone,
mmp.MEMMP_email1,
mmp.MEMMP_source,
mmp.MEMMP_refOther,
(select PROM_CODE from promotion where PROM_ID = mm.PROM_ID),
mm.MEMMT_cmpgn,
mm.MEMMT_premium,
mm.MEMMT_gateway
FROMMember_Meeting mm, Member_MeetingProfile mmp, Meeting m
LEFT JOIN Transtype tt ON mm.transtype_id = tt.transtype_id--556
LEFT JOIN Member_MeetingStatus mms ON mm.MEMMS_ID = mms.MEMMS_ID--556
WHEREm.mt_id = mm.mt_id
ANDmm.memmt_id = @memmtid
ANDmmp.memmt_id = @memmtid

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

If you're already working on it, why won't you go the whole way?

SELECTmm.MEMMT_ID,

mm.MEM_ID,

mmp.MEMMP_salutation,

mmp.MEMMP_nickName,

mmp.MEMMP_firstName,

mmp.MEMMP_MI,

mmp.MEMMP_lastName,

mmp.MEMMP_company,

mmp.MEMMP_title,

mmp.MEMMP_address1,

mmp.MEMMP_address2,

mmp.MEMMP_address3,

mmp.MEMMP_city,

mmp.STA_ID,

mmp.MEMMP_zipCode,

mmp.MEMMP_country,

mmp.MEMMP_homePhone,

mmp.MEMMP_bizphone,

mmp.MEMMP_bizPhoneExt,

mmp.MEMMP_fax,

mmp.MEMMP_cellPhone,

mmp.MEMMP_email1,

mmp.MEMMP_source,

mmp.MEMMP_refOther,

(select PROM_CODE from promotion where PROM_ID = mm.PROM_ID),

mm.MEMMT_cmpgn,

mm.MEMMT_premium,

mm.MEMMT_gateway

FROMMember_Meeting mm

JOIN Member_MeetingProfile mmp ON mm.memmt_id = mmp.memmt_id

JOIN Meeting m ON mm.mt_id = m.mt_id

LEFT JOIN Transtype tt ON mm.transtype_id = tt.transtype_id--556

LEFT JOIN Member_MeetingStatus mms ON mm.MEMMS_ID = mms.MEMMS_ID--556

WHERE mm.memmt_id = @memmtid

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

I only went part way because I often test things a step at a time to cut down the variables if I made a mistake somewhere along the line. Curious about what causes the partial upgrade to fail.

Edit: Looks like converting it all the way works, thenks