SQL Queries - CAST Management Base - Queries on Generic set - How to check if a referenced generic set does not exists

Purpose of Query

The query checks if a referenced generic set does not exists

Applicable CAST Version

ReleaseYes/No
8.3.x✅
8.2.x✅
8.1.x✅
8.0.x✅

Applicable RDBMS

RDBMSYes/No
Oracle Server✅
Microsoft SQL Server✅
CSS2✅

Query for CSS

SELECT EXISTS 
       ( 
              SELECT osd.settype, 
                     osd.setname, 
                     osd.setdefinition 
              FROM   cal_objsetdef osd, 
                     cal_setassociation sa 
              WHERE  sa.set_id = osd.set_id 
              AND    setdefinition LIKE '<Generic_set_name>' 
              AND 
                     ( 
                            SELECT EXISTS 
                                   ( 
                                          SELECT 1 
                                          FROM   cal_objsetdef osd, 
                                                 cal_setassociation sa 
                                          WHERE  sa.set_id = osd.set_id 
                                          AND    osd.settype = 'Generic sets' 
                                          AND    setname = '<Generic_set_name>' ) ) )

Query result example

“t”

Query result interpretation

The query return “t” if the generic set does not exist

Query for Oracle

SELECT EXISTS 
       ( 
              SELECT osd.settype, 
                     osd.setname, 
                     osd.setdefinition 
              FROM   cal_objsetdef osd, 
                     cal_setassociation sa 
              WHERE  sa.set_id = osd.set_id 
              AND    setdefinition LIKE '<Generic_set_name>' 
              AND 
                     ( 
                            SELECT EXISTS 
                                   ( 
                                          SELECT 1 
                                          FROM   cal_objsetdef osd, 
                                                 cal_setassociation sa 
                                          WHERE  sa.set_id = osd.set_id 
                                          AND    osd.settype = 'Generic sets' 
                                          AND    setname = '<Generic_set_name>' ) ) )

Query result example

“t”

Query result interpretation

The query return “t” if the generic set does not exist.

Query for SQL server

SELECT EXISTS 
       ( 
              SELECT osd.settype, 
                     osd.setname, 
                     osd.setdefinition 
              FROM   cal_objsetdef osd, 
                     cal_setassociation sa 
              WHERE  sa.set_id = osd.set_id 
              AND    setdefinition LIKE '<Generic_set_name>' 
              AND 
                     ( 
                            SELECT EXISTS 
                                   ( 
                                          SELECT 1 
                                          FROM   cal_objsetdef osd, 
                                                 cal_setassociation sa 
                                          WHERE  sa.set_id = osd.set_id 
                                          AND    osd.settype = 'Generic sets' 
                                          AND    setname = '<Generic_set_name>' ) ) )

Query result example

“t”

Query result interpretation

The query return “t” if the generic set does not exists

Notes/comments

Related Pages