Oracle problem with using Both OrderBy & ROwNum

Dev Shed Forums Sponsor:

November 22nd, 2012, 03:03 AM

hamed88

Registered User

Join Date: Nov 2012

Posts: 1

Time spent in forums: 6 m 15 sec
Reputation 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?

December 11th, 2012, 09:42 AM

LKBrwn_DBA

Contributing User

Join Date: Sep 2006

Posts: 751

Time spent in forums: 1 Week 2 h 56 m 5 sec
Reputation Power: 348

Quote:

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
/

__________________

Viewing: Dev Shed Forums > Databases > Oracle Development > Oracle problem with using Both OrderBy & ROwNum

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump