Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Order of the output in different is different regions-Query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
arif.md

New User


Joined: 13 Jun 2005
Posts: 10

PostPosted: Fri Jul 27, 2007 2:51 pm    Post subject: Order of the output in different is different regions-Query
Reply with quote

I have same set of data in Production and Test region in two tables for a particular Employee.

Now when I am selecting columns1, 2 and 3 from the Table1 and it has a join on Table2.

The data selected is not in the same order in both regions. I am not supposed to use ORDER BY clause here as it will affect the other data.

Query I have used.

SELECT
T2.column1,
T3.column1,
T3. Column2

FROM Table1 T1
,Table2 T2
,Table2 T3

WHERE T2.A_ID = T1.A_ID
AND T2.CANC_DT = '12/31/9999'
AND T2.A_TYP_CD IN ('L', 'D')
AND T2.BAID = T3.A_ID
AND T2.BAID_TYP = T3.A_TYP_CD
AND T2.P_ID = T3.P_ID
AND T2.T_ID = T3.T_ID
AND T2.T_TYP_CD = T3.T_TYP_CD
AND T2.P_ID = :Numeric Value
AND T1.Z_CD = :Numeric Value
AND T1.Z_4_CD = :Numeric Value


The above query is showing data in different order in different regions.
Thanks
Arif
Back to top
View user's profile Send private message

William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Fri Jul 27, 2007 3:07 pm    Post subject:
Reply with quote

That's the breaks.....
order-by-clause
Quote:
If you do not specify ORDER BY, the rows of the result table have an arbitrary order.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jul 27, 2007 6:13 pm    Post subject:
Reply with quote

Hello,

If you do not specify ordering criteria, the system will only return the rows you asked for - in no predictable order.

Where does a requirement come from that demands a sequence, but permission to put the rows in sequence is denied?

Obviously, there is more involved than has been posted. . .
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Fri Jul 27, 2007 8:10 pm    Post subject:
Reply with quote

But but but.... I need my database queries to always be in the same order unless I use the ORDER BY to put them in a different order. icon_smile.gif

As William said, thems the breaks. That damn optimizer to improve performance....
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri Jul 27, 2007 9:22 pm    Post subject:
Reply with quote

stodolas wrote:
But but but.... I need my database queries to always be in the same order unless I use the ORDER BY to put them in a different order. icon_smile.gif

As William said, thems the breaks. That damn optimizer to improve performance....


Without an order by there is no way to know what the order will be.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jul 27, 2007 9:41 pm    Post subject:
Reply with quote

Hello,

I just re-read the original post and this
Quote:
I am not supposed to use ORDER BY clause here as it will affect the other data.
makes me ask - how will it "affect the other data"?

If you describe what your process should do, we may be able to offer suggestions.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Sat Jul 28, 2007 2:06 am    Post subject:
Reply with quote

Craq: apparently I didn't drip enough sarcasm into my typing
Back to top
View user's profile Send private message
bhushan.shete

New User


Joined: 14 Nov 2006
Posts: 17

PostPosted: Mon Jan 16, 2012 4:10 pm    Post subject:
Reply with quote

Quote:
"If you do not specify ORDER BY, the rows of the result table have an arbitrary order."

Is this mentioned in manual? I could not find it.

Suppose if we have employee table with empno as primary key (unique index). If i donot mention ORDER BY then, will order of output rows in order of empno (i.e. as per primary key).

In other words, if EMPNO is primary key will output of below queries different?
1.
Code:
SELECT EMPNO FROM EMPLOYEE;

2.
Code:
SELECT EMPNO FROM EMPLOYEE ORDER BY EMPNO;
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7308

PostPosted: Mon Jan 16, 2012 4:26 pm    Post subject: Reply to: Order of the output in different is different regi
Reply with quote

After 4 1/2 years, I don't suppose anyone is interested in suddenly taking-up that conversation again.

Have you tried it out? You can always post as a new topic if you have a question/useful piece of information, and can always link to the original topic (look at the URL button).
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Mon Jan 16, 2012 4:32 pm    Post subject:
Reply with quote

I think it depends whether the index is a clustering index or not.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7308

PostPosted: Mon Jan 16, 2012 4:43 pm    Post subject:
Reply with quote

bhushan.shete wrote:
Quote:
"If you do not specify ORDER BY, the rows of the result table have an arbitrary order."

Is this mentioned in manual? I could not find it.



Looking back through the topic, I suppose you could try the link just a little above the text that you quoted?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Problem reading GTF trace output danik56 ABENDS & Debugging 7 Thu Mar 16, 2017 1:02 pm
No new posts SORT VSAM file with each field one by... maxsubrat DFSORT/ICETOOL 6 Tue Mar 14, 2017 1:07 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts How to get current date -1 and curren... murali.andaluri DFSORT/ICETOOL 3 Wed Mar 08, 2017 2:09 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us