SQL Server Performance Forum – Threads Archive
Help with this query!!
This one is giving me headaches!! I have this query: select m.EmpID as EmpID, m.fecha as Since,m.motivo as Notes, m.ClvDelMovto AS AreaFamilia, p.sexo as Sexo,
p.activo as Activo from movimientos m inner join personal p on m.empid=p.empid
group by Movimientos.empid,Personal.sexo,Personal.activo; i parse it and is correct, but when y try to run it, it returns this error: Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "Movimientos.empid" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "Personal.sexo" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "Personal.activo" could not be bound. Can somebody help me? Love… how is it?
Change the last line of your query to group by m.empid,p.sexo,p.activo; Roji. P. Thomas
http://toponewithties.blogspot.com
select m.EmpID as EmpID, m.fecha as Since,
m.motivo as Notes, m.ClvDelMovto AS AreaFamilia, p.sexo as Sexo,
p.activo as Activo from movimientos m inner join personal p on m.empid=p.empid
group by m.empid,p.sexo,p.activo; —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/
hi Roji. P. Thomas and thanks for your help!
the error is gone, but now i got this one: Msg 8120, Level 16, State 1, Line 2
Column ‘movimientos.Fecha’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. what do this mean or reffer to? Love… how is it?
Sorry, I missed that. But you have to use all the columns in the GROUP BY or use a proper aggregate function. Roji. P. Thomas
http://toponewithties.blogspot.com
select m.EmpID as EmpID, m.fecha as Since,
m.motivo as Notes, m.ClvDelMovto AS AreaFamilia, p.sexo as Sexo,
p.activo as Activo from movimientos m inner join personal p on m.empid=p.empid
group by m.empid, m.fecha,m.motivo, m.ClvDelMovto , p.sexo,p.activo; —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/
Can you explain to me whats a proper aggregate function? the custom ones?
Ok, i did change the script, it ended like this: select m.EmpID as EmpID, max(m.fecha) as Since,
m.motivo as Notes, m.ClvDelMovto AS AreaFamilia, p.sexo as Sexo,
p.activo as Activo from movimientos m inner join personal p on m.empid=p.empid
WHERE P.Activo<>NULL and M.TipoDeMovto="CA"
group by M.empid,P.sexo,P.activo,m.fecha,m.motivo,m.ClvDelMovto; but it returns me this error:
Msg 207, Level 16, State 1, Line 4
Invalid column name ‘CA’. i guess that sql does not accept this kind of comparation, ’cause i had another error, comparing to "" it returner other error, so i changed it to null, but that was null, so how can i solve this that is not null, that is a data? By the way, i adapted the script from an old version (access97), i did it right?
This is the old script: SELECT Movimientos.EmpID AS EmpID,Max(Movimientos.Fecha) AS Since,
Last(Movimientos.Motivo) AS Notes,Last(Movimientos.ClvDelMovto) AS AreaFamilia,
Personal.Sexo AS Sexo, Personal.Activo AS Activo
FROM Movimientos INNER JOIN Personal ON Movimientos.EmpID=Personal.EmpID
WHERE Personal.Activo<>"" and Movimientos.TipoDeMovto="CA"
GROUP BY Movimientos.EmpID, Personal.Sexo, Personal.Activo; Love… how is it?
WHERE P.Activo<>NULL and M.TipoDeMovto="CA" Change this to
WHERE P.Activo<>NULL and M.TipoDeMovto=’CA’
—
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
Thanks FrankKalis, as i though, it solved the problem, but it returns nothing, any suggestions? Love… how is it?
Oops, sorry. I’ve focused that much on this "CA" thingy, that I’ve overlooked that you should change <> NULL to IS NOT NULL. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
Ohhh god! thank you FrankKalis, I overlooked it too, but I have one last question, on the old script, it returns 2804 rows, but it does not has the other columns in the group by sentence, so when i added them, it returns 4154, anyway, i dont really care, it works now, but the question is, why sql server 2k5 does not accept the script without the other columns? is not posible? so much thanks!
ohne gott Love… how is it?
This is not for SQL Server 2k5 alone. It is because of RDBMS.If you are going to select some columns and have the group by functionality then all the columns in the select Clause must be there in the Group by clause and it is logical. When the group by is done for more columns naturally the query will result in more rows only.
I think no need of all columns in select query should come in the GROUPBY,only non-aggregate cols
for example :in the above query by Ohne ,he has used max(m.fecha) in select query and then m.fecha in GROUP BY.i dont think it is needed to be used in GROUP BY again.Can anyone give a suggestion
Manesh, that is correct. Muthuselvan wasn’t terribly precise, but (s)he must have meant all non-aggregate columns.
]]>