| The problem of use Order By [message #571219] |
Thu, 22 November 2012 04:15  |
 |
namacan
Messages: 17
Registered: November 2012
Location: India
|
Junior Member |
|
|
Hi
I have an oracle query as follows :
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='EF38398F7291491898AF63AC024 9DDBF'
)
-- OR A.RegistrarUserId = '392182A64BD5499685FFAA2E570344D9'
)B
WHERE A.Id = B.Id
ORDER BY B.of0
)A
In the final ouput 'Attachment' and 'No' fields have no values but when ignore Order By clause , the above fields have values , why this happen ?
[Updated on: Thu, 22 November 2012 04:16] Report message to a moderator |
|
|
|
|
|
|
|
| Re: The problem of use Order By [message #571248 is a reply to message #571247] |
Thu, 22 November 2012 07:59  |
cookiemonster
Messages: 9169
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That's not all of the results is it?
You're using PL/SQL Developer. That can, depending on preferences get some of the data from a query and then stop, until you tell it to get the rest of the data. I suggest you tell it to fetch all of the data, sort it in the grid (click on the column headings) and see if the data is in fact the same - it will be.
|
|
|
|
| Re: The problem of use Order By [message #571249 is a reply to message #571248] |
Thu, 22 November 2012 08:08 |
cookiemonster
Messages: 9169
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Though I should obviously point out that since you have rownum in the outer query the query with the order by will have different values for that column to the one without.
More precisely they'll have the same set of values but those values will be attached to different rows.
|
|
|
|
|
|
| Re: The problem of use Order By [message #571264 is a reply to message #571219] |
Thu, 22 November 2012 11:46 |
Solomon Yakobson
Messages: 1404
Registered: January 2010
|
Senior Member |
|
|
namacan wrote on Thu, 22 November 2012 05:15In the final ouput 'Attachment' and 'No' fields have no values but when ignore Order By clause , the above fields have values , why this happen ?
Most likely nothing. Without order by rows are returned in no particular order of createdatetime so you see non-null attachment and no columns here and there on first screen. When you add order by createdatetime rows returned on first screen have null attachment and no columns. So scroll down to screen where createdatetime is 1390/11/23-11:38 and you'll find what you are looking for.
SY.
|
|
|
|
|
|
| Re: The problem of use Order By [message #571320 is a reply to message #571266] |
Fri, 23 November 2012 05:29 |
|
namacan
Messages: 17
Registered: November 2012
Location: India
|
Junior Member |
|
|
Hi
When i add 'Rownum' in the query with the 'order by' cluse the Problem solved .
However i dont now why ?
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 ,RowNum 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='EF38398F7291491898AF63AC024 9DDBF'
)
-- OR A.RegistrarUserId = '392182A64BD5499685FFAA2E570344D9'
)B
WHERE A.Id = B.Id
ORDER BY B.of0
)A
|
|
|
|
| Re: The problem of use Order By [message #571324 is a reply to message #571320] |
Fri, 23 November 2012 06:08 |
cookiemonster
Messages: 9169
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Since all really know about your problem is that you are getting stuff in a different order it's hard to say.
We really need more information on what the query is supposed to do.
However - if you always want the data in a consistent order then the order by should be on the outer query.
|
|
|
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
