| write a oracle query [message #612037] |
Fri, 11 April 2014 05:29  |
 |
9390512774
Messages: 103
Registered: January 2011
Location: hyd
|
Senior Member |
|
|
Hi Experts,
I have a table abc. it has a column with the data like below and data it contian is either 1 or 2 in that column.
ex:
col1
1
1
1
1
2
2
1
2
1
2
1
1
2
2
I want a query to dispay the data like below thru my query
col1 need_to_display
1 1
1 1
1 1
1 1
2 2
2 3
1 3
2 4
1 4
2 5
1 5
1 5
2 6
2 7
i.e. whenever it finds data value as 2 in col1 then it has to increase the value by 1 to the previous display value. if the col1 value is 1 then it will continue the same value which displayed in previous row.
THANKS IN ADVANCE!!
|
|
|
|
| Re: write a oracle query [message #612038 is a reply to message #612037] |
Fri, 11 April 2014 06:09  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Siva, I have glanced through your recent posts, and I notice that you have NEVER given any thanks to anyone. This does not make me particularly inclined to assist.
However, I shall give you a suggestion: you need to consider that when selecting rows there is no deterministic result for the order in which the rows will be returned. For example:orclz>
orclz> select dname from dept;
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS
orclz> select dname from dept;
DNAME
--------------
ACCOUNTING
OPERATIONS
RESEARCH
SALES
orclz>
|
|
|
|
|
|
| Re: write a oracle query [message #612041 is a reply to message #612039] |
Fri, 11 April 2014 06:28 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Again, there is no row order in relational tables unless order by or hierarchical query is used, therefore "whenever it finds data value as 2 in col1 then it has to increase the value by 1 to the previous display value" can produce different results next time you run it.
SY.
|
|
|
|
| Re: write a oracle query [message #612044 is a reply to message #612037] |
Fri, 11 April 2014 06:50 |
 |
vslabs
Messages: 26
Registered: March 2014
Location: Cape Town
|
Junior Member |
|
|
Not sure whether this can be done using analytical functions, as such a function cannot reference itself (i.e. function cannot reference its calculated value in previous/lagging rows).
The pipeline approach looks as follows.
SQL> create table testtab( seq integer, i integer );
Table created.
SQL>
SQL> declare
2 type TInts is table of integer;
3 ints TInts := new TInts( 1, 1, 1, 1, 2, 2, 1, 2, 1, 2, 1, 1, 2, 2 );
4 begin
5 for i in 1..ints.Count loop
6 insert into testtab values ( i, ints(i) );
7 end loop;
8 commit;
9 end;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from testtab order by seq;
SEQ I
---------- ----------
1 1
2 1
3 1
4 1
5 2
6 2
7 1
8 2
9 1
10 2
11 1
12 1
13 2
14 2
14 rows selected.
SQL>
SQL> create or replace type TTestInt as object(
2 seq integer,
3 i_current integer,
4 i_projected integer
5 );
6 /
Type created.
SQL>
SQL> create type TTestArray is table of TTestInt;
2 /
Type created.
SQL>
SQL> create or replace function ProcessInts( c in sys_refcursor ) return TTestArray pipelined is
2 currInt testtab%RowType;
3 prevInt testtab%RowType;
4 testInt TTestInt;
5 begin
6 testInt := new TTestInt(null,null,null);
7 loop
8 fetch c into currInt;
9 exit when c%NotFound;
10
11 if prevInt.seq is null then
12 prevInt := currInt;
13 end if;
14
15 testInt.seq := currInt.seq;
16 testInt.i_current := currInt.i;
17
18 case
19 when currInt.i = 1 then testInt.i_projected := prevInt.i;
20 when currInt.i = 2 then testInt.i_projected := prevInt.i + 1;
21 else
22 testInt.i_projected := null; -- error!
23 end case;
24
25 pipe row( testInt );
26
27 prevInt.seq := currInt.seq;
28 prevInt.i := testInt.i_projected;
29 end loop;
30 return;
31 end;
32 /
Function created.
SQL>
SQL> select * from table(ProcessInts(cursor(select * from testtab order by seq)));
SEQ I_CURRENT I_PROJECTED
---------- ---------- -----------
1 1 1
2 1 1
3 1 1
4 1 1
5 2 2
6 2 3
7 1 3
8 2 4
9 1 4
10 2 5
11 1 5
12 1 5
13 2 6
14 2 7
14 rows selected.
SQL>
I added a SEQ column to add a sequence/order to the test data. Without such a column, the exercise is senseless as the order in which rows are read and new/expected integer value calculated, illogical.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: write a oracle query [message #612080 is a reply to message #612079] |
Fri, 11 April 2014 13:44 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
ORDER BY ROWNUM is useless since rownum is non-deterministic. It depends on execution plan. There is nothing to order it by unless OP adds a column defining row order.
SY.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: write a oracle query [message #612086 is a reply to message #612085] |
Fri, 11 April 2014 14:43 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Reason is windowing. By default it is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW which means all rows with same ORDER BY column value will end up in same window. And since NULL is not distinct all rows end up in one window. As soon as you change windowing to ROWS:
WITH temp AS (SELECT 1 col1 FROM DUAL
UNION
SELECT 1 FROM DUAL
UNION ALL
SELECT 1 FROM DUAL
UNION ALL
SELECT 1 FROM DUAL
UNION ALL
SELECT 2 FROM DUAL
UNION ALL
SELECT 2 FROM DUAL
UNION ALL
SELECT 1 FROM DUAL
UNION ALL
SELECT 2 FROM DUAL
UNION ALL
SELECT 1 FROM DUAL
UNION ALL
SELECT 2 FROM DUAL
UNION ALL
SELECT 1 FROM DUAL
UNION ALL
SELECT 1 FROM DUAL
UNION ALL
SELECT 2 FROM DUAL
UNION ALL
SELECT 2 FROM DUAL)
SELECT col1,
(COUNT (DECODE (col1, 2, col1)) OVER (ORDER BY NULL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) + 1
req_col_val
FROM temp
/
COL1 REQ_COL_VAL
---------- -----------
1 1
1 1
1 1
2 2
2 3
1 3
2 4
1 4
2 5
1 5
1 5
COL1 REQ_COL_VAL
---------- -----------
2 6
2 7
13 rows selected.
SQL >
But the only reason you are getting expected results is rows are entered in correct sequence and execution plan is using full scan. Look what happens if rows are entered in a different sequence:
SQL > WITH temp AS (SELECT 1 col1 FROM DUAL
2 UNION
3 SELECT 2 FROM DUAL
4 UNION ALL
5 SELECT 2 FROM DUAL
6 UNION ALL
7 SELECT 1 FROM DUAL
8 UNION ALL
9 SELECT 2 FROM DUAL
10 UNION ALL
11 SELECT 1 FROM DUAL
12 UNION ALL
13 SELECT 2 FROM DUAL
14 UNION ALL
15 SELECT 1 FROM DUAL
16 UNION ALL
17 SELECT 1 FROM DUAL
18 UNION ALL
19 SELECT 1 FROM DUAL
20 UNION ALL
21 SELECT 1 FROM DUAL
22 UNION ALL
23 SELECT 1 FROM DUAL
24 UNION ALL
25 SELECT 2 FROM DUAL
26 UNION ALL
27 SELECT 2 FROM DUAL)
28 SELECT col1,
29 (COUNT (DECODE (col1, 2, col1)) OVER (ORDER BY ROWNUM)) + 1
30 req_col_val
31 FROM temp;
COL1 REQ_COL_VAL
---------- -----------
1 1
2 2
2 3
1 3
2 4
1 4
2 5
1 5
1 5
1 5
1 5
COL1 REQ_COL_VAL
---------- -----------
1 5
2 6
2 7
14 rows selected.
SQL >
Similar thing will happen if, for example COL1 is NOT NULL and is indexed. As others already said, we can't get deterministic results without having something deterministic to order it by. And ROWNUM isn't deterministic.
SY.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
