Home » Developer & Programmer » Forms » Calculation mode need closing value as shown in image (3 merged)
Calculation mode need closing value as shown in image (3 merged) [message #660143] Thu, 09 February 2017 13:21 Go to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
/forum/fa/13444/0/

blue query from Query data source column
needed red

Oracle form builder>>
can any one help me??? i tried but failed to solve
Re: Calculation mode need closing value as shown in image [message #660146 is a reply to message #660143] Thu, 09 February 2017 23:04 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
littlefoot

can you help me for this
Re: Calculation mode need closing value as shown in image [message #660149 is a reply to message #660146] Fri, 10 February 2017 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 66787
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Have a look at SUM in its analytic form: Database SQL Reference.

Re: Calculation mode need closing value as shown in image [message #660164 is a reply to message #660143] Fri, 10 February 2017 07:51 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
You will need some kind of order to the rows or nothing will work.
Re: Calculation mode need closing value as shown in image [message #660175 is a reply to message #660149] Fri, 10 February 2017 11:36 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
okay i m looking at....

its better to provide some coding

thanks...
Re: Calculation mode need closing value as shown in image [message #660177 is a reply to message #660175] Fri, 10 February 2017 12:15 Go to previous messageGo to next message
Michel Cadot
Messages: 66787
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Its better you try to do it by yourself and tell us where you are stuck.

Re: Calculation mode need closing value as shown in image [message #660178 is a reply to message #660177] Fri, 10 February 2017 12:47 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
i have two table qtyrecd and qtyissued
suppose i have 5 records/rows
queried into form
-------- qtyrecd ---- qtyissued --- closing
---------- 1 ---------- 0 ---------- 1
---------- 2 ---------- 0 ---------- 3
---------- 0 ---------- 2 ---------- 1
---------- 0 ---------- 1 ---------- 0
---------- 3 ---------- 0 ---------- 3
------------------------------------------------------
total------ 6 ------------ 3 ------------ 3
done sum using total but totally clueless on closing
needed closing coding
looked Analytic Functions
no idea how to code this for each row

[Updated on: Fri, 10 February 2017 12:51]

Report message to a moderator

Re: Calculation mode need closing value as shown in image [message #660179 is a reply to message #660178] Fri, 10 February 2017 12:51 Go to previous messageGo to next message
Michel Cadot
Messages: 66787
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Show us what you did.
Please read How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

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: Calculation mode need closing value as shown in image [message #660180 is a reply to message #660179] Fri, 10 February 2017 13:11 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
/forum/fa/13448/0/
  • Attachment: PIC.JPG
    (Size: 216.09KB, Downloaded 670 times)
Re: Calculation mode need closing value as shown in image [message #660181 is a reply to message #660180] Fri, 10 February 2017 13:12 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
/forum/fa/13449/0/
Re: Calculation mode need closing value as shown in image [message #660182 is a reply to message #660181] Fri, 10 February 2017 13:13 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
fmb
Re: Calculation mode need closing value as shown in image [message #660190 is a reply to message #660182] Sat, 11 February 2017 06:36 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
I assume you mean you want a running total. I would take this approach. I presume that you have a field defined on the block 'Running total' which is not based on a table.

Define a global variable in the when-new-form-instance trigger. In the Post-Query trigger:

Code (Text):
:global.var := :global.var + :qty-:issqty;
:run_tot := :global.var;

:qty is the field in the block which you are receiving and :issqty is issued qty. Remember to initialize :global .

Or
You can use Try the ROLLUP analytical function. ROLLUP enables a SQL statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use. It also only does one-pass through the table, so it's efficient too!
SELECT
   deptno,
   job,
   count(*),
   sum(sal)
FROM
   emp
GROUP BY
   ROLLUP(deptno,job);

DEPTNO JOB         COUNT(*)   SUM(SAL)
--------- --------- ---------   ---------
       10 CLERK              1       1300
       10 MANAGER            1       2450
       10 PRESIDENT          1       5000
 -->   10                    3       8750
       20 ANALYST            2       6000
       20 CLERK              2       1900
       20 MANAGER            1       2975
 -->   20                    5      10875


Send Test case. it will be more practical.

[Updated on: Sat, 11 February 2017 06:48]

Report message to a moderator

Re: Calculation mode need closing value as shown in image [message #660191 is a reply to message #660190] Sat, 11 February 2017 08:02 Go to previous messageGo to next message
Michel Cadot
Messages: 66787
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You cannot compute a running total with ROLLUP and your example clearly shows this is not what it does.
Only analytic function can do this and it is, in my opinion, easier to use than ROLLUP which is not so simple than you say.

Re: Calculation mode need closing value as shown in image [message #660193 is a reply to message #660190] Sat, 11 February 2017 11:31 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
you are right i need running total
qtyrecd and qtyissued from table & closing is non table, running total
Re: Calculation mode need closing value as shown in image [message #660194 is a reply to message #660193] Sat, 11 February 2017 11:33 Go to previous messageGo to next message
Michel Cadot
Messages: 66787
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And I told you how to do it.

Re: Calculation mode need closing value as shown in image [message #660195 is a reply to message #660194] Sat, 11 February 2017 11:47 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
very tuff as i am beginner but i am searching how analytical function procedures run n how to code in calculation mode...
which one use for running total
1. query_partition_clause
2. windowing_clause
or simply analytic_function OVER()...

i'm confused....

[Updated on: Sat, 11 February 2017 11:52]

Report message to a moderator

Re: Calculation mode need closing value as shown in image [message #660196 is a reply to message #660195] Sat, 11 February 2017 11:51 Go to previous messageGo to next message
Michel Cadot
Messages: 66787
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Did you read the doc I pointed you too?
Did you understand the examples in it?

Also if you post a test case as asked and repeated you may have more accurate answers.

Re: Calculation mode need closing value as shown in image [message #660197 is a reply to message #660196] Sat, 11 February 2017 11:56 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
ohh i skipped now will study test case
thank you...
Re: Calculation mode need closing value as shown in image [message #660210 is a reply to message #660196] Sun, 12 February 2017 08:09 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON


SQL>
SQL>  select dname,
  2             ename,
  3             sal,
  4             sum(sal) over ( partition by dname
  5                                 order by dname, ename ) dept_running_total,
  6             sum(sal) over ( order by dname, ename ) running_total
  7        from emp, dept
  8       where emp.deptno = dept.deptno
  9       order by dname, ename
 10     /

DNAME          ENAME             SAL DEPT_RUNNING_TOTAL RUNNING_TOTAL
-------------- ---------- ---------- ------------------ -------------
ACCOUNTING     CLARK            2450               2450          2450
               KING             5000               7450          7450
               MILLER           1300               8750          8750

RESEARCH       ADAMS            1100               1100          9850
               FORD             3000               4100         12850
               JONES            2975               7075         15825
               SCOTT            3000              10075         18825
               SMITH             800              10875         19625

SALES          ALLEN            1600               1600         21225

DNAME          ENAME             SAL DEPT_RUNNING_TOTAL RUNNING_TOTAL
-------------- ---------- ---------- ------------------ -------------
SALES          BLAKE            2850               4450         24075
               JAMES             950               5400         25025
               MARTIN           1250               6650         26275
               TURNER           1500               8150         27775
               WARD             1250               9400         29025


14 rows selected.

its only addition how i divide from another table???



[Edit MC: remove execution of SCOTT's CREATE TABLE and INSERT statements code and execution]

[Updated on: Sun, 12 February 2017 09:55] by Moderator

Report message to a moderator

Re: Calculation mode need closing value as shown in image [message #660215 is a reply to message #660210] Sun, 12 February 2017 09:53 Go to previous messageGo to next message
Michel Cadot
Messages: 66787
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You don't need to post the code for Oracle standard tables (in addition it is copyrighted, so you must not).
Also a test case is just the statements WITHOUT their execution, but you must verify that the statements are correct executing at your side but NOT posting the execution listing.
In the end, use "set pages 1000" to avoid column headers repetition and post a clean listing.

Quote:
ts only addition how i divide from another table???
What does this mean?
Post a test case showing what you mean.

[Updated on: Sun, 12 February 2017 09:56]

Report message to a moderator

Re: Calculation mode need closing value as shown in image [message #660264 is a reply to message #660215] Mon, 13 February 2017 13:25 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
i don't know how to do....
no idea...
Re: Calculation mode need closing value as shown in image [message #660265 is a reply to message #660264] Mon, 13 February 2017 13:27 Go to previous messageGo to next message
Michel Cadot
Messages: 66787
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Sun, 12 February 2017 16:53

...
Quote:
ts only addition how i divide from another table???
What does this mean?
Post a test case showing what you mean.
Re: Calculation mode need closing value as shown in image [message #660283 is a reply to message #660265] Tue, 14 February 2017 03:30 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
I HAVE TWO COLUMN QUERIED FROM TABLE ON FORM BUILDER [NEED BALANCE] LIKE THIS...
  DEBIT CREDIT [BALANCE]
    1     0      [1]
    0     1      [0]
    2     0      [2]
    0     1      [1]
   ---   ---     ---
   (3)   (2)     
(CALCULATED USING CALCULATION SUMMARY)
[HOW TO CALCULATE BALANCE LIKE ABOVE]

[Updated on: Tue, 14 February 2017 03:35]

Report message to a moderator

Re: Calculation mode need closing value as shown in image [message #660284 is a reply to message #660283] Tue, 14 February 2017 03:33 Go to previous messageGo to next message
Michel Cadot
Messages: 66787
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

POST A TEST CASE

Re: Calculation mode need closing value as shown in image [message #660285 is a reply to message #660284] Tue, 14 February 2017 03:38 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
I HAVE TWO COLUMN QUERIED FROM TABLE ON FORM BUILDER [NEED BALANCE] LIKE THIS...
  DEBIT CREDIT [BALANCE]
    1     0      [1]
    0     1      [0]
    2     0      [2]
    0     1      [1]
   ---   ---     ---
   (3)   (2)     
(CALCULATED USING CALCULATION SUMMARY)
[HOW TO CALCULATE BALANCE LIKE ABOVE]

I HAVE NOTHING TO POST
Re: Calculation mode need closing value as shown in image [message #660287 is a reply to message #660285] Tue, 14 February 2017 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 66787
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Tue, 14 February 2017 10:33

POST A TEST CASE
If you have read the links I repeated you, you SHOULD know what you have to post.


Re: Calculation mode need closing value as shown in image [message #660290 is a reply to message #660287] Tue, 14 February 2017 04:18 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
Really i have no idea, i am beginner i stuck here from so long please help me..
can you post a test case behalf of me Razz
Re: Calculation mode need closing value as shown in image [message #660308 is a reply to message #660290] Tue, 14 February 2017 08:15 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
sr8464 wrote on Tue, 14 February 2017 05:18
Really i have no idea, i am beginner i stuck here from so long please help me..
can you post a test case behalf of me Razz
Expert, beginner, doesn't matter. A test case has nothing to do with expertise in SQL. Please read the links so you understand what a test case is.
Re: Calculation mode need closing value as shown in image [message #660311 is a reply to message #660308] Tue, 14 February 2017 11:49 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
1.0- I have a Text Item text1 in block1 where i click f9 it shows list of values from table1
1.1- assigned a trigger in text1 KEY-NEXT-ITEM
go_block('block2');
set_block_property('block2', default_where, '');
execute_query;
2.0- Under block2 assigned a trigger PRE-QUERY
if :block1.text1 is not null then
	set_block_property ('block2', default_where, 'block1 = :block1.text1');
end if;
2.1- On block2 Property Pallet under Query Data Source Name > table1
2.2- On block2 i have 3 Text Item/ Display Item Called Debit, Credit & Closing where Debit & Credit Value queried from database
2.3- if Debit value is 1,2... then Credit Value is always 0 and if Credit value is 1,2... then Debit Value is always 0
2.4- Calculated Sum of Total Credit or Debit using Summary function Sum Credit & Sum Debit
3.0- how to calculate closing
  DEBIT CREDIT [Closing]
    1     0      [1]
    0     1      [0]
    2     0      [2]
    0     1      [1]
   ---   ---     ---
   (3)   (2)     (1)
(CALCULATED USING CALCULATION SUMMARY)
[HOW TO CALCULATE BALANCE LIKE ABOVE]
Re: Calculation mode need closing value as shown in image [message #660312 is a reply to message #660311] Tue, 14 February 2017 12:27 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
Either you are ignoring the request or you really do not comprehend what a test case is

create table foo (col1 varchar2(10), col2 number(10));

insert into foo (col1, col2) values ('A',1);
insert into foo (col1, col2) values ('B',2);
Re: Calculation mode need closing value as shown in image [message #660313 is a reply to message #660308] Tue, 14 February 2017 12:49 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
CREATE TABLE table1(Debit NUMBER(12,3) DEFAULT 0.000,Credit NUMBER(12,3) DEFAULT 0.000);
insert into table1 (Debit, Credit) values ('1',0);
insert into table1 (Debit, Credit) values ('0',1);
insert into table1 (Debit, Credit) values ('2',0);
insert into table1 (Debit, Credit) values ('0',1);

[Updated on: Tue, 14 February 2017 12:54]

Report message to a moderator

Re: Calculation mode need closing value as shown in image [message #660314 is a reply to message #660313] Tue, 14 February 2017 12:57 Go to previous messageGo to next message
Michel Cadot
Messages: 66787
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why do you put quotes around numbers which converts them to strings when what you want are numbers and then forces Oracle to convert them back to numbers?

In addition, I told you:

Quote:
Also a test case is just the statements WITHOUT their execution, but you must verify that the statements are correct executing at your side but NOT posting the execution listing.
SQL> CREATE TABLE table1
  2  (
  3  Debit NUMBER(12,3) DEFAULT 0.000,
  4  Credit NUMBER(12,3) DEFAULT 0.000,
  5  );
)
*
ERROR at line 5:
ORA-00904: : invalid identifier

Re: Calculation mode need closing value as shown in image [message #660316 is a reply to message #660314] Tue, 14 February 2017 13:43 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
CREATE TABLE table1(Debit NUMBER(12,3) DEFAULT 0.000,Credit NUMBER(12,3) DEFAULT 0.000);
insert into table1 (Debit, Credit) values (1,0);
insert into table1 (Debit, Credit) values (0,1);
insert into table1 (Debit, Credit) values (2,0);
insert into table1 (Debit, Credit) values (0,1);
Re: Calculation mode need closing value as shown in image [message #660318 is a reply to message #660316] Tue, 14 February 2017 13:50 Go to previous messageGo to next message
Michel Cadot
Messages: 66787
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Good, now you have to answer joy_division's point: "You will need some kind of order to the rows or nothing will work.".
If you want a running total, you need to determine in which way you run.
Which row is the first one? And the second one? And... These questions must be answered from the data themselves and only them. Don't say in the order I posted them, this is meaningless from the data, there is no order in a table.

Re: Calculation mode need closing value as shown in image [message #660327 is a reply to message #660318] Wed, 15 February 2017 00:23 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
Order by vrno asc
DROP TABLE TABLE1;
CREATE TABLE table1(VRNO NUMBER(5),Debit NUMBER(12,3) DEFAULT 0.000,Credit NUMBER(12,3) DEFAULT 0.000);
insert into table1 (VRNO, Debit, Credit) values (1,1,0);
insert into table1 (VRNO, Debit, Credit) values (2,0,1);
insert into table1 (VRNO, Debit, Credit) values (3,2,0);
insert into table1 (VRNO, Debit, Credit) values (4,0,1);

[Updated on: Wed, 15 February 2017 00:24]

Report message to a moderator

Re: Calculation mode need closing value as shown in image [message #660330 is a reply to message #660327] Wed, 15 February 2017 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 66787
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Now you can do it.
The balance of the day is "credit-debit", so the running balance is the running sum of this expression:
SQL> select vrno, debit, credit,
  2         sum(credit-debit) over (order by vrno) balance
  3  from table1
  4  order by vrno
  5  /
      VRNO      DEBIT     CREDIT    BALANCE
---------- ---------- ---------- ----------
         1          1          0         -1
         2          0          1          0
         3          2          0         -2
         4          0          1         -1
Re: Calculation mode need closing value as shown in image [message #660337 is a reply to message #660330] Wed, 15 February 2017 02:34 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
okay now getting results... thank you

but where i mention on form builder
property pallet or using any trigger???


can i get the solution...

[Updated on: Wed, 15 February 2017 12:10]

Report message to a moderator

Re: Calculation mode need closing value as shown in image [message #660416 is a reply to message #660330] Thu, 16 February 2017 01:05 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
is this possible to use this code into property palette > Calculation Mode Formula... if not where i use it???
select vrno, debit, credit, sum(credit-debit) over (order by vrno) balance from table1 order by vrno
Re: Property Palette > Calculation Mode > Formula [message #660579 is a reply to message #660143] Tue, 21 February 2017 08:23 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
A formula returns one value. I see multiple columns and rows in your query.
Re: Property Palette > Calculation Mode > Formula [message #660580 is a reply to message #660579] Tue, 21 February 2017 08:31 Go to previous messageGo to previous message
cookiemonster
Messages: 13781
Registered: September 2008
Location: Rainy Manchester
Senior Member
To be fair the OP is asking how to implement the formula he was given in this thread: http://www.orafaq.com/forum/t/202912/
If the datablock isn't updateable or insertable then it's easy - create a view that implements the analytic and base the block on that.
Otherwise it's going to be awkward.
So can you insert/update data in the block?

Previous Topic: FORM LEVEL AUTO SERIAL NO
Next Topic: Insert into form first then into the database
Goto Forum:
  


Current Time: Wed Jan 22 20:59:56 CST 2020