|
View previous topic :: View next topic
|
| Author |
Message |
shamsundar_mk
New User
Joined: 18 May 2007
Posts: 30
Location: CHENNAI
|
|
|
|
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 |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
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 |
|
|
nabarundas
New User
Joined: 21 Jun 2007
Posts: 28
Location: pune
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
nabarundas,
nice suggestion - good thinking. |
|
| Back to top |
|
|
shamsundar_mk
New User
Joined: 18 May 2007
Posts: 30
Location: CHENNAI
|
|
|
|
| Thanks for your suggestions.. i will try and let you know.. |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
nabarundas,
this will cause problem if one or more rows have been deleted in between 000 and 999 |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
|
|
