1. Forum
  2. Usenet
  3. COMP.DATABASES.IBM-DB2

  • SQL group by having issue

    From declamps@gmail.com@21:1/5 to All on Tue Sep 20 09:31:47 2016
    Hi

    I have this code:
    I am getting NEWID is not valid in the context where it is used sqlstate 42703,I think the issue is with the having clause, does anyone know why I cant use having NewID even though its a column in my select block?



    HATSTABLE1 (HATId, NewID) as (
    select HA.HATId as "ID",
    round(
    cast(
    sum(
    case when HA.ID = 4 or
    HA.ID < 0
    then 1 else 0 end
    ) AS FLOAT
    ) / count(*) * 100,
    2
    ) AS NewID
    from Hats T
    join Heads HD on
    T.ID=HD.HatID
    group by T.ID
    having NewID >1)

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From --CELKO--@21:1/5 to All on Wed Sep 21 15:25:33 2016
    Where is the DDL? Did you notice that the alias HA is not defined in this code? Why you think T is a good alias for hats? Why are you using float and comparing it to integers? You need to get a book on basic data modeling; there is no such thing as "id"
    in RDBMS. It has to be "<something in particular>_id" to be valid under ISO 11179 rules and common sense.

    It looks like you expected the group by to work on the some and count in your second expression. This is really not a good, good things you found out. I put your code fragment interview so they could actually be compiled.

    CREATE VIEW Hats (hat_id, new_hat_id)
    AS
    SELECT HA.hat_id,
    ROUND(CAST(SUM(
    CASE WHEN HA.hat_id = 4.0
    OR HA.hat_id < 0.0
    THEN 1.0 ELSE 0.0 END) AS FLOAT)
    / COUNT(*) * 100.00, 2)
    AS new_hat_id
    FROM Hats AS T,
    Heads AS HD
    WHERE T.hat_id = HD.hat_id
    GROUP BY T.hat_id
    HAVING new_hat_id > 1.0;

    I will be honest, I cannot figure out what you are trying to do. However, Jeremy, having clause does not have to have a group by associated with it. When the group by is missing the entire table is taken as a single group. This implies that everything
    has to be aggregated in the select list.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)