-
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
-
-
sorry, how do you put the substr to not null since it's not a column.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|