Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2006
    Posts
    56

    Unanswered: Subquery: rownum Trouble

    I am having hard time understanding why an SQL I wrote fails. Much appreciated if anybody can help. . . .

    Code:
    select *
      from CALC_RESULTS1
     where CALC__KEY in (
                  select CALC__KEY
                    from CALC_HDR1
                   where EMPLOYEES__KEY = 'Q000041'
                     and rownum = 1
                   order by CALC__KEY desc)
    Why this ^ returns an error, "Right parenthesis is missing,"

    Code:
    select *
      from CALC_RESULTS1
     where CALC__KEY in (
                  select max(CALC__KEY)
                    from CALC_HDR1
                   where EMPLOYEES__KEY = 'Q000041')
    whereas this ^ runs okay?

    The purpose of these SQL commands is to retrieve the latest payment calculation results for Employee Q000041. Employee IDs are linked with calculation keys in Table CALC_HDR1. The CALC_RESULTS1 table stores calc results of salary calculations. Each row represents a different payment element such as base salary, overtime pay and expenses (redering the relationship between CALC_HDR1 and CALC_RESULTS1 "one or many").

    Each time the payment calculation is executed, the system issues an calc key based on nextVal. Thus the larger the calc key is, the newer the results are. As payment calculation can take place any number of times, the relationship between EMPLOYEES (whose primary key being EMPLOYEES__KEY) and CALC_HDR1 is also "one or many".

    When executed independently, the subquery fetching the latest CALC__KEY from the header table works fine. Why does it fail when part of a larger query?

    Code:
    select CALC__KEY
      from CALC_HDR1
     where EMPLOYEES__KEY = 'Q000041'
       and rownum = 1
     order by CALC__KEY desc

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what is the purpose of the rownum column, and how are its values stored?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2006
    Posts
    56

    This Thread Needs to Be Moved?

    The column rownum is a database-internal column used to count the number of the records retrieved: at least I understand it so, using it in Oracle implementations. If the rownum column/parameter/function is not part of standard SQL but Oracle vernacular, I would like the moderators to move the thread to Oracle Forum.

    select * from TABLE
    where rownum = 10

    is used to limit the rows to betrieved (up) to ten.

    Sorry for the confusion,
    Ikviens

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aha, that thing

    okay, i'll move this thread to the oracle forum for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >select * from TABLE where rownum = 10 is used to limit the rows to betrieved (up) to ten.
    WRONG!
    The query above will always return ZERO rows.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I know you can't use order by in a sub-query. I was trying to find it in the documents to back-up this statement but I cannot find it. However, what is wrong with your second query ? The way you're trying to use rownum is not correct either. You should first order by and then limit the query resultset with rownum in an outer query for that to work.

  7. #7
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    I think ORDER BY in a subquery was a new feature feature in Oracle 8i (i.e. late last century).

    Quote Originally Posted by Ikviens
    select * from TABLE
    where rownum = 10

    is used to limit the rows to retrieved (up) to ten.
    I think you meant

    Code:
    where rownum <= 10
    ROWNUM (which is purely an Oracle thing, or at least if other vendors have something with the same name I wouldn't count on it working the same way) is assigned to the result set after filtering but before ordering, so there is no rownum=2 until there has first been a row 1.
    Last edited by WilliamR; 04-17-06 at 12:53.

  8. #8
    Join Date
    Sep 2004
    Posts
    60
    Ikvines,

    Can't we use 'Rowid' in place of rownum?
    For example changing condition to ::

    select *
    from CALC_RESULTS1 C1
    where C1.CALC__KEY in (
    select CALC__KEY
    from CALC_HDR1 CH1
    where CH1.rowid = (select max (rowid) from CALC_HDR1 where EMPLOYEES__KEY = 'Q000041')
    )

    This code may not be 100 % correct (for your tables ), but I tried same concept in my test tables.

    AD

  9. #9
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You cant use rowid to get the last record in calc_hdr1 (or max(calc_key), you will just get a row at random potentially as rowid can be reused.

    Stick with JMartinez's solution.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •