SQL & PL/SQL :: Calculate With Calculated Fields?
Feb 24, 2010can 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?
ADVERTISEMENT
SQL & PL/SQL :: To Calculate Minutes From Difference Of To Number Fields
Apr 9, 2013I need the query to calculate minutes from difference of to number fields. Test case is as below.
DROP TABLE tmp;
CREATE TABLE tmp
(
code NUMBER(4),
stime NUMBER(4,2),
otime NUMBER(4,2)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
[code]......
CODE STIME OTIME
---------- ---------- ----------
1065 20 19.49
1082 20 18.57
1279 19.3 18.59
2075 19.3 15.32
Required output is
CODE STIME OTIME HR_MIN MINUTES
---------- ---------- ---------- ------------- --------
1065 20 19.49 00 HR 21 MIN 21
1082 20 18.57 01 HR 03 MIN 63
1279 19.3 18.59 00 HR 31 MIN 31
2075 19.3 15.32 03 HR 58 MIN 238
Forms :: Calculate Difference Time Between Two Fields
Mar 24, 2011I have two different date in my payroll software,
1-Shift_date shift date *used to contain shift timings
2-Attendace_datedate *used to contain employee IN timings
As you all know that shift is a setup form, where user input data once in the starting of software so the shift_date can be "01/jan/2011 16:00 pm" but attendance loads daily and attendance field data can be in this form "24/mar/2011 16:15 pm"..Now I want to calculate difference time between these two fields therefore I used this statement
SQL> Select to_char(attendance_date,'HH24:MI') to_char(shift_date,'HH24:MI') from dual;
but it is showing error: ORA-01722: invalid number...I used hours/minutes format mask in my query because you can see there is a difference of dates between these fields and it will be increase in the coming future and I need late hours and minutes.
How Memory Is Calculated
Nov 16, 2012I am not a major member of performance tuning area.Performance team is saying DB utilizing high memory. DB team is telling DB utilizing low memory.
How it was calculated here ?
SQL & PL/SQL :: Calculated Column In 10g R2
Nov 19, 2012In my table there are 188 column , i compare these with other table to know which column values have changed. i use ora_hash to get hash value and then i compare with other table.
I want to create calculated column but i am unable to create as it says invalid data type.
alter table STG_TBLDISRUPTIONS add stg_hash as ORA_HASH (INTINTERNALID || VCHIMSNO || CHRFANDDNO || INTINCIDENTNO || VCHDESCRIPTIONFAULT
|| TININCIDENTTYPEID || TINLINEID || DTMINCIDENTFAULTSTARTDATETIME || DTMNOTIFICATIONDATETIME
|| DTMSTARTDATETIME || DTMENDDATETIME || DTMACTUALENDDATETIME || DTMDURATIONENDDATETIME
|| CHRFROMBRSSTATIONID || CHRTOBRSSTATIONID || VCHLOCATIONDESC || CHRDIRECTIONID || SINEXTRARUNNINGTIME
|| CHRPLATFORMNO || BLNMULTIPLESIGNAL || TINNOOFPEOPLETRAPPED || CHRMODEOFFAILURE || TINDAYOFWEEKTABLEID
[code].........
Forms :: Continuous Update Of Calculated Field?
Aug 6, 2013I have seen in probably VB or Dot net software being used at some premises that there "formula computation is live and interactive".
What i means that suppose a cashier has made invoice having total of 83$. Mow in the text box for entering amount received, the cashier is about to enter 100 but surprisingly the returned value/remaining value (total invoice - amount received) is being computer LIVE.
This is what happens.
1) The cashier entered 1 and the remaining value instantly showed 82 (83-1)
2) then on other key stroke of 0 by cashier and then then the remaining value instantly showed 73 (83-10)
3) then on other key stroke of 0 by cashier and then then the remaining value instantly showed 17 (83-100)
Hence a continuous updation/changing of the remaining value on every key stroke is observed.
Forms :: Populating Form With Calculated Values
Aug 19, 2010I have a form in which the fields are calculated fields. So i need to populate the form after calculating the values based on a database columns. For example if i have a table as below
COL A | Col B | Col C | Col D
2 3 4 5
3 4 5 6
I have 3 fields on my form which i need to populate such that field1 maps to col A. Field2 value is COL B * COL D for that value of COL A. Field 3 value is COLB * COL D for that value of col A.
I tried something like this
PROCEDURE NAME IS
cursor c1 is SELECT * FROM TABLEL;
begin
go_block('Block1');
clear_block(no_validate);
for i in c1 loop
:BLOCK.field1 := i.COL_A;
[code].......
this fetched all the records for field1 but for field2 and field3 it only calculates and populates the field row.
How Kernel Parameters Values Calculated In 10g
Feb 9, 2013*10g Oracle installation on OEL*
How Kernel Parameters values calculated in 10g
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
+# semaphores: semmsl, semmns, semopm, semmni+
kernel.sem = 250 32000 100 128
[code].......
Run the following command to change the current kernel parameters
/sbin/sysctl -p
** "/etc/security/limits.conf" **
* soft nproc 2047
* hard nproc 16384
* soft nofile 1024
* hard nofile 65536
My question I think 32 bit or 64 bit OS calculation will be same only. I want know how it was calculated ?
Server Utilities :: Calculated Column Population Using Sql Loader?
Aug 1, 2012I want to insert calculated data while loading the data from sql loader.
eg let say column INTERNAL_TRANSACTION_ID,WAIT_TIME,MESSAGE_GUID,TRS_SIZE,RETURN_MESSAGE_GUID are null in input file then i want to populate column DEV_FLAG as 'U' ELSE 'D'
My control file looks like below but doesn't have DEV_FLAG.
load data
BADFILE '/backup/temp/rajesh/RIO/BadFiles/FILENAME'
append into table TEMP_rio_RESP_TIME_LND
TRAILING NULLCOLS
(
INSTALLATION_ID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
TRANSACTION_ID CHAR
[code]....
Forms :: Frm-41375 - Cannot Set Queryable Property Of Calculated Item
Jun 10, 2013frm-41375: cannot set queryable property of calculated item :emp.sumsal.
View 8 Replies View RelatedCalculate On Itself
Jun 3, 2011only one table A(autoid, number)
how SELECT statment result: B(autoid, number, numberB)
numberB is sum of all the number have autoid > its autoid.
SQL & PL/SQL :: Package To Calculate Pay
Oct 20, 2010Trying 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
SQL & PL/SQL :: How To Calculate Exact Age
Oct 21, 2011How 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 RelatedSQL & PL/SQL :: How To Calculate Working Day
Jan 1, 2013I 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.
PL/SQL :: How To Calculate Value Column
Dec 21, 2012We 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)
How To Calculate Redo Rate
Nov 14, 2006how 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
SQL & PL/SQL :: How To Calculate Running Percentage
Mar 13, 2013I 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
Forms :: How To Calculate Average In 6i
Apr 5, 2011How 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 RelatedSQL & PL/SQL :: How To Calculate Proper Hrs Between Two Dates
Jun 25, 2011i 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.
SQL & PL/SQL :: How To Calculate Period Date
Sep 14, 2011Following 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,,,,,
SQL & PL/SQL :: How To Calculate Time Difference
Aug 10, 2003I 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 RelatedSQL & PL/SQL :: Calculate Balances For Each Month
Apr 8, 2013have 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]....
Forms :: Calculate Sum In A Form?
Nov 23, 2007how 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 RelatedSQL & PL/SQL :: Function To Calculate The Vacation?
Oct 31, 2011how 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
SQL & PL/SQL :: Calculate Outage Time For Each Day
Jul 8, 2010We 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
SQL & PL/SQL :: Calculate Two Parameters A And B Which Are Interdependent?
Mar 2, 2010I 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
SQL & PL/SQL :: Calculate Sanctions To Employee
Aug 1, 2012i 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
SQL & PL/SQL :: PMT Function Which Calculate Payment
Dec 14, 2010PMT 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 RelatedDoesn't Calculate Any Excel Functions
Jul 26, 2010My 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.
SQL & PL/SQL :: Cursor Calculate Number / Average / If
Dec 15, 2011I 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.