Oracle 11G: Default to Static Value When Query Returns Nothing

Oracle 11g: Default to static value when query returns nothing

This should be a simpler version of what you did:

SELECT NVL(desired_datum, 'default') AS desired_datum
FROM DUAL LEFT JOIN  data_table ON the_key = &input_value

How to set a default row for a query that returns no rows?

One approach for Oracle:

SELECT val
FROM myTable
UNION ALL
SELECT 'DEFAULT'
FROM dual
WHERE NOT EXISTS (SELECT * FROM myTable)

Or alternatively in Oracle:

SELECT NVL(MIN(val), 'DEFAULT')
FROM myTable

Or alternatively in SqlServer:

SELECT ISNULL(MIN(val), 'DEFAULT')
FROM myTable

These use the fact that MIN() returns NULL when there are no rows.

How to add static values to SQL QUERY Result set in ORACLE

you can do it this way,

SELECT 'UPCOMING FLIHGTS' text, NULL sched
  FROM dual
UNION ALL
<your select query for upcoming flights>
UNION ALL
SELECT 'PAST FLIGHTS' text, NULL sched
  FROM dual
UNION ALL
<your select query for past flights>

Return result from query even if WHERE clause not met

If you want to return those values just wrap each column with a SUM and an ISNULL:

SELECT ISNULL(SUM(CASE
        WHEN TaskId IS NULL THEN 0
        ELSE 1  
    END), 0) AS TaskExists, 
    ISNULL(SUM(CASE
        WHEN IsDownTask = 0 AND TaskStatus = 63 THEN 1
        WHEN IsDownTask = 1 THEN 1
        ELSE 0
    END), 0) AS PressReady,
    ISNULL(SUM(CASE 
        WHEN IsDownTask = 1 AND MachineId <> 2710 THEN 1
        ELSE 0
    END), 0) AS DownTaskAssignedToDifferentMachine    

How to return default value when no rows return from table

You could use NOT EXISTS and UNION ALL

SELECT errorCode, errorText
FROM ERRORMESSAGE WHERE <condition>
UNION ALL
SELECT 'NOERRORCODE', 'NOERROR'
FROM ERRORMESSAGE
WHERE NOT EXISTS (SELECT * FROM ERRORMESSAGE WHERE <condition>)

You need to replace <condition> with actual criteria. So, when there is are row exists, then SELECT after UNION ALL will get executed, which will show static row.

Please note: I have never used HSQLDB, so I wasn't sure how to show static rows, but I used as explained here

How to set default value for column of new created table from select statement in 11g

You can specify the constraints and defaults in a CREATE TABLE AS SELECT, but the syntax is as follows

create table t1 (id number default 1 not null);
insert into t1 (id) values (2);

create table t2 (id default 1 not null)
as select * from t1;

That is, it won't inherit the constraints from the source table/select. Only the data type (length/precision/scale) is determined by the select.

---

Related Topics