DBAsupport.com Forums - Powered by vBulletin
Page 3 of 3 FirstFirst 123
Results 21 to 28 of 28

Thread: Determining if a data is an integer or not?

  1. #21
    Join Date
    May 2002
    Posts
    29

    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!!!

  2. #22
    Join Date
    Aug 2001
    Posts
    267
    Hm, I did not get you .What's wrong with that query.
    Raghu

  3. #23
    Join Date
    May 2002
    Posts
    29
    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'
    )

  4. #24
    Join Date
    Aug 2001
    Posts
    267
    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

  5. #25
    Join Date
    Aug 2001
    Posts
    267
    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

  6. #26
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  7. #27
    Join Date
    May 2002
    Posts
    29
    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%'

  8. #28
    Join Date
    May 2002
    Posts
    108

    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
  •  


Click Here to Expand Forum to Full Width