Oracle - No rows returned/inserting a value

Question

I'm trying to create a report which checks an SQL statement for the data returned, and based on result set populate a column with either Y/N.

I've made an attempt at using the NVL functionality, but I think I'm missing the point of this function entirely - The query below returns no rows whatsoever, so there is no 'null value' which is why the NVL function won't work in my case - I've tried COALESCE but I can't get my head around the embedded SELECT and calling a dummy 'DUAL' table.

Is there a method I can employ to provide a value in case of 'no rows returned'?

SELECT
TO_CHAR(AR_WD_EVENTS.EVENT_ID)
FROM
AR_WD_EVENTS, AR_PI_SITE_BLOCKS
WHERE AR_WD_EVENTS.EVENT_BLOCK_ID = {!AR_PI_SITE_BLOCKS.SITE_ID}
AND TO_CHAR(AR_WD_EVENTS.EVENT_CONTRACT_ID) = 1555

This is the QB syntax - '6. Shutters - Event' is the name of the SQL query above, basically I want the SQL statement to return a corresponding 'Event ID', if it doesn't find an 'Event ID' then give a value of NULL/Zero/NA, so that I can use it in the QBF statement below - as it stands the SQL query works fine but only when there's a value to return.

$QBFvalue = 'Y';

if ({%6. Shutters - Event} = 'N/A') $QBFvalue = 'N';

return $QBFvalue;

Many Thanks

Answer 1

If you will always get either one (single event id) or zero results, you can use the MAX function:

SELECT
MAX(TO_CHAR(AR_WD_EVENTS.EVENT_ID))
FROM
AR_WD_EVENTS, AR_PI_SITE_BLOCKS
WHERE AR_WD_EVENTS.EVENT_BLOCK_ID = {!AR_PI_SITE_BLOCKS.SITE_ID}
AND TO_CHAR(AR_WD_EVENTS.EVENT_CONTRACT_ID) = 1555

The MAX function will return Null when there are no matching rows, and it will return EVENT_ID when there is one matching row.