OraFAQ Forum: SQL & PL/SQL » help on sql query

Home » SQL & PL/SQL » SQL & PL/SQL » help on sql query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

help on sql query [message #216606]

Mon, 29 January 2007 12:18

jinga
Messages: 116
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

Report message to a moderator

Re: help on sql query [message #216636 is a reply to message #216606]

Mon, 29 January 2007 15:43

Littlefoot
Messages: 21823
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;

Report message to a moderator

Re: help on sql query [message #216665 is a reply to message #216606]

Mon, 29 January 2007 19:28

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

Report message to a moderator

Re: help on sql query [message #216812 is a reply to message #216665]

Tue, 30 January 2007 09:51

jinga
Messages: 116
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

Report message to a moderator

Re: help on sql query [message #216889 is a reply to message #216606]

Tue, 30 January 2007 15:37

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 14 12:46:01 CST 2024