November 30, 2017 at 2:52 pm
Hi experts,
I would like to restructure this query in a different way to use OPENQUERY instead but I am getting an error when it is trying to access the table 'TPM_Matl_Mstr' and this database [XYZ] is local to the SQL instance. I am not sure how to work around this so that it doesn't search for the table on the linkedserver instead fetch the results from local server.
CREATE view [dbo].[abc]
as
select * from openquery([xy],'
SELECT
T1.Material COLLATE SQL_Latin1_General_CP850_CI_AS AS Material,
T2.Material_Desc,
T1.Matl_Grp_Nbr COLLATE SQL_Latin1_General_CP850_CI_AS AS Matl_Grp_Nbr,
T3.Matl_Grp_Desc,
T4.Matl_Addl_Desc_1,
T4.Matl_Addl_Desc_2,
T5.TT_Matl_Drwg_Doc_Typ,
T5.TT_Matl_Drwg_Doc_Nbr,
T5.TT_Matl_Drwg_Doc_Vrsn,
T5.TT_Matl_Drwg_Size_Dimn,
RTRIM(ISNULL(T5.TT_Matl_Drwg_Desc_1, '''')) + RTRIM(ISNULL(T5.TT_Matl_Drwg_Desc_2, '''')) + RTRIM(ISNULL(T5.TT_Matl_Drwg_Desc_3, '''')) AS TT_Matl_Drwg_Desc_1,
T1.Matl_Type
FROM [ABC].dbo.vw_Matl_Desc_tbl T2,
((([ABC].dbo.vw_Matl_Master_Gen_tbl
T1
LEFT OUTER JOIN [ABC].dbo.vw_Matl_Grps_tbl T3
ON T1.Matl_Grp_Nbr COLLATE SQL_Latin1_General_CP1_CI_AS =
T3.Matl_Grp_Nbr COLLATE SQL_Latin1_General_CP1_CI_AS)
LEFT OUTER JOIN XYZ.dbo.TPM_Matl_Mstr T4
ON T1.Material COLLATE SQL_Latin1_General_CP1_CI_AS =
T4.Matl_Ref_Nbr COLLATE SQL_Latin1_General_CP1_CI_AS)
LEFT OUTER JOIN XYZ.dbo.TPM_Matl_Drwg T5
ON T4.Matl_Ref_Nbr =
T5.Matl_Ref_Nbr)
WHERE T1.Material = T2.Material
AND T1.Matl_Type = ''zalb''
AND T4.Matl_Plan_Stat_Ind <> ''o''')
GO
November 30, 2017 at 4:22 pm
Okay, the database [ABC] is on the linked server [xy] and the database [XYZ] is on the local server, correct?
November 30, 2017 at 4:31 pm
If the answer to my question is yes, then try this:CREATE VIEW [dbo].[abc]
AS
SELECT
rmt.Material,
rmt.Material_Desc,
rmt.Matl_Grp_Nbr,
rmt.Matl_Grp_Desc,
T4.Matl_Addl_Desc_1,
T4.Matl_Addl_Desc_2,
T5.TT_Matl_Drwg_Doc_Typ,
T5.TT_Matl_Drwg_Doc_Nbr,
T5.TT_Matl_Drwg_Doc_Vrsn,
T5.TT_Matl_Drwg_Size_Dimn,
RTRIM(ISNULL(T5.TT_Matl_Drwg_Desc_1, '''')) + RTRIM(ISNULL(T5.TT_Matl_Drwg_Desc_2, '''')) + RTRIM(ISNULL(T5.TT_Matl_Drwg_Desc_3, '''')) AS TT_Matl_Drwg_Desc_1,
rmt.Matl_Type
FROM
OPENQUERY
([xy], '
SELECT
T1.Material COLLATE SQL_Latin1_General_CP850_CI_AS AS Material,
T2.Material_Desc,
T1.Matl_Grp_Nbr COLLATE SQL_Latin1_General_CP850_CI_AS AS Matl_Grp_Nbr,
T3.Matl_Grp_Desc,
-- T4.Matl_Addl_Desc_1,
-- T4.Matl_Addl_Desc_2,
-- T5.TT_Matl_Drwg_Doc_Typ,
-- T5.TT_Matl_Drwg_Doc_Nbr,
-- T5.TT_Matl_Drwg_Doc_Vrsn,
-- T5.TT_Matl_Drwg_Size_Dimn,
-- RTRIM(ISNULL(T5.TT_Matl_Drwg_Desc_1, '''')) + RTRIM(ISNULL(T5.TT_Matl_Drwg_Desc_2, '''')) + RTRIM(ISNULL(T5.TT_Matl_Drwg_Desc_3, '''')) AS TT_Matl_Drwg_Desc_1,
T1.Matl_Type
FROM [ABC].dbo.vw_Matl_Desc_tbl T2
INNER JOIN [ABC].dbo.vw_Matl_Master_Gen_tbl T1
ON T1.Material = T2.Material
LEFT OUTER JOIN [ABC].dbo.vw_Matl_Grps_tbl T3
ON T1.Matl_Grp_Nbr COLLATE SQL_Latin1_General_CP1_CI_AS = T3.Matl_Grp_Nbr COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE
T1.Matl_Type = ''zalb''
') rmt
LEFT OUTER JOIN XYZ.dbo.TPM_Matl_Mstr T4
ON rmt.Material COLLATE SQL_Latin1_General_CP1_CI_AS = T4.Matl_Ref_Nbr COLLATE SQL_Latin1_General_CP1_CI_AS
LEFT OUTER JOIN XYZ.dbo.TPM_Matl_Drwg T5
ON T4.Matl_Ref_Nbr = T5.Matl_Ref_Nbr
WHERE
T4.Matl_Plan_Stat_Ind <> 'o';
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply