December 19, 2011 at 2:00 am
When i run this stored proc it falls over with the multi-part idenfier....Please any help will be appreciaated..Many thanks
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Expr1015'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col2408'.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1007.numSale_route" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1005.numAsset_instance_id" could not be bound.
ALTER PROCEDURE AS
BEGIN
SET NOCOUNT ON
-- clear table
TRUNCATE TABLE dbo.VR_Auction_PricingVolumes
CREATE TABLE #a
(AssetInstanceId int, DisposalType varchar(50))
INSERT #a
SELECT
currA.numAsset_instance_id,
CASEWHEN isnull(vcca.numAsset_instance_id, 0) > 0 THEN ''Retail''
WHEN isnull(vcaa.numAsset_instance_id, 0) > 0 THEN ''Auction''
ELSE ''Unassigned''
END
FROM VR_DataLoad.prdrmkt.dbo.v_current_asset_instance currA
LEFT JOIN VR_DataLoad.prdrmkt.dbo.v_current_channel_asset vcca
ON vcca.numAsset_instance_id = currA.numAsset_instance_id
LEFT JOIN VR_DataLoad.prdrmkt.dbo.v_current_auction_asset vcaa
ON vcaa.numAsset_instance_id = currA.numAsset_instance_id
JOIN (
SELECT MAX(dteCreated) Created, numAsset_instance_id
FROM VR_DataLoad.prdrmkt.dbo.tbl_asset_status
WHERE numStatus_id = 57 or numStatus_id = 59
GROUP BY numAsset_instance_id
) ast
ON ast.numAsset_instance_id = currA.numAsset_instance_id
-- New section added here to resolve issue with linked server access
-- Get all of the data from the linked server first, before doing the SELECT/WHERE
CREATE TABLE #b
(SalesRoute varchar(50), SalesRouteId int, DisposalRoute varchar(50),
ActionedBy varchar(201), CreatedDate datetime, FullName varchar(201), UserId varchar(50))
INSERT #b
(SalesRoute, SalesRouteId, DisposalRoute, ActionedBy, CreatedDate, FullName, UserId)
SELECT
isNull(lsr.vchrDescription, '''') AS SaleRoute,
isNull(ai.numSale_route, 0) AS SalesRouteId,
isNull(#a.DisposalType, '''') AS DisposalRoute,
isnull(u.vchrFull_name, isnull(ap.vchrUser_id,''Unknown'')) ActionedBy,
isNull(ap.dteCreated, ''1-Jan-1900'') AS CreatedDate,
u.vchrFull_name AS FullName,
ap.vchrUser_id AS UserId
FROM VR_DataLoad.prdrmkt.dbo.v_current_asset_instance currA
JOIN VR_DataLoad.prdrmkt.dbo.tbl_asset_instance ai
ON ai.numAsset_instance_id = currA.numAsset_instance_id
JOIN (
SELECT MAX(dteCreated) Created, numAsset_instance_id
FROM VR_DataLoad.prdrmkt.dbo.tbl_asset_status
WHERE numStatus_id = 57 OR numStatus_id = 59
GROUP BY numAsset_instance_id
) ast
ON ast.numAsset_instance_id = currA.numAsset_instance_id
JOIN (
SELECT MAX(dteCreated) Created, numAsset_instance_id
FROM VR_DataLoad.prdrmkt.dbo.tbl_asset_price
GROUP BY numAsset_instance_id
) tap
ON tap.numAsset_instance_id = ast.numAsset_instance_id
JOIN (
SELECT DISTINCT numAsset_instance_id, dteCreated, vchrUser_id
FROM VR_DataLoad.prdrmkt.dbo.tbl_asset_price
) ap
ON ap.numAsset_instance_id = tap.numAsset_instance_id
AND ap.dteCreated = tap.Created
JOIN VR_DataLoad.prdrmkt.dbo.tbl_lu_sale_route lsr
ON lsr.numSale_route_id = ai.numSale_route
JOIN #a
ON #a.AssetInstanceId = currA.numAsset_instance_id
LEFT JOIN VR_DataLoad.prdrmkt.dbo.tbl_user u
ON u.vchrUser_id = ap.vchrUser_id
INSERT dbo.VR_Auction_PricingVolumes
(SalesRoute, SalesRouteId, DisposalRoute, ActionedBy, CreatedDate)
SELECT SalesRoute, SalesRouteId, DisposalRoute, ActionedBy, CreatedDate
FROM #b
WHERE
FullName NOT IN (''HPI'', ''CPI'')
AND UserId NOT IN (''HPI'', ''CPI'')
GROUP BY
SalesRoute,
SalesRouteId,
DisposalRoute,
ActionedBy,
CreatedDate
DROP TABLE #a
DROP TABLE #b
END
'
December 19, 2011 at 2:19 am
pespes009 (12/19/2011)
When i run this stored proc it falls over with the multi-part idenfier.......
Easy to check if this is the case
SELECT TOP 10 * FROM VR_DataLoad.prdrmkt.dbo.v_current_asset_instance
SELECT TOP 10 * FROM VR_DataLoad.prdrmkt.dbo.v_current_channel_asset
SELECT TOP 10 * FROM VR_DataLoad.prdrmkt.dbo.v_current_auction_asset
SELECT TOP 10 * FROM VR_DataLoad.prdrmkt.dbo.tbl_asset_status
SELECT TOP 10 * FROM VR_DataLoad.prdrmkt.dbo.tbl_asset_instance
SELECT TOP 10 * FROM VR_DataLoad.prdrmkt.dbo.tbl_asset_price
SELECT TOP 10 * FROM VR_DataLoad.prdrmkt.dbo.tbl_lu_sale_route
SELECT TOP 10 * FROM VR_DataLoad.prdrmkt.dbo.tbl_user
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 19, 2011 at 2:34 am
Tried it and it worked but the i think the join on VR_DataLoad.prdrmkt.dbo.tbl_asset_instance ai
December 19, 2011 at 2:35 am
Are you sure column names are correct?
"Keep Trying"
December 19, 2011 at 2:36 am
Tried it and it worked but the i think the join on VR_DataLoad.prdrmkt.dbo.tbl_asset_instance ai is actually the issue cos the multi-part identifier is the challenge
cos thats where the error is coming from...pls look at the error message again
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Expr1015'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col2408'.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1007.numSale_route" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1005.numAsset_instance_id" could not be bound.
December 19, 2011 at 2:50 am
The second column here isn't named:
INSERT #a
SELECT
currA.numAsset_instance_id,
CASE WHEN isnull(vcca.numAsset_instance_id, 0) > 0 THEN ''Retail''
WHEN isnull(vcaa.numAsset_instance_id, 0) > 0 THEN ''Auction''
ELSE ''Unassigned''
END
FROM VR_DataLoad.prdrmkt.dbo.v_current_asset_instance currA
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 19, 2011 at 3:06 am
Which column please highlight ...
Many thanks
December 19, 2011 at 3:11 am
pespes009 (12/19/2011)
Which column please highlight ...Many thanks
The one which is referred to in the second query:
isNull(#a.DisposalType, '''') AS DisposalRoute,
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 19, 2011 at 3:23 am
You will need to work outside of the stored procedure, checking each query separately, in order to get this working. Can you do this? Here's the first part. You don't need to worry about the insert into the temp table at this point.
SELECT
currA.numAsset_instance_id,
DisposalType = CASE
WHEN isnull(vcca.numAsset_instance_id, 0) > 0 THEN ''Retail''
WHEN isnull(vcaa.numAsset_instance_id, 0) > 0 THEN ''Auction''
ELSE ''Unassigned''
END
FROM VR_DataLoad.prdrmkt.dbo.v_current_asset_instance currA
LEFT JOIN VR_DataLoad.prdrmkt.dbo.v_current_channel_asset vcca
ON vcca.numAsset_instance_id = currA.numAsset_instance_id
LEFT JOIN VR_DataLoad.prdrmkt.dbo.v_current_auction_asset vcaa
ON vcaa.numAsset_instance_id = currA.numAsset_instance_id
-- an existence check would be more efficient than this
JOIN (
SELECT MAX(dteCreated) Created, numAsset_instance_id
FROM VR_DataLoad.prdrmkt.dbo.tbl_asset_status
WHERE numStatus_id = 57 or numStatus_id = 59
GROUP BY numAsset_instance_id
) ast
ON ast.numAsset_instance_id = currA.numAsset_instance_id
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 19, 2011 at 3:27 am
its being highlighted in the temporary table above...although i did not paste that here
I think the problem is with the join on the
JOIN VR_DataLoad.prdrmkt.dbo.tbl_asset_instance ai
not that at all
Please take another look at the error generated
Many Thanks
December 19, 2011 at 3:34 am
pespes009 (12/19/2011)
its being highlighted in the temporary table above...although i did not paste that hereI think the problem is with the join on the
JOIN VR_DataLoad.prdrmkt.dbo.tbl_asset_instance ai
not that at all
Please take another look at the error generated
Many Thanks
No. Please run the code I posted.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply