At 04:17 PM 2/23/2009, jan johansen wrote:
What is the best way to check for the existance of a temporary table?
Jan,
When defined, all TEMPorary tables/views are handled differently than
a normal (permanent) table/view.
Technically, once a TEMPorary (R:BASE Session Specific) table/view is
created, the following tables are added to the R:BASE database dynamic
SYS_TABLES.
. SYSTMP_COMMENTS
. SYSTMP_CONSTRAINTS
. SYSTMP_DEFAULTS
. SYSTMP_RULES
. SYSTMP_SERVERS
. SYSTMP_TRIGGERS
. SYSTMP_VIEWS
To find the existence of an "actual TEMPorary table", use the following
technique.
-- To find the existence of TEMPORARY TABLE (R:BASE Session Specific):
SET VAR vTempTableRows INTEGER = 0
SELECT COUNT(*) INTO vTempTableRows INDIC ivTempTableRows +
FROM SYS_TABLES WHERE SYS_TABLE_NAME = 'yourTEMPtablename' +
AND SYS_TABLE_TYPE = 'TABLE' AND SYS_TEMPORARY = 1
IF vTempTableRows = 1 THEN
CLS
PAUSE 2 USING 'Temporary Table Exist!' +
CAPTION ' Temporary Tables' +
ICON CONFIRM +
BUTTON 'Press any key to continue ...' +
OPTION BACK_COLOR WHITE +
|MESSAGE_COLOR WHITE +
|MESSAGE_FONT_NAME VERDANA +
|MESSAGE_FONT_SIZE 10 +
|MESSAGE_FONT_COLOR GREEN +
|MESSAGE_FONT_BOLD OFF +
|BUTTON_COLOR WHITE +
|BUTTON_FONT_COLOR GREEN +
|THEMENAME R:BASE Rocks!
ELSE
CLS
PAUSE 2 USING 'No Temporary Table with Such Name!' +
CAPTION ' Temporary Tables' +
ICON INFO +
BUTTON 'Press any key to continue ...' +
OPTION BACK_COLOR WHITE +
|MESSAGE_COLOR WHITE +
|MESSAGE_FONT_NAME VERDANA +
|MESSAGE_FONT_SIZE 10 +
|MESSAGE_FONT_COLOR RED +
|MESSAGE_FONT_BOLD OFF +
|BUTTON_COLOR WHITE +
|BUTTON_FONT_COLOR GREEN +
|THEMENAME R:BASE Rocks!
ENDIF
CLEAR VARIABLES iv%,vTempTableRows
RETURN
To find the existence of an "actual TEMPorary view", use the following
technique.
-- To find the existence of TEMPORARY VIEW (R:BASE Session Specific):
SET VAR vTempViewRows INTEGER = 0
SELECT COUNT(*) INTO vTempViewRows INDIC ivTempViewRows +
FROM SYS_TABLES WHERE SYS_TABLE_NAME = 'yourTEMPviewname' +
AND SYS_TABLE_TYPE = 'VIEW' AND SYS_TEMPORARY = 1
IF vTempViewRows = 1 THEN
CLS
PAUSE 2 USING 'Temporary View Exist!' +
CAPTION ' Temporary Views' +
ICON CONFIRM +
BUTTON 'Press any key to continue ...' +
OPTION BACK_COLOR WHITE +
|MESSAGE_COLOR WHITE +
|MESSAGE_FONT_NAME VERDANA +
|MESSAGE_FONT_SIZE 10 +
|MESSAGE_FONT_COLOR GREEN +
|MESSAGE_FONT_BOLD OFF +
|BUTTON_COLOR WHITE +
|BUTTON_FONT_COLOR GREEN +
|THEMENAME R:BASE Rocks!
ELSE
CLS
PAUSE 2 USING 'No Temporary View with Such Name!' +
CAPTION ' Temporary Views' +
ICON INFO +
BUTTON 'Press any key to continue ...' +
OPTION BACK_COLOR WHITE +
|MESSAGE_COLOR WHITE +
|MESSAGE_FONT_NAME VERDANA +
|MESSAGE_FONT_SIZE 10 +
|MESSAGE_FONT_COLOR RED +
|MESSAGE_FONT_BOLD OFF +
|BUTTON_COLOR WHITE +
|BUTTON_FONT_COLOR GREEN +
|THEMENAME R:BASE Rocks!
ENDIF
CLEAR VARIABLES iv%,vTempViewRows
RETURN
Both techniques should provide the existence of a TRUE TEMPorary
Table or View.
Hope that helps!
Very Best R:egards,
Razzak.
