0

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

cstones88
  • 13
  • 1
  • 8

1 Answers1

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.

Daniel B
  • 797
  • 4
  • 13
  • The count/max solutions work in some respect i.e. that they get the job done, but I want to be able to call upon the resultset of the SQL statement to populate the report - so in this case a count/max would allow me to say either Y/N a value had been found, what I want to be able to do is show what that value is, using a max/count function wouldn't allow this, hence why I was looking at NVL/2 and COALESCE functions - Thank you for your input though – cstones88 Dec 17 '13 at 09:29
  • MAX() will return the value found, and you can show this value. The only limitation is that only a single value will be returnes (the highest value), but this is not an issue if a given EVENT_CONTRACT_ID may only be associated to a single EVENT_ID. Since you have not shown the structure of the tables involved then this is unclear. – Daniel B Dec 18 '13 at 13:03