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


ADVERTISEMENT

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

PL/SQL :: Calculate Business Hours Between Two Dates Within A Cursor

Jul 24, 2013

I need a similar function to determinate difference between two dates, but i need other business hours; Monday - Friday: 9:00 - 21:00 (this is OK)Saturday: 09:00 - 14:00  (and this is my problem, how to add this condition in this function) 

View 4 Replies View Related

Syntax To Calculate Number Of Days Difference

Sep 27, 2010

setting up the query/correcting the syntax below so that it calculates the 'number of days difference' between whatever the 'Biggest Date' field value is and whatever the 'current date' is using the 'sysdate'. So far, I've only managed to get the query to calculate the number of days difference (days past due) between the 'need date' and 'estimated delivery date'.

CODESELECT
To_Date(need_date, 'YYYYMMDD') Need_Dt,
To_Date(Case when estimated_delivery > ' ' THEN estimated_delivery ELSE need_date END, 'YYYYMMDD') Biggest_Date,  
To_Date(need_date, 'YYYYMMDD') - To_Date(Case when estimated_delivery > ' ' THEN estimated_delivery ELSE need_date END, 'YYYYMMDD') Date_Diff
        
FROM tableT

WHERE
need_date <=  (Case when estimated_delivery > ' ' THEN  estimated_delivery ELSE need_date END)

ORDER BY Date_Diff ASC

View 6 Replies View Related

SQL & PL/SQL :: To Calculate Minutes From Difference Of To Number Fields

Apr 9, 2013

I 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

View 8 Replies View Related

Server Administration :: How To Calculate Number Of Hash Keys

Mar 31, 2011

I was reading the documentation for oracle 11gr2, with reference to URL>.....

The following examples show how to correctly choose the cluster key and set the HASH IS, SIZE, and HASHKEYS parameters. For all examples, assume that the data block size is 2K and that on average, 1950 bytes of each block is available data space (block size minus overhead).Note that 34 hash keys are assigned for each data block

how they arrive at 34 hash keys because another portion of the document states

This space determines the maximum number of cluster or hash values stored in a data block. If SIZE is not a divisor of the data block size, then Oracle Database uses the next largest divisor.

if that is the case, then number of hash keys should be 1900/55 = 34.55 which should have rounded up to 35.

View 1 Replies View Related

SQL & PL/SQL :: Oracle Stored Function / Procedure To Calculate Number Of Working Days Between Two Dates

Feb 17, 2010

I want Oracle stored function/procedure to calculate number of working days between two dates. We need to exclude Firdays and Saturdays as there are weekend holidays and also exclude official holidasy that lie between two dates.

View 7 Replies View Related

SQL & PL/SQL :: Cursor For N Number Of Columns

Apr 21, 2010

in retrieve column data in the cursor.My requirement is I created a table dynamically as I don't know how many fields will be there.And the table structure would be like this

Filed1 varchar2(10)
Filed2 varchar2(10)
-----------
-----------
Filedn-1 varchar2(10)
Filedn varchar2(10)
[code]...

As in the cur.filed value.

View 1 Replies View Related

SQL & PL/SQL :: Reference Cursor To Generate Sequence Number

Apr 1, 2013

I want the query to generate the sequenctial number from 1 for the new financial year in some query.

View 6 Replies View Related

PL/SQL :: Forming Number Type List In A Cursor?

Jul 12, 2013

we use oracle 11g. 11.2. I have a situation where in a pl-sql package, there is a modification needs to be done . Every User is associated with destination codes. For eg, user1==>0101,0104  There is a  destination_number and country field in the table1.Each of the destination number is associated with a country code and this combination is unique. for eg: 

dest_code   country_code 0101 CHINA 0202 UK 0101 Mexico 0104   Mexico 

