|
View previous topic :: View next topic
|
| Author |
Message |
nag
Warnings : 1
New User
Joined: 29 Jul 2005
Posts: 20
|
|
|
|
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 |
|
 |
nag
Warnings : 1
New User
Joined: 29 Jul 2005
Posts: 20
|
|
|
|
| Can any one help me on this please? |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10886
Location: italy
|
|
|
|
| soliciting an answer after only 30 minutes from the original post is not very considerate |
|
| Back to top |
|
|
nag
Warnings : 1
New User
Joined: 29 Jul 2005
Posts: 20
|
|
|
|
| enrico-sorichetti wrote: |
| soliciting an answer after only 30 minutes from the original post is not very considerate |
HI Enrico,
It's a request  |
|
| Back to top |
|
|
mallik4u
New User
Joined: 17 Sep 2008
Posts: 75
Location: bangalore
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
Yes, a quite inconsiderate request. . . 
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 |
|
|
nag
Warnings : 1
New User
Joined: 29 Jul 2005
Posts: 20
|
|
|
|
| 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 |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Or, heaven forbid, actually write a bit of code. . .  |
|
| Back to top |
|
|
nag
Warnings : 1
New User
Joined: 29 Jul 2005
Posts: 20
|
|
|
|
| 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 |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8700
Location: Dubuque, Iowa, USA
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
nag
Warnings : 1
New User
Joined: 29 Jul 2005
Posts: 20
|
|
|
|
| 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 |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
|
|
|
|
| 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.. 
| 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 |
|
|
|
|
