IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

To determine the numeric data of a column


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
nag
Warnings : 1

New User


Joined: 29 Jul 2005
Posts: 20

PostPosted: Wed Aug 05, 2009 12:17 am
Reply with quote

Hi,
I need a generate a report such that if the column data contains any numeric data then i need to display "Numeric".

For an example

If a table INSURANCE_TABLE has a column called INSURANCE_POLICY which conatins the data as follows

MEDICI12CLAIM
123456
MEDI

The result should be

MEDICI12CLAIM Numeric
123456 Numeric
MEDI NonNumeric
Back to top
View user's profile Send private message
nag
Warnings : 1

New User


Joined: 29 Jul 2005
Posts: 20

PostPosted: Wed Aug 05, 2009 12:48 am
Reply with quote

Can any one help me on this please?
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Wed Aug 05, 2009 12:59 am
Reply with quote

soliciting an answer after only 30 minutes from the original post is not very considerate
Back to top
View user's profile Send private message
nag
Warnings : 1

New User


Joined: 29 Jul 2005
Posts: 20

PostPosted: Wed Aug 05, 2009 10:08 am
Reply with quote

enrico-sorichetti wrote:
soliciting an answer after only 30 minutes from the original post is not very considerate


HI Enrico,

It's a request icon_biggrin.gif
Back to top
View user's profile Send private message
mallik4u

New User


Joined: 17 Sep 2008
Posts: 75
Location: bangalore

PostPosted: Wed Aug 05, 2009 10:52 am
Reply with quote

Hi Nag,

Following query will give you the results as you stated in your requirement.

SELECT INSURANCE_POLICY ,
CASE
WHEN INSURANCE_POLICY LIKE '%1%' OR
INSURANCE_POLICY LIKE '%2%' OR
INSURANCE_POLICY LIKE '%3%' OR
INSURANCE_POLICY LIKE '%4%' OR
INSURANCE_POLICY LIKE '%5%' OR
INSURANCE_POLICY LIKE '%6%' OR
INSURANCE_POLICY LIKE '%7%' OR
INSURANCE_POLICY LIKE '%8%' OR
INSURANCE_POLICY LIKE '%9%' THEN
'NUMERIC'
ELSE
'NON-NUMERIC'
END CASE
FROM INSURANCE_TABLE

Please note that this is just a way to get the result. This might not be the best way (optimized) to get the results.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Wed Aug 05, 2009 7:25 pm
Reply with quote

Hello,

Quote:
It's a request
Yes, a quite inconsiderate request. . . icon_sad.gif

There is no need to reply, but please consider why you believed it appropriate to "remind" everyone that you had question only 30 minutes after starting the topic. . .
Back to top
View user's profile Send private message
nag
Warnings : 1

New User


Joined: 29 Jul 2005
Posts: 20

PostPosted: Fri Aug 07, 2009 10:38 pm
Reply with quote

mallik4u wrote:
Hi Nag,

Following query will give you the results as you stated in your requirement.

SELECT INSURANCE_POLICY ,
CASE
WHEN INSURANCE_POLICY LIKE '%1%' OR
INSURANCE_POLICY LIKE '%2%' OR
INSURANCE_POLICY LIKE '%3%' OR
INSURANCE_POLICY LIKE '%4%' OR
INSURANCE_POLICY LIKE '%5%' OR
INSURANCE_POLICY LIKE '%6%' OR
INSURANCE_POLICY LIKE '%7%' OR
INSURANCE_POLICY LIKE '%8%' OR
INSURANCE_POLICY LIKE '%9%' THEN
'NUMERIC'
ELSE
'NON-NUMERIC'
END CASE
FROM INSURANCE_TABLE

Please note that this is just a way to get the result. This might not be the best way (optimized) to get the results.


Hi dick sorry for that.

Hi mallik,
Thanks for the reply.
Ur query works fine.

But
If i had alphabetic then ----> Alphabetic
Both alphabetic n Numeric then ---->alphanumeric
if just numeric then -----> Numeric

Could you pls help me on this?
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Sat Aug 08, 2009 11:06 am
Reply with quote

Quote:

But
If i had alphabetic then ----> Alphabetic
Both alphabetic n Numeric then ---->alphanumeric
if just numeric then -----> Numeric

I suggest you unload table with required columns to flat file and then use some utility like sort to do this..
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Sat Aug 08, 2009 8:54 pm
Reply with quote

Or, heaven forbid, actually write a bit of code. . . icon_rolleyes.gif
Back to top
View user's profile Send private message
nag
Warnings : 1

New User


Joined: 29 Jul 2005
Posts: 20

PostPosted: Sun Aug 09, 2009 10:52 pm
Reply with quote

Sambhaji wrote:
Quote:

But
If i had alphabetic then ----> Alphabetic
Both alphabetic n Numeric then ---->alphanumeric
if just numeric then -----> Numeric

I suggest you unload table with required columns to flat file and then use some utility like sort to do this..


Thanks for your suggestion.
I too know that it can be done thrrough Sort or by COBOL program.
But i need to do this through a query.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8700
Location: Dubuque, Iowa, USA

PostPosted: Sun Aug 09, 2009 10:58 pm
Reply with quote

Quote:
I too know that it can be done thrrough Sort or by COBOL program.
But i need to do this through a query.
Sometimes what you "need" (want) to do cannot be done and you have to do it the way it can be done. Placing artificial constraints like this on our solutions generally leads to less than optimal solutions, or even the determination that it cannot be done in the way you "need".
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Mon Aug 10, 2009 1:53 am
Reply with quote

Hello,

Quote:
But i need to do this through a query.
Says who? Why?

Please explain a business reason for this requirement.
Back to top
View user's profile Send private message
nag
Warnings : 1

New User


Joined: 29 Jul 2005
Posts: 20

PostPosted: Wed Aug 12, 2009 10:51 pm
Reply with quote

dick scherrer wrote:
Hello,

Quote:
But i need to do this through a query.
Says who? Why?

Please explain a business reason for this requirement.


Client asked for a DB2 query. I do not have any answers for why ?
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Thu Aug 13, 2009 10:13 am
Reply with quote

nag wrote:
Client asked for a DB2 query. I do not have any answers for why ?


Probably you have to explain below lines of Robert to your Client.. icon_wink.gif
Robert wrote:
Sometimes what you "need" (want) to do cannot be done and you have to do it the way it can be done
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts db2 vs static data COBOL Programming 1
No new posts External data queue (changes?) CLIST & REXX 0
No new posts Pull data using date difference betwe... DB2 6
No new posts fast data scrambling PL/I & Assembler 10
No new posts ISPF Table to add a new column TSO/ISPF 1
Search our Forums:

Back to Top