Determining if a data is an integer or not?

[rkeokitv]

Originally posted by nandu
A touch of Genius ! jmodic you have ur own ways of refining!

- Nandu

DITTO!!!

[raghud]

Hm, I did not get you .What's wrong with that query.

[rkeokitv]

Okay if I have a DECODE in the statement, can I use the BETWEEN function to finc the character value? For example:

DECODE(SUBSTR(RES.ACTIVITY_ID,2,1)
,'1','Water'
,'2','Solid Waste'
,'3','Drainage/Wastewater'
,'5','Shared'
,'Unknown'
)

[raghud]

In some cases while building the sql statements dynamically in some front end languages, we need to concatenate strings . While concatenation it is very difficult to merge strings like 'A' or '1'. Because single quote means different. In that case it is very easy to use chr(65) like to build dynamic SQL's . Any way it depends case by case.

[raghud]

for example

string str1

str1='SELECT ename FROM Emp
WHERE upper(substr(ename,2,1)) BETWEEN'

string str2

str2 = str1 + ''A'' + ' AND ' + ''B''

It will trough an error

str2 = str1 + 'char(65)' it is easy so that we can get our whole select statement for immediate execute.

[jmodic]

Hm, wonder why they invented a concept of BIND VARIABLES....

[rkeokitv]

If I put the Between clause in quotes, it tries to interpret it as a literal. Do you know exactly how to code a between in the decode? I can't find any literature on it specifically.

SELECT
DECODE(UPPER(SUBSTR(ACTIVITY_ID,2,1))
,'1','Water'
,BETWEEN 'A' and 'Z','Water'
,'2','Waste'
,'3','ToolA/ToolB'
,'5','Shared'
,'Unknown'
) fund
,activity_id
from
SCHEMA.TABLE_INFO
where table_id like 'C%'

[nandu]

Try this way !

SELECT case when UPPER(SUBSTR(ename,2,1)) = '1' then 'Fire'
when UPPER(SUBSTR(ename,2,1)) BETWEEN 'A' and 'M' then 'Water'
when UPPER(SUBSTR(ename,2,1)) BETWEEN 'L' and 'Z' then 'Waste' else 'Unknown' end from emp

HTH

- Nandu