SQL question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: SQL question

  1. #1
    Join Date
    Jan 2005
    Posts
    221

    SQL question

    Below is the code I have

    Code:
    SELECT DISTINCT Y.TABLE_NAME,
    SUBSTR(Z.TEXT, INSTR(Z.TEXT, '''')+1, INSTR(Z.TEXT, '''', 1, 2) - INSTR(Z.TEXT, '''') -1) --AS LOG_TABLE
    FROM ALL_TRIGGERS Y
    LEFT OUTER JOIN
         (
          SELECT B.TABLE_NAME, B.TRIGGER_NAME, A.TEXT, B.TRIGGER_BODY
          FROM ALL_SOURCE A
          INNER JOIN ALL_TRIGGERS B
          ON A.OWNER = B.OWNER
          AND  A.NAME = B.TRIGGER_NAME
          WHERE A.TYPE = 'TRIGGER'
          AND INSTR(A.TEXT, 'ls_log_table') <> 0 AND INSTR(A.TEXT, ':=') <> 0
         ) Z
         ON 
         Y.TABLE_NAME = Z.TABLE_NAME AND
         Y.TRIGGER_NAME = Z.TRIGGER_NAME
    INNER JOIN ALL_SOURCE T
    ON T.OWNER = Y.OWNER
    AND  T.NAME = Y.TRIGGER_NAME
    WHERE T.TYPE = 'TRIGGER'
    AND INSTR(T.TEXT, 'insert_history_log') <> 0
    --AND LOG_TABLE is not null
    ORDER BY Y.TABLE_NAME
    in the SubStr column, I have null value, what is the syntax to exclude the null value.

    thanks

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    where is not null

  3. #3
    Join Date
    Jan 2005
    Posts
    221
    sorry, how do you put the substr to not null since it's not a column.

  4. #4
    Join Date
    Nov 2002
    Posts
    170
    Quote Originally Posted by hannah00
    sorry, how do you put the substr to not null since it's not a column.


    substr(nvl(abc,'null nalue'),.....)

    nvl will replace null value to what value you want

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    Quote Originally Posted by hannah00
    sorry, how do you put the substr to not null since it's not a column.
    you pt it in a where clause so the nulls arent selected

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