DB 2 query into table with char field - need max by number

The table is
Name       Type Length Scale Nulls Default
RATE_GROUP_CODE  CHAR  8  0  N  Y
The query is
select max(rate_group_code) as rgc
from projinfo.cims_rate_groups
where substr(rate_group_code, 1, 2) = 'OD        '
But, the result of the above query is always OD9.
However the table contains rate_group_code data from OD01 to OD28.
I need to change the query to find the top number after the OD in this table column, so that the next number can be incremented from it. For example, if rgc is OD28, then insert OD29 next. The column type cannot be changed.
Many thanks.
mchristopherAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Hi mchristoper,

There are several ways to do this.  If you're specifically looking for the largest value when the first two characters are '0D', this works well.

SELECT max (cast (substring (rate_group_code, 3) as integer))
FROM projinfo.cims_rate_groups 
WHERE left (rate_group_code, 2) = '0D'

Open in new window


If you're looking for all items with this trait, try this:

SELECT left (rate_group_code, 2), max (cast (substring (rate_group_code, 3) as integer))
FROM projinfo.cims_rate_groups 
GROUP BY left (rate_group_code, 2)

Open in new window


Good Luck!
Kent
0
PortletPaulfreelancerCommented:
don't have access to DB2 to test this, while I agree casting to integer will be needed you might want to couple that with removing the 'OD' prefix. That way the full string could grow to (say) 'OD9999' and the process would still work and not be tied to particular lengths.

select
          max( cast( replace(rate_group_code,'OD','') as integer ) ) as rgc
from projinfo.cims_rate_groups
where substr(rate_group_code, 1, 2) = 'OD'

but of course once you reach 'OD999999' you have reached the maximum length/value
0
PortletPaulfreelancerCommented:
ignore that comment - just realised substring (rate_group_code, 3) does what is needed
- sorry Kdo
0
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

mchristopherAuthor Commented:
DB2 throws the following error for example no 1:
SELECT max (cast (substring (rate_group_code, 3) as integer))
FROM projinfo.cims_rate_groups
WHERE left (rate_group_code, 2) = 'OD'
(OD as in Orphan Day)
---
error DSNT408I SQLCODE = -170, ERROR: THE NUMBER OF ARGUMENTS SPECIFIED FOR SUBSTRING IS INVALID

The SQL Statement that caused the error is ELECT MAX (CAST (SUBSTRING (RATE_GROUP_CODE, 3) AS INTEGER))
FROM PROJINFO.CIMS_RATE_GROUPS
WHERE LEFT (RATE_GROUP_CODE, 2) = 'OD'

The error message returned is SQLCA.SQLMSG

Thanks, Mark
0
PortletPaulfreelancerCommented:
Ah, then maybe I'm not too far wrong...

SELECT max (cast (substring (rate_group_code, 3, 8) as integer))

-- as 8 is the max length of that field

or you could you try the use of replace as I suggested.
0
Kent OlsenDBACommented:
Hi Mark,

That means that your query isn't running on DB2.  :)  DB2 has a 2 argument form of the SUBSTRING function that copies from the starting point to the end of the string.  Oracle and SQL Server don't have an equivalent.

SELECT max (cast (right(rate_group_code, len (rate_group_code)-2) as integer))
FROM projinfo.cims_rate_groups
WHERE left (rate_group_code, 2) = '0D'
                                           
That query should work fine.  But be advised that this kind of string manipulation is sensitive to funny data.  For instance, a rate_group_code value of 'X' could cause the query to fail.  len('X')-2 would be -1 and passing that value through the RIGHT function would throw an error.  Bullet proof queries of this kind are "wordy", but you might not need one, either.


Good Luck,
Kent
0
mchristopherAuthor Commented:
Kent: Thanks but it is DB2. The results from your query are:
error DSNT408I SQLCODE = -440, ERROR: NO AUTHORIZED FUNCTION NAMED LEN HAVING COMPATIBLE ARGUMENTS WAS FOUND
-Mark
0
mchristopherAuthor Commented:
And I tried the second query by PortletPaul:

select max( cast( replace(rate_group_code,'OD','') as integer ) ) as rgc
from projinfo.cims_rate_groups
where substr(rate_group_code, 1, 2) = 'OD'

error DSNT408I SQLCODE = -420, ERROR: THE VALUE OF A STRING ARGUMENT WAS NOT ACCEPTABLE TO THE SYSIBM.INTEGER FUNCTION
0
SharathData EngineerCommented:
Instead of LEN, try LENGTH in Kdo's query.
SELECT max (cast (right(rate_group_code, length(rate_group_code)-2) as integer))
FROM projinfo.cims_rate_groups 
WHERE left (rate_group_code, 2) = '0D'

Open in new window

0
PortletPaulfreelancerCommented:
"STRING ARGUMENT WAS NOT ACCEPTABLE TO THE SYSIBM.INTEGER FUNCTION"

that would indicate some values cannot be converted to integer so regardless of which functions are used (substr/length/replace) you may have troubles.

could we try discovering what is after the 'OD' please?
SELECT
       REPLACE(rate_group_code,'OD','') AS by_replace
     , CASE TRANSLATE(replace(rate_group_code,'OD','') , '*' , ' 0123456789')
          WHEN '' THEN 'Valid.'
          ELSE 'Not valid.'
       END                              AS assessed
FROM projinfo.cims_rate_groups

WHERE SUBSTR(rate_group_code, 1, 2) = 'OD'
AND TRANSLATE(REPLACE(rate_group_code,'OD','') , '*' , ' 0123456789') <> ''

FETCH FIRST 10 ROWS ONLY /* << choose how many you want to see */

Open in new window

& an alternative to replace may be: substring-after e.g.

substring-after(rate_group_code, 'OD') as by_substr_after

http://publib.boulder.ibm.com/infocenter/db2luw/v10r5/index.jsp?topic=%2Fcom.ibm.db2.luw.xml.doc%2Fdoc%2Fxqrfnsba.html
0
Kent OlsenDBACommented:
Hi Mark,

Apologies....  My original query works just fine if you call SUBSTR instead of SUBSTRING.  IBM includes the SUBSTRING function for external compatibility and I just used the wrong function.

Here's a test example:

WITH cims_rate_groups (rate_group_code)
AS
(
  SELECT rate_group_code FROM (values '0D001', '0D012', '0D009') t(rate_group_code)
)
SELECT max (cast (substr (rate_group_code, 3) as integer))
FROM cims_rate_groups 
WHERE left (rate_group_code, 2) = '0D';

Open in new window


You don't need the CTE.  All it's doing is providing sample data.  The lower SELECT clause should work just fine for you.


Kent
0
mchristopherAuthor Commented:
This query will output the rate_group_code, but not in any order by, I need to trap the 'largest' one, e.g. OD28 and increment it by 1.
The query lists the rate_group_code from OD01 to OD28
select rate_group_code
from projinfo.cims_rate_groups
where substr(rate_group_code, 1, 2) = 'OD'
0
Kent OlsenDBACommented:
Works just fine....  :)

Kent


SELECT max (cast (substr (rate_group_code, 3) as integer)) + 1
FROM projinfo.cims_rate_groups
WHERE left (rate_group_code, 2) = '0D';

Open in new window

0
mchristopherAuthor Commented:
SELECT max (cast (substr (rate_group_code, 3) as integer))
FROM cims_rate_groups
WHERE left (rate_group_code, 2) = 'OD'

error DSNT408I SQLCODE = -204, ERROR: ZZYZAR1.CIMS_RATE_GROUPS IS AN UNDEFINED NAME
0
mchristopherAuthor Commented:
Kent;
SELECT max (cast (substr (rate_group_code, 3) as integer)) + 1
FROM projinfo.cims_rate_groups
WHERE left (rate_group_code, 2) = '0D';

PROJINFO.CIMS_RATE_GROUPS
SQLCOL1
-NULL-
0
Kent OlsenDBACommented:
Looks like a data issue.  Try this query:

SELECT rate_group_code, count(*)
FROM projinfo.cims_rate_groups
WHERE left (rate_group_code, 2) = '0D'
group by rate_group_code
order by 1;

Open in new window

0
Kent OlsenDBACommented:
How is rate_group_code defined?  CHAR or VARCHAR?
0
PortletPaulfreelancerCommented:
could we see some sample data from that column please? e.g.

select
        rate_group_code
from projinfo.cims_rate_groups
where substr(rate_group_code, 1, 2) = 'OD'
order by rate_group_code DESC
fetch first 10 rows only --<< choose how many
0
mchristopherAuthor Commented:
kdo: char
The table is
Name       Type Length Scale Nulls Default
RATE_GROUP_CODE  CHAR  8  0  N  Y
0
mchristopherAuthor Commented:
Sample data from the column:

select rate_group_code
from projinfo.cims_rate_groups
where substr(rate_group_code, 1, 2) = 'OD'

RATE_GROUP_CODE
OD  
OD0001  
OD10  
OD11  
OD12  
OD13  
OD14  
OD15  
OD16  
OD17  
OD18  
OD19  
OD2  
OD20  
OD21  
OD22  
OD23  
OD24  
OD25  
OD26  
OD27  
OD28  
OD3  
OD4  
OD5  
OD6  
OD7  
OD8  
OD9
0
Kent OlsenDBACommented:
Hmmmm.....  trailing spaces.....

So let's try this:

SELECT max (cast (substr (cast (rate_group_code as varchar(8)), 3) as integer)) + 1
FROM projinfo.cims_rate_groups
WHERE left (rate_group_code, 2) = 'OD';

Open in new window



