Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: Decode (instr Question

    Hello,
    Is it possible to run the below decode statement with 2 values??

    select
    DECODE (instr(C.display_value,'Plan'),0,null,C.display_va lue

    I want anything that doesn't have rate or plan in it to display null.

    Thanks in advance,
    Russ

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Decode (instr Question

    PHP Code:
    DECODE instr(C.display_value,'Plan'),
             
    0null,
                
    DECODE instr(C.display_value,'Rate'),
                         
    0null,
                            
    C.display_value 
                       
    )
           ) 
    Or with CASE:
    PHP Code:
    CASE WHEN instr(C.display_value,'Plan') = 0 THEN NULL
         WHEN instr
    (C.display_value,'Rate') = 0 THEN NULL
         
    ELSE C.display_value 
    END 

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    you could do it that way or you could use CASE.

    PHP Code:
    (CASE
        
    WHEN C.display_value IN ('Plan','Rate'THEN ''
        
    WHEN C.display_value NOT IN ('Plan','Rate'THEN C.display_value  END) AS DISPLAY_VALUE 

    I forget if you can just use an ELSE clause or not.
    Either way it should work.

    Look up the docs if you get an error.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Feb 2004
    Posts
    4
    Thank you very much for the responses. It is greatly appreciated. I already learned something about the case statement!!

    It doesn't seem to be worknig though. I need to it work like an or statement and it doesn't seem to be doing so.

    Sample data:
    1 Business Line 1 Year
    2 Business Rate 1 Year
    3 Business Plan 1 Year

    I would like to come up with:
    1 null
    2 Business Rate 1 Year
    3 Business Plan 1 Year

    Thanks again in advance,
    Russ

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    Andrew beat me to it last time.
    I think his CASE statement is better written than mine since it has the instr function in it.

    your original requirements were the reverse of your new ones.

    Describe your table and maybe we could help you more efficiently.
    It is impossible to tell if you have 2 columns or 5 columns.

    Let's assume 2 columns:

    PHP Code:
    (CASE
        
    WHEN C.display_value NOT LIKE '%Plan%' THEN ''
        
    WHEN C.display_value NOT LIKE '%Rate%' THEN ''
        
    ELSE C.display_value END) AS DISPLAY_VALUE 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Sorry, wrong way round:

    PHP Code:
    CASE WHEN instr(C.display_value,'Plan') > 0 THEN c.display_value
         WHEN instr
    (C.display_value,'Rate') > 0 THEN c.display_value
         END 
    or simplified:

    PHP Code:
    CASE WHEN instr(C.display_value,'Plan') > OR instr(C.display_value,'Rate') > 0
         THEN c
    .display_value
         END 

  7. #7
    Join Date
    Feb 2004
    Posts
    4
    It seems like your statement works like an "AND" it only gives me results where rate and plan is in the display.

    I apologize for the confusion, my requirements didn't change, I think I just didn't describe what I was looking for as accurately as possible.

    Here's a better example of data
    1 Business Line 1 Year
    2 Business Rate 1 Year
    3 Businsess Plan 1 Year
    4 Business Rate Plan 1 Year

    When I ran what you so kindly provided me with....(thanks)

    I got
    4 Business Rate Plan 1 Year

    Thanks again,
    Russ

  8. #8
    Join Date
    Feb 2004
    Posts
    4
    Tony, It worked!!!! THANK YOU!!!!

    Duck, Thank You also!!!

    You have both been extremely helpful and educating!!!!!

Posting Permissions

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