[DB2 V8 NFM z/OS] MAX, STRIP and DIGITS

Walter Janißen

[DB2 V8 NFM z/OS] MAX, STRIP and DIGITS
Hi

Would you mind to execute the following query:

SELECT MAX(STRIP(DIGITS(DSNUM), L, '0'))
FROM SYSIBM.SYSCOPY

What did you get?

And if you execute the following (functions only turn araound):

SELECT STRIP(DIGITS(MAX(DSNUM)), L, '0')
FROM SYSIBM.SYSCOPY

The same result or a different?

Well, the first query came up with 99, the second with the correct result.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Max Scarpa

Re: [DB2 V8 NFM z/OS] MAX, STRIP and DIGITS
(in response to Walter Janißen)
SELECT MAX(STRIP(DIGITS(DSNUM), L, '0'))
FROM SYSIBM.SYSCOPY ;
---------+---------+---------+---------+---------+---------+-----

---------+---------+---------+---------+---------+---------+-----
9
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+-----
SELECT STRIP(DIGITS(MAX(DSNUM)), L, '0')
FROM SYSIBM.SYSCOPY ;
---------+---------+---------+---------+---------+---------+-----

---------+---------+---------+---------+---------+---------+-----
32

DB2 V7 z/OS

Max Scarpa

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Michael Ebert

Re: [DB2 V8 NFM z/OS] MAX, STRIP and DIGITS
(in response to Max Scarpa)
The first query does a character comparison MAX, so '99' > '100'. The
second does a numeric comparison MAX, so 99<100.

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




Walter Janißen <[login to unmask email]>
To
[login to unmask email]
cc

bcc

Subject
[DB2-L] [DB2 V8 NFM z/OS] MAX, STRIP and DIGITS





Walter Janißen <[login to unmask email]>
Please respond to DB2 Database Discussion list at IDUG
<[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
11-12-06 10:28


Hi

Would you mind to execute the following query:

SELECT MAX(STRIP(DIGITS(DSNUM), L, '0'))
FROM SYSIBM.SYSCOPY

What did you get?

And if you execute the following (functions only turn araound):

SELECT STRIP(DIGITS(MAX(DSNUM)), L, '0')
FROM SYSIBM.SYSCOPY

The same result or a different?

Well, the first query came up with 99, the second with the correct result.


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Jani&#223;en

Re: [DB2 V8 NFM z/OS] MAX, STRIP and DIGITS
(in response to Michael Ebert)
Michael

That's it. And I already thought, that this would be a DB2 bug. I love this
list. Again and again one gets precious hints.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

ANAND JAYACHANDRAN

Re: [DB2 V8 NFM z/OS] MAX, STRIP and DIGITS
(in response to Walter Janißen)
We are DB2 V8 ENFM, the results are:

SELECT MAX(STRIP(DIGITS(DSNUM), L, '0'))
FROM SYSIBM.SYSCOPY
;
---------+---------+---------+---------+---------+---------+---

---------+---------+---------+---------+---------+---------+---
4
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---
SELECT STRIP(DIGITS(MAX(DSNUM)), L, '0')
FROM SYSIBM.SYSCOPY
;
---------+---------+---------+---------+---------+---------+---

---------+---------+---------+---------+---------+---------+---
4



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Kate Kavanaugh

Re: [DB2 V8 NFM z/OS] MAX, STRIP and DIGITS
(in response to ANAND JAYACHANDRAN)
We are at V8 NFM and receive the following:

---------+---------+---------+---------+---------+---------+---------+----
SELECT MAX(STRIP(DIGITS(DSNUM), L, '0'))
FROM SYSIBM.SYSCOPY
;
---------+---------+---------+---------+---------+---------+---------+----

---------+---------+---------+---------+---------+---------+---------+----
5

DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+----
SELECT STRIP(DIGITS(MAX(DSNUM)), L, '0')
FROM SYSIBM.SYSCOPY
;
---------+---------+---------+---------+---------+---------+---------+-----

---------+---------+---------+---------+---------+---------+---------+-----
26

DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+-----

In this case STRIP is the one that converts the value to a character and
then the MAX chooses a 5 over the 2 as the MAX string.

Kate Kavanaugh

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm