Results 1 to 9 of 9

Thread: Help With Query

  1. #1
    Join Date
    Feb 2004
    Location
    india
    Posts
    23

    Exclamation Unanswered: Help With Query

    hi,
    I am trying to write a query that will return me data as follows ,
    If the field retrunname has the string 'Schedule' then retrun 'S' , If it has 'Breakdown' retrun 'B" and I wrote it as follows :


    SELECT CASE returnname
    WHEN INSTR(returnname,UPPER('schedule')) =1 THEN
    UPPER('s')
    CASE WHEN INSTR(returnname,UPPER('return')) =1 THEN UPPER('r')
    ELSE returnname
    FROM xxxx


    Alternatively I tried :
    SELECT DECODE (returnname, INSTR(returnname,UPPER('schedule')) > 0 , 'S') FROM xxxx

    but nothing seems to work ...
    can someone help me with this ? Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Is it what ur looking for.?
    select decode(returnname,'schedule','S','breakdown','B') FROM xxxx
    Pagnint
    (No need to search web before posting new question)

  3. #3
    Join Date
    Feb 2004
    Location
    india
    Posts
    23
    Originally posted by paginit
    Is it what ur looking for.?
    select decode(returnname,'schedule','S','breakdown','B') FROM xxxx
    thanks for the reply but its not what i was looking for...
    The retrunname will be like this :
    ' This is the schedule of .....' so i need to pick the schedule or it'll be like thhis :
    ' Breakdown of the monthy retruns'

    So i need to use string functio to check ..but how??????

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    3 problems with your code:
    1) INSTR returns the position of the substring within the string, so would not be 1 in your examples
    2) You should be applying the UPPER function to the variable text, not to your own constants.
    3) Your CASE syntax was wrong

    SELECT CASE returnname
    WHEN INSTR(UPPER(returnname),'SCHEDULE') > 0 THEN 'S'
    WHEN INSTR(UPPER(returnname),'RETURN') > 0 THEN 'R'
    ELSE returnname
    END
    FROM xxxx

  5. #5
    Join Date
    Feb 2004
    Location
    india
    Posts
    23
    hello andrew.
    But even what u suggessted is giving me ORA-00905 error ........

    (missing keyword ) ..

    SQL> SELECT CASE returnname
    2 WHEN INSTR(UPPER(returnname),'SCHEDULE') > 0 THEN 'S'
    3 WHEN INSTR(UPPER(returnname),'RETURN') > 0 THEN 'R'
    4 ELSE returnname
    5 END
    6 FROM BAS_BDERETURN_MST
    7 /
    WHEN INSTR(UPPER(returnname),'SCHEDULE') > 0 THEN 'S'
    *
    ERROR at line 2:
    ORA-00905: missing keyword

  6. #6
    Join Date
    Feb 2004
    Posts
    18
    Originally posted by sching
    thanks for the reply but its not what i was looking for...
    The retrunname will be like this :
    ' This is the schedule of .....' so i need to pick the schedule or it'll be like thhis :
    ' Breakdown of the monthy retruns'

    So i need to use string functio to check ..but how??????
    If these are the only two values you are expecting then you could use the following query:
    select decode(lower(substr(returnname,instr(lower(returnn ame),'schedule'),length('schedule'))),'schedule',' S','B')
    from dual

  7. #7
    Join Date
    Feb 2004
    Location
    india
    Posts
    23
    hello andrew.
    But even what u suggessted is giving me ORA-00905 error ........

    (missing keyword ) ..

    SQL> SELECT CASE returnname
    2 WHEN INSTR(UPPER(returnname),'SCHEDULE') > 0 THEN 'S'
    3 WHEN INSTR(UPPER(returnname),'RETURN') > 0 THEN 'R'
    4 ELSE returnname
    5 END
    6 FROM BAS_BDERETURN_MST
    7 /
    WHEN INSTR(UPPER(returnname),'SCHEDULE') > 0 THEN 'S'
    *
    ERROR at line 2:
    ORA-00905: missing keyword

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    True, I failed to correct your 4th error!

    PHP Code:
    SELECT CASE WHEN INSTR(UPPER(returnname),'SCHEDULE') > 0 THEN 'S'
                
    WHEN INSTR(UPPER(returnname),'RETURN') > 0 THEN 'R'
                
    ELSE returnname
           END
    FROM xxxx 

  9. #9
    Join Date
    Feb 2004
    Location
    india
    Posts
    23
    hi andrew , muffi, paginit
    Thanks for the replies ....
    Got what I was looking for ...
    I would like to close this thread .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •