Forum Stats

  • 3,838,564 Users
  • 2,262,383 Discussions
  • 7,900,687 Comments

Discussions

Results not correct when using HAVING

User_K2YTR
User_K2YTR Member Posts: 5 Green Ribbon

I keep receiving two results when it should be one. Any ideas why? I have tried using FETCH but I believe since I am running 11g, it will not work. Included screenshot and example data from the view.


select woq.wq_number,woq.quote_amount,woq.wq_version from ba_view_wo_quote woq

    left join sm_header smh on woq.woo_auto_key=smh.woo_auto_key

    where WOQ.WOO_AUTO_KEY = SMH.WOO_AUTO_KEY and woq.wo_number = 'WO1110885'

     and woq.quote_amount<>'0'

    group by woq.wq_version,woq.quote_amount,woq.wq_number,woq.quote_date

    having woq.wq_number=max(woq.wq_number)

    order by woq.wq_version desc

Tagged:
«1

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,243 Red Diamond
    edited Nov 8, 2021 2:14PM

    Hi, @User_K2YTR

    Not everyone who wants to help you can or will open attachments. Post everything, including sample data (CREATE TALE and INSERT statements) and desired results, right in this space.

     group by woq.wq_version,woq.quote_amount,woq.wq_number,woq.quote_date

    means the result set (before applying the HAVING clause) will contain one row for each distinct combination of woq.wq_version,woq.quote_amount, woq.wq_number and woq.quote_date. After applying

    having woq.wq_number=max(woq.wq_number)

    the result set can still have one row for each distinct combination of woq.wq_version,woq.quote_amount and woq.quote_date.

    [EDIT: Actually, the result will still have one row for each distinct combination of all three. See my next message.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,594 Red Diamond

    Not many people want to download files from unknown sources, So post create table and insert data sql statements. Anyway, your query returns one row per woq.wq_version, woq.quote_amount, woq.wq_number, woq.quote_date. So adding having woq.wq_number=max(woq.wq_number) simply makes no difference. Explain in words what you are trying to achieve.

    SY.

  • User_K2YTR
    User_K2YTR Member Posts: 5 Green Ribbon

    Thank you for the response and comments. I need to bring in the highest WQ_VERSION for each WO_NUMBER or WOO_AUTO_KEY. In other words, the latest version of the quote.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,243 Red Diamond

    Hi, @User_K2YTR

    The HAVING clause is evaluated separately for each group. If wq_number is in the GROUP BY clause, then every row in the group will have the same value of wq_number (unless wq_number is NULL), and that value will be the same as MAX (wq_number).

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,243 Red Diamond

    Hi,

    I need to bring in the highest WQ_VERSION for each WO_NUMBER or WOO_AUTO_KEY. In other words, the latest version of the quote.

    If you want the highest version of wq_version, then use MAX (wq_version), not MAX (wq_number)

    Once again, post CREATE TABLE and INSERT statements for a little sample data, and the exact results you want from that data. Explain what you want to do in case of ties, where mutiple groups have the same value of wq_version.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,594 Red Diamond
    edited Nov 8, 2021 2:25PM

    Since your query already selects specific WO_NUMBER you can get last version using:

    select  woq.wq_number,
            woq.quote_amount,
            woq.wq_version
      from      ba_view_wo_quote woq
            left join
                sm_header smh
              on woq.woo_auto_key=smh.woo_auto_key
      where WOQ.WOO_AUTO_KEY = SMH.WOO_AUTO_KEY
        and woq.wo_number = 'WO1110885'
        and woq.quote_amount<>'0'
      order by woq.wq_version desc
      fetch first row only
    /
    

    SY.

  • User_K2YTR
    User_K2YTR Member Posts: 5 Green Ribbon

    Hi Sy, I thought of using FETCH but I keep getting the error below when using your statement. Is it because I am running 11g?



    ORA-00933: SQL command not properly ended

    00933. 00000 - "SQL command not properly ended"

    *Cause:   

    *Action:

    Error at Line: 12 Column: 3

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,594 Red Diamond

    Ah, I missed you are on 11G:

    with t as (
               select  woq.wq_number,
                       woq.quote_amount,
                       woq.wq_version,
                       row_number() over(order by woq.wq_version desc) rn
                 from      ba_view_wo_quote woq
                       left join
                           sm_header smh
                         on woq.woo_auto_key=smh.woo_auto_key
                 where WOQ.WOO_AUTO_KEY = SMH.WOO_AUTO_KEY
                   and woq.wo_number = 'WO1110885'
                   and woq.quote_amount<>'0'
              )
    select  woq.wq_number,
            woq.quote_amount,
            woq.wq_version
      from  t
      where rn = 1
    /
    

    SY.

  • User_K2YTR
    User_K2YTR Member Posts: 5 Green Ribbon

    Thank you Sy but I am getting this error:

    ORA-00904: "WOQ"."WQ_VERSION": invalid identifier

    00904. 00000 - "%s: invalid identifier"

    *Cause:   

    *Action:

    Error at Line: 16 Column: 9

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,594 Red Diamond

    Oops, cut & paste error:

    with t as (
               select  woq.wq_number,
                       woq.quote_amount,
                       woq.wq_version,
                       row_number() over(order by woq.wq_version desc) rn
                 from      ba_view_wo_quote woq
                       left join
                           sm_header smh
                         on woq.woo_auto_key=smh.woo_auto_key
                 where WOQ.WOO_AUTO_KEY = SMH.WOO_AUTO_KEY
                   and woq.wo_number = 'WO1110885'
                   and woq.quote_amount<>'0'
              )
    select  wq_number,
            quote_amount,
            wq_version
      from  t
      where rn = 1
    /
    


    SY.