-
Re: Isn't the following variant much more "human"?
Originally posted by nandu
A touch of Genius ! jmodic you have ur own ways of refining!
- Nandu
DITTO!!!
-
Hm, I did not get you .What's wrong with that query.
Raghu
-
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'
)
-
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.
Raghu
-
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.
Raghu
-
Hm, wonder why they invented a concept of BIND VARIABLES....
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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%'
-
Do you know exactly how to code
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
Never give up !
Nanda Kumar - Vellore
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|