SQL & PL/SQL :: Calculate Two Parameters A And B Which Are Interdependent?

Mar 2, 2010

I want to calculate two parameters A and B which are interdependent. Unfortunately, my code is not working correctly:

create table temp_res1 as
select *
from temp_res
model
dimension by (rn)
measures ( 0 as A,
0 as B

[code]....

The correct result should be:

create table temp_res (rn integer, a integer, b integer);
insert into temp_res values(1, 5, 10);
insert into temp_res values(2, 11, 6);
insert into temp_res values(3, 7, 12);
insert into temp_res values(4, 13, 8);
insert into temp_res values(5, 9, 14);
insert into temp_res values(6, 15, 10);
insert into temp_res values(7, 11, 16);
insert into temp_res values(8, 17, 12);

but I get:

create table temp_res (rn integer, a integer, b integer);
insert into temp_res values(1, 5, 10);
insert into temp_res values(2, 11, 6);
insert into temp_res values(3, 1, 12);
insert into temp_res values(4, 1, 2);
insert into temp_res values(5, 1, 2);
insert into te

View 4 Replies


ADVERTISEMENT

Calculate On Itself

Jun 3, 2011

only one table A(autoid, number)

how SELECT statment result: B(autoid, number, numberB)

numberB is sum of all the number have autoid > its autoid.

View 4 Replies View Related

SQL & PL/SQL :: Package To Calculate Pay

Oct 20, 2010

Trying to accomplish:

I am trying to calculate pay with a package which consists of four functions for calculations and a procedure that calls the functions to calculate net pay.

DML DDL and package

I the DML and DDL and the package as an attachment.

Problem

Errors below

32/9 PLS-00103: Encountered the symbol "E" when expecting one of the following:

, ; for group having intersect minus order start union where connect The symbol "having" was substituted for "E" to continue.

32/54 PLS-00103: Encountered the symbol ")" when expecting one of the following:

LINE/COL ERROR
-------- ----------------------------------------------------------------
. ( * @ % & - + ; / at for mod remainder rem <an exponent (**)> and or group having intersect minus order start union where connect || multiset

33/9 PLS-00103: Encountered the symbol "INTO" when expecting one of the following:

. ( ) , * @ % & = - + < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || member submultiset

View 39 Replies View Related

SQL & PL/SQL :: How To Calculate Exact Age

Oct 21, 2011

How to calculate exact age for example my date of birth is 10-04-1972 and today current date is 21-10-2011 so i want to calculate age how many years, how many months and how many days.

View 27 Replies View Related

SQL & PL/SQL :: How To Calculate Working Day

Jan 1, 2013

I need a query to find out the working days. I have attached the sample script to create table and data. Here is the description of the tables.

Emp1 : To record the employe's information and weekly rest day.
Attendance :- To record daily attendance.
Leave_appovd : To record the approved leaves.
Holiday : To record the holidays.

W = Attendance
R = Weekly Rest
L = Leave
H = Holiday
A = Absent

Required output is

Emp No.In Date Out Date Type
736912/1/201212/1/2012W
736912/2/201212/2/2012R
736912/3/201212/3/2012W
736912/4/201212/4/2012W
736912/5/201212/5/2012W
736912/6/201212/6/2012L
736912/7/201212/7/2012H
736912/8/201212/8/2012H
736912/9/201212/9/2012R
736912/10/201212/10/2012A
736912/11/201212/11/2012W
736912/12/201212/12/2012W
736912/13/201212/13/2012W
736912/14/201212/14/2012W
736912/15/201212/15/2012L
736912/16/201212/16/2012L
736912/17/201212/17/2012L
736912/18/201212/18/2012W
736912/19/201212/19/2012W
736912/20/201212/20/2012W
736912/21/201212/21/2012W
736912/22/201212/22/2012W
736912/23/201212/23/2012R
736912/24/201212/24/2012A
736912/25/201212/25/2012A
736912/26/201212/26/2012A
736912/27/201212/27/2012W
736912/28/201212/28/2012W
736912/29/201212/29/2012W
736912/30/201212/30/2012R
736912/31/201212/31/2012W
778212/1/201212/1/2012W
778212/2/201212/2/2012W
778212/3/201212/3/2012W
778212/4/201212/4/2012W
778212/5/201212/5/2012W
778212/6/201212/6/2012W

Separate Query will be run for the following output.

Wroking Days
EmpnoAttend WeeklyRestsLeavesHolidays TotalAbsentsG. Total
7369 17 4 4 2 27 4 31
7782 6 0 0 0 6 25 31

If any body work on weekly rest or holiday, it will be considered as weekly rest and holiday. There working on these days will be treated separately.

View 10 Replies View Related

PL/SQL :: How To Calculate Value Column

Dec 21, 2012

We have 2 tables.

Example :
Table Name1: Sales_orders - Columns(Cust_Id,Customer,Quantity,Unit_Price,........etc)
Table name2: Sales_taxes - Columns(Central_sales_Tax,Service_Tax,ValueAdd_Tax,Entry_Tax,Professional_Tax)

My requirement is;
I what create new table/view which contains all value of both table and new column called Total Amount.
Total amount=SUM(Quantity*Unit_Price+Central_sales_Tax+Service_Tax+ValueAdd_Tax+Entry_Tax+Professional_Tax)

View 9 Replies View Related

How To Calculate Redo Rate

Nov 14, 2006

how would I be able to calculate the Redo rate for using in the Required bandwidth Formula as seen below :

Required bandwidth Formula ((Redo rate in bytes per second / 0.7) * 8) / 1,000,000 = bandwidth in Mbps

Example: 385 KB/sec peak rate would require an available network bandwidth of at least

((394253 / 0.7) * 8) / 1,000,000 = 4.5 Mbps.

SOURCE OF FORMULA Network Bandwidth Implications of Oracle Data Guard...I'm using Oracle 10g

View 3 Replies View Related

SQL & PL/SQL :: How To Calculate Running Percentage

Mar 13, 2013

I want to calculate the running percentage. sample data is as below.

CREATE TABLE prod
(
code VARCHAR2(8),
name VARCHAR2(30),
stdate DATE,
itons NUMBER(7,3),
rtons NUMBER(7,3)

[code]....

Required Output is

CODE NAME STDATE ITONS RTONS %age
-------- ------------------------------ --------- ---------- ---------- -------
0-01-001 SEEDS 17-JUL-12 193.155 0
1-01-001 OIL 17-JUL-12 0 81.906 42.404
1-02-001 MEAL 17-JUL-12 0 104.304 54.000
1-03-001 DURT OIL 17-JUL-12 0 1.242 0.643
2-01-001 WASTE 17-JUL-12 0 5.703 2.953

[code]....

Percentage will be calculated as rtons of code not having first digit 0 devided by itons having having code 0 result multiply 100 for the same date code wise e.g. For dated 17-jul-13 meal percentage will be calculated as round((104.304/193.155)*100,3)=54.000

View 6 Replies View Related

Forms :: How To Calculate Average In 6i

Apr 5, 2011

How To Calculate Average in Forms 6i for example a summary column named (Amount = 5000) and i want to calculate 15% average of this amount i want to calculate it like summary column .

View 2 Replies View Related

SQL & PL/SQL :: How To Calculate Proper Hrs Between Two Dates

Jun 25, 2011

i have two dates like

30-may-2011 11:50:34 and 27-may-2011 13:59:37

how i can calculate proper hrs between these dates? My condition is time calculate from 8.30AM to 5.30PM

it does not considered 24 hrs

if i calculate hrs

30-may-2011 11:50:34 - 27-may-2011 13:59:37

then it shows 69.85 hrs . but it considered full 24 hrs.

View 5 Replies View Related

SQL & PL/SQL :: How To Calculate Period Date

Sep 14, 2011

Following is the scenario:

CREATE TABLE EMP_VACATION(
EMP_NO NUMBER(3),
VAC_TYPE NUMBER(1),
START_DATE DATE,
END_DATE DATE,
START_PERIOD_DATE DATE,
END_PERIOD_DATE DATE,
PRIMARY KEY (EMP_NO,VAC_TYPE,START_DATE))
[code]....

--How to calculate START_PERIOD_DATE and END_PERIOD_DATE for every time insert reocrd, like for 4 cases?
--I need to check start_date with last period date for same employee.

And based upon calculate START_PERIOD_DATE and END_PERIOD_DATE,,,,,

View 7 Replies View Related

SQL & PL/SQL :: How To Calculate Time Difference

Aug 10, 2003

I want to calculate time difference b/w two time clocks, just like we calculate the date difference and answer is in days, In the same way i like to have answer in hrs,min and ss.

View 4 Replies View Related

SQL & PL/SQL :: Calculate Balances For Each Month

Apr 8, 2013

have this SQL query works fine but I want to calculate the balances for each month...I STARTING to do so: I have to:

JanuaryBalance = Initialbalance + January;
FebruaryBalance = JanuaryBalance+February;
MarchBalance = FebruaryBalance + March;

SELECT
Sum("F0902"."GBAPYC"/100) AS "initialBalance",
Sum("F0902"."GBAN01"/100) AS "January",
Sum("F0902"."GBAN02"/100) AS "February",
Sum("F0902"."GBAN03"/100) AS "March",
[code]....

View 10 Replies View Related

Forms :: Calculate Sum In A Form?

Nov 23, 2007

how do w calculate sum in a form? for example in datablock sale, I have few rows of data, i want to calculate the total of product purchased (column totitem) and , sum of the customer have to pay. (totalprice). how to do the calculation in a program unit?

View 13 Replies View Related

SQL & PL/SQL :: Function To Calculate The Vacation?

Oct 31, 2011

how can create function to calculate the vacation example if i takes 10 days vacation and Friday , Saturday only a normal vacation but The rest of the days is Within the vacation

how can calculate the vacation and make this function dynamic such as when takes the vacation 1-1-2011 and number of days vacation 10 not including the Friday, Saturday days

View 4 Replies View Related

SQL & PL/SQL :: Calculate Outage Time For Each Day

Jul 8, 2010

We have a table which contains the outage data as below,

TAG CI_NAME OUTAGE_START OUTAGE_END
IM10366 FD0004 06-jul-2010 10:00:00 10-jul-2010 10:00:00

so from the above record, the outage is from 6th - 10th July. So for the given TAG & CI_NAME, we have to calculate the outage for day. So the above record should be split as below

TAG CI_NAME O_START O_END TA_PERIOD OUT_HRS
IM10366 FD0004 06-jul-2010 10-jul-2010 06-jul-2010 14HRS
IM10366 FD0004 06-jul-2010 10-jul-2010 07-jul-2010 24HRS
IM10366 FD0004 06-jul-2010 10-jul-2010 08-jul-2010 24HRS
IM10366 FD0004 06-jul-2010 10-jul-2010 09-jul-2010 24HRS
IM10366 FD0004 06-jul-2010 10-jul-2010 10-jul-2010 10HRS

View 1 Replies View Related

SQL & PL/SQL :: Calculate With Calculated Fields?

Feb 24, 2010

can I use calculated fields (b) for another calculation (c) in the same select?

select 10*a as b,
10*b as c
from tab

The code gives an error, but how can I do this?

View 12 Replies View Related

SQL & PL/SQL :: Calculate Sanctions To Employee

Aug 1, 2012

i have two tables

first table
employees
emp_no pk
emp_name
emp_salary
emp_hiredate
emp_birthdate
-------------------
second table
sanctions
sanc_id
sanc_name
emp_no fk

i need the following

if employees take sanctions and his salary 1000 and sanctions value 50 then the net salary is 950 . I need if the same employee take another sanctions values 50
the result will be
the old net salary is 950
and the new old salary is 900

View 17 Replies View Related

SQL & PL/SQL :: PMT Function Which Calculate Payment

Dec 14, 2010

PMT function which calculates the payment for a loan based on constant payments and a constant interest rate. I need to build PMT function in oracle.

View 2 Replies View Related

Doesn't Calculate Any Excel Functions

Jul 26, 2010

My applications is integrate with Excel 2003 by Ole2 package. Some of them excel books are using functions activates with Excel Complements (activate with men options: Tools-Complements-Analyisis Complements and Analysis complements VBA).

The problem is when I invoke this excel book in Oracle Forms, this functions don't work.

View 3 Replies View Related

SQL & PL/SQL :: Cursor Calculate Number / Average / If

Dec 15, 2011

I have to procedure that computes number of project, and average working hours of employees where employee id is passed as a parameter to the procedure. If the average working hours is less than 10 then employee's salary remain the same, otherwise check if number of project is less than 4 then 5% of salary, else 10% of salary is added to the salary.

my tables are:

CREATE TABLE employee(
empid number(5),
empname varchar(20),
address varchar(20),
no_of_dependents number(5),
deptno number(5),
CONSTRAINT EMPLOYEE_PKEY PRIMARY KEY(empid),
CONSTRAINT EMPLOYEE_FKEY FOREIGN KEY(deptno) REFERENCES department(deptno));
[code]...

the problem is i have to use only 2 cursors - 1 for select and 1 for update.

View 27 Replies View Related

SQL & PL/SQL :: Calculate Difference Between Multiple Numbers

Feb 28, 2013

I need to subtract multiple numbers to get the difference between each numbers, the amount of numbers to subtract between each others can vary between 2 and 10.

create table table_1 (
col1 varchar2(5),
col2 number(6,0),
col3 number(12,0)
)

insert into table_1 values ('AAA', 34379, 11111);
insert into table_1 values ('AAA', 39032, 11111);
insert into table_1 values ('AAA', 54337, 11111);
insert into table_1 values ('AAA', 78005, 11111);

insert into table_1 values ('AAA', 66793, 22222);
insert into table_1 values ('AAA', 74323, 22222);
insert into table_1 values ('AAA', 81426, 22222);

Expected Output:

col1 col2 col3
AAA -4653 11111
AAA -15305 11111
AAA -23668 11111
AAA -7530 22222
AAA -7103 22222

View 6 Replies View Related

SQL & PL/SQL :: Calculate Time Difference Between 2 Date

Nov 29, 2011

during application migration, i got one table from MS Access, and have situation where two events are splited into 4 columns (start: date1 time1 and stop: dat2 and time2). How to properly calculate duration between these two events, and show it in format: hh:mi ?

CREATE TABLE ACCBTP_DCZASTOJ
(ID NUMBER(11,0),
NASTANAK_KVARA DATE,
VRIJ_NASTKVARA DATE,
VRIJEME_OPRAVKE DATE,
DATUM_OPRAVKE DATE);

[Code]....

View 6 Replies View Related

SQL & PL/SQL :: How To Calculate Highest Common Factor

Oct 5, 2005

Is there any function avaialble in SQL that can return the highest common factor among a bunch of numbers. For example
10,20,25 have a highest common factor of 5.

View 9 Replies View Related

SQL & PL/SQL :: Calling Function Which Calculate Sum Of Salary

May 8, 2010

I created a PROCEDURE in that i am calling function which calculate sum of salary...I just want Output in format for that which function i need to use...?

Actual Output:::

DEPt_Name SALARY
ACCOUNTING 8750
RESEARCH 10875
SALES 11600

I want Output in well alignment column...i WANT Output IN column format but my output in not geting in that format...Is there any function to align output...I want Output in well alignment column

View 4 Replies View Related

How To Calculate Memory Used By Each Databases In A Server

Aug 27, 2013

we have 7 databases running in a server .      

OS : solaris     

i want to add a new database on that server . So i want to find , whether we have a sufficient memory to add a new database . how to find it ?

View 5 Replies View Related

SQL & PL/SQL :: Reuse Calculate Column In Same View?

Sep 15, 2011

Correct example

select
pp.IVA
, pp.CNR
, pp.POLO
,(pp.CNR-pp.POLO) diff
, ((pp.CNR-pp.POLO) * pp.IVA / 100) + (pp.CNR-pp.POLO) diva
from AB_PERIODI_PREZZI pp

Invalid example

select
pp.IVA
, pp.CNR
, pp.POLO
,(pp.CNR-pp.POLO) diff
, ((diff * pp.IVA / 100) + diff) diva
from AB_PERIODI_PREZZI pp

In my real view, the diff calculate column is very long (with case etc...) I must reuse many time the diff calculate column to calculate other columns.I do not want use subquery because there is many join to do in the same query. How can I reuse a calculate column in the same view?

View 1 Replies View Related

SQL & PL/SQL :: Procedure To Calculate Summary Session?

Aug 13, 2013

I have a below requirement from client.

Create a procedure generate_daily_session_summary that will read the pcm_session_summary (pk sess_id) table and create a per day summary in the pcm_session_summary_daily table.

1. Summarize all records from pcm_session_summary for session start timestamp (START_TS) greater than the max existing SESS_DT from pcm_session_summary_daily table and less than the start of current day. Insert the records into the pcm_session_summary_daily table.

2. Populate the fields in pcm_session_summary_daily as follows :

sess_dt - truncated date from pcm_session_summary.start_ts unique_user_cnt - count of unique cws ids encountered in that day, sess_cnt number - count of total sessions in that day , max_concurrent_sess_cnt number - maximum number of concurrent sessions at any point of time in that day. Concurrent sessions are defined as those that have overlapping start_ts or end_ts - sessions that were active at the same moment in time would be concurrent with each other.

3. The procedure will be scheduled to run every day in a batch job and will summarize session data until the end of the previous day

I have the table with below details.

desc PCM_SESSION_SUMMARY
Name Null Type
---------- -------- ------------
SESS_ID NOT NULL NUMBER
USER_CUPID VARCHAR2(10)
START_TS TIMESTAMP(6)
END_TS TIMESTAMP(6)
ACT_CNT NUMBER

[code]....

I have written the below query to get the result for question no:1

select trunc(start_ts,'DDD') start_ts,count(distinct(user_cupid)) user_cnt,count(SESS_ID) Sess_cnt
from PCM_SESSION_SUMMARY
where START_TS < sysdate or START_TS > (select max(sess_dt) from pcm_session_summary_daily where sess_dt < sysdate)
group by trunc(start_ts,'DDD')
order by start_ts;

However i tried a lot but not sure how to get maximum concurrent session count for a day.One of my friend comment below.

The Pcm_session_summary_daily is empty so you need to handle nulls when doing max. That will fix the issue of no results for the Greater Than condition.

You need to get concurrent count of sessions with respect to one another, the query that you have will not work. Do it in PL/SQL using procedural logic. The queries would return multiple rows, you need to handle that.

View 3 Replies View Related

SQL & PL/SQL :: Calculate Count Of Range By A Query?

Jun 30, 2010

I have a table which contains two columns containing the range of some products. These range are alphanumeric and i want to calculate the count of this range by a query. .

test case:
CREATE TABLE MARRIAGE_FILE_NAME
(
START_SERIAL_NO VARCHAR2(10 BYTE) NOT NULL,
END_SERIAL_NO VARCHAR2(10 BYTE) NOT NULL,
CATCODE VARCHAR2(10 BYTE) NOT NULL,

[code]....

View 2 Replies View Related

PL/SQL :: How To Calculate Interest Percentage Dynamically

Feb 24, 2013

look at these tables (Customer and Rates), having some sample data. I've created a function for returning the interest percentage for a particular customer based upon his/her investment start date and end date. I'm getting the desired result but I'm wondering if I need calculate the composite interest here...then how can I do that? Suppose a customer has invested some amount for 45 days, then I need to calculate the interest for initial 30 days as 5% and additional 15 days as 6% and I need to give the final amount. In this case how can I pull this task off? how can we do this interest calculation by an Oracle program?

Customer Table
CUSTOMER_ID     AMOUNT     START_DATE     END_DATE
1000             40000     15-JAN-13     15-FEB-13
1001             34000     15-DEC-12     15-FEB-13
1002             35678     15-NOV-12     15-FEB-13

Rates Table

RATE_PCT     LOW_TENURE     HIGH_TENURE
5                    0     30
6                    0     60
7                    0     90CREATE OR REPLACE FUNCTION
F_INTEREST_PCT (V_CUSTOMER_ID IN CUSTOMER.CUSTOMER_ID%TYPE)
  RETURN NUMBER AS V_RATE_PCT NUMBER;
 
[code]...

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved