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
 

 

To determine the numeric data of a column

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: To determine the numeric data of a column
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    Post subject: Reply to: To determine the numeric data of a column
Reply with quote

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

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Wed Aug 05, 2009 12:59 am    Post subject: Reply to: To determine the numeric data of a column
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    Post subject: Re: Reply to: To determine the numeric data of a column
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    Post subject:
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

Site Director


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

PostPosted: Wed Aug 05, 2009 7:25 pm    Post subject:
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    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Sat Aug 08, 2009 8:54 pm    Post subject: Reply to: To determine the numeric data of a column
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    Post subject:
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: 7908
Location: Bellevue, IA

PostPosted: Sun Aug 09, 2009 10:58 pm    Post subject:
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

Site Director


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

PostPosted: Mon Aug 10, 2009 1:53 am    Post subject:
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    Post subject:
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    Post subject:
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    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 Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 1 Tue Dec 06, 2016 4:38 am
No new posts How to move a long alphanumeric data ... lind sh COBOL Programming 5 Mon Dec 05, 2016 7:51 pm
No new posts ODPP(Optim Data privacy Provider) Iss... Rama kishore IBM Tools 1 Mon Nov 07, 2016 5:46 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm


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