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
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
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.
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
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)
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
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 .
--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,,,,,
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.
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]....
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?
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
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
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
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.
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.
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.
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 ?
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.
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
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?
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.
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,
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?
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;