January 7, 2010 at 7:33 am
The following query throws an error...
Msg 4104, Level 16, State 1, Procedure prc_Recalc_Rates, Line 24
The multi-part identifier "a.delivery_dtime" could not be bound.
Msg 4104, Level 16, State 1, Procedure prc_Recalc_Rates, Line 24
The multi-part identifier "a.delivery_dtime" could not be bound.
CREATE PROCEDURE prc_Recalc_Rates
.......................................................
.......................................................
.......................................................
IF OBJECT_ID('tempdb..##tmp_transaction') is null
SELECT * INTO ##tmp_transaction
FROM dbo.tr_transactions a
INNER JOIN
(SELECT DISTINCT scheme_id
FROM dbo.tr_scheme_rates
WHERE recalculate_transactions = 1
AND (([start_date] < a.delivery_dtime) AND (end_date > a.delivery_dtime))) b
ON a.scheme_id = b.scheme_id
.......................................................
.......................................................
The tr_scheme_rates table does have multiple instances of same schemeId, however, there are'nt any repeatition of the scheme_id, start_date, end_date combination. I need to weed out the mutiple instances of schemeid (from tr_scheme_rates), otherwise, mutiple instances of a single schemeid will cause the records from tr_transaction (INNER JOINED with schemeid) to be reported multiple times.
Where I am going wrong ? Any word of advise will be greatly appreciated please.
Regards
January 7, 2010 at 7:42 am
Dont you think you should be putting this after the ON clause ?
AND (([start_date] < a.delivery_dtime) AND (end_date > a.delivery_dtime))
---------------------------------------------------------------------------------
January 7, 2010 at 7:45 am
or may be this? (untested as there is no test data)
SELECT * INTO ##tmp_transaction
FROM dbo.tr_transactions a
Where a.scheme_id in (
(SELECT DISTINCT scheme_id
FROM dbo.tr_scheme_rates
WHERE recalculate_transactions = 1
AND (([start_date] < a.delivery_dtime) AND (end_date > a.delivery_dtime)))
---------------------------------------------------------------------------------
January 7, 2010 at 7:58 am
Thank you so much,
The second query works like a breeze.......However, still I want to know where I was going wrong please.
Regards,
January 7, 2010 at 8:02 am
SSC, the reason you have the error is you are trying to reference the main table in your inline view query in the inner join and you can not do that nabha statement should work but you will have to test or send data and structures
January 7, 2010 at 8:28 am
Thanks, I got confused with the co-related subquery.
Regards.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply