#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    1
    Rep Power
    0

    Oracle problem with using Both OrderBy & ROwNum


    Hello EveryBody!
    I tried to make my Query as simple as possible but also it contains the problem:

    Code:
    SELECT A.Id,A.Attachment,A.CreateDateTime,A.No 
    ,rownum as RowNumber 
    FROM (
           SELECT A.Id,A.Attachment,A.CreateDateTime,A.No FROM GOutgoingLetter A , 
           (
                  SELECT B.Id, B.createdatetime as of0  FROM GOutgoingLetter B 
                  WHERE  
                          Exists 
                         (Select BC.id 
                          From XCase BC, GOutgoingLetterCase BG 
                          Where 
                          --    BG.CaseMechanizeState=1 And
                              B.id=BG.GOutgoingLetterId  
                         --    And BG.RelatedCaseID=BC.Id 
                       --   And BC.LASTEMPEXPERTMANID='EF38398F7291491898AF63AC0249DDBF'
                          ) 
       --           OR     A.RegistrarUserId =   '392182A64BD5499685FFAA2E570344D9'
           )B 
           WHERE A.Id = B.Id
           ORDER BY B.of0
           )A
    the problem is :

    when I use Both Order By And RowNum in my Query, Two columns of final Select Are Null: NO & Attachment. whereas this Columns aren't empty.

    when I comment each one of "ORDER BY B.of0" or ",rownum as RowNumber " everything is correct!!!

    what's wrong in my sql query?
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    858
    Rep Power
    388

    Thumbs down


    Originally Posted by hamed88
    Hello EveryBody!
    I tried to make my Query as simple as possible but also it contains the problem:
    . . . Etc . . .
    what's wrong in my sql query?
    Normally the problem can be found between the chair and the keyboard.
    Try this:
    Code:
    SELECT a.id, a.attachment, a.createdatetime, a.no
         , ROW_NUMBER () OVER (ORDER BY b.of0) AS rownumber
      FROM (SELECT a.id, a.attachment, a.createdatetime, a.no, b.of0
              FROM goutgoingletter a
                 , (SELECT b.id, b.createdatetime AS of0
                      FROM goutgoingletter b
                     WHERE EXISTS
                             (SELECT bc.id
                                FROM xcase bc, goutgoinglettercase bg
                               WHERE             
                               --    BG.CaseMechanizeState=1 And
                                    b.id = bg.goutgoingletterid 
                               --    And BG.RelatedCaseID=BC.Id
                               --   And BC.LASTEMPEXPERTMANID='EF38398F7291491898AF63AC0249DDBF'
                             ) --  OR     A.RegistrarUserId =   '392182A64BD5499685FFAA2E570344D9'
                            ) b
             WHERE a.id = b.id) a
    /

IMN logo majestic logo threadwatch logo seochat tools logo