7 Replies Latest reply: Oct 16, 2013 7:52 AM by ammmca11 RSS

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

    ammmca11

      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

          > 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

            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

              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

                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.

                  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

                    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

                      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