Search code examples
sqloracle-databaseselect-query

How to create anonymous field with values in oracle select query

I have following select query which returns KPI_DEF_ID values.

Select KPI_DEF_ID from RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION WHERE KPI_DEF_ID NOT IN (Select KPI_DEF_ID FROM KPI_STATUS);

The query returns the output:

KPI_DEF_ID
10001
10002

Now i want to modify the select query with anonymous fields KPI_STATUS_BEFORE and KPI_STATUS_NOW and it contains the values as 'G'. The KPI_DEFINTION table does not have this columns.The output should something look likes:

KPI_DEF_ID    KPI_STATUS_BEFORE    KPI_STATUS_NOW
10001         G                    G
10002         G                    G
Solution

  • The term you're looking for is not anonymous columns but computed columns (or derived columns). you create those by just specifying the expression in the query:

    Select 
    KPI_DEF_ID ,
    'G' AS KPI_STATUS_BEFORE,
    'G' AS KPI_STATUS_AFTER
from RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION 
WHERE KPI_DEF_ID NOT IN (Select KPI_DEF_ID FROM KPI_STATUS)