multi-part identifier could not be bound

Question

Post reply

multi-part identifier could not be bound

Kaushik Majumder

Ten Centuries

Points: 1009
More actions
January 7, 2010 at 7:33 am

#156563

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Nabha SSCrazy Eights Points: 8534 More actions · Answer 1

Dont you think you should be putting this after the ON clause ?

AND (([start_date] < a.delivery_dtime) AND (end_date > a.delivery_dtime))

---------------------------------------------------------------------------------

Nabha SSCrazy Eights Points: 8534 More actions · Answer 2

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)))

---------------------------------------------------------------------------------

Kaushik Majumder Ten Centuries Points: 1009 More actions · Answer 3

Thank you so much,

The second query works like a breeze.......However, still I want to know where I was going wrong please.

Regards,

jcdyntek Ten Centuries Points: 1153 More actions · Answer 4

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

Kaushik Majumder Ten Centuries Points: 1009 More actions · Answer 5

Thanks, I got confused with the co-related subquery.

Regards.