Oracle SQL – Return value regardless of record existance

RSS

Oracle SQL – Return value regardless of record existance

24 Mar

Here’s the situation:

You want to query the status of a member and would always want to return result for your application regardless if it exists or not.

Despite having two queries to do this, we can do it in just one query. In my case, it’s due to my program structure and multiple database connectivity that do not allow me to make multiple queries and checking here and there yada yada to come to the conclusion that, the member doesn’t exists in the database.

Table: member

ID	Name	Country	Status
111	Evan	Singapore	Active
222	Tanny	Malaysia	Active
333	Dennis	Indonesia	Inactive

Here’s what we can do

SELECT status FROM member WHERE id = ’444′ UNION SELECT ‘NOT EXISTS’ FROM dual WHERE NOT EXISTS(SELECT 1 FROM member WHERE id = ’444′ )

or you can further simplify it into one query rather than using UNION

SELECT NVL( (SELECT status FROM member WHERE item_id = ’444′ ),’NOT EXISTS’ ) FROM dual

Result:

Status

NOT EXISTS

Discussion:

The major advantage of doing so is that you can always be assured that the connection to your database is OK and the query result is OK as well. You can be assured that you can always get your result (and return some message if there isn’t), eliminating any other failing factors (db connection problem yada yada yada, of course you need to write your query properly)

Now, if there’s isn’t any result returned, something must have gone wrong with your database!

Alternatively, you can always structure your coding properly, include try and catch exception procedure as well.

HugoLim.com