Kent
0
mchristopherAuthor Commented:
Kent:
SELECT max (cast (substr (rate_group_code, 3) as integer)) + 1
FROM projinfo.cims_rate_groups
WHERE left (rate_group_code, 2) = '0D'
 
1 row returned

PROJINFO.CIMS_RATE_GROUPS
SQLCOL1
-NULL-
0
mchristopherAuthor Commented:
Kent;
SELECT max (cast (substr (cast (rate_group_code as varchar(8)), 3) as integer)) + 1
FROM projinfo.cims_rate_groups
WHERE left (rate_group_code, 2) = 'OD';

error DSNT408I SQLCODE = -420, ERROR: THE VALUE OF A STRING ARGUMENT WAS NOT ACCEPTABLE TO THE SYSIBM.INTEGER FUNCTION
0
Kent OlsenDBACommented:
I have been reading that as '0D' (zero-D).  Looks like it's really 'OD' (oh-D).

SELECT max (cast (substr (rate_group_code, 3) as integer)) + 1
FROM projinfo.cims_rate_groups
WHERE left (rate_group_code, 2) = 'OD'

Open in new window



Kent
0
mchristopherAuthor Commented:
select rate_group_code
from projinfo.cims_rate_groups
where substr(rate_group_code, 1, 2) = 'OD'
order by rate_group_code DESC

PROJINFO.CIMS_RATE_GROUPS
RATE_GROUP_CODE
OD9  
OD8  
OD7  
OD6  
OD5  
OD4  
OD3  
OD28  
OD27  
OD26  
OD25  
OD24  
OD23  
OD22  
OD21  
OD20  
OD2  
OD19  
OD18  
OD17  
OD16  
OD15  
OD14  
OD13  
OD12  
OD11  
OD10  
OD0001  
OD
0
mchristopherAuthor Commented:
SELECT max (cast (substr (rate_group_code, 3) as integer)) + 1
FROM projinfo.cims_rate_groups
WHERE left (rate_group_code, 2) = 'OD'

error DSNT408I SQLCODE = -420, ERROR: THE VALUE OF A STRING ARGUMENT WAS NOT ACCEPTABLE TO THE SYSIBM.INTEGER FUNCTION
0
Kent OlsenDBACommented:
What version of DB2 are you running?
0
PortletPaulfreelancerCommented:
I think it may be the last line of the sample where the value is just 'OD'

all the others look like they will translate to an integer, but don't have DB2 to trial.

could you try by excluding that 'OD'

SELECT max (cast (substr (rate_group_code, 3) as integer)) + 1
FROM projinfo.cims_rate_groups
WHERE left (rate_group_code, 2) = 'OD'
and rate_group_code <> 'OD'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kent OlsenDBACommented:
Good catch, PP.  :)
0
mchristopherAuthor Commented:
The DB2 UDB version is depicted below (10.1)

********************************* Top of Data **********************************
DSN7100I  ) DSN7GCMD                                                            
*** BEGIN DISPLAY OF GROUP(........)                CATALOG LEVEL(101) MODE(CM8 )              
                              PROTOCOL LEVEL(2)                GROUP ATTACH NAME(....)                    
--------------------------------------------------------------------            
DB2                                                                                  DB2 SYSTEM  IRLM                      
MEMBER   ID  SUBSYS CMDPREF     STATUS   LVL NAME      SUBSYS IRLMPROC            
                                                                       --------         ----    --------       --------       --------  
........   0 DSNP   )                                        ACTIVE     101   SYS9         PRLM      DSNPIRLM            
--------------------------------------------------------------------            
SPT01 INLINE LENGTH:                      32138                                              
*** END DISPLAY OF GROUP(........)                                              
DSN9022I  ) DSN7GCMD 'DISPLAY GROUP ' NORMAL COMPLETION                        
******************************** Bottom of Data ********************************

DB2 UDB for z/OS v10, which is located on an IBM mainframe
0
mchristopherAuthor Commented:
Oh Yeah! Here it is.

SELECT max (cast (substr (rate_group_code, 3) as integer)) + 1
FROM projinfo.cims_rate_groups
WHERE left (rate_group_code, 2) = 'OD'
and rate_group_code <> 'OD'

PROJINFO.CIMS_RATE_GROUPS
SQLCOL1
29
0
mchristopherAuthor Commented:
Is 60% for PortletPaul and 40% for KDO OK with you?
0
PortletPaulfreelancerCommented:
Cool!....

note to self: ask for sample earlier :)

a slight re-arrangement may have avoided the problem?

SELECT cast(  max(  substr(rate_group_code, 3)  )  as integer) + 1
FROM projinfo.cims_rate_groups
WHERE left (rate_group_code, 2) = 'OD'

but, when casting strings to numbers the validity of the string is always a potential issue.
0
PortletPaulfreelancerCommented:
I played a support role only - main task was by kdo I believe.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.