Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2009

    Question Unanswered: conversion of rownum in db2

    I know how to cover a single rownum in aststement. But i am confused with multiple rownums. Please help me in converting this statement to db2

    select distinct process_step.process_step_Name, process_step.process_step_Level,unit_operation.uni t_operation_Name from
    process_step,Product, Unit_Operation where product.product_id=unit_operation.product_id and process_step.process_step_id in
    (select process_step.process_step_id from process_step where process_step.unit_operation_id in
    (select unit_operation_id from unit_operation where unit_operation.product_id in
    (select product_id from product where product.product_type_id in
    (select product_type_id from
    (select product_type_id from product_type order by product_type_name) where rownum<=1)
    and Rownum<=1)
    and Rownum<=1))

    Thanks in Advance

  2. #2
    Join Date
    Sep 2004
    Quote Originally Posted by vivek.vivek
    Please help me in converting this statement to db2
    ... WHERE rownum<=1
    Replace this by "FETCH FIRST ROW ONLY".
    "WHERE rownum <= 7" would become "FETCH FIRST 7 ROWS ONLY".

    The second part of "rownum <= 1 AND Rownum <= 1" is superfluous, so again this is "FETCH FIRST ROW ONLY".
    The rownum in the subquery can be replaced similarly, at least when you are on DB2 9.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting

  3. #3
    Join Date
    Jan 2009
    Thank you Sir. before i forgot to remove the "and" from the "and ROWNUM<=" statement. Thats why i was not getting the output.
    Thank you for your help.
    Have a great day

Posting Permissions

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