This discussion is archived
7 Replies Latest reply: Oct 16, 2013 5:52 AM by ammmca11 RSS

select null values else not null values in table???

ammmca11 Newbie
Currently Being Moderated

table desc:


Name                                      Null?    Type

-----------------------------------------     --------    ----------------------------

CARD_ID                                                   NUMBER

REWARD_POINT                                       NUMBER

REWARD_MILES                                       NUMBER


insert values:


insert into example values (9,null,null);

insert into example values (9,2000,null);

insert into example values (13,null,null);

insert into example values (13,null,null);

insert into example values (14,null,null);

insert into example values (14,3000,null);

insert into example values (20,null,null);

insert into example values (20,5000,null);


table name: example

   CARD_ID     REWARD_POINT    REWARD_MILES

   ----------         ------------ ------------      -------------------------

         9             

         9              2000

         9             

        13            

        13             

        14             

        14             

        14           3000

        20             

        20             

        20            5000


i want to select the card_id and reward_point without duplicate values in card_id. the expected output is like


CARD_ID     REWARD_POINT  REWARD_MILES   NVL

----------         ------------ ------------     -----------------------    -------

     9              2000                                   null                     yes

    13             null                                      null                      no

    14             3000                                   null                     yes

     20            5000                                   null                     yes

 

i use this query:

select distinct card_id, reward_point,nvl2(to_char(reward_point),'yes','no') from example

where exists (select card_id from example where reward_point is null)

and reward_point is null or reward_point is not null;

 

and got output like this:

 

 

 

   CARD_ID REWARD_POINT NVL

   ----------     ------------------------- -------

         9                                          no

        20         5000                      yes

        13                                        no

        14         3000                      yes

        20                                        no

         9         2000                       yes

        14                                        no

i don't know how to omitted the duplicate card_id from the out-put.

 

 

plz reply soon as possible and help me to solve this query,,,,

 

 

thank you,,,,

  • 1. Re: select null values else not null values in table???
    SomeoneElse Guru
    Currently Being Moderated

    > plz reply soon as possible and help me to solve this query,,,,

     

    If you gave us table data in a usable format, you'd have your answer faster.

     

  • 2. Re: select null values else not null values in table???
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    So you want only 1 row of output for each value of card_id: that sounds like a job for GROUP BY card_id.

    NVL2 is a good idea, but why TO_CHAR?  TO_CHAR (reward_point) will be NULL if and only if reward_point (by itself) is NULL.

     

    As Someoneelse said, for better, faster replies, post a complete test script that people can run to re-create the problem and test their ideas.  Include CREATE TABLE and INSERT statements for your sample data.

     

    See the forum FAQ: https://forums.oracle.com/message/9362002

     

    If you can show the problem using commonly available tables (like those in the scott schema) then you don't have to post any sample data; just make it clear what table(s) you're using, what output you want, and why.  For example, I think your problem is very similar to this:  How can I show which departments in scott.emp have non-NULL comms?  That is, how can I get this output?

     

       DEPTNO ANY_COMM

    --------- --------

           10 no

           20 no

           30 yes

     

    One way is:

     

    SELECT    deptno

    ,         NVL2 (MIN (comm), 'yes', 'no') AS any_comm

    FROM      scott.emp

    GROUP BY  deptno

    ORDER BY  deptno

    ;

  • 3. Re: select null values else not null values in table???
    kendenny Expert
    Currently Being Moderated

    What if you have the same card_id more than once with different reward_point values? Use the max? OK

     

    select card_id, reward_point, case when reward_point is null then 'no' else 'yes' end nvl

    from (select card_id,max(reward_point) reward_point from example group by card_id);

  • 4. Re: select null values else not null values in table???
    Purvesh K Guru
    Currently Being Moderated

    This way:, but you need to explain why NVL for card id 13 is No. Is that a Typo?

     

     

    with data as

    (

      select 9 card_id, null rew_pt from dual union all

      select 9 card_id, 2000 rew_pt from dual union all

      select 9 card_id, null rew_pt from dual union all

      select 13 card_id, null rew_pt from dual union all

      select 13 card_id, null rew_pt from dual union all

      select 14 card_id, null rew_pt from dual union all

      select 14 card_id, 3000 rew_pt from dual union all

      select 20 card_id, null rew_pt from dual union all

      select 20 card_id, 5000 rew_pt from dual

    )

    select distinct card_id, max(rew_pt) over (partition by card_id) rew_pt,

           NVL2(max(rew_pt) over (partition by card_id), 'Yes', 'No')IS_NULL

      from data

    order by card_id;


    CARD_ID                REW_PT                 IS_NULL

    ---------------------- ---------------------- -------

    9                      2000                   Yes    

    13                                            No     

    14                     3000                   Yes    

    20                     5000                   Yes

  • 5. Re: select null values else not null values in table???
    Sven W. Guru
    Currently Being Moderated

    Distinct is not useful for that. But it looks as if you can use GROUP BY.

    Just find the most fitting aggregate function to return the right reward_point information.

    I used SUM for example,but MIN/MAX would also be possible. Then use NVL2 or a case expression or a decode expression onto that

     

    untested example

    select card_id
            , sum(reward_point) as reward_point_sum
            , nvl2(sum(reward_point),'yes','no') yn_flag
    from example 
    group by card_id
    ;
    
     
    
  • 6. Re: select null values else not null values in table???
    ammmca11 Newbie
    Currently Being Moderated

    i got the output like this:

       CARD_ID   REWARD_POINT NVL

         ----------       ------------             ---

             9         2000                     Yes

            11                                     No

            13                                     No

            14         3000                     Yes

            20         5000                     Yes

     

    but i want, if the null value is displayed as null in output like this

     

    CARD_ID   REWARD_POINT    NVL

         ----------       ------------              ---

             9         2000                     Yes

            11          null                       No

            13          null                       No

            14         3000                     Yes

            20         5000                     Yes

  • 7. Re: select null values else not null values in table???
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    hi, what you mean under "null" you want result with "null" value or null? Anyway try Sven method like this :

     

    select card_id, nvl(reward_point_sum ,'null'), yn_flag from(

    select card_id 

               ,  sum(reward_point) as reward_point_sum 

              , nvl2(sum(reward_point),'yes','no') yn_flag 

    from example  

    group by card_id  )

     

     

    ----

    Ramin Hashimzade

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points