For example, if user1 logs in to the system, then pl sql logic will look if that dest_code is in a table1,If the dest_code is present in the table1, then that dest_code number wont be added to the dest_code list formed for that user. For example,if user1 got dest_codes  0101,0104  associated with him.IF 0101 is in exception table then 0101 and 0104wont be used to form dest_code list. If NOT, a dest_code list will formed like 0101||0104. I am able to form the logic like I have a cursor that loops for each dest_code.For each dest_code , it will get that dest_code  into v_dest_code_no number type variable. open cursor fetch dest_code  no into v_dest_code  v_dummy number:=0;v_dest_code_list number:=0; LOOP v_dest_code_list:=v_dest_code ||v_dest_code ;  v_dest_code :=0; END LOOP:  How to make sure that for each dest_code set, if one of them is in table1, then the next number also will not be added to the list.

View 1 Replies View Related

SQL & PL/SQL :: How To Pass Output Of Regexp_substr As Number Input To A Cursor

Sep 13, 2012

I am using regexp_substr to break the pipe delimited string. Want to use the output as the NUMBER input to a cursor.Its not working and not getting any error also.

Here is an example.

initial input : '5545|4124|12456'
using the sql below to break the string into columns.
select regexp_substr('5545|4124|12456','[^|]+', 1, level)) from dual
connect by regexp_substr(, '[^|]+', 1, level) is not null;

But when i try to pass the output of the above query to a cursor with input defined as number, its not accepting. I tried using to_number for the output of above query.

View 12 Replies View Related

Does Column V$SQL.CHILD_NUMBER Related On Number Of Current Cursor Only

Jan 11, 2013

