multipart identifier could not be bound on sql with cases when

Question

hello everyone i have a sql query in which i am using case when and inner join,

the problem i am facing is i am getting the error of multipart identifier

here is my sql query

SELECT 
CASE when row_num = 1 THEN bill_id ELSE NULL 
END as bill_id, listinvoice.sonvinid,
listinvoice.date, listinvoice.brandname,listinvoice.venue,listinvoice.zone,
listinvoice.location,
listinvoice.instructore,listinvoice.paymentid,listinvoice.amount
FROM (
select bill_id, row_number()
 over
 (partition by bill_id order by listinvoice.date asc)
  row_num, listinvoice.sonvinid, tid, listinvoice.date
  , listinvoice.brandname,listinvoice.venue,
  listinvoice.zone,listinvoice.location,
  listinvoice.instructore,paymentid,amount
from listinvoice
inner join sonvininsert
on 
sonvininsert.sonvinid=listinvoice.sonvinid
 where 
tid in (select tid from trainerdetails where empname='andrew charles')
and listinvoice.[date] between
'2015-02-02' and '2017-02-02'
)data

and my error

Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "listinvoice.sonvinid" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "listinvoice.date" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "listinvoice.brandname" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "listinvoice.venue" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "listinvoice.zone" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "listinvoice.location" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "listinvoice.instructore" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "listinvoice.paymentid" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "listinvoice.amount" could not be bound.

what will be the possible fixes for this?

Try changing all of the first SELECTs to data.columnnames from listinvoice.columnnames — Simon
From what you posted there is no need for this to have a subquery at all. — Sean Lange
@SeanLange This is off topic, but you have a really great understanding of queries and aggregation in general without visually seeing data samples... it is amazing. How do you do it? — Simon
I stand corrected. I didn't see the part where you were referencing your row_number column in the outer query. But others have already answered your question. — Sean Lange

DhruvJoshi DhruvJoshi · Accepted Answer · 2017-09-26T19:09:44

Your query is correct except for the alias of the inner query and requires a single change:

SELECT 
    CASE 
        when row_num = 1 
        THEN bill_id 
        ELSE NULL 
    END as bill_id, 
    listinvoice.sonvinid,
    listinvoice.date,
    listinvoice.brandname,
    listinvoice.venue,
    listinvoice.zone,
    listinvoice.location,
    listinvoice.instructore,
    listinvoice.paymentid,
    listinvoice.amount
FROM (
    select 
        bill_id, 
        row_number() over
            (partition by bill_id order by listinvoice.date asc) row_num, 
        listinvoice.sonvinid, 
        tid, 
        listinvoice.date , 
        listinvoice.brandname,
        listinvoice.venue,  
        listinvoice.zone,
        listinvoice.location,
        listinvoice.instructore,
        paymentid,
        amount
    from listinvoice
        inner join sonvininsert
            on 
            sonvininsert.sonvinid=listinvoice.sonvinid
    where 
        tid in 
            (
                select 
                    tid 
                from trainerdetails 
                where empname='andrew charles'
            )
    and listinvoice.[date] 
        between '2015-02-02' and '2017-02-02'
        )listinvoice -- change required here to correct the alias

multipart identifier could not be bound on sql with cases when

2 Answers