if my first query returns null value then my second query has to run even the second query is null then my default value as to shown
In a procedure if my first query returns null value or returns no records then my second query has to run even the second query returns null value or returns no records then a default value has to return. how to make this procedure? should i use if else statement or exception handler?
One way of doing this would be to nest IF statements, something like this:
create or replace function get_queue_id (p_product_code in mo_product_master.product_code%type , p_intermediary_code in intrfc_intermediary_mstr_view.intermediary_code%type) return mo_product_master.queue_id%type as return_value number; begin -- preferred_choice begin select pm.queue_id into return_value from mo_product_master pm where pm.product_code=p_product_code exception when no_data_found then null; end; if return_value is null then -- second_choice begin select qim.queue_id into return_value from mo_queue_inter_map_master qim , intrfc_intermediary_mstr_view imv where qim.category_code =imv.category_code and imv.intermediary_code=p_intermediary_code; exception when no_data_found then null; end; if return_value is null then -- default_value select id into return_value from mo_queue_master where queue_name='others' and status='Active'; end if; end if; return return_value; end; /
It is a bit clunky but it does the job.
Suppressing the NO_DATA_FOUND exception is not usually recommended practice but I think it fits this scenario: not finding the first QUEUE_ID is part of the regular business logic rather than an exception which needs to be handled. I don't think nesting the subsequent selects in the exception handler is nearly as expressive of the business rules.