Home » SQL & PL/SQL » SQL & PL/SQL » Replacing a Null column value with the value of the above record (Oracle 10.2.0.1.0, Solaris)
Replacing a Null column value with the value of the above record [message #386187] Fri, 13 February 2009 01:18 Go to next message
mvmdas
Messages: 6
Registered: February 2009
Junior Member
I am having a customer_status table, which has got the following columns and also given the records in it.


customer_id from_date Status
999 09/27/2008 200

999 10/01/2008 300

999 10/02/2008 400

999 11/05/2008 820



These records tells that the customer was in,

status 200 from 09/27/2008 to 09/30/2008
status 300 on 10/01/2008
status 400 from 10/02/2008 to 11/04/2008
status 820 from 11/05/2008 onwards


I want to generate a result set for a period of 09/27/2008 to 11/05/2008. So first I tried to generate dates dynamicaly using the dual table. And using an outer join I tried to join the dates with the customer_status table.


select customer_id c_id,nvl(FROM_DATE,DY_DATE),STATUS
from (SELECT to_date('09/27/2008','mm/dd/yyyy')+rownum DY_DATE
FROM DUAL
CONNECT BY LEVEL <= 40)
left join customer_status on DY_DATE=FROM_DATE
and customer_id=999



My problem is that. This query will return only the following resultset.

c_id from_date Status
---------------------------
999 09/27/2008 200
999 09/28/2008
999 09/29/2008
999 09/30/2008
999 10/01/2008 300
999 10/02/2008 400
999 10/03/2008
999 10/04/2008
999 10/05/2008
999 10/06/2008
999 10/07/2008
999 10/08/2008
999 10/09/2008
999 10/10/2008
999 10/11/2008
999 10/12/2008
999 10/13/2008
999 10/14/2008
999 10/15/2008
999 10/16/2008
999 10/17/2008
999 10/18/2008
999 10/19/2008
999 10/20/2008
999 10/21/2008
999 10/22/2008
999 10/23/2008
999 10/24/2008
999 10/25/2008
999 10/26/2008
999 10/27/2008
999 10/28/2008
999 10/29/2008
999 10/30/2008
999 10/31/2008
999 11/01/2008
999 11/02/2008
999 11/03/2008
999 11/04/2008
999 11/05/2008 820


where as I need the status of the customer to be returned for other dates as well based on the date range, i.e.,

status 200 from 09/27/2008 to 09/30/2008
status 300 on 10/01/2008
status 400 from 10/02/2008 to 11/04/2008
status 820 from 11/05/2008 onwards

So the result set I want is given below.

c_id from_date Status
---------------------------
999 09/27/2008 200
999 09/28/2008 200
999 09/29/2008 200
999 09/30/2008 200
999 10/01/2008 300
999 10/02/2008 400
999 10/03/2008 400
999 10/04/2008 400
999 10/05/2008 400
999 10/06/2008 400
999 10/07/2008 400
999 10/08/2008 400
999 10/09/2008 400
999 10/10/2008 400
999 10/11/2008 400
999 10/12/2008 400
999 10/13/2008 400
999 10/14/2008 400
999 10/15/2008 400
999 10/16/2008 400
999 10/17/2008 400
999 10/18/2008 400
999 10/19/2008 400
999 10/20/2008 400
999 10/21/2008 400
999 10/22/2008 400
999 10/23/2008 400
999 10/24/2008 400
999 10/25/2008 400
999 10/26/2008 400
999 10/27/2008 400
999 10/28/2008 400
999 10/29/2008 400
999 10/30/2008 400
999 10/31/2008 400
999 11/01/2008 400
999 11/02/2008 400
999 11/03/2008 400
999 11/04/2008 400
999 11/05/2008 820


Can you please help me modify the query to get the above resultset.
Re: Replacing a Null column value with the value of the above record [message #386189 is a reply to message #386187] Fri, 13 February 2009 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Replacing a Null column value with the value of the above record [message #386198 is a reply to message #386187] Fri, 13 February 2009 01:52 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Let's be a bit creative on friday the 13th Smile


WITH    data     AS
    (
    -- Mock the data
    SELECT  999 as  customer_id ,
            TO_DATE('09/27/2008','mm/dd/yyyy') as from_date,
            200 as status
    FROM    DUAL
    UNION
    SELECT  999 as  customer_id ,
            TO_DATE('10/01/2008','mm/dd/yyyy') as from_date,
            300 as status
    FROM    DUAL
    UNION
    SELECT  999 as  customer_id ,
            TO_DATE('10/02/2008','mm/dd/yyyy') as from_date,
            400 as status
    FROM    DUAL
    UNION
    SELECT  999 as  customer_id ,
            TO_DATE('11/05/2008','mm/dd/yyyy') as from_date,
            820 as status
    FROM    DUAL
    ) ,
    date_ranges  AS
    ( 
    -- We're missing the end dates: fix this with LEAD       
    SELECT  customer_id ,
            from_date   ,
            LEAD(from_date,1,SYSDATE)
                 OVER ( ORDER BY from_date ) as end_date  ,
            status
    FROM    data
    ) ,
    date_range  AS
    (
    -- Get the min and max to use further on
    SELECT  MIN( from_date ) min_date ,
            MAX( from_date ) max_date
    FROM    date_ranges
    ) ,
    date_list   AS
    (
    -- Generate all days between min and max
    SELECT  min_date + rownum - 1 a_date
    FROM    date_range
    CONNECT BY LEVEL <= max_date - min_date + 1    
    )            
SELECT  dl.a_date   ,
        dr.status
FROM    date_list   dl    ,
        date_ranges dr
WHERE   dl.a_date >= dr.from_date
AND     dl.a_date <  dr.end_date    


You can do two things:
- Copy and modify with the proper names, but you'll learn nothing from it

- Print this out and build your own query from scratch and see what happens

And I'm sure my example can be written far more better, but it's a step-by-step approach.
Re: Replacing a Null column value with the value of the above record [message #386209 is a reply to message #386189] Fri, 13 February 2009 02:35 Go to previous messageGo to next message
mvmdas
Messages: 6
Registered: February 2009
Junior Member
Sorry for the inconvenience. Going forward I will make sure that I follow proper format.
Re: Replacing a Null column value with the value of the above record [message #386212 is a reply to message #386198] Fri, 13 February 2009 02:45 Go to previous messageGo to next message
mvmdas
Messages: 6
Registered: February 2009
Junior Member
Thanking you for the excellent suggestion MarcS.

I do need to modify the query to satisfy my requirement.

For me the customer id will change, so is the from_date and status.

Thanks once again for the quick help.
Re: Replacing a Null column value with the value of the above record [message #386296 is a reply to message #386198] Fri, 13 February 2009 08:22 Go to previous messageGo to next message
mvmdas
Messages: 6
Registered: February 2009
Junior Member
I am a novice in analytic functions. I understood how the Lead() function is working. Out of curiosity I tried to find the status of all the customers on a given day, let's say 15th of October 2008. Customer_status table will be having at least one record corresponding to a customer_mst record. I have used the query given below. I know in this case probably we don't have to generate a range of dates. I tried various options, but haven't succeeded in getting this one right. Can you please let me know what is that I am missing in this query?

WITH    data     AS
    (
    -- Show all customers
    SELECT  customer_id as customer_id ,
            from_date as from_date,
            status as status
    FROM    customer_status cs,customer_mst cm
    where cm.customer_id=cs.customer_id
    ) ,
    date_ranges  AS
    ( 
    -- We're missing the end dates: fix this with LEAD       
    SELECT  customer_id ,
            from_date   ,
            LEAD(from_date,1,SYSDATE)
                 OVER ( ORDER BY from_date ) as end_date  ,
            status
    FROM    data
    ) ,
    date_range  AS
    (
    -- Get the min and max to use further on
    SELECT  TO_DATE ('08/10/2008', 'mm/dd/yyyy') min_date ,
            trunc(sysdate) max_date
    FROM    dual
    ) ,
    date_list   AS
    (
    -- Generate all days between min and max
    SELECT  min_date + rownum - 1 a_date
    FROM    date_range
    CONNECT BY LEVEL <= max_date - min_date + 1    
    )            
SELECT  dl.a_date   ,
        dr.status,
        dr.customer_id 
FROM    date_list   dl    ,
        date_ranges dr
WHERE   dl.a_date >= dr.from_date
AND     dl.a_date <  dr.end_date    
AND  dl.a_date=TO_DATE ('10/15/2008', 'mm/dd/yyyy')

Re: Replacing a Null column value with the value of the above record [message #386298 is a reply to message #386187] Fri, 13 February 2009 08:44 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Try a step-by-step approach and see what you get and what you can do to get the result you want Wink

You can combine the first two parts ( WITH-parts "data" and "date_ranges" ) into one.
I used the "data" because I don't have the table.

Re: Replacing a Null column value with the value of the above record [message #386309 is a reply to message #386298] Fri, 13 February 2009 09:32 Go to previous messageGo to next message
mvmdas
Messages: 6
Registered: February 2009
Junior Member
Thank You MarcS for the suggestion. I will try this out. Wanted to know if there is a way to get this done without generating the date range. In this case we will be generating a large resultset initially (let's say January 1st to 31st Dec) from which we will be trying to pick one date.
Re: Replacing a Null column value with the value of the above record [message #386430 is a reply to message #386187] Sat, 14 February 2009 18:46 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member
Here is a slightly different version .


create table system.foo (id number , fdt date stat number);
insert into system.foo values
  (999,to_date('09/27/2008','mm/dd/yyyy'),200);
insert into system.foo values
  (999,to_date('10/01/2008','mm/dd/yyyy'),300);
insert into system.foo values
  (999,to_date('10/02/2008','mm/dd/yyyy'),400);
insert into system.foo values
  (999,to_date('11/05/2008','mm/dd/yyyy'),820);
==============

SQL> select * from system.foo;

ID FDT STAT
---------- --------- ----------
999 27-SEP-08 200
999 01-OCT-08 300
999 02-OCT-08 400
999 05-NOV-08 820



Now the SQL.
 with
 dt_range as ( select max(fdt) mxdt , min(fdt)mndt from system.foo)  ,
dtset as (select mndt+level-1 dt  from dt_range
               connect by level <= (mxdt-mndt+1)    )
select
  max(id) over (order by dt nulls first ) mid ,
   dt,max(stat) over ( order by dt nulls first ) st   from
  system.foo  t , dtset  d
   where  t.fdt(+)=d.dt
;
Re: Replacing a Null column value with the value of the above record [message #386431 is a reply to message #386187] Sat, 14 February 2009 19:13 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member
If you have multiple id values , then we need to tweak the SQL a little bit.

Let us say the data id like this.


SQL> select * from system.foo;

ID FDT STAT
---------- --------- ----------
999 27-SEP-08 200
999 01-OCT-08 300
999 02-OCT-08 400
999 05-NOV-08 820
888 06-JAN-08 1000
777 05-FEB-08 33
777 10-FEB-08 33

7 rows selected.
-------------------------------------------



Then the SQL should be modified as

with
     dt_range as
  ( select id, max(fdt) mxdt , min(fdt)mndt from system.foo group by id)  ,
    dtset as (select  id,mndt+level-1 dt  from dt_range
                   connect by level <= (mxdt-mndt+1)
                          and connect_by_root id= id   )
select
  max(t.id) over (partition by d.id order by dt ) mid ,
   dt,max(stat) over (partition by d.id  order by dt ) st   from
  system.foo  t , dtset  d
   where  t.fdt(+)=d.dt


Srini


Re: Replacing a Null column value with the value of the above record [message #386432 is a reply to message #386187] Sat, 14 February 2009 19:23 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member
If you have overlapping dates for various id values , we need to tweak it a little more.

with
     dt_range as
  ( select id, max(fdt) mxdt , min(fdt)mndt from system.foo group by id)  ,
    dtset as (select  id,mndt+level-1 dt  from dt_range
                   connect by level <= (mxdt-mndt+1)
                          and connect_by_root id= id   )
select
  max(t.id) over (partition by d.id order by dt ) mid ,
   dt,max(stat) over (partition by d.id  order by dt ) st   from
  system.foo  t , dtset  d
   where  t.fdt(+)=d.dt  and t.id(+)=d.id


Srini
Re: Replacing a Null column value with the value of the above record [message #386435 is a reply to message #386432] Sat, 14 February 2009 21:34 Go to previous message
mvmdas
Messages: 6
Registered: February 2009
Junior Member
Thank You Srini and MarcS for the great help. This also made me understand the necessity of learning Analytic Functions in detail.
Previous Topic: 22 to 30% data need to be deleted from very large DWH tables (406,609,065 rows)
Next Topic: Converting duplicate data to one row
Goto Forum:
  


Current Time: Mon Dec 05 13:01:18 CST 2016

Total time taken to generate the page: 0.12446 seconds