Eroor : Invalid object name 'messages'.

Question

Eroor : Invalid object name 'messages'.

atikdesai

Mr or Mrs. 500

Points: 517
More actions
May 25, 2010 at 4:32 am

#99235

when I run this SP its giving me Eroor : Invalid object name 'messages'.
What is the alternate way?
WITH messages AS
(
SELECT MM.mobile_number,MM.pbuserid,MM.SWID,MM.[message],MM.characterID,MM.tacticID,MM.destID,
MM.subject_ln,MM.body,MM.campaign_code,
M.mobile_opt_dts,
Row_Num = ROW_NUMBER() OVER(PARTITION BY MM.mobile_number ORDER BY M.mobile_opt_dts DESC)
FROM tbl_Mobile_Messages as MM
Inner Join tbl_Mobile as M
On M.SWID = MM.SWID
)
INSERT INTO TEMP_FINAL_DEDUPED (pbuserid , mobile_number , SWID , [message] , characterID , tacticID, destID,
subject_ln, body ,campaign_code ,mobile_opt_dts ,Row_Num)
SELECT pbuserid , mobile_number , SWID , [message] , characterID , tacticID, destID,
subject_ln, body ,campaign_code ,mobile_opt_dts ,Row_Num
FROM messages WHERE Row_Num = 1 and tacticID=8
INSERT INTO TEMP_DEDUPED (pbuserid , mobile_number , SWID , [message] , characterID , tacticID, destID,
subject_ln, body ,campaign_code,SWID_dedupe)
SELECT pbuserid , mobile_number , SWID , [message] , characterID , tacticID, destID,
subject_ln, body ,campaign_code,NULL
FROM messages WHERE Row_Num > 1 and tacticID=8
May 25, 2010 at 5:06 am

This was removed by the editor as SPAM

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

A CTE can only be referenced in the query that immediately follows it. It doesn't have batch or procedure scoping. You can repeat the CTE definition before the second insert, that'll work.

So

WITH messages AS

(

SELECT MM.mobile_number,MM.pbuserid,MM.SWID,MM.[message],MM.characterID,MM.tacticID,MM.destID,

MM.subject_ln,MM.body,MM.campaign_code,

M.mobile_opt_dts,

Row_Num = ROW_NUMBER() OVER(PARTITION BY MM.mobile_number ORDER BY M.mobile_opt_dts DESC)

FROM tbl_Mobile_Messages as MM

Inner Join tbl_Mobile as M

On M.SWID = MM.SWID

)

INSERT INTO TEMP_FINAL_DEDUPED (pbuserid , mobile_number , SWID , [message] , characterID , tacticID, destID,

subject_ln, body ,campaign_code ,mobile_opt_dts ,Row_Num)

SELECT pbuserid , mobile_number , SWID , [message] , characterID , tacticID, destID,

subject_ln, body ,campaign_code ,mobile_opt_dts ,Row_Num

FROM messages WHERE Row_Num = 1 and tacticID=8

WITH messages AS

(

SELECT MM.mobile_number,MM.pbuserid,MM.SWID,MM.[message],MM.characterID,MM.tacticID,MM.destID,

MM.subject_ln,MM.body,MM.campaign_code,

M.mobile_opt_dts,

Row_Num = ROW_NUMBER() OVER(PARTITION BY MM.mobile_number ORDER BY M.mobile_opt_dts DESC)

FROM tbl_Mobile_Messages as MM

Inner Join tbl_Mobile as M

On M.SWID = MM.SWID

)

INSERT INTO TEMP_DEDUPED (pbuserid , mobile_number , SWID , [message] , characterID , tacticID, destID,

subject_ln, body ,campaign_code,SWID_dedupe)

SELECT pbuserid , mobile_number , SWID , [message] , characterID , tacticID, destID,

subject_ln, body ,campaign_code,NULL

FROM messages WHERE Row_Num > 1 and tacticID=8

It's one option, far from the only option.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

atikdesai Mr or Mrs. 500 Points: 517 More actions · Answer 2

Thanks Bro. I knew the reason but wanted to find alternative way.

Please anyone if you can find an alternate way.