SQL Server Performance

t-sql in sql server 2005

Discussion in 'General Developer Questions' started by aaron, Jan 23, 2010.

  1. aaron New Member

    I am new to t-sql in the last few months. I am getting the following error in sql server 2005 and would like to know how to get around the problem:
    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "Z.MaxReceiveDate" could not be bound.
    The sql that I am running is the following:
    select distinct Z.gnumber, R.Requests, Z.MaxReceiveDate


    (select distinct gnumber,
    count(*) as Requests
    from dbo.table1
    where receive_date > Z.MaxReceiveDate
    group by gnumber
    ) R

    left join
    (select distinct P.gnumber,
    MaxReceiveDate = Max(Received_Date),
    from dbo.table2
    group by P.gnumber
    ) Z
    On Z.HNumber = R.Hnumber
    I am bascially trying to use the maxreceivedate value from a subquery.
    Can you suggest ways to solve this problem?
  2. preethi Member

    Welcome to forums!
    As some of the columns do not have prefix to specify from which table you are getting them, This code may through few errors, but a code like this may help you
    select distinct Z.gnumber, count(R.*) AS Requests, Z.MaxReceiveDate
    from dbo.table1 R
    (select distinct P.gnumber,
    MaxReceiveDate = Max(P.Received_Date),
    from dbo.table2 P
    group by P.gnumber
    ) Z
    On Z.HNumber = R.Hnumber
    where receive_date > Z.MaxReceiveDate
    group by z.gnumber, Z.MaxReceiveDate
    [quote user="aaron"] where receive_date > Z.MaxReceiveDate [/quote]
    The Issue you have in your code is, you are referring the columns taken from Z. But within the query Z is not defined.
    One more point: Please do not post multiple entries. Keep it in one place.

Share This Page