SQL Help, Livereport Multi Part Identifier error

Hi all,

I am getting the following error when I run the code below:-

[[Microsoft][ODBC SQL Server Driver][SQL Server]The multi-part identifier "k.groupid" could not be bound.

select k2.name, k.firstname + ' ' + k.lastname "User Name", k.name "Login Name",
convert (char(10), max(d.auditdate), 111) "Last Login Date"

from kuaf k,dauditnew d

Inner join kuaf k2 on k.groupid = k2.id

where d.performerid = k.id
and not exists (select performerid from dauditnew
where (performerid = k.id) and (auditid = 23)
and (auditdate >= (getdate()-%1))) and k.deleted <> 1 and k.type = 0
and k.userprivileges = 15
and k.id NOT IN ('1000','2022','2023','4093','425718')
group by k.lastname, k.firstname, k.name, k.groupid, k2.name
order by k.lastname

If I take out my Inner Join then the report runs ok, but I have added the inner join so that I can get the group name of users listed. Can anyone see any faults with my inner join?

Kind regards,

Fraz

Find more posts tagged with

Comments

Firaz_Alyas

Can anyone from OpenText help with this one?

Bhupinder_Singh

Let me know if the explanation for this error provided on the following site is of help:

http://aartemiou.blogspot.ca/2009/01/multi-part-identifier-could-not-be.html

- Bhupinder
----------------------------------------------
Bhupinder Singh, B.Math, B.Ed.
Systems Administrator, Cloud Services
Open Text, Waterloo, Ontario, Canada
----------------------------------------------

Firaz_Alyas

Hi Bhupinder,

Thanks for the link but im afraid I still cannot find the issue of adding my inner join in my query?

My Query works fine as :-

select k.name "Login Name",
convert (char(10), max(d.auditdate), 111) "Last Login Date", g.name as Department

from kuaf k,dauditnew d

group by k.lastname, k.firstname, k.name

order by k.lastname

but when i add in a inner join to get the users department group i start getting the error, where am i going wrong?

select k.name "Login Name",
convert (char(10), max(d.auditdate), 111) "Last Login Date", g.name as Department

from kuaf k,dauditnew d
INNER JOIN KUAF G on k.groupid = g.id

order by k.lastname

Colin_Schmidt

I don’t know whether you can mix the types of table references (I’m far from any authority).

from kuaf k,dauditnew d
INNER JOIN KUAF G on k.groupid = g.id

Try making it look like this and try it:

From kuaf k

Inner Join dauditnew d on k.id = d.performerid and d.auditid = 23 and d.auditdate >= (getdate()-%1)

Inner join kuag g on k.groupid = g.id

I may not have the parameters re-typed. I use this approach because it displays the reasons for extracting data table-by-table instead of all mixed together, but I don’t know which is “better”.

Colin

Tulsa, OK 74172

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, May 22, 2013 8:30 AM
To: eLink Recipient
Subject: SQL Help, Livereport Multi Part Identifier error

SQL Help, Livereport Multi Part Identifier error

Posted by falyas@buckscc.gov.uk (Alyas, Firaz) On 05-22-2013 09:28

Hi Bhupinder,

Thanks for the link but im afraid I still cannot find the issue of adding my inner join in my query?

My Query works fine as :-

select k.name "Login Name",
convert (char(10), max(d.auditdate), 111) "Last Login Date", g.name as Department

from kuaf k,dauditnew d

group by k.lastname, k.firstname, k.name

order by k.lastname

but when i add in a inner join to get the users department group i start getting the error, where am i going wrong?

select k.name "Login Name",
convert (char(10), max(d.auditdate) , 111) "Last Login Date", g.name as Department

from kuaf k,dauditnew d
INNER JOIN KUAF G on k.groupid = g.id

order by k.lastname

[To post a comment, use the normal reply function]
Topic:	SQL Help, Livereport Multi Part Identifier error
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Firaz_Alyas

Hi guys,

I have managed to get it going by using the following code:-

select k.name "Login Name",
convert (char(10), max(d.auditdate), 111) "Last Login Date", (select k2.name from [KUAF] k2 where k.groupid = k2.id) as Department

from kuaf k,dauditnew d

where d.performerid = k.id
and not exists (select performerid from dauditnew
where (performerid = k.id) and (auditid = 23)
and (auditdate >= (getdate()-%1))) and k.deleted <> 1 and k.type = 0
and k.userprivileges = 15
and k.id NOT IN ('1000','2022','2023','4093','425718')

group by k.lastname, k.firstname, k.name, k.groupid
order by k.lastname

Thanks for your advice though.

Fraz