Home » SQL & PL/SQL » SQL & PL/SQL » help on sql query
help on sql query [message #216606] Mon, 29 January 2007 12:18 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
In the table i have the following two records.

  item          mthyr   anum    amonth   amt     pct     fact
100039873	200601	2	04	6	%	1.06
100039873	200601	2	09	5	$	5



I would like to make up null amt, pct,fact for the months inwhich there no record in the table. In other words i have to make up record for all 12 month(amonth).


   item         mthyr   anum   amonth   amt     pct     fact
100039873	200601	2	01	null     null   null 	  
100039873	200601	2	02	null     null   null	  
100039873	200601	2	03	null     null   null	  
100039873	200601	2	04	6	%	1.06	  
100039873	200601	2	05	null     null   null	  
100039873	200601	2	06	null     null   null	  
100039873	200601	2	07	null     null   null	  
100039873	200601	2	08	null     null	null	  
100039873	200601	2	09	5	$	5	  
100039873	200601	2	10	null     null   null	  
100039873	200601	2	11	null     null   null	  
100039873	200601	2	12	null     null	null	  
Re: help on sql query [message #216636 is a reply to message #216606] Mon, 29 January 2007 15:43 Go to previous messageGo to next message
Littlefoot
Messages: 20892
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, this isn't very nice solution, but I hope it returns what you need.
SELECT a.item, a.mthyr, a.anum, a.amonth, a.amt, a.fact
  FROM TEST a
UNION
SELECT x.item, x.mthyr, x.anum, LPAD(x.amonth, 2, '0') amonth, NULL amt, NULL fact
  FROM 
    (SELECT t.item, t.mthyr, t.anum, LEVEL amonth, t.amt, t.fact
     FROM TEST t
     CONNECT BY LEVEL <= 12
    ) x
  WHERE LPAD(x.amonth, 2, '0') NOT IN (SELECT t1.amonth FROM TEST t1)
ORDER BY 4;
Re: help on sql query [message #216665 is a reply to message #216606] Mon, 29 January 2007 19:28 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member



create table foo
(item varchar2(10),
mthyr varchar2(7), anum varchar2(4),
amonth number(2), amt varchar2(4),
pct varchar2(4), fact varchar2(4)
);

begin
insert into foo values
('100039873','200601','2','04','6','%','1.06') ;
insert into foo values
('100039873','200601','2','09','5','%','5.01') ;
end ;



So our data is like this


SQL> select * from foo;

ITEM MTHYR ANUM AMONTH AMT PCT FACT
---------- ------- ---- ---------- ---- ---- ----
100039873 200601 2 4 6 % 1.06
100039873 200601 2 9 5 % 5.01

SQL>
===================

Now, let us trick our model a bit.



Quote:
select item,mthyr,anum,amonth,
coalesce(amt,'null'),
coalesce(pct,'null'),
coalesce(fact,'null')
from (
select item,mthyr,anum,amonth,amt,pct,fact
from
foo
model return all rows
main grr
partition by (item,mthyr,anum )
dimension by (amonth)
measures (amt,pct,fact)
rules sequential order iterate (13)
(
pct[iteration_number]=pct[iteration_number]
)
) where amonth > 0
order by item,mthyr,anum, amonth
SQL> /

ITEM MTHYR ANUM AMONTH COAL COAL COAL
---------- ------- ---- ---------- ---- ---- ----
100039873 200601 2 1 null null null
100039873 200601 2 2 null null null
100039873 200601 2 3 null null null
100039873 200601 2 4 6 % 1.06
100039873 200601 2 5 null null null
100039873 200601 2 6 null null null
100039873 200601 2 7 null null null
100039873 200601 2 8 null null null
100039873 200601 2 9 5 % 5.01
100039873 200601 2 10 null null null
100039873 200601 2 11 null null null
100039873 200601 2 12 null null null

12 rows selected.


======

Only requirement is that amonth be a number and that the year should have only 12 months.

If amonth is not a number datatype use to_number on amonth in the model query and to_char in the outer query.


Srini

Re: help on sql query [message #216812 is a reply to message #216665] Tue, 30 January 2007 09:51 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member

Srinivas,

i should have mentioned i am using 9.2(looks like we better move to 10g because most of my queries can be written in a better way in 10g!!!!!!!!!). So i can not use your query.

If you think of a way in 9i, let me know.

Anu
Re: help on sql query [message #216889 is a reply to message #216606] Tue, 30 January 2007 15:37 Go to previous message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member




Quote:
with m12 as (select ITEM,MTHYR,ANUM,lv
from (
select
ITEM,MTHYR,ANUM,lv,
row_number() over (partition by
ITEM,MTHYR,ANUM,lv
order by ITEM,MTHYR,ANUM, amonth ) rn from foo,
(select level lv from dual connect by level <13)
) where rn =1)
select m12.item,m12.mthyr,m12.anum,lv,
coalesce( amt,'null'), coalesce(pct,'null'),coalesce(fact,'null' )
from m12 , foo
where foo.amonth(+)=m12.lv
order by m12.item,m12.mthyr,m12.anum,lv
SQL> /

ITEM MTHYR ANUM LV COAL COAL COAL
---------- ------- ---- ---------- ---- ---- ----
100039873 200601 2 1 null null null
100039873 200601 2 2 null null null
100039873 200601 2 3 null null null
100039873 200601 2 4 6 % 1.06
100039873 200601 2 5 null null null
100039873 200601 2 6 null null null
100039873 200601 2 7 null null null
100039873 200601 2 8 null null null
100039873 200601 2 9 5 % 5.01
100039873 200601 2 10 null null null
100039873 200601 2 11 null null null
100039873 200601 2 12 null null null

12 rows selected.



Srini

Previous Topic: Index column name- where is it stored?
Next Topic: How can i insert or search for single quotes( ' )?
Goto Forum:
  


Current Time: Mon Dec 05 02:50:39 CST 2016

Total time taken to generate the page: 0.08116 seconds