select statement from two separate databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select statement from two separate databases

Author

Topic

noms
Starting Member

22 Posts

Posted - 2009-02-17 : 06:20:53

hi experts,
i have two databases one is live the other is test db, on the test db i've created a view to select from the live db but there's something wrong with my select query:
here it is:
SELECT astAssets.ID AS AssetID,
vw_astDepreciationTransactions.FinPeriodID AS PeriodID,
finPeriods.Code AS PeriodCode,
vw_astDepreciationLinks.StartDate AS StartDate,
finCostCentres.ID AS CostCentreID,
astTypes.SeqNum AS AssetFinancialClassSeqNum
FROM [ON_KEY_42R5_UDM2]..astAssets INNER JOIN [ON_KEY_42R5_UDM2]..vw_astDepreciationLinks ON [ON_KEY_42R5_UDM2]..astAssets.ID = [ON_KEY_42R5_UDM2]..vw_astDepreciationLinks.RecordID
INNER JOIN [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions ON [ON_KEY_42R5_UDM2]..vw_astDepreciationLinks.ID = [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.DepreciationLinksID
INNER JOIN [ON_KEY_42R5_UDM2]..finPeriods ON [ON_KEY_42R5_UDM2]..finPeriods.ID = [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.FinPeriodID
INNER JOIN [ON_KEY_42R5_UDM2]..finGLCodes ON [ON_KEY_42R5_UDM2]..finGLCodes.ID = [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.GLID
INNER JOIN [ON_KEY_42R5_UDM2]..finCostCentres ON [ON_KEY_42R5_UDM2]..finCostCentres.ID = [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.CostCentreID
INNER JOIN [ON_KEY_42R5_UDM2]..astTypes ON [ON_KEY_42R5_UDM2]..astTypes.ID = [ON_KEY_42R5_UDM2]..[dbo.fn_astTypeGetSecondHighestAssetTypeBelowRoot(astAssets.AssetTypeID)]

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-02-17 : 06:27:37

What is the error message you get.

Karthik

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-17 : 06:35:10

Put code tag around your query, makes it readible.
Try this,,

SELECT astAssets.ID AS AssetID,
vw_astDepreciationTransactions.FinPeriodID AS PeriodID,
finPeriods.Code AS PeriodCode,
vw_astDepreciationLinks.StartDate AS StartDate,
finCostCentres.ID AS CostCentreID,
astTypes.SeqNum AS AssetFinancialClassSeqNum
FROM [ON_KEY_42R5_UDM2]..astAssets INNER JOIN [ON_KEY_42R5_UDM2]..vw_astDepreciationLinks ON [ON_KEY_42R5_UDM2]..astAssets.[ID] = [ON_KEY_42R5_UDM2]..vw_astDepreciationLinks.RecordID
INNER JOIN [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions ON [ON_KEY_42R5_UDM2]..vw_astDepreciationLinks.[ID] = [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.DepreciationLinksID
INNER JOIN [ON_KEY_42R5_UDM2]..finPeriods ON [ON_KEY_42R5_UDM2]..finPeriods.[ID] = [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.FinPeriodID
INNER JOIN [ON_KEY_42R5_UDM2]..finGLCodes ON [ON_KEY_42R5_UDM2]..finGLCodes.[ID] = [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.GLID
INNER JOIN [ON_KEY_42R5_UDM2]..finCostCentres ON [ON_KEY_42R5_UDM2]..finCostCentres.[ID] = [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.CostCentreID
INNER JOIN [ON_KEY_42R5_UDM2]..astTypes ON [ON_KEY_42R5_UDM2]..astTypes.[ID] = [ON_KEY_42R5_UDM2].dbo.fn_astTypeGetSecondHighestAssetTypeBelowRoot(astAssets.AssetTypeID)

noms
Starting Member

22 Posts

Posted - 2009-02-17 : 07:03:22

hi guys
thanks for the quick response, i ran your query but i was still getting the same error "The multi-part could not be bound.", so i added [ON_KEY_42R5_UDM2]..astAssets.ID in the select for all the fields then i removed the last inner join cause i was getting the error from that line "The multi-part identifier "astAssets.AssetTypeID" could not be bound." this "dbo.fn_astTypeGetSecondHighestAssetTypeBelowRoot" from the last inner join i'm joining the function and i'm not sure if that's the problem

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-17 : 07:14:59

try this,

SELECT astAssets.ID AS AssetID,
vw_astDepreciationTransactions.FinPeriodID AS PeriodID,
finPeriods.Code AS PeriodCode,
vw_astDepreciationLinks.StartDate AS StartDate,
finCostCentres.ID AS CostCentreID,
astTypes.SeqNum AS AssetFinancialClassSeqNum
FROM [ON_KEY_42R5_UDM2]..astAssets astAssets INNER JOIN [ON_KEY_42R5_UDM2]..vw_astDepreciationLinks ON [ON_KEY_42R5_UDM2]..astAssets.[ID] = [ON_KEY_42R5_UDM2]..vw_astDepreciationLinks.RecordID
INNER JOIN [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions ON [ON_KEY_42R5_UDM2]..vw_astDepreciationLinks.[ID] = [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.DepreciationLinksID
INNER JOIN [ON_KEY_42R5_UDM2]..finPeriods ON [ON_KEY_42R5_UDM2]..finPeriods.[ID] = [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.FinPeriodID
INNER JOIN [ON_KEY_42R5_UDM2]..finGLCodes ON [ON_KEY_42R5_UDM2]..finGLCodes.[ID] = [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.GLID
INNER JOIN [ON_KEY_42R5_UDM2]..finCostCentres ON [ON_KEY_42R5_UDM2]..finCostCentres.[ID] = [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.CostCentreID
INNER JOIN [ON_KEY_42R5_UDM2]..astTypes ON [ON_KEY_42R5_UDM2]..astTypes.[ID] = [ON_KEY_42R5_UDM2].dbo.fn_astTypeGetSecondHighestAssetTypeBelowRoot(astAssets.AssetTypeID)

noms
Starting Member

22 Posts

Posted - 2009-02-17 : 07:20:19

i tried that, the query is working the only problem is this last join "[ON_KEY_42R5_UDM2].dbo.fn_astTypeGetSecondHighestAssetTypeBelowRoot(astAssets.AssetTypeID)", i i remove it it's works fine but with it i'm getting the error "The multi-part identifier "astAssets.AssetTypeID" could not be bound."

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-17 : 07:49:05

Can you post the code of fn_astTypeGetSecondHighestAssetTypeBelowRoot

noms
Starting Member

22 Posts

Posted - 2009-02-17 : 07:54:55

here it is:
"ALTER FUNCTION [dbo].[fn_astTypeGetSecondHighestAssetTypeBelowRoot](@aAssetTypeID int) RETURNS INT
AS
BEGIN
DECLARE @liParentID INT
DECLARE @liTypeID INT
DECLARE @liTreeLevel INT

-- Make sure the type exists, otherwise will go into endless loop
SELECT @liParentID = ParentID,
@liTreeLevel = TreeLevel
FROM astTypes
WHERE ID = @aAssetTypeID

IF (@liParentID IS NULL)
RETURN NULL

IF @liTreeLevel = 1
BEGIN
SET @liTypeID = @aAssetTypeID
END
ELSE
BEGIN
SET @liTypeID = dbo.fn_astTypeGetSecondHighestAssetTypeBelowRoot(@liParentID)
END

RETURN @liTypeID
END"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-17 : 08:28:22

seems like this:-


SELECT astAssets.ID AS AssetID,
vw_astDepreciationTransactions.FinPeriodID AS PeriodID,
finPeriods.Code AS PeriodCode,
vw_astDepreciationLinks.StartDate AS StartDate,
finCostCentres.ID AS CostCentreID,
astTypes.SeqNum AS AssetFinancialClassSeqNum
FROM 
(SELECT *,dbo.fn_astTypeGetSecondHighestAssetTypeBelowRoot(AssetTypeID) AS DerValue
FROM [ON_KEY_42R5_UDM2]..astAssets 
)
astAssets 
INNER JOIN [ON_KEY_42R5_UDM2]..vw_astDepreciationLinks 
ON astAssets.[ID] =ON_KEY_42R5_UDM2]..vw_astDepreciationLinks.RecordID
INNER JOIN [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions 
ON [ON_KEY_42R5_UDM2]..vw_astDepreciationLinks.[ID] = [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.DepreciationLinksID
INNER JOIN [ON_KEY_42R5_UDM2]..finPeriods 
ON [ON_KEY_42R5_UDM2]..finPeriods.[ID] = [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.FinPeriodID
INNER JOIN [ON_KEY_42R5_UDM2]..finGLCodes 
ON [ON_KEY_42R5_UDM2]..finGLCodes.[ID] = [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.GLID
INNER JOIN [ON_KEY_42R5_UDM2]..finCostCentres 
ON [ON_KEY_42R5_UDM2]..finCostCentres.[ID] = [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions.CostCentreID
INNER JOIN [ON_KEY_42R5_UDM2]..astTypes 
ON [ON_KEY_42R5_UDM2]..astTypes.[ID] = astAssets.DerValue

noms
Starting Member

22 Posts

Posted - 2009-02-17 : 09:17:50

thanks,
i've changed it slightly assigning alias like this:

SELECT astAssets.ID AS AssetID,
DT.FinPeriodID AS PeriodID,
FP.Code AS PeriodCode,
DL.StartDate AS StartDate,
FCC.ID AS CostCentreID,
AT.SeqNum AS AssetFinancialClassSeqNum
FROM
(SELECT *,dbo.fn_astTypeGetSecondHighestAssetTypeBelowRoot(AssetTypeID) AS DerValue
FROM [ON_KEY_42R5_UDM2]..astAssets
)
astAssets
INNER JOIN [ON_KEY_42R5_UDM2]..vw_astDepreciationLinks DL
ON astAssets.[ID] =DL.RecordID
INNER JOIN [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions DT
ON DL.[ID] = DT.DepreciationLinksID
INNER JOIN [ON_KEY_42R5_UDM2]..finPeriods FP
ON FP.[ID] = DT.FinPeriodID
INNER JOIN [ON_KEY_42R5_UDM2]..finGLCodes GLC
ON GLC.[ID] = DT.GLID
INNER JOIN [ON_KEY_42R5_UDM2]..finCostCentres FCC
ON FCC.[ID] = DT.CostCentreID
INNER JOIN [ON_KEY_42R5_UDM2]..astTypes AT
ON AT.[ID] = astAssets.DerValue

it's works but i'm concerned about this 'astAssets' table in the from clause after the inner select, does it also select
astAssets.ID from the [ON_KEY_42R5_UDM2] database??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-17 : 09:23:18

quote:
Originally posted by noms

thanks,
i've changed it slightly assigning alias like this:

SELECT astAssets.ID AS AssetID,
DT.FinPeriodID AS PeriodID,
FP.Code AS PeriodCode,
DL.StartDate AS StartDate,
FCC.ID AS CostCentreID,
AT.SeqNum AS AssetFinancialClassSeqNum
FROM
(SELECT *,dbo.fn_astTypeGetSecondHighestAssetTypeBelowRoot(AssetTypeID) AS DerValue
FROM [ON_KEY_42R5_UDM2]..astAssets
)
astAssets
INNER JOIN [ON_KEY_42R5_UDM2]..vw_astDepreciationLinks DL
ON astAssets.[ID] =DL.RecordID
INNER JOIN [ON_KEY_42R5_UDM2]..vw_astDepreciationTransactions DT
ON DL.[ID] = DT.DepreciationLinksID
INNER JOIN [ON_KEY_42R5_UDM2]..finPeriods FP
ON FP.[ID] = DT.FinPeriodID
INNER JOIN [ON_KEY_42R5_UDM2]..finGLCodes GLC
ON GLC.[ID] = DT.GLID
INNER JOIN [ON_KEY_42R5_UDM2]..finCostCentres FCC
ON FCC.[ID] = DT.CostCentreID
INNER JOIN [ON_KEY_42R5_UDM2]..astTypes AT
ON AT.[ID] = astAssets.DerValue

it's works but i'm concerned about this 'astAssets' table in the from clause after the inner select, does it also select
astAssets.ID from the [ON_KEY_42R5_UDM2] database??

i've put *. so it will select all columns from [ON_KEY_42R5_UDM2]..astAssets table. in practise, please replace * with only columns you want for optimal performance.

noms
Starting Member

22 Posts

Posted - 2009-02-17 : 09:30:13

thanks so much,
i will run it tomorrow

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-17 : 09:33:05

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources