Ian_Hill

CA Tuesday Tip: (CA IDMS) SYSCA.SINGLETON_NULL.

Discussion created by Ian_Hill Employee on Dec 11, 2012
CA IDMS Tuesday Tip by Ian Hill, Principal Support Engineer for December 11, 2012.

Release 17.0 saw the introduction of a new pseudo table called SYSCA.SINGLETON_NULL.

By definition, it has one row and no columns:-

SELECT * FROM SYSCA.SINGLETON_NULL;
*+ Status = -4       SQLSTATE = 42000        Messages follow:               
*+ DB005000 T90 C0M324: Message for module IDMSOCF, SQL statement number : 1.
*+ DB005271 T90 C-4M324: No columns are being projected         

The purpose of SYSCA.SINGLETON_NULL is so that you can use it to test scalar function call results with constant input values. It saves you from doing a dummy SELECT against a catalog table with few rows, such as SYSTEM.SCHEMA.

For example ...

SELECT DAYNAME('2012-12-11') AS "TODAY IS ..." FROM SYSCA.SINGLETON_NULL;
*+                                                                      
*+ TODAY IS ...                                                         
*+ ------------                                                         
*+ Tuesday                                                              
*+                                                                      
*+ 1 row processed

Outcomes