Home » Developer & Programmer » Forms » Result of one column will input to next column (Oracle Forms 10g)
Result of one column will input to next column [message #629610] Fri, 12 December 2014 04:59 Go to next message
Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra
Member
Hi,
I have two column A, B. How to achieve output as below:
A | B (10-5)
--------------------
5 | (10-5) = 5
2 | (5-2) = 3
2 | (3-2) = 1
1 | (1-1) = 0
---------------------
i.e 10 = predefined value
step 1: 10- value of Column A i.e 10- 5 = 5
step 2: output of Step 1 - value of Column A i.e 5- 3 = 2


Thank you
Re: Result of one column will input to next column [message #629614 is a reply to message #629610] Fri, 12 December 2014 06:33 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Are the valuues already in the B column?

is this SQL related or form related Question?

[Updated on: Fri, 12 December 2014 06:33]

Report message to a moderator

Re: Result of one column will input to next column [message #629616 is a reply to message #629610] Fri, 12 December 2014 06:48 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
i mean 

10
5
3
1 

are already in the B Column?
Re: Result of one column will input to next column [message #629621 is a reply to message #629610] Fri, 12 December 2014 08:22 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Without any defined order, the answer is no.
Re: Result of one column will input to next column [message #629684 is a reply to message #629610] Mon, 15 December 2014 00:49 Go to previous messageGo to next message
Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra
Member
Actually, I need solution for oracle forms.
The vale 10 is sum of column 'A'.
So at first step, operation will be like Column "B" = sum(column"A") - max(column"A").
i.e at first row of column B = 10 - 5 = 5

Now, at second step, operation will be like
Column "B" = value of First row of Column "B" - next maximum value of column "A".
i.e at second row of column B = 5 - 2 = 3

In next step, operation will be like
Column "B" = value of Second row of Column "B" - next maximum value of column "A".
i.e at second row of column B = 3 - 2 = 1

In next step, operation will be like
Column "B" = value of Third row of Column "B" - next maximum value of column "A".
i.e at second row of column B = 1 - 1 = 0


Please help me.
Thank you.
Re: Result of one column will input to next column [message #629687 is a reply to message #629684] Mon, 15 December 2014 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please help us to help you.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: Result of one column will input to next column [message #629688 is a reply to message #629687] Mon, 15 December 2014 02:54 Go to previous messageGo to next message
Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra
Member
Table Structre
--------------
Name  Null?  Datatype
-------------
A     Not Null  number
B               number


Table Data
---------------
A   |   B  |
----|------|-----
5   |      |
2   |      |
2   |      |
1   |      |
-----------------------------
Expected Output
---------------------------
A   |   B  |
----|-------
5   |   5
2   |   3
2   |   1
1   |   0 
----|------|-----------------

[Updated on: Mon, 15 December 2014 02:57]

Report message to a moderator

Re: Result of one column will input to next column [message #629692 is a reply to message #629688] Mon, 15 December 2014 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read the links and sentences I posted and post back the requested and required elements and information.

Re: Result of one column will input to next column [message #629693 is a reply to message #629692] Mon, 15 December 2014 03:24 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
And bare in mind, as Joy pointed out earlier, if you don't have a column that defines the order of the rows no answer is possible.
Re: Result of one column will input to next column [message #629758 is a reply to message #629684] Mon, 15 December 2014 22:55 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
select
  a
,b-a b
from sampl1
order by a desc
Re: Result of one column will input to next column [message #629762 is a reply to message #629758] Mon, 15 December 2014 23:35 Go to previous messageGo to next message
Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra
Member
But, at first row of column b, calculation is like sum(column A) - first value of column A
i.e. 10 - 5 = 5
Re: Result of one column will input to next column [message #629764 is a reply to message #629762] Tue, 16 December 2014 00:09 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
But, at first row of column b, calculation is like sum(column A) - first value of column A
i.e. 10 - 5 = 5

Ok there is no values in B Column's right? so you want to display using Query right? Is this Form Requirement?
Re: Result of one column will input to next column [message #629765 is a reply to message #629764] Tue, 16 December 2014 00:15 Go to previous messageGo to next message
Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra
Member
Yes, Exactly...
Re: Result of one column will input to next column [message #629766 is a reply to message #629765] Tue, 16 December 2014 00:18 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
So B field (column) is data base or non-db item?
Re: Result of one column will input to next column [message #629767 is a reply to message #629765] Tue, 16 December 2014 00:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Mon, 15 December 2014 08:49

Please help us to help you.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.


Is this so hard? Or don't you understand what it means?

Re: Result of one column will input to next column [message #629768 is a reply to message #629766] Tue, 16 December 2014 00:23 Go to previous messageGo to next message
Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra
Member
non database
Re: Result of one column will input to next column [message #629771 is a reply to message #629767] Tue, 16 December 2014 00:37 Go to previous messageGo to next message
Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra
Member
Oracle Version : 10.2.0.5.0
SQL> desc m_test;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 A                                                  NUMBER
 B                                                  NUMBER

SQL> select * from m_test;

         A          B
---------- ----------
         5
         2
         2
         1

SQL> 



Re: Result of one column will input to next column [message #629773 is a reply to message #629771] Tue, 16 December 2014 00:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl12c> SELECT * FROM m_test
  2  /

         A          B
---------- ----------
         5
         2
         2
         1

4 rows selected.

SCOTT@orcl12c> SELECT a,
  2  	    10 - SUM (a) OVER (ORDER BY a DESC, ROWID) b
  3  FROM   m_test
  4  /

         A          B
---------- ----------
         5          5
         2          3
         2          1
         1          0

4 rows selected.

Re: Result of one column will input to next column [message #629775 is a reply to message #629773] Tue, 16 December 2014 00:56 Go to previous messageGo to next message
Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra
Member
Thanks.
I used same query for the mentioned case.
Problem is, same query is not worked in Oracle Forms 10g.
And we can hard code value as "10"

[Updated on: Tue, 16 December 2014 00:58]

Report message to a moderator

Re: Result of one column will input to next column [message #629776 is a reply to message #629775] Tue, 16 December 2014 01:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
Senior Member
Since it works in SQL, but you say it does not works in Forms, I will move this to the Forms forum, so that people with more expertise in that area may see it and help.
Re: Result of one column will input to next column [message #629778 is a reply to message #629775] Tue, 16 December 2014 01:13 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
mayur.shinde511@gmail.com wrote on Tue, 16 December 2014 07:56

Problem is, same query is not worked in Oracle Forms 10g.

What does it mean? Did you get any error? If so, which one?

There's a chance that analytic functions won't work in Forms. If that's the case, create a database (stored) function which will do the calculation and then call that function from a form.
Re: Result of one column will input to next column [message #629780 is a reply to message #629778] Tue, 16 December 2014 01:17 Go to previous messageGo to next message
Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra
Member
Yes,
I guess analytic functions are not working in oracle forms. So, could you help me for creation of function for the above case.
Re: Result of one column will input to next column [message #629784 is a reply to message #629771] Tue, 16 December 2014 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
mayur.shinde511@gmail.com wrote on Tue, 16 December 2014 07:37
Oracle Version : 10.2.0.5.0
SQL> desc m_test;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 A                                                  NUMBER
 B                                                  NUMBER

SQL> select * from m_test;

         A          B
---------- ----------
         5
         2
         2
         1

SQL> 


SQL> desc m_test;
ERROR:
ORA-04043: object m_test does not exist


SQL> select * from m_test;
select * from m_test
              *
ERROR at line 1:
ORA-00942: table or view does not exist


Do you understand now?

Re: Result of one column will input to next column [message #629786 is a reply to message #629784] Tue, 16 December 2014 02:16 Go to previous messageGo to next message
Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra
Member
sql> Create table m_test
     (a number, b number);



Re: Result of one column will input to next column [message #629787 is a reply to message #629786] Tue, 16 December 2014 02:22 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi ,

you can do this with navigating at the block from first record to last record.

SUM_A is the summary column
declare
      v_sum  number := 0 ;    
begin
      go_block('block');
      first_record ;
      loop
            v_sum:=  v_sum+ :a ;
            :b   :=  :SUM_A - v_sum;
            exit when :system.last_record = 'TRUE' ;
            next record ;
      end loop ;
end ;
Re: Result of one column will input to next column [message #629790 is a reply to message #629787] Tue, 16 December 2014 03:10 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
And which trigger would you run that code from?

Create a view using Barbara's query. Base the block on the view.
Re: Result of one column will input to next column [message #629792 is a reply to message #629786] Tue, 16 December 2014 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
mayur.shinde511@gmail.com wrote on Tue, 16 December 2014 09:16
sql> Create table m_test
     (a number, b number);


SQL> Create table m_test
  2       (a number, b number);

Table created.

SQL> select * from m_test;

no rows selected


OK I have the table but nothing in it.
Re: Result of one column will input to next column [message #629794 is a reply to message #629792] Tue, 16 December 2014 03:22 Go to previous messageGo to next message
Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra
Member
sql> insert into m_test (a) values(5);

sql> insert into m_test (a) values(2);

sql> insert into m_test (a) values(2);

sql> insert into m_test (a) values(1);
Re: Result of one column will input to next column [message #629795 is a reply to message #629787] Tue, 16 December 2014 03:23 Go to previous messageGo to next message
Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra
Member
Thank you Surya sir.
Problem is solved...

[Updated on: Tue, 16 December 2014 03:24]

Report message to a moderator

Re: Result of one column will input to next column [message #629798 is a reply to message #629795] Tue, 16 December 2014 03:45 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
You can also use window clause in the Database , i don't know window clause how to use this in the Oracle Forms
http:// www.java2s.com/Tutorial/Oracle/0320__Analytical-Functions/UseROWSUNBOUNDEDPRECEDINGtoimplicitlyindicatetheendofthewindowisthecurrentr ow.htm
Re: Result of one column will input to next column [message #629801 is a reply to message #629798] Tue, 16 December 2014 03:56 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
As I just said - create a view, base block on view.
Re: Result of one column will input to next column [message #629802 is a reply to message #629794] Tue, 16 December 2014 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
mayur.shinde511@gmail.com wrote on Tue, 16 December 2014 10:22
sql> insert into m_test (a) values(5);

sql> insert into m_test (a) values(2);

sql> insert into m_test (a) values(2);

sql> insert into m_test (a) values(1);


OK now we have a test case and we can work.

SQL> insert into m_test (a) values(1);

1 row created.

SQL> insert into m_test (a) values(2);

1 row created.

SQL> insert into m_test (a) values(2);

1 row created.

SQL> insert into m_test (a) values(5);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from m_test;
         A          B
---------- ----------
         1
         2
         2
         5

4 rows selected.

SQL> with total as (select sum(a) total from m_test)
  2  select a,
  3         total - sum(a) over (order by a desc) b
  4  from m_test, total
  5  order by a desc
  6  /
         A          B
---------- ----------
         5          5
         2          1
         2          1
         1          0

4 rows selected.

So:
SQL> create or replace view v as
  2  with total as (select sum(a) total from m_test)
  3  select a,
  4         total - sum(a) over (order by a desc) b
  5  from m_test, total
  6  /

View created.

SQL> select * from v order by a desc;
         A          B
---------- ----------
         5          5
         2          1
         2          1
         1          0

4 rows selected.

which can be used with your Forms version.

Hope you will correctly post in your next topic.

Re: Result of one column will input to next column [message #629803 is a reply to message #629801] Tue, 16 December 2014 04:00 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
As I just said - create a view, base block on view.

Ok got it thanks.. Smile

[Updated on: Tue, 16 December 2014 04:00]

Report message to a moderator

Re: Result of one column will input to next column [message #629805 is a reply to message #629802] Tue, 16 December 2014 04:03 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Nice Query.. Smile
Re: Result of one column will input to next column [message #629810 is a reply to message #629802] Tue, 16 December 2014 04:28 Go to previous message
Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra
Member
This mistake never happen again.
Thank you very much...
Previous Topic: ** Refresh another running form from another form, how to?
Next Topic: how to use Cursor for loop ?
Goto Forum:
  


Current Time: Thu Apr 25 20:37:39 CDT 2024