Home » SQL & PL/SQL » SQL & PL/SQL » write a oracle query (oracle 10gR2 RHEL)
write a oracle query [message #612037] Fri, 11 April 2014 05:29 Go to next message
9390512774
Messages: 97
Registered: January 2011
Location: hyd
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 Go to previous messageGo to next message
John Watson
Messages: 4610
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>
I set up this example by adjusting an instance parameter, from another session. How would you handle it in your problem?
Re: write a oracle query [message #612039 is a reply to message #612038] Fri, 11 April 2014 06:22 Go to previous messageGo to next message
9390512774
Messages: 97
Registered: January 2011
Location: hyd
Member
THANKS FOR YOUR basic question.But,
I don't mine the way it pulls(order) the data from database. My intension is when it is displaying the data in output screen based on that it has to display the data in the requested format.
Re: write a oracle query [message #612041 is a reply to message #612039] Fri, 11 April 2014 06:28 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2036
Registered: January 2010
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 Go to previous messageGo to next message
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 #612045 is a reply to message #612044] Fri, 11 April 2014 06:59 Go to previous messageGo to next message
9390512774
Messages: 97
Registered: January 2011
Location: hyd
Member
THANKS AGAIN!!
can I use LAG function here along with DECODE to get.

[Updated on: Fri, 11 April 2014 06:59]

Report message to a moderator

Re: write a oracle query [message #612062 is a reply to message #612044] Fri, 11 April 2014 10:42 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ You have an order which does not exist in the question, so it does not answer the original question
2/ There is a far much simpler way to do it in a single SQL with no type, no PL/SQL.

I give you a hint: just use COUNT.

Re: write a oracle query [message #612075 is a reply to message #612062] Fri, 11 April 2014 12:00 Go to previous messageGo to next message
manubatham20
Messages: 468
Registered: September 2010
Location: Champaign, IL
Senior Member

Not sure how to use count, I tried below:

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,
       (SUM (DECODE (col1, 2, 1, 0)) OVER (ORDER BY rowno)) + 1 req_col_val
  FROM (SELECT ROWNUM rowno, col1 FROM temp);


Manu
Re: write a oracle query [message #612076 is a reply to message #612075] Fri, 11 April 2014 12:11 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Replace SUM by COUNT, you do not want to SUM a value of 1 you want to count the 2:
COUNT(DECODE(col1, 2, col1)) OVER (ORDER BY <something that does not exist in OP's table) + 1


Re: write a oracle query [message #612079 is a reply to message #612076] Fri, 11 April 2014 13:36 Go to previous messageGo to next message
manubatham20
Messages: 468
Registered: September 2010
Location: Champaign, IL
Senior Member

Thanks.

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 ROWNUM)) + 1
          req_col_val
  FROM temp;


What else I can put in "ORDER BY" clause.

Manu
Re: write a oracle query [message #612080 is a reply to message #612079] Fri, 11 April 2014 13:44 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2036
Registered: January 2010
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 #612081 is a reply to message #612079] Fri, 11 April 2014 13:45 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Nothing, you can put nothing (and NULL is more appropriate than ROWNUM for the same result) as there is nothing in OP's data and this is the problem.

Re: write a oracle query [message #612082 is a reply to message #612081] Fri, 11 April 2014 13:49 Go to previous messageGo to next message
manubatham20
Messages: 468
Registered: September 2010
Location: Champaign, IL
Senior Member

(COUNT (DECODE (col1, 2, col1)) OVER (ORDER BY NULL)) + 1

and

(COUNT (DECODE (col1, 2, col1)) OVER (ORDER BY ROWNUM)) + 1

giving different results.
Re: write a oracle query [message #612083 is a reply to message #612082] Fri, 11 April 2014 13:54 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Show.

Re: write a oracle query [message #612084 is a reply to message #612083] Fri, 11 April 2014 14:08 Go to previous messageGo to next message
manubatham20
Messages: 468
Registered: September 2010
Location: Champaign, IL
Senior Member

Here it is:

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 ROWNUM)) + 1
          req_col_val
  FROM temp;


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
Re: write a oracle query [message #612085 is a reply to message #612084] Fri, 11 April 2014 14:09 Go to previous messageGo to next message
manubatham20
Messages: 468
Registered: September 2010
Location: Champaign, IL
Senior Member

/* Formatted on 4/11/2014 2:06:52 PM (QP5 v5.149.1003.31008) */
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)) + 1
          req_col_val
  FROM temp;


1	7
1	7
1	7
2	7
2	7
1	7
2	7
1	7
2	7
1	7
1	7
2	7
2	7


select * from v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE	11.2.0.3.0	Production
TNS for HPUX: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


Manu
Re: write a oracle query [message #612086 is a reply to message #612085] Fri, 11 April 2014 14:43 Go to previous message
Solomon Yakobson
Messages: 2036
Registered: January 2010
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.
Previous Topic: Error in Stored procedure
Next Topic: # character is used in column
Goto Forum:
  


Current Time: Wed Oct 01 11:00:08 CDT 2014

Total time taken to generate the page: 0.09986 seconds