Hi,
Oracle version is: 10.1.0.5.0
Hyperion SQR Production Reporting Studio 9.3.1.0
Within an SQR I have a PL/SQL query to exclude table names containing at least 4 consecutive numbers (in red below) -- these represent dates (either YYYY or YYMMDD/DDMMYY) and are not production tables
(I have asked our DBA if he could put them in a separate table space but for now I need to exclude them through coding)
This works in SQL Developer:
SELECT DISTINCT
ALL_TABLES.OWNER,
ALL_TABLES.TABLE_NAME,
ALL_TAB_COLUMNS.COLUMN_NAME
FROM ALL_TAB_COLUMNS, ALL_TABLES
WHERE ( ALL_TAB_COLUMNS.OWNER = ALL_TABLES.OWNER ) and
( ALL_TAB_COLUMNS.TABLE_NAME = ALL_TABLES.TABLE_NAME ) and
(( ALL_TAB_COLUMNS.COLUMN_NAME like '%POINTER%' ) AND
( ALL_TABLES.TABLESPACE_NAME <> 'PRODUCTION' ) AND
( ALL_TABLES.TABLESPACE_NAME <> 'DEVELOPMENT' ) AND
( ALL_TABLES.TABLESPACE_NAME <> 'FR_PDW' ) AND
( ALL_TABLES.TABLESPACE_NAME <> 'RETENTION' ) AND
( ALL_TABLES.TABLE_NAME not like 'BIN%' ) AND
( ALL_TABLES.OWNER not like '%_ARCH' ) AND
( ALL_TABLES.OWNER not like 'OPS%' ) AND
( ALL_TAB_COLUMNS.COLUMN_NAME <> 'UPDATE_POINTER' ) AND
( ALL_TAB_COLUMNS.COLUMN_NAME <> 'ADDED_POINTER' ) ) AND
NOT ( REGEXP_LIKE(ALL_TABLES.TABLE_NAME,'[[:digit:]]{4}') )
However, it does not work in SQR - I receive a message:
(SQR 4707) No value found for substitution variable: {4}
NOT ( REGEXP_LIKE(ALL_TABLES.TABLE_NAME,'[[:digit:]]{4}') )
I googled that and it is an SQR issue: {} and [] are compiler commands in SQR.
So my question is, does anyone have any suggestions on rewriting that line to find 4 consecutive numbers without using brackets or {}?
Thank you for your time and help!
Annie