Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2012
    Posts
    1

    Question Unanswered: Oracle Email Script

    I am trying to figure out how to get my script to email by looking at two characters instead of one. This is what I have now using one character:
    my $sql = qq{ select Connection_symbol, Name_0, decode(substr(name_0, 1,1), 'K', 'Line1', '3', 'Line2', 'P', 'Line3', '5',
    'Line4', 'No Pattern') from p_connections where error_text like 'SYS disconnected' and connection_symbol like '%SYS'};

    The reason I need this is because the 'Line1' connections use different names and they resemble the 'Line4' naming convention.

    ANY HELP????

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Review the comments I added to your sql:
    Code:
    select Connection_symbol, Name_0,
           decode( substr(name_0, 1,1 ), --Returns 1st position, Make sure character your looking for is always in position 1.
             'K', 'Line1',               --Returns 'Line1' if 1st pos is a 'K'
             '3', 'Line2',               --Returns 'Line2' if 1st pos is a '3'
             'P', 'Line3',               --Returns 'Line3' if 1st pos is a 'P'
             '5', 'Line4',               --Returns 'Line4' if 1st pos is a '5'
                  'No Pattern' )         --Returns 'No Pattern' if 1st pos is not in( 'K', '3', 'P', '5' )
     from  p_connections
    where  error_text like 'SYS disconnected' -- Add '%' either to beginning or end of pattern or just use '=' to compare.
     and   connection_symbol like '%SYS'

Posting Permissions

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