SQL Server Performance

Help with this query!!

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by ohne, Oct 10, 2006.

  1. ohne New Member

    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?
  2. Roji. P. Thomas New Member

  3. dineshasanka Moderator

    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/
  4. ohne New Member

    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?
  5. Roji. P. Thomas New Member

  6. dineshasanka Moderator

    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/
  7. ohne New Member

    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?
  8. FrankKalis Moderator


    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
  9. ohne New Member

    Thanks FrankKalis, as i though, it solved the problem, but it returns nothing, any suggestions?

    Love... how is it?
  10. FrankKalis Moderator

    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
  11. ohne New Member

    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!

    :confused:
    ohne gott

    Love... how is it?
  12. Muthuselvan New Member

    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.
  13. Manesh Mathen New Member

    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
  14. Adriaan New Member

    Manesh, that is correct. Muthuselvan wasn't terribly precise, but (s)he must have meant all non-aggregate columns.

Share This Page