Does the column V$SQL.CHILD_NUMBER related on number of current cursor only ? V$SQL lists statistics on shared SQL areas without the GROUP BY clause and contains one row for each child of the original SQL text enteredI see in V$SQL big numbers in V$SQL.CHILD_NUMBER ( HASH_VALUE and PLAN_ HASH_VALUE is in my quewry fixed ) , am I wrong that this column displayed not summary count of "objects"(in terminology of Steeve. URL....

View 0 Replies View Related

Bulk Collect Cursor That Can Reduce Number Of Context Switches

Oct 4, 2008

Create a table with 100 records.Then write a BULK COLLECT Cursor that can reduce the number of context switches by using a bulk fetch to query records in sets or all at once.

View 1 Replies View Related

SQL & PL/SQL :: Average Over Time

Sep 27, 2011

I have a table with products (xPRODUCT), dates (xDATE) and parameters (xPARAMETER). Now I want to calculate the rolling 12 month average of the parameters over all products for all dates. I tried this:

select xDATE,
avg(xPARAMETER) over ( order by xDATE
range between numtodsinterval(365,'day')
preceding and current row )

[Code]....

... but this does not work. How can I do this?

View 5 Replies View Related

SQL & PL/SQL :: AVERAGE By Hour

May 9, 2010

I wish to query the pro_vitual_bytes by hourly average by server name from 04/01/2010 to 04/02/2010. how i going to perform the query.

SERVER_NAME DATE_TIME PRO_VITUAL_BYTES

MLYORABA04/01/2010 00:00:402,238,312,448.00
MLYORABA04/01/2010 00:01:402,241,458,176.00
MLYORABA04/01/2010 00:02:402,238,312,448.00
MLYORABA04/01/2010 00:03:402,241,458,176.00

[Code]....

View 4 Replies View Related

SQL & PL/SQL :: Average Of Time In HH:MM:SS Format

Jun 17, 2010

how to calculate the average of the time in th e HH:MM:SS format stored database table.. column contains hundreds of time values and need to table the avergae of it

my col look like,and column is declared as timestamp(6).

MY COL
------
1:13:00
1:06:00
0:43:00
0:47:00
0:32:00
0:19:00
0:39:00
0:46:00
0:56:00
1:39:00

[Code]...

View 29 Replies View Related

PL/SQL :: Get Sum Average And Projected Totals In Same SQL

Nov 23, 2012

Version : 11g

I have a table with the following format and data

Serial no    exp_Date  exp_type   exp_amount
1              01-nov-2012   Rent       10000
2              02-nov-2012   gas          250
3              02-nov-2012   insurance   9500
.
.
.

I want to create a sql output for a yearly view in the format

   exp_type        JAN            FEB      MAR        APR ..... NOV  DEC          TOTAL     AVERAGE    PROJECTED
    Rent             10000        10000   10000     10000   10000  10000      120000     10000        120000
    gas                250             250     250        250      250      250          3000         250           3000
.
.

Now a couple of things in this

1. the average gives the average for the year, so lets say its start of 2013 and we are in feb, there will not be any values for the remaining months, so it should do the average for that exp_type for Jan and Feb based on the exp_amount entered against that type and show what is the expected average. Similary, projected will that average amount and mulitply it by 12 to show the exp amount expected based on current expenses

I was able to come up with the following sql to get the sum based on months, was not sure about average, total and projected

SELECT       exp_type
,        SUM (CASE WHEN to_char(exp_date,'Mon') =  'Jan' THEN exp_amt END)     AS Jan
,       SUM (CASE WHEN to_char(exp_date,'Mon') =  'Feb' THEN exp_amt END)     AS feb
,       SUM (CASE WHEN to_char(exp_date,'Mon') =  'Mar' THEN exp_amt END)     AS Mar
,       SUM (CASE WHEN to_char(exp_date,'Mon') =  'Apr' THEN exp_amt END)     AS Apr
,       SUM (CASE WHEN to_char(exp_date,'Mon') =  'May' THEN exp_amt END)     AS May


[Code]....

getting the correct avg, total and projected fields also in the same sql?

View 8 Replies View Related

Load Average In AWR Report?

Jul 26, 2013

,I've some database in 11.2 RAC on AIX.I was analyzing the root causes of eviction.Looking AWR Report before the reboot I see: 

DB1  Host CPU (CPUs:    6 Cores:    3 Sockets: )~~~~~~~~         Load Average               Begin       End     %User   %System      %WIO     %Idle           --------- --------- --------- --------- --------- ---------                4.18     12.33     60.9      12.6       1.6      26.5  Instance CPU~~~~~~~~~~~~              % of total CPU for Instance:      27.4              % of busy  CPU for Instance:      37.3 %DB time waiting for CPU - Resource Mgr:      10.6  DB2   Host CPU (CPUs:    6 Cores:    3 Sockets: )~~~~~~~~         Load Average               Begin       End     %User   %System      %WIO     %Idle           --------- --------- --------- --------- --------- ---------                3.77    13.93     60.7      12.5       1.6      26.7  Instance CPU~~~~~~~~~~~~              % of total CPU for Instance:       6.9              % of busy  CPU for Instance:       9.5  %DB time waiting for CPU - Resource Mgr:       0.0 

Do you think these value ar high? This is vmstats at the time of reboot:

  DATARUNBCKAVMFREPREPPIPPOPFRPSRPCYFINFSYFCSCUSCSYCIDCWA07/21/2013  00:08:173107.400.345579.92308100003.292187.01019.56084160007/21/2013  00:08:171717.390.187589.884017600003.681169.99421.48281190007/21/2013  00:08:172717.402.121577.816011500003.150157.21018.50384160007/21/2013 

[code]...

View 3 Replies View Related

PL/SQL :: Sql Query To Build Average Value

Jul 18, 2013

I'm trying to build a query capture the below information. Table looks something like below . 

RoomSubjectStudentsA1Science10A1Maths20B1Science15B2English25C1Deutsch20A1French15C1English10 

How can i write a query so that i takes the average of students in each room and display the output? I need to write a query so that it also picks up any new rooms and students added.  Average is sum of total students in room by number of times each room is specified.  

eg: A1 is listed 3 times a the result would be 10+20+15 divided by 3.    

A1    B1    B2    C115    15    20    15 

View 7 Replies View Related

SQL & PL/SQL :: Conditional Average Calculation Of Column?

Jul 22, 2012

I want to write a sql qeuery to get result similar to expected average column as shown in sheet.

Record can be uniquely identified by columns ID, PRODUCT and OFFICE. I want to calculate average for each date considering the same record does not exists earlier i.e. I want to consider the latest vote while calculating average. E.g.

Average till date 1/1/2011 is Avg (2, 4, 3) = 3 (as no repeating vote value exists)
Average till date 2/1/2011 is Avg (4, 3, 5, 3) = 3.75 (excluding vote value 2 as 122_UK_LONDON was provided his vote earlier, so considered latest vote value i.e. 5)
Average till date 3/1/2011 is Avg (3, 5, 3, 6, 5, 8 ) = 5 (excluding vote value 2 for 122_UK_LONDON and vote value 4 for 967_Europe_London)

View 15 Replies View Related

SQL & PL/SQL :: Analytical Query Moving Average

Aug 10, 2013

I need to calcaulate the salary avarage for three days prior, leaving the current row. That should happen to every row moving back words.I have given all the details.

create table Employee(
ID VARCHAR2(4 BYTE) NOT NULL,
name varchar(20),
Start_Date DATE,
Salary Number(8,2),
mv_avg number(8,2)
[code]....

View 17 Replies View Related

SQL & PL/SQL :: Convert Query To A Rolling Average?

May 2, 2013

I have the following query that creates sums by week.

SELECT cidterr.rnam,
cidterr.rnum,
cidterr.tnam,
cidterr.tnum,

[code]...

I want to convert this query to just return a single line for -cidterr.rnam, cidterr.rnum, cidterr.tnam, cidterr.tnum

With an average sum by week. Similar to how if I did a sum by week from the original query and placed the results into an excel pivot and said show total as average.

View 3 Replies View Related

PL/SQL :: Average In Tree With Partial Results

Feb 27, 2013

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE     11.2.0.1.0     Production"
TNS for HPUX: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

I have a problem related with hierarchical queries, I have this tree.
-    a
    /
   b   c
/ |
d  e  fI need to calculate an average per node but using always the child's results for example: Only leafs will have values:

d = 10
e = 20
f = 30
c = 40So the expected result is:
d = 10
e = 20
f = 30
c = 40
b = (10(d) + 20(e) + 30(f)) / 3 (number of child nodes) = 20
a = (20(b) + 40(c)) / 2 (number of child nodes) = 30.

I have tried with recursive queries, hierarchical queries, I guess it's possible with model too but I can't produce the exact results that I need. Maybe in fact there is a very simple solution but I cannot figure it.

Here is an auxiliary WITH that you can use to start your tests:
WITH tree AS
(
     SELECT 'd' child, 'b' parent FROM dual UNION ALL
     SELECT 'e' child, 'b' parent FROM dual UNION ALL
     SELECT 'f' child, 'b' parent FROM dual UNION ALL
     SELECT 'b' child, 'a' parent FROM dual UNION ALL
     SELECT 'c' child, 'a' parent FROM dual UNION ALL
     SELECT 'a' child, null parent FROM dual
[code]....

View 3 Replies View Related

SQL & PL/SQL :: Average For Month (no Weekend Data In Table)

Jun 10, 2011

I've got values for each workday of month in table. Chief said we don't load values of weekend because of space and time economy. Weekend value is copy of last workday. How do I count average for month in this case? Technical task in Excel worksheet consist of each day of month, so average value is 14 689 262,86. I should get the same result in my query. My example below shows values, 0 is workday (present in table), 1 is weekend (absent in table)..

select 12230427, 0 from dual
union all
select 11960157, 0 from dual
union all
select 12965902, 0 from dual
union all
select 13939736, 0 from dual

[Code]...

View 31 Replies View Related

RAC & Failsafe :: Global Cache Average CR Get Time?

Mar 4, 2012

from last two weeks we are receiving rhe below database alert on our second NODE (RAC ENV).

Global cache Average CR get time " is at .61789
Threshold is set on >.5 (M.Second).

me to figure this out because i really dont know the nature of this alert. what value i can set for the warning threshold.

View 1 Replies View Related

PL/SQL :: Average / Standard Deviation Of Multi-Column Record

Jun 28, 2012

I want to read a record that has 6 columns

RECORD     |Col1     |Col2     |Col3      |Col4     |Average      |Standard Deviation     
0001____|Null_|5___|8___|10__|8.75___|2.986079

With SQL I want to calculate Average and Standard Deviation.

View 4 Replies View Related

Server Administration :: Sun Solaris - Load Average Too High At Specific Days

Oct 16, 2010

My problem is only on Some Saturday's my Oracle server's Load average goes high (more than 300 ).

-- oracle version 11.2.0.1.0
-- runs on Sun solaris 10
-- Sun fire V 440
-- Sun storEdge 3315 connected to the server.

Same setup is working find with higher volumes without any problem but only on saturday's, that too not on all saturdays, some specific saturdays the load average goes high.

At the time nothing will be processed from the application side and the cpu utilisation goes high upto 95 %.

I am sending necessary information as follows.

vmstat 6 10
kthr memory page disk faults cpu
r b w swap free re mf pi po fr de sr s0 s1 s3 s4 in sy cs us sy id
3 0 0 28660024 6513408 285 212 2044 2 2 0 0 0 17 0 42 830 3469 1380 22 5 74
125 0 0 29027480 6156256 2 6 34 0 0 0 0 0 5 0 10 791 53770 30278 83 17 0
125 0 0 29027680 6157496 29 140 21 1 1 0 0 0 5 0 9 786 52756 30309 83 17 0
116 0 0 29031600 6159896 24 125 0 0 0 0 0 7 3 0 5 819 54081 31069 83 17 0

[code]....

View 1 Replies View Related

Meaning Of Lines CPU Cores And 99th Percentile In Average Active Session

Apr 18, 2013

I´m monitoring a 11g database with OEM. I have a couple of questions regarding the Average Active Sessions chart:

- What does the line 'CPU core' means? The DB is running in a virtual server with 8 CPUs. However, the line 'CPU core' is in 1. Does it mean that Oracle is just using 1 CPU?
- What represents the '99th percentile' line? The chart shows several sessions above that line, something is not working well, isn't it?

View 4 Replies View Related

Precompilers, OCI & OCCI :: Pro*C - Cursor Leak With Cursor Array

Sep 7, 2007

I'm dealing with an ORA-1000 error in a Pro*C application where all the cursors are correctly closed (or so it seems to me).

Here is the code for a simple program which reproduces the problem:

Each cursor is opened in a PL/SQL package:

CREATE OR REPLACE PACKAGE emp_demo_pkg AS
TYPE emp_cur_type IS REF CURSOR;
PROCEDURE open_cur(curs IN OUT emp_cur_type, dept_num IN NUMBER);
END emp_demo_pkg;

[Code]....

While testing the initialization parameter open_cursors is set to 50.

It's my understanding that Oracle doesn't close the cursors until it needs the space for another cursor, which in my test case seems to happen when I enter a value of 50 or bigger for "number of loops". To see how oracle is reusing the cursors, while the test program is running I run SQL*Plus and query v$sesstat for the session that's running the test with the following sentence:

select name, value
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and sid = 7
and name like '%cursor%';

Even before I enter a value for number of loops I can see that the session opened 4 cursors and closed 2 of them:

NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 4
opened cursors current 2

Entering a value of 5 for number of loops yields

NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 11 <----- 7+
opened cursors current 8 <----- 6+

With a value of 30

NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 36 <----- 25+ (apparently, Oracle reused at least 5 cursors)
opened cursors current 33 <----- 25+

With a value of 47

NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 53 <----- 17+
opened cursors current 50 <----- 17+

Now I reached the upper limit set by the initialization parameter open_cursors.

Entering a value of 48, I get the ORA-1000 error.

ORA-01000: maximum open cursors exceeded
ORA-06512: at "SCOTT.EMP_DEMO

Since I open and close the cursor in the same loop iteration, I expect to find in every iterarion 1 explicit cursor and a number of implicit cursors (the PL/SQL call along with the so-called recursive cursors), but I don't expect the sum of all of them to be greater than 50. If my understanding is correct Oracle should be reusing the 50 cursors previously marked as "closeable", not raising the ORA-1000 error.

View 1 Replies View Related

SQL & PL/SQL :: Cursor With Bind Variable And Cursor Record

Feb 25, 2011

Is it possible to:

-define a cursor with bind variables
-get a cursor record from these cursor
-and pass the bind variable in the OPEN clause

Did'nt succeed as shown in the example.

SET SERVEROUTPUT ON SIZE 900000;
DECLARE
--works fine
CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<3;
--doesn't work
--CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<:1;
crec c1%rowtype;
BEGIN
--works fine
OPEN c1;
--isn't possible ?
--OPEN c1 USING 3;

[Code]....

View 3 Replies View Related







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