SQL question

[hannah00]

Below is the code I have

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

[davey23uk]

where is not null

[hannah00]

sorry, how do you put the substr to not null since it's not a column.

[dbasupuser]

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

[davey23uk]

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