Author |
Topic |
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-06-17 : 06:53:17
|
Hey Guys This is query below, and i am keep receiving the following error message and i don’t know why , “Msg 4701, Level 16, State 1, Line 4Cannot find the object "#502" because it does not exist or you do not have permissions.”Would appreciate any help possible , regards -- Selecting Internal & External Where confirmation = 'y'--- --if existstruncate table #502 CREATE UNIQUE NONCLUSTERED INDEX [pk] ON #502 ( [FDMSAccountNo] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]insert into #502([FDMSAccountNo],[External_ID])select [FDMSAccountNo],[External_ID]from [FDMS].[dbo].[stg_LMPAB502]where Client_Conf ='y'--- Insert into xref Link --- insert into #502([FDMSAccountNo],[External_ID]) --- Selecting x ref table-- Select [FDMSAccountNo],left([ExternalAccountNo],15) from [FDMS].[dbo].[stg_FDMS_Link_New_Xref] where [Correspondence_Flag] ='y' --External Mid join -- SELECT [Account_ID],[FDMSAccountNo]Into #Update FROM [FDMS].[SalesForce].[DailyAccounts] inner join [FDMS].[dbo].[stg_LMPAB502] onRIGHT('0000000000000000' + [FDMS].[SalesForce].[DailyAccounts].[MID_External],18) = right('0000000000000000' + [FDMS].[dbo].[stg_LMPAB502].[External_ID],18) --select * from #Updateinsert into #Update([Account_ID],[FDMSAccountNo])--Internal Mid join -- SELECT [Account_ID],[FDMSAccountNo]FROM [FDMS].[SalesForce].[DailyAccounts] inner join [FDMS].[dbo].[stg_LMPAB502] onRIGHT('0000000000000000' + [FDMS].[SalesForce].[DailyAccounts].[MID_Internal],18) = right('0000000000000000' + [FDMS].[dbo].[stg_LMPAB502].[FDMSAccountNo],18) --select distinct* from #updateSelect distinctAccount_id,#Update.FDMSAccountNo,External_ID into #sf from #Updateinner join #502 on #502.FDMSAccountNo = #Update.FDMSAccountNoselect * from #Update--where FDMSAccountNo = '878202467881'----- Upload File ----- ---need to write sql query ----------------------- Kick Back File eg Incorrect Mids ------------------------- --SELECT -- [#sf].*, SalesForce.DailyAccounts.ID, --SalesForce.DailyAccounts.Account_ID, --SalesForce.DailyAccounts.Account_Name, --SalesForce.DailyAccounts.MID_External, --SalesForce.DailyAccounts.MID_Internal--FROM [#sf] RIGHT OUTER JOIN-- SalesForce.DailyAccounts ON [#sf].account_id = SalesForce.DailyAccounts.Account_ID-- where #sf.account_id is null-- and (MID_External is not null and MID_Internal is not null) --- linking Address to correct Sf account id --- SELECT [FDMSAccountNo] ,right(substring(Parentsfid,0,14),12)as Parentsfid,[MM3-DBA-NAME] as [DBA Name],[LEGAL-NAME] as [Legal Name],[MM3-DBA-ADDR1] as 'Street',[MM3-DBA-ADDR2],[MM3-DBA-ADDR4],[MM3-DBA-CITY] as 'City',[MM3-DBA-COUNTY] as 'County',[MM3-DBA-POSTAL-ZIP] as 'Postalcode',[MM3-DBA-COUNTRY]as 'Country',[MM3-DBA-TELEPHONE]as 'Phone'--,[INSTALLATION_DATE],,left (CONVERT(varchar(20),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime,[INSTALLATION_DATE],112)),126),16)as Open_Date,CASE WHEN LEFT (CANCELLATION_DATE , 4) >= '1970' AND LEFT (CANCELLATION_DATE , 4) < '9999' AND CANCELLATION_DATE <> '20' AND CANCELLATION_DATE <> '20000000' THEN CONVERT(varchar(20),convert(date,CANCELLATION_DATE,109))+'T23:01' end as Cancel_Date,CASE WHEN LEFT (last_posting_date , 4) >= '1980' AND LEFT (last_posting_date , 4) < '9999' AND last_posting_date <> '20' AND last_posting_date <> '20000000' THEN CONVERT(varchar(20),convert(date,last_posting_date,109))+'T23:01' end as Last_Post_Date,CASE WHEN LEFT (First_Post_Date , 4) >= '1980' AND LEFT (First_Post_Date , 4) < '9999' AND First_Post_Date <> '20' AND First_Post_Date <> '20000000' THEN CONVERT(varchar(20),convert(date,First_Post_Date,109))+'T23:01' end as First_Post_Date,--left(CONVERT(varchar(20),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime2,[CANCELLATION_DATE],112)),126),16)as Cancel_Date,--left(CONVERT(varchar(20),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime,[LAST_POSTING_DATE],112)),126),16)as First_Post_Date,--left(CONVERT(varchar(20),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime,[FIRST_POST_DATE],112)),126),16) as Last_Post_Date,Case when [FDMSAccountNo]IS NOT NULL THEN '00530000007Yscj' ELSE '00530000007Yscj' END [BoS Owner],Case when [FDMSAccountNo]IS NOT NULL THEN '01230000000LUxt' ELSE '01230000000LUxt' END [RecordTypeId]Into #AddressFROM [FDMS].[dbo].[stg_LMPAB501]--- Building ParentSf Hierarchy -- inner join ( --- Hierarchy Table Content-- --SELECT [FDMSAccountNo]--,[MM-CHN-AGENT]--,[MM-CHN-CORP]--,[MM-CHN-CHAIN]--FROM [FDMS].[dbo].[stg_LMPAB501]--- Hierarchy -- --Outlet to chain -- select distinct [MM-CHN-CHAIN] as Parentsfid ,fdmsaccountno as child FROM [FDMS].[dbo].[stg_LMPAB501]where [MM-CHN-CHAIN] <> '000000000000'and FDMSAccountNo <> [MM-CHN-CHAIN] -- Outlet to Corp--union all select distinct [MM-CHN-CORP] as Parentsfid ,fdmsaccountno as child FROM [FDMS].[dbo].[stg_LMPAB501]where [MM-CHN-CORP] <> '000000000000'and [MM-CHN-CHAIN] = '000000000000'and FDMSAccountNo <> [MM-CHN-CORP] -- Outlet to Agent -- union all select distinct[MM-CHN-AGENT] as Parentsfid ,fdmsaccountno as child FROM [FDMS].[dbo].[stg_LMPAB501]where [MM-CHN-CORP] = '000000000000'and [MM-CHN-CHAIN] = '000000000000'and FDMSAccountNo <> [MM-CHN-AGENT] -- Chain to Corp--union all select distinct [MM-CHN-CORP] as Parentsfid ,[MM-CHN-CHAIN] as child FROM [FDMS].[dbo].[stg_LMPAB501]where [MM-CHN-CORP] <> '000000000000'and [MM-CHN-CHAIN] <> '000000000000'-- Chain to agent-- union allselect distinct [MM-CHN-AGENT] as Parentsfid ,[MM-CHN-CHAIN] as Child FROM [FDMS].[dbo].[stg_LMPAB501]where [MM-CHN-CHAIN] <> '000000000000'and [MM-CHN-CORP] = '000000000000'-- Corp to agent -- union allselect Distinct[MM-CHN-AGENT] as Parentsfid ,[MM-CHN-CORP] as Child FROM [FDMS].[dbo].[stg_LMPAB501]where [MM-CHN-CORP] <> '000000000000')xon x.child = [FDMS].[dbo].[stg_LMPAB501].[FDMSAccountNo]group by [FDMSAccountNo], Parentsfid, [MM3-DBA-NAME] ,[LEGAL-NAME], [MM3-DBA-ADDR1],[MM3-DBA-ADDR2],[MM3-DBA-ADDR4],[MM3-DBA-CITY] ,[MM3-DBA-COUNTY] ,[MM3-DBA-POSTAL-ZIP] ,[MM3-DBA-COUNTRY],[MM3-DBA-TELEPHONE],[INSTALLATION_DATE],[CANCELLATION_DATE],LAST_POSTING_DATE,FIRST_POST_DATE-----select distinct *from #Address inner join #Update on #Address.FDMSAccountNo = #Update.FDMSAccountNoorder by Open_Date desc -- Getting the parent_Id-- Select distinct#Address.FDMSAccountNo,#Address.[DBA Name],#Address.[Legal Name],#Address.Street,#Address.[MM3-DBA-ADDR2],#Address.[MM3-DBA-ADDR4],#Address.City,#Address.County,#Address.Postalcode,#Address.Country,#Address.Phone,#Address.Open_Date,#Address.Cancel_Date,#Address.Last_Post_Date,#Address.[BoS Owner],#Address.RecordTypeId,u.FDMSAccountNo,#Address.Parentsfid,u.account_id,u1.account_id as parentidfrom #Address inner join #Update u on #Address.FDMSAccountNo = u.FDMSAccountNoleft join #Update u1 on #Address.Parentsfid = u1.fdmsaccountno----There are duplicate mids in the table above -- -- need to develop code below to counter this -- -- Finding Duplicates Mids on fdms account no Num----SELECT *--From #mason--fdmsaccountno, COUNT(*) TotalCount --FROM #accounts--GROUP BY ExternalAccountNo--HAVING COUNT(*) > 0--ORDER BY COUNT(*) DESC--drop table #Address,#Update,#sf |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 06:57:27
|
where are you creating #502 ? As of now first statement I see is a trucate so if you dont have any statements before it it wont work as table is not created yetAlso please keep in mind that # tables have scope only within connection and will get automatically destroyed once connection terminates.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-06-17 : 07:31:30
|
HI Visakh16Thank you for your swift response Re-looking at the code it doesnt look like have created table #501 Where do you suggest creating the #501 table ? NB : I have never used the trucate function before, and it was suggested by another anlayst , and its confused me with my query |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 09:13:59
|
it should be on topcreate table #502(...)-- Selecting Internal & External Where confirmation = 'y'--- --if existstruncate table #502 CREATE UNIQUE NONCLUSTERED INDEX [pk] ON #502 ([FDMSAccountNo] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]insert into #502([FDMSAccountNo],[External_ID])select [FDMSAccountNo],[External_ID]from [FDMS].[dbo].[stg_LMPAB502]where Client_Conf ='y'--- Insert into xref Link --- insert into #502([FDMSAccountNo],[External_ID])--- Selecting x ref table-- Select [FDMSAccountNo],left([ExternalAccountNo],15)from [FDMS].[dbo].[stg_FDMS_Link_New_Xref]where [Correspondence_Flag] ='y'--External Mid join -- SELECT [Account_ID],[FDMSAccountNo]Into #Update FROM [FDMS].[SalesForce].[DailyAccounts] inner join [FDMS].[dbo].[stg_LMPAB502] onRIGHT('0000000000000000' + [FDMS].[SalesForce].[DailyAccounts].[MID_External],18) = right('0000000000000000' + [FDMS].[dbo].[stg_LMPAB502].[External_ID],18) --select * from #Updateinsert into #Update([Account_ID],[FDMSAccountNo])--Internal Mid join -- SELECT [Account_ID],[FDMSAccountNo]FROM [FDMS].[SalesForce].[DailyAccounts] inner join [FDMS].[dbo].[stg_LMPAB502] onRIGHT('0000000000000000' + [FDMS].[SalesForce].[DailyAccounts].[MID_Internal],18) = right('0000000000000000' + [FDMS].[dbo].[stg_LMPAB502].[FDMSAccountNo],18) --select distinct* from #updateSelect distinctAccount_id,#Update.FDMSAccountNo,External_ID into #sf from #Updateinner join #502 on #502.FDMSAccountNo = #Update.FDMSAccountNoselect * from #Update--where FDMSAccountNo = '878202467881'----- Upload File ----- ---need to write sql query ----------------------- Kick Back File eg Incorrect Mids ------------------------- --SELECT -- [#sf].*, SalesForce.DailyAccounts.ID, --SalesForce.DailyAccounts.Account_ID, --SalesForce.DailyAccounts.Account_Name, --SalesForce.DailyAccounts.MID_External, --SalesForce.DailyAccounts.MID_Internal--FROM [#sf] RIGHT OUTER JOIN-- SalesForce.DailyAccounts ON [#sf].account_id = SalesForce.DailyAccounts.Account_ID-- where #sf.account_id is null-- and (MID_External is not null and MID_Internal is not null)--- linking Address to correct Sf account id --- SELECT [FDMSAccountNo] ,right(substring(Parentsfid,0,14),12)as Parentsfid,[MM3-DBA-NAME] as [DBA Name],[LEGAL-NAME] as [Legal Name],[MM3-DBA-ADDR1] as 'Street',[MM3-DBA-ADDR2],[MM3-DBA-ADDR4],[MM3-DBA-CITY] as 'City',[MM3-DBA-COUNTY] as 'County',[MM3-DBA-POSTAL-ZIP] as 'Postalcode',[MM3-DBA-COUNTRY]as 'Country',[MM3-DBA-TELEPHONE]as 'Phone'--,[INSTALLATION_DATE],,left (CONVERT(varchar(20),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime,[INSTALLATION_DATE],112)),126),16)as Open_Date,CASE WHEN LEFT (CANCELLATION_DATE , 4) >= '1970' AND LEFT (CANCELLATION_DATE , 4) < '9999' AND CANCELLATION_DATE <> '20' AND CANCELLATION_DATE <> '20000000' THEN CONVERT(varchar(20),convert(date,CANCELLATION_DATE,109))+'T23:01' end as Cancel_Date,CASE WHEN LEFT (last_posting_date , 4) >= '1980' AND LEFT (last_posting_date , 4) < '9999' AND last_posting_date <> '20' AND last_posting_date <> '20000000' THEN CONVERT(varchar(20),convert(date,last_posting_date,109))+'T23:01' end as Last_Post_Date,CASE WHEN LEFT (First_Post_Date , 4) >= '1980' AND LEFT (First_Post_Date , 4) < '9999' AND First_Post_Date <> '20' AND First_Post_Date <> '20000000' THEN CONVERT(varchar(20),convert(date,First_Post_Date,109))+'T23:01' end as First_Post_Date,--left(CONVERT(varchar(20),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime2,[CANCELLATION_DATE],112)),126),16)as Cancel_Date,--left(CONVERT(varchar(20),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime,[LAST_POSTING_DATE],112)),126),16)as First_Post_Date,--left(CONVERT(varchar(20),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime,[FIRST_POST_DATE],112)),126),16) as Last_Post_Date,Case when [FDMSAccountNo]IS NOT NULL THEN '00530000007Yscj' ELSE '00530000007Yscj' END [BoS Owner],Case when [FDMSAccountNo]IS NOT NULL THEN '01230000000LUxt' ELSE '01230000000LUxt' END [RecordTypeId]Into #AddressFROM [FDMS].[dbo].[stg_LMPAB501]--- Building ParentSf Hierarchy -- inner join ( --- Hierarchy Table Content-- --SELECT [FDMSAccountNo]--,[MM-CHN-AGENT]--,[MM-CHN-CORP]--,[MM-CHN-CHAIN]--FROM [FDMS].[dbo].[stg_LMPAB501]--- Hierarchy -- --Outlet to chain -- select distinct [MM-CHN-CHAIN] as Parentsfid ,fdmsaccountno as childFROM [FDMS].[dbo].[stg_LMPAB501]where [MM-CHN-CHAIN] <> '000000000000'and FDMSAccountNo <> [MM-CHN-CHAIN] -- Outlet to Corp--union all select distinct [MM-CHN-CORP] as Parentsfid ,fdmsaccountno as childFROM [FDMS].[dbo].[stg_LMPAB501]where [MM-CHN-CORP] <> '000000000000'and [MM-CHN-CHAIN] = '000000000000'and FDMSAccountNo <> [MM-CHN-CORP] -- Outlet to Agent -- union all select distinct[MM-CHN-AGENT] as Parentsfid ,fdmsaccountno as childFROM [FDMS].[dbo].[stg_LMPAB501]where [MM-CHN-CORP] = '000000000000'and [MM-CHN-CHAIN] = '000000000000'and FDMSAccountNo <> [MM-CHN-AGENT] -- Chain to Corp--union all select distinct [MM-CHN-CORP] as Parentsfid ,[MM-CHN-CHAIN] as childFROM [FDMS].[dbo].[stg_LMPAB501]where [MM-CHN-CORP] <> '000000000000'and [MM-CHN-CHAIN] <> '000000000000'-- Chain to agent-- union allselect distinct [MM-CHN-AGENT] as Parentsfid ,[MM-CHN-CHAIN] as Child FROM [FDMS].[dbo].[stg_LMPAB501]where [MM-CHN-CHAIN] <> '000000000000'and [MM-CHN-CORP] = '000000000000'-- Corp to agent -- union allselect Distinct[MM-CHN-AGENT] as Parentsfid ,[MM-CHN-CORP] as Child FROM [FDMS].[dbo].[stg_LMPAB501]where [MM-CHN-CORP] <> '000000000000')xon x.child = [FDMS].[dbo].[stg_LMPAB501].[FDMSAccountNo]group by [FDMSAccountNo], Parentsfid, [MM3-DBA-NAME] ,[LEGAL-NAME], [MM3-DBA-ADDR1],[MM3-DBA-ADDR2],[MM3-DBA-ADDR4],[MM3-DBA-CITY] ,[MM3-DBA-COUNTY] ,[MM3-DBA-POSTAL-ZIP] ,[MM3-DBA-COUNTRY],[MM3-DBA-TELEPHONE],[INSTALLATION_DATE],[CANCELLATION_DATE],LAST_POSTING_DATE,FIRST_POST_DATE-----select distinct *from #Address inner join #Update on #Address.FDMSAccountNo = #Update.FDMSAccountNoorder by Open_Date desc -- Getting the parent_Id-- Select distinct#Address.FDMSAccountNo,#Address.[DBA Name],#Address.[Legal Name],#Address.Street,#Address.[MM3-DBA-ADDR2],#Address.[MM3-DBA-ADDR4],#Address.City,#Address.County,#Address.Postalcode,#Address.Country,#Address.Phone,#Address.Open_Date,#Address.Cancel_Date,#Address.Last_Post_Date,#Address.[BoS Owner],#Address.RecordTypeId,u.FDMSAccountNo,#Address.Parentsfid,u.account_id,u1.account_id as parentidfrom #Address inner join #Update u on #Address.FDMSAccountNo = u.FDMSAccountNoleft join #Update u1 on #Address.Parentsfid = u1.fdmsaccountno----There are duplicate mids in the table above -- -- need to develop code below to counter this -- -- Finding Duplicates Mids on fdms account no Num----SELECT *--From #mason--fdmsaccountno, COUNT(*) TotalCount--FROM #accounts--GROUP BY ExternalAccountNo--HAVING COUNT(*) > 0--ORDER BY COUNT(*) DESC--drop table #Address,#Update,#sf ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|