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
 

 

DB2 query reaches a maximum limit of 999

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

New User


Joined: 18 May 2007
Posts: 30
Location: CHENNAI

PostPosted: Thu May 08, 2008 3:19 pm    Post subject: DB2 query reaches a maximum limit of 999
Reply with quote

Hi,

I am in search of a query in DB2. My scenario is..

i have a column of x(3) to store the values like
001
002
.
.
999
when it reaches a maximum limit of 999, i do a maximum to select the highest value.... after that i added A01 from there..
A01
A02
..
..
..
A99
It still needs to be added with more values..
So i did a maximum number search to add the B01,B02..
when i am searching for the maximum value, it was still 999 not A99.
Let me know how to modify my query to get the maximum value as A99..
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu May 08, 2008 4:00 pm    Post subject:
Reply with quote

probably would have helped had you taken into consideration the coallating sequence on the ibm mainframe.

really poor design. the sql necessary to come up with a99 as max would have such poor performance that you are better off redesigning.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu May 08, 2008 4:28 pm    Post subject:
Reply with quote

as dick said the design is not good but still u can try this out ...i guess this will help you ...

Code:


SELECT COL FROM TABLE WHERE ASCII(COL) IN (SELECT MAX(ASCII(COL)) FROM TABLE)
ORDER BY COL DESC FETCH FIRST 1 ROWS ONLY;



try and let us know ...

thanks,
ashimer
Back to top
View user's profile Send private message
nabarundas

New User


Joined: 21 Jun 2007
Posts: 28
Location: pune

PostPosted: Thu May 08, 2008 4:36 pm    Post subject: hi
Reply with quote

hi shamsundar_mk,

In general, character data held in DB2 z/OS is encoded in EBCDIC format. So your query is giving proper result. If it was in ASCII then only you would get A99. So you have to take care programmatically.

If this is an INSERT query then I can suggest to one logic. Instead of using MAX you use COUNT(*) to find how many rows are there in the table.

Suppose after 999 you have to give A01 and then B01.
So we will do

IF MAX(col) > 999 then
IF (COUNT(*) - 999) / 99 = 0 then
insert A01 or B01 accordingly.
END-IF
END-IF

Although I don't know what your program is doing actually...


Regards,
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu May 08, 2008 4:48 pm    Post subject:
Reply with quote

nabarundas,

nice suggestion - good thinking.
Back to top
View user's profile Send private message
shamsundar_mk

New User


Joined: 18 May 2007
Posts: 30
Location: CHENNAI

PostPosted: Thu May 08, 2008 4:51 pm    Post subject: Reply to: DB2 query reaches a maximum limit of 999
Reply with quote

Thanks for your suggestions.. i will try and let you know..
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu May 08, 2008 5:14 pm    Post subject:
Reply with quote

nabarundas,

this will cause problem if one or more rows have been deleted in between 000 and 999
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 May 09, 2008 4:03 am    Post subject:
Reply with quote

Hello,

Quote:
you are better off redesigning.
I'm with DBZ.

You will be ahead if you correct the design flaw and move on.
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 Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am


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