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?

Answers


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.


Need Your Help

ApnsPHP - Getting error while sending Push Notification

php ios apple-push-notifications apns-php

I integrated Push Notification library for sending out Push Notifications. When i tested it on my local machine it worked fine but when i uploaded library sample code to live server it throws me er...

Objective-C++ importing C++ class fails, cassert not found

objective-c ios cocos2d-iphone objective-c++ box2d-iphone

So I want to publicly expose a Box2D (C++) pointer to other Objective-C++ classes in my cocos2d + box2d project. I declare a method "getWorld" in my interface which references C++ class b2World and

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.