Multi part identifier - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multi part identifier

Author

Topic

rich122
Starting Member

2 Posts

Posted - 2011-04-26 : 07:54:03

Hi,

Hope you can help with this query, which is generating the following error:

The multi-part identifier "partnum" could not be bound

SELECT
max(a.TranDate),
a.PartNum,
a.PartDescription
FROM
dbo.Parttran a,
dbo.Partbin
INNER JOIN
partbin b on a.partnum = b.partnum
GROUP BY
a.TranDate
HAVING max(a.TranDate) < dateadd(year,-2,getdate())

What this query should hopefully do is show me all partnumbers that have had no transactions against them in the last two years so they can be retired. I am fairly new to the HAVING statement, and I seem to gather its not the best filter method but can't seem to stop this query pulling in transaction less than two years.

Thanks in advance.

Pass it on

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-26 : 08:01:57

I think you use two time pardbin table in join
Remove 7 row and (,) form sixt row and try

Raghu' S

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-26 : 08:02:58

You have 2 references to the partbin table, but you only join to one of them. You are also grouping by TranDate, while trying to filter it in the HAVING clause. This should do the trick:

SELECT
max(a.TranDate),
a.PartNum,
a.PartDescription
FROM dbo.Parttran a
INNER JOIN partbin b on a.partnum = b.partnum
GROUP BY a.PartNum, a.PartDescription
HAVING max(a.TranDate) < dateadd(year,-2,getdate())

rich122
Starting Member

2 Posts

Posted - 2011-04-27 : 10:38:48

Hi,

Thanks to both of you for your replies. I managed to get it working, had to take out the description field as it seemed to create some sort of varchar error. But not an essential field.

I appreciate the quick response.

Rich

Pass it on

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources