Home » SQL & PL/SQL » SQL & PL/SQL » Fill the Blank Values with Data (merged 3)
Fill the Blank Values with Data (merged 3) [message #406077] Tue, 02 June 2009 02:01 Go to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
TABLE1


PT	STATUS	        ID	   EVENT     VIS_ID	DOC_NUM	      PAGE_NUM	    TS
-----------------------------------------------------------------------------------------------------
AA10	PASS 2 COMPLETE	11	SCREENING	0	SS60506540	P001	10/23/2007 11:18:31 AM
AA10	PASS 2 COMPLETE	11	SCREENING	0	SS67018040	P002	10/23/2007 11:18:31 AM
AA10	PASS 2 COMPLETE	11	SCREENING	0	SS61469140	P003	10/23/2007 11:18:16 AM
AA10	PASS 2 COMPLETE	11	SCREENING	0	SS61469240	P004	10/23/2007 11:18:16 AM
AA10	PASS 2 COMPLETE	11	SCREENING	0	SS60507140	P005	10/23/2007 11:18:28 AM
AA10	PASS 2 COMPLETE	11	SCREENING	0	SS60507440	P006	10/23/2007 11:18:28 AM
AA10	PASS 2 COMPLETE	11	SCREENING	0	SS60507640	P007	10/23/2007 11:17:33 AM
AA10	PASS 2 COMPLETE	11	SCREENING	0	SS60507840	P008	10/23/2007 11:17:33 AM
AA10	PASS 2 COMPLETE	11	SCREENING	0	SS60508040	P009	6/10/2008 7:44:35 AM
AA10	PASS 2 COMPLETE	11	SCREENING	0	SS60508240	P010	6/21/2008 5:10:36 AM
AA10	PASS 2 COMPLETE	11	SCREENING	0	SS60508440	P011	6/10/2008 7:44:10 AM
AA10	PASS 2 COMPLETE	11	SCREENING	0	SS60508540	P012	6/21/2008 5:09:58 AM
AA10	PASS 2 COMPLETE	11	SCREENING	0	SS60508740	P013	11/27/2007 9:09:30 AM
AA10	PASS 2 COMPLETE	11	VISIT 0	        1	SS61214940	P014	11/27/2007 9:09:24 AM
AA10	PASS 2 COMPLETE	11	VISIT 0	        1	SS61215540	P015	11/27/2007 9:12:38 AM
AA10	PASS 2 COMPLETE	11	VISIT 0	        1	SS61218440	P016	11/27/2007 9:12:26 AM
AA10	PASS 2 COMPLETE	11	VISIT 1	        2	SS61218840	P017	10/23/2007 11:19:14 AM


TABLE2

STUDY	DOCNUM	         PAT	CPEVENT	       VISIT	PG_NUM	DATE
-------------------------------------------------------------------------
ABC	SS60506540	AA10	SCREENING	0	P001	16MAY2007
ABC	SS61214940	AA10	VISIT 0	        1	P014	06JUN2007
ABC	SS61218840	AA10	VISIT 1	        2 	P017	19JUN2007
ABC	SS61218841	AA11	SCREENING	0	P001	23MAY2007
ABC	SS61218842	AA11	VISIT 0	        1	P014	06JUN2007
ABC	SS61218843	AA11	VISIT 1	        2	P017	19JUN2007
ABC	SS61218844	AA12	SCREENING	0	P001	30AUG2007
ABC	SS61218845	AA12	VISIT 0	        1	P014	16MAY2007
ABC	SS61218846	AA12	VISIT 1	        2	P017	30AUG2007



The below ouput is the Outer Join Result


SELECT distinct B.PAT, A.PT, A.PAGE_NUM PNO, A.VIS_ID, 
B.CPEVENT "VISIT_NAME", B.DATE "VISIT_DATE", 
to_char(A.TS,'DDMONYYYY') "P_DATE"
FROM TABLE1,TABLE2 b
where A.ID = 111440
and   B.DOCNUM (+) = A.DOC_NUM
and   A.STATUS = 'PASS 2 COMPLETE'
order by A.VIS_ID;

PAT	PT	PAGE_NUM VIS_ID VISIT_NAME	VISIT_DATE	P_DATE
--------------------------------------------------------------------------------------
AA10	AA10	P001	0	 SCREENING	16MAY2007	23OCT2007
	AA10	P002	0			10JUN2008
	AA10	P002	0			21JUN2008
	AA10	P003	0			27NOV2007
	AA10	P004	0			27NOV2007
	AA10	P005	0			23OCT2007
	AA10	P006	0			23OCT2007
	AA10	P007	0			23OCT2007
	AA10	P008	0			23OCT2007
	AA10	P009	0			23OCT2007
	AA10	P010	0			23OCT2007
	AA10	P011	0			23OCT2007
	AA10	P012	0			23OCT2007
	AA10	P013	0			23OCT2007
AA11	AA11	P001	0	 SCREENING	23MAY2007	23OCT2007
	AA11	P002	0			24JUL2008
	AA11	P003	0			27NOV2007
	AA11	P004	0			24OCT2007
	AA11	P005	0			23OCT2007
	AA11	P006	0			23OCT2007
	AA11	P006	0			24OCT2007
	AA11	P007	0			24OCT2007
	AA11	P008	0			24OCT2007
	AA11	P009	0			24OCT2007
	AA11	P010	0			24OCT2007
	AA11	P011	0			24OCT2007
	AA11	P012	0			24OCT2007
	AA11	P013	0			24OCT2007
AA12	AA12	P001	0	 SCREENING	30AUG2007	27NOV2007
	AA12	P002	0			27NOV2007
	AA12	P003	0			27NOV2007
	AA12	P004	0			27NOV2007
	AA12	P005	0			27NOV2007
	AA12	P006	0			27NOV2007
	AA12	P007	0			27NOV2007
	AA12	P008	0			27NOV2007
	AA12	P009	0			27NOV2007
	AA12	P010	0			15FEB2008
	AA12	P011	0			27NOV2007
	AA12	P012	0			27NOV2007
	AA12	P013	0			27NOV2007





Please refer the Table1 and Table2 data structure for reference and also the OUTER JOIN output. My requirement is to fill the blank values with the data as shown in the desired output below. That is Pat,Visit_Date, Visit_name values should be the same to the corresponding Visit_number.

Desired output should be like this:

PAT	PT   PAGE_NUM VISIT_ID	VISIT_NAME	VISIT_DATE	P_DATE
--------------------------------------------------------------------------
AA10	AA10	P001	0	SCREENING	16-May-07	23OCT2007
AA10	AA10	P002	0	SCREENING	16-May-07	10JUN2008
AA10	AA10	P002	0	SCREENING	16-May-07	21JUN2008
AA10	AA10	P003	0	SCREENING	16-May-07	27NOV2007
AA10	AA10	P004	0	SCREENING	16-May-07	27NOV2007
AA10	AA10	P005	0	SCREENING	16-May-07	23OCT2007
AA10	AA10	P006	0	SCREENING	16-May-07	23OCT2007
AA10	AA10	P007	0	SCREENING	16-May-07	23OCT2007
AA10	AA10	P008	0	SCREENING	16-May-07	23OCT2007
AA10	AA10	P009	0	SCREENING	16-May-07	23OCT2007
AA10	AA10	P010	0	SCREENING	16-May-07	23OCT2007
AA10	AA10	P011	0	SCREENING	16-May-07	23OCT2007
AA10	AA10	P012	0	SCREENING	16-May-07	23OCT2007
AA10	AA10	P013	0	SCREENING	16-May-07	23OCT2007
AA11	AA11	P001	0	SCREENING	23-May-07	23OCT2007
AA11	AA11	P002	0	SCREENING	23-May-07	24JUL2008
AA11	AA11	P003	0	SCREENING	23-May-07	27NOV2007
AA11	AA11	P004	0	SCREENING	23-May-07	24OCT2007
AA11	AA11	P005	0	SCREENING	23-May-07	23OCT2007
AA11	AA11	P006	0	SCREENING	23-May-07	23OCT2007
AA11	AA11	P006	0	SCREENING	23-May-07	24OCT2007
AA11	AA11	P007	0	SCREENING	23-May-07	24OCT2007
AA11	AA11	P008	0	SCREENING	23-May-07	24OCT2007
AA11	AA11	P009	0	SCREENING	23-May-07	24OCT2007
AA11	AA11	P010	0	SCREENING	23-May-07	24OCT2007
AA11	AA11	P011	0	SCREENING	23-May-07	24OCT2007
AA11	AA11	P012	0	SCREENING	23-May-07	24OCT2007
AA11	AA11	P013	0	SCREENING	23-May-07	24OCT2007
AA12	AA12	P001	0	SCREENING	30-Aug-07	27NOV2007
AA12	AA12	P002	0	SCREENING	30-Aug-07	27NOV2007
AA12	AA12	P003	0	SCREENING	30-Aug-07	27NOV2007
AA12	AA12	P004	0	SCREENING	30-Aug-07	27NOV2007
AA12	AA12	P005	0	SCREENING	30-Aug-07	27NOV2007
AA12	AA12	P006	0	SCREENING	30-Aug-07	27NOV2007
AA12	AA12	P007	0	SCREENING	30-Aug-07	27NOV2007
AA12	AA12	P008	0	SCREENING	30-Aug-07	27NOV2007
AA12	AA12	P009	0	SCREENING	30-Aug-07	27NOV2007
AA12	AA12	P010	0	SCREENING	30-Aug-07	15FEB2008
AA12	AA12	P011	0	SCREENING	30-Aug-07	27NOV2007
AA12	AA12	P012	0	SCREENING	30-Aug-07	27NOV2007
AA12	AA12	P013	0	SCREENING	30-Aug-07	27NOV2007


Please suggest me how should I achieve this?. Thanks

[Updated on: Tue, 02 June 2009 02:06] by Moderator

Report message to a moderator

Re: Fill the Blank Values with Data [message #406078 is a reply to message #406077] Tue, 02 June 2009 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
My requirement is to fill the blank values with the data as shown in the desired output below

This is not a specification. What is the rule?

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

Also always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Tue, 02 June 2009 02:08]

Report message to a moderator

Re: Fill the Blank Values with Data [message #406121 is a reply to message #406078] Tue, 02 June 2009 06:15 Go to previous messageGo to next message
elaiyavel
Messages: 114
Registered: April 2008
Senior Member
Try withe nvl's
Re: Fill the Blank Values with Data [message #406122 is a reply to message #406121] Tue, 02 June 2009 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
elaiyavel wrote on Tue, 02 June 2009 13:15
Try withe nvl's


./fa/449/0/

Re: Fill the Blank Values with Data [message #406285 is a reply to message #406122] Wed, 03 June 2009 02:49 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member

PAT	PT	PAGE_NUM	VISIT_ID	VISIT_NAME	VISIT_DATE	P_DATE
------------------------------------------------------------------------------------------
AA10	AA10	P001	               0	SCREENING	16-May-07	23OCT2007
AA10	AA10	P002	               0			                10JUN2008
AA10	AA10	P003	               0			                27NOV2007
AA11	AA11	P004	               1	VISIT 1 	23-May-07	23OCT2007
AA11	AA11	P005	               1			                24JUL2008
AA11	AA11	P006	               1			                27NOV2007
AA12	AA12	P007	               2	VISIT 2 	30-Aug-07	27NOV2007
AA12	AA12	P008	               2			                27NOV2007
AA12	AA12	P009	               2			                27NOV2007




I tried with NVL but not able to get the desired output.

My requirement is to fill the visit_date and visit_name in the blank values according to the visit_id.

That is SCREENING, 16-May-07 should be reflected for PAGE_NUM P002 AND P003 respectively of visit_id 0.


VISIT 1, 23-May-07 should be reflected for PAGE_NUM P002 AND P003 respectively of visit_id 1.


VISIT 2, 30-Aug-07 should be reflected for PAGE_NUM P002 AND P003 respectively of visit_id 2.


How do i achieve this. Please help.
Re: Fill the Blank Values with Data [message #406287 is a reply to message #406285] Wed, 03 June 2009 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How do i achieve this. Please help.

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

You need to help us help you.

Regards
Michel
Re: Fill the Blank Values with Data [message #406304 is a reply to message #406285] Wed, 03 June 2009 04:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to look at the LAST_VALUE analytic function

Here's an example of how to use it:
create table test_211 (col_1 number, col_2 number,col_3 number);

insert into test_211 values (1,1,4);
insert into test_211 values (1,2,null);
insert into test_211 values (1,3,null);
insert into test_211 values (1,4,null);
insert into test_211 values (1,5,5);
insert into test_211 values (1,6,null);
insert into test_211 values (2,1,2);
insert into test_211 values (2,2,null);
insert into test_211 values (2,3,7);
insert into test_211 values (2,4,null);

select col_1,col_2,col_3,last_value(col_3 ignore nulls) over (partition by col_1  order by col_2)
from   test_211;
Re: Fill the Blank Values with Data [message #406322 is a reply to message #406304] Wed, 03 June 2009 05:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As far I remember, "ignore nulls" does not exist in 9i.

Regards
Michel
Re: Fill the Blank Values with Data [message #406330 is a reply to message #406322] Wed, 03 June 2009 06:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Good spot.

Try this instead:
select col_1
      ,col_2
      ,col_3
      ,max(col_3) over (partition by col_1 order by col_2 rows between unbounded preceding and current row)
from   test_211;
icon7.gif  Re: Fill the Blank Values with Data [message #406546 is a reply to message #406330] Thu, 04 June 2009 09:20 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Thanks a lot JRowbottom Smile but how to populate or fill the data for the null values in the below mentioned output? In this case since 7 is the last value in the col_3, i need value 7 to populate for the rest of the rows. How do i achieve this. Thanks in advance

create table test_211 (col_1 number, col_2 number,col_3 number);

insert into test_211 values (1,1,4);
insert into test_211 values (1,2,null);
insert into test_211 values (1,3,null);
insert into test_211 values (1,4,null);
insert into test_211 values (1,5,5);
insert into test_211 values (1,6,null);
insert into test_211 values (2,1,2);
insert into test_211 values (2,2,null);
insert into test_211 values (2,3,7);
insert into test_211 values (2,4,null);
insert into test_211 values (3,5,null);
insert into test_211 values (3,6,null);
insert into test_211 values (4,1,null);
insert into test_211 values (4,2,null);
insert into test_211 values (4,3,null);

OUTPUT
select col_1 ,col_2, col_3,
max(col_3) over (partition by col_1 order by col_2 rows between unbounded preceding and current row) result
from test_211;

    COL_1      COL_2      COL_3     RESULT
--------- ---------- ---------- ----------
        1          1          4          4
        1          2                     4
        1          3                     4
        1          4                     4
        1          5          5          5
        1          6                     5
        2          1          2          2
        2          2                     2
        2          3          7          7
        2          4                     7
        3          5
        3          6
        4          1
        4          2
        4          3


Re: Fill the Blank Values with Data [message #406551 is a reply to message #406546] Thu, 04 June 2009 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to read, learn and understand how analytic function works and not wait for someone to produce the answer.
The solution is there, you just have to modify the analytic clause.

Regards
Michel
Re: Fill the Blank Values with Data [message #406804 is a reply to message #406546] Sat, 06 June 2009 00:09 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
The below query i tried to populate value 7 for the rest of the rows. I am getting desired output. How can i still advance the query? Please help me thanks in advance

select col_1 ,col_2, col_3,
nvl(max(col_3) over (partition by col_1 order by  col_2  rows between unbounded preceding and current row),
(select max(result) from (select max(col_3) over (partition by col_1 order by col_2  rows between unbounded
preceding and current row) result
from test_211;

Re: Fill the Blank Values with Data [message #406813 is a reply to message #406804] Sat, 06 June 2009 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL Formatter and repost it in a pretty way.

Regards
Michel
Retrieve last non-blank value in the column [message #406873 is a reply to message #406077] Sun, 07 June 2009 00:52 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
I need SQL Function or Query wherein it will retrieve the last non-blank value in the column. In the below mentioned case, I need to retrieve value 7. How to achieve it? Thanks in advance

create table test_211 (col_1 number, col_2 number,col_3 number);

insert into test_211 values (1,1,4);
insert into test_211 values (1,2,null);
insert into test_211 values (1,3,null);
insert into test_211 values (1,4,null);
insert into test_211 values (1,5,5);
insert into test_211 values (1,6,null);
insert into test_211 values (2,1,2);
insert into test_211 values (2,2,null);
insert into test_211 values (2,3,7);
insert into test_211 values (2,4,null);
insert into test_211 values (3,5,null);
insert into test_211 values (3,6,null);
insert into test_211 values (4,1,null);
insert into test_211 values (4,2,null);
insert into test_211 values (4,3,null);

OUTPUT
select col_1 ,col_2, col_3,
max(col_3) over (partition by col_1 order by col_2 rows between unbounded preceding and current row) result
from test_211;

    COL_1      COL_2      COL_3     RESULT
--------- ---------- ---------- ----------
        1          1          4          4
        1          2                     4
        1          3                     4
        1          4                     4
        1          5          5          5
        1          6                     5
        2          1          2          2
        2          2                     2
        2          3          7          7
        2          4                     7
        3          5
        3          6
        4          1
        4          2
        4          3






Re: Retrieve last non-blank value in the column [message #406886 is a reply to message #406873] Sun, 07 June 2009 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is useless to repeat the same question in another topic, it will be merge with the other one.

Regards
Michel
Re: Retrieve last non-blank value in the column [message #406887 is a reply to message #406873] Sun, 07 June 2009 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain why did you use the following clause?
"(partition by col_1 order by col_2 rows between unbounded preceding and current row)"

Regards
Michel
Re: Retrieve last non-blank value in the column [message #406948 is a reply to message #406873] Sun, 07 June 2009 23:47 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
I need the query or function at the earliest, can anyone please give me the same. Thanks in advance.
Re: Fill the Blank Values with Data (merged) [message #406949 is a reply to message #406077] Sun, 07 June 2009 23:55 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I need the query or function at the earliest
What happens when your boss realizes you are depending upon the kindness of strangers to solve his problems?
Re: Retrieve last non-blank value in the column [message #406958 is a reply to message #406948] Mon, 08 June 2009 00:22 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
ygsunilkumar wrote on Mon, 08 June 2009 06:47
I need the query or function at the earliest, can anyone please give me the same. Thanks in advance.

And at the second? Will you try to understand it?
What about understanding the current query at first (especially the meaning of PARTITION BY and ORDER BY clauses) and then accomodate it to your needs?

By the way, it is not clear to me, what shall be the result in the place of the question marks (?) in this case:
    COL_1      COL_2      COL_3     RESULT
--------- ---------- ---------- ----------
        2          1          2          2
        2          2                     2
        2          3          7          7
        2          4                     7
        4          1                     ?
        4          2          9          ?
        4          3                     ?
Re: Retrieve last non-blank value in the column [message #406961 is a reply to message #406958] Mon, 08 June 2009 00:27 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Quote:


By the way, it is not clear to me, what shall be the result in the place of the question marks (?) in this case:


In place of the question marks (?) i need the value of 9 to be populated.

So i need the query or function which will identify the value 9 so that using nvl i would make reflect the same value in place of question marks (?)

[Updated on: Mon, 08 June 2009 00:29]

Report message to a moderator

Re: Retrieve last non-blank value in the column [message #406965 is a reply to message #406961] Mon, 08 June 2009 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Answer the question flyboy and I asked:
Quote:
Explain why did you use the following clause?
"(partition by col_1 order by col_2 rows between unbounded preceding and current row)"

or is this just what I said:
Quote:
You have to read, learn and understand how analytic function works and not wait for someone to produce the answer.
The solution is there, you just have to modify the analytic clause.


Regards
Michel
Populating the last non blank value [message #407032 is a reply to message #406077] Mon, 08 June 2009 04:00 Go to previous messageGo to next message
sharmakranthi
Messages: 14
Registered: February 2009
Location: Bangalore
Junior Member
As per the below query, I got the output as mentioned below.

select col_1 ,col_2, col_3,
max(col_3) over (partition by col_1 order by col_2 rows between unbounded preceding and current row) result
from test_211;


Row #	Product	Order Date
1	ABC	5-Jun-09
2	ABC	5-Jun-09
3	ABC	5-Jun-09
4	ABC	5-Jun-09
5	XYZ	6-Jun-09
6	XYZ	6-Jun-09
7	XYZ	6-Jun-09
8	XYZ	6-Jun-09
9	XYZ	6-Jun-09
10	PQR	7-Jun-09
11	PQR	
12	PQR	
13	PQR	


I tried in many ways to overcome the above issue, where it need to populate the 7-Jun-09 from Row # 11-13.
[Note: PQR is the last product which is purchased in this test case.I.e Row# 13 is the last row in the above table wherein the data is not populating as per the above query.

Kindly help me on this.

Thanks in advance,

Regards,
Kranthi
Re: Populating the last non blank value [message #407034 is a reply to message #407032] Mon, 08 June 2009 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same answer than to your colleague ygsunilkumar as it is the same question and both topics will be merged.

Regards
Michel
Re: Retrieve last non-blank value in the column [message #407046 is a reply to message #406873] Mon, 08 June 2009 04:38 Go to previous messageGo to next message
karthick_arp
Messages: 13
Registered: February 2006
Location: hyderabad
Junior Member
solution using MODEL clause


SQL> select col_1, col_2, col_3
  2    from (select row_number() over(order by col_1, col_2) rno,
  3            col_1, col_2, col_3
  4       from test_211)
  5    model
  6    return updated rows
  7    dimension by (rno)
  8    measures(col_1,col_2,col_3)
  9    rules update
 10    (
 11     col_3[any]= case when nvl(col_3[cv()],0) = 0 then nvl(col_3[cv()-1],0) else col_3[cv()] end
 12    )
 13  /

     COL_1      COL_2      COL_3
---------- ---------- ----------
         1          1          4
         1          2          4
         1          3          4
         1          4          4
         1          5          5
         1          6          5
         2          1          2
         2          2          2
         2          3          7
         2          4          7
         3          5          7

     COL_1      COL_2      COL_3
---------- ---------- ----------
         3          6          7
         4          1          7
         4          2          7
         4          3          7

15 rows selected.
Re: Retrieve last non-blank value in the column [message #407122 is a reply to message #407046] Mon, 08 June 2009 09:45 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Hi.. Thanks you so much for your query but i am getting below error. Please help

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> select col_1, col_2, col_3
  2    from (select row_number() over(order by col_1, col_2) rno,
  3            col_1, col_2, col_3
  4       from test_211)
  5    model
  6    return updated rows
  7    dimension by (rno)
  8    measures(col_1,col_2,col_3)
  9    rules update
 10    (
 11     col_3[any]= case when nvl(col_3[cv()],0) = 0 then nvl col_3[cv()-1],0) else col_3[cv()] end
 12    )
 13  /

   return updated rows
   *
ERROR at line 6:
ORA-00933: SQL command not properly ended



Re: Retrieve last non-blank value in the column [message #407123 is a reply to message #407122] Mon, 08 June 2009 09:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Too bad you didn't mention your version as specified in OraFAQ Forum Guide.

Regards
Michel
Re: Retrieve last non-blank value in the column [message #407163 is a reply to message #407123] Mon, 08 June 2009 20:03 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Michel, whenever I post a topic I would meniton the Version Number, if you notice that three posts are merged into one topic, i guess because of this, version missed out.

How do I write the same query in Oracle 9i because MODEL clause is the new feauture introduced from the 10g it won't work in Oracle 9i.

Please help me. Thanks
Re: Fill the Blank Values with Data (merged 3) [message #407178 is a reply to message #406077] Mon, 08 June 2009 23:50 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
> it won't work in Oracle 9i.
9i is obsoleted & has not been supported this century.
Perhaps it is time for you to join the 21st century.

V8.1.6 was the 1st version to support sending email.
At the time I was supporting a Production application based upon V7.3.4.5.
So I installed V8.1.6.
From V7.3.4.5 I could call a custom procedure in V8.1.6 by passing
an email message in a VARCHAR2(32000) variable.
This allowed me to send email from V7.3.4.5 application.

From V9 you could call a procedure to do as needed.
Have At It!
Re: Fill the Blank Values with Data (merged 3) [message #407220 is a reply to message #407178] Tue, 09 June 2009 03:06 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Does this works in your Oracle 9i?


SCOTT@XE> ed
Wrote file afiedt.buf

  1  select  col_1,
  2        col_2,
  3        col_3,
  4         max(col_3) over (partition by groups ) col_4
  5      from (
  6         select
  7               'set'||sum(col_3) over(order by col_1, col_2) groups,
  8                col_1,
  9                col_2,
 10                     col_3
 11              from     test_211
 12     )
 13* order by col_1,col_2
SCOTT@XE> /

     COL_1      COL_2      COL_3      COL_4
---------- ---------- ---------- ----------
         1          1          4          4
         1          2                     4
         1          3                     4
         1          4                     4
         1          5          5          5
         1          6                     5
         2          1          2          2
         2          2                     2
         2          3          7          7
         2          4                     7
         3          5                     7

     COL_1      COL_2      COL_3      COL_4
---------- ---------- ---------- ----------
         3          6                     7
         4          1                     7
         4          2                     7
         4          3                     7

15 rows selected.

SCOTT@XE>



You may have to test it thoroughly as I guess there must be some flaw in the technique which I am unable to see it as of now.

[Update] 2 flaws in the logic

Negative numbers:

SCOTT@XE> update test_211 set col_3 = -9 where col_1 = 3 and col_2 = 6
  2  /

1 row updated.

SCOTT@XE> commit;

Commit complete.


SCOTT@XE> ed
Wrote file afiedt.buf

  1    select  col_1,
  2            col_2,
  3            col_3,
  4            max(col_3) over (partition by groups ) col_4
  5          from (
  6             select
  7                   'set'||sum(col_3) over(order by col_1, col_2) groups,
  8                    col_1,
  9                    col_2,
 10                        col_3
 11                 from     test_211
 12        )
 13*  order by col_1,col_2
SCOTT@XE> /

     COL_1      COL_2      COL_3      COL_4
---------- ---------- ---------- ----------
         1          1          4          4
         1          2                     4
         1          3                     4
         1          4                     4
         1          5          5          5
         1          6                     5
         2          1          2          2
         2          2                     2
         2          3          7          7
         2          4                     7
         3          5                     7

     COL_1      COL_2      COL_3      COL_4
---------- ---------- ---------- ----------
         3          6         -9          5 <= Incorrect value
         4          1                     5 <= Incorrect value
         4          2                     5 <= Incorrect value
         4          3                     5 <= Incorrect value

15 rows selected.

SCOTT@XE> -- Now  this can be solved using absolute function

SCOTT@XE>

SCOTT@XE> ed
Wrote file afiedt.buf

  1    select  col_1,
  2            col_2,
  3            col_3,
  4            max(col_3) over (partition by groups ) col_4
  5          from (
  6             select
  7                   'set'||sum(abs(col_3)) over(order by col_1, col_2) groups,

  8                    col_1,
  9                    col_2,
 10                        col_3
 11                 from     test_211
 12        )
 13*  order by col_1,col_2
SCOTT@XE> /

     COL_1      COL_2      COL_3      COL_4
---------- ---------- ---------- ----------
         1          1          4          4
         1          2                     4
         1          3                     4
         1          4                     4
         1          5          5          5
         1          6                     5
         2          1          2          2
         2          2                     2
         2          3          7          7
         2          4                     7
         3          5                     7

     COL_1      COL_2      COL_3      COL_4
---------- ---------- ---------- ----------
         3          6         -9         -9
         4          1                    -9
         4          2                    -9
         4          3                    -9

15 rows selected.

SCOTT@XE>




Zero Value:

SCOTT@XE> update test_211 set col_3 = 0 where col_1 = 3 and col_2 = 6;

1 row updated.

SCOTT@XE> commit;

Commit complete.

SCOTT@XE> ed
Wrote file afiedt.buf

  1    select  col_1,
  2            col_2,
  3            col_3,
  4            max(col_3) over (partition by groups ) col_4
  5          from (
  6             select
  7                   'set'||sum(abs(col_3)) over(order by col_1, col_2) groups,

  8                    col_1,
  9                    col_2,
 10                        col_3
 11                 from     test_211
 12        )
 13*  order by col_1,col_2
SCOTT@XE> /

     COL_1      COL_2      COL_3      COL_4
---------- ---------- ---------- ----------
         1          1          4          4
         1          2                     4
         1          3                     4
         1          4                     4
         1          5          5          5
         1          6                     5
         2          1          2          2
         2          2                     2
         2          3          7          7
         2          4                     7
         3          5                     7

     COL_1      COL_2      COL_3      COL_4
---------- ---------- ---------- ----------
         3          6          0          7 <= Incorrect value
         4          1                     7 <= Incorrect value
         4          2                     7 <= Incorrect value
         4          3                     7 <= Incorrect value
15 rows selected.

SCOTT@XE> -- This 0 value condition I am unable to find any solution as of  now



[Updated on: Tue, 09 June 2009 03:50]

Report message to a moderator

Re: Fill the Blank Values with Data (merged 3) [message #407238 is a reply to message #407220] Tue, 09 June 2009 04:14 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Due to some error,I am unable to edit my previous reply.

Anway The zero value condition could be solved as under.



SCOTT@XE> ed
Wrote file afiedt.buf

  1      select  col_1,
  2         col_2,
  3         col_3,
  4              max(col_3) over (partition by groups ) col_4
  5              from (
  6                 select
  7                       'set'||sum(abs(case when col_3 = 0 then 1 else col_3 e
nd)) over(order by col_1, col_2) groups,
  8                       col_1,
  9                        col_2,
 10                 col_3
 11               from     test_211
 12     )
 13* order by col_1,col_2
SCOTT@XE> /

     COL_1      COL_2      COL_3      COL_4
---------- ---------- ---------- ----------
         1          1          4          4
         1          2                     4
         1          3                     4
         1          4                     4
         1          5          5          5
         1          6                     5
         2          1          2          2
         2          2                     2
         2          3          7          7
         2          4                     7
         3          5                     7

     COL_1      COL_2      COL_3      COL_4
---------- ---------- ---------- ----------
         3          6          0          0
         4          1                     0
         4          2                     0
         4          3                     0

15 rows selected.

SCOTT@XE>



As mentioned you may have to test it thoroughly, you may find any other flaws as well.
Re: Fill the Blank Values with Data (merged 3) [message #407355 is a reply to message #407238] Tue, 09 June 2009 20:23 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Thank you very much bonker Smile . I got the output as desired.
Re: Fill the Blank Values with Data (merged 3) [message #407364 is a reply to message #407355] Tue, 09 June 2009 23:10 Go to previous messageGo to next message
karthick_arp
Messages: 13
Registered: February 2006
Location: hyderabad
Junior Member
SQL> select col_1, col_2, col_3, last_value(col_3 ignore nulls) over(order by col_1, col_2) col_3_new
  2    from test_211
  3  /

     COL_1      COL_2      COL_3  COL_3_NEW
---------- ---------- ---------- ----------
         1          1          4          4
         1          2                     4
         1          3                     4
         1          4                     4
         1          5          5          5
         1          6                     5
         2          1          2          2
         2          2                     2
         2          3          7          7
         2          4                     7
         3          5                     7

     COL_1      COL_2      COL_3  COL_3_NEW
---------- ---------- ---------- ----------
         3          6                     7
         4          1                     7
         4          2                     7
         4          3                     7

15 rows selected.
Re: Fill the Blank Values with Data (merged 3) [message #407378 is a reply to message #407364] Wed, 10 June 2009 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 03 June 2009 12:34
As far I remember, "ignore nulls" does not exist in 9i.

Regards
Michel


Please read the whole topic before repeating what has already been said and rejected.

Regards
Michel
Re: Fill the Blank Values with Data (merged 3) [message #407396 is a reply to message #407378] Wed, 10 June 2009 01:39 Go to previous message
bonker
Messages: 402
Registered: July 2005
Senior Member
@ygsunilkumar

I think we can avoid all those zero and negative condition by using row_number() to identify the groups like this.


SCOTT@XE> select col_1,col_2,col_3,max(col_3) over (partition by grpset) col_4 f
rom
  2  (
  3  select col_1,col_2,col_3, sum(groups) over(order by col_1,col_2) grpset,gro
ups
  4  from(
  5   select
  6                       case when col_3 is not null then row_number() over (or
der by col_1,col_2) end groups,
  7                       col_1,
  8                        col_2,
  9                 col_3
 10               from     test_211
 11  )
 12  )
 13  /

     COL_1      COL_2      COL_3      COL_4
---------- ---------- ---------- ----------
         1          1          4          4
         1          2                     4
         1          3                     4
         1          4                     4
         1          5          5          5
         1          6                     5
         2          1          2          2
         2          2                     2
         2          3          7          7
         2          4                     7
         3          5                     7

     COL_1      COL_2      COL_3      COL_4
---------- ---------- ---------- ----------
         3          6                     7
         4          1                     7
         4          2                     7
         4          3                     7

15 rows selected.

SCOTT@XE>

SCOTT@XE> update test_211 set col_3 = -9 where col_1 = 3 and col_2 = 6;

1 row updated.

SCOTT@XE> commit;

Commit complete.

SCOTT@XE>

SCOTT@XE> select col_1,col_2,col_3,max(col_3) over (partition by grpset) col_4 f
rom
  2  (
  3  select col_1,col_2,col_3, sum(groups) over(order by col_1,col_2) grpset,gro
ups
  4  from(
  5   select
  6                       case when col_3 is not null then row_number() over (or
der by col_1,col_2) end groups,
  7                       col_1,
  8                        col_2,
  9                 col_3
 10               from     test_211
 11  )
 12  )
 13  /

     COL_1      COL_2      COL_3      COL_4
---------- ---------- ---------- ----------
         1          1          4          4
         1          2                     4
         1          3                     4
         1          4                     4
         1          5          5          5
         1          6                     5
         2          1          2          2
         2          2                     2
         2          3          7          7
         2          4                     7
         3          5                     7

     COL_1      COL_2      COL_3      COL_4
---------- ---------- ---------- ----------
         3          6         -9         -9
         4          1                    -9
         4          2                    -9
         4          3                    -9

15 rows selected.

SCOTT@XE> update test_211 set col_3 = 0 where col_1 = 3 and col_2 = 6;

1 row updated.

SCOTT@XE> commit;

Commit complete.

SCOTT@XE> select col_1,col_2,col_3,max(col_3) over (partition by grpset) col_4 f
rom
  2  (
  3  select col_1,col_2,col_3, sum(groups) over(order by col_1,col_2) grpset,gro
ups
  4  from(
  5   select
  6                       case when col_3 is not null then row_number() over (or
der by col_1,col_2) end groups,
  7                       col_1,
  8                        col_2,
  9                 col_3
 10               from     test_211
 11  )
 12  )
 13  /

     COL_1      COL_2      COL_3      COL_4
---------- ---------- ---------- ----------
         1          1          4          4
         1          2                     4
         1          3                     4
         1          4                     4
         1          5          5          5
         1          6                     5
         2          1          2          2
         2          2                     2
         2          3          7          7
         2          4                     7
         3          5                     7

     COL_1      COL_2      COL_3      COL_4
---------- ---------- ---------- ----------
         3          6          0          0
         4          1                     0
         4          2                     0
         4          3                     0

15 rows selected.

SCOTT@XE>



Previous Topic: snapshot too old: rollback segment number 6 with name "_SYSSMU6$" too small
Next Topic: how to add same account number debit n credits
Goto Forum:
  


Current Time: Sat Dec 10 13:06:42 CST 2016

Total time taken to generate the page: 0.10820 seconds