1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Hi i have a question with coalesce

Discussion in 'SQL PL/SQL' started by Shanmugapriya, Jun 16, 2016.

  1. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    I get column values as mentioned below:

    sub_premises_no_o ,
    premises_name_o ,
    street_no_o ,
    thrfare_name_o ,
    postal_locality_o ,
    post_town_o.
    county_name_o ,
    postcode_o

    My requirement is to get the first two non null values from them and assign it to two different variables namely
    address_01 and address_02.

    To be more precise, say if sub_premises_no_o is null and all others are not null, then
    address_01 := premises_name_o;
    address_02 := street_no_o;

    Similarly all other possibilities.......

    Any alternative method apart my IF-else -end if????????? Would it be possible to use COALESCE in this scenario?
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    use of the coalesce function won't help with the solution of this task
    What is your oracle version ?

    For example (Oracle version >=11):
    Code (SQL):
    WITH your_data AS
    (
    SELECT
        1 id ,
        100 sub_premises_no_o , NULL premises_name_o ,NULL street_no_o , -22 thrfare_name_o ,300 postal_locality_o ,NULL post_town_o, -23 county_name_o ,0 postcode_o
    FROM
        dual
    ),
    step1
    AS(
    SELECT
    id,
    col,
    val,
    rownum rn1,
    ROW_NUMBER() OVER(ORDER BY nvl2(val,rownum,NULL) NULLS LAST )rn
    FROM
        your_data
    unpivot include NULLS
    ( val FOR col IN (sub_premises_no_o ,premises_name_o ,street_no_o ,thrfare_name_o ,postal_locality_o ,post_town_o,county_name_o ,postcode_o))
    )
    SELECT
        st1.*,
        MAX(CASE WHEN st1.rn = 1 THEN val END )  OVER(partition BY id) v1,
        MAX(CASE WHEN st1.rn = 2 THEN val END )  OVER(partition BY id) v2      
    FROM step1 st1
    ORDER BY id,rn1;

     
     
    Last edited: Jun 16, 2016
  3. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    Thank you for your reply. It looks more complex for me. Shall you tell the simple if-else-endif please for this scenario?
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    By means of only coalesce it is impossible to solve your problem.
    it is possible to use coalesce/nullif for the comparison/choice or to write simple function on pl/sql or to use SQL MODEL.
    Code (SQL):
    WITH your_data AS
    (
    SELECT
        1 id ,
        100 sub_premises_no_o , to_number(NULL) premises_name_o ,to_number(NULL) street_no_o , -22 thrfare_name_o ,300 postal_locality_o ,to_number(NULL) post_town_o, -23 county_name_o ,0 postcode_o
    FROM
        dual
    ),
    step1 AS
    (SELECT
    y.*,
    COALESCE (sub_premises_no_o ,
    premises_name_o ,
    street_no_o ,
    thrfare_name_o ,
    postal_locality_o ,
    post_town_o,
    county_name_o ,
    postcode_o)  v1 FROM your_data y)


    SELECT st1.v1,
    COALESCE (NULLIF(sub_premises_no_o ,st1.v1),
    NULLIF(premises_name_o ,st1.v1),
    NULLIF(street_no_o ,st1.v1),
    NULLIF(thrfare_name_o ,st1.v1),
    NULLIF(postal_locality_o ,st1.v1),
    NULLIF(post_town_o,st1.v1),
    NULLIF(county_name_o ,st1.v1),
    NULLIF(postcode_o,st1.v1)) v2
    FROM step1 st1;
     
    Last edited: Jun 17, 2016
    Shanmugapriya likes this.
  5. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    Hi Sergey Krasnoslobodtsev,
    This one worked. Thank you again for your help :)

    Regards,
    Priya