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


ADVERTISEMENT

SQL & PL/SQL :: To Get The Results In The Form Of A Tree

Nov 25, 2012

My table structure is like this

CREATE TABLE Tab1(LVL1_NM VARCHAR2(20), LVL2_NM VARCHAR2(20), LVL3_NM VARCHAR2(20), LVL4_NM VARCHAR2(20))

and the values will be for one value of LVL1_NM there will be different LVL2_NM values like wise for each and every value of LVL2_NM column there will different values in LVL3_NM column like wise for LVL4_NM is there any way to get the results as per their relation ships.

View 7 Replies View Related

Partial Multibyte Character

Sep 5, 2013

Getting below error while select statement execution. I have searched in google and oracle But didn't find satisfication answer. how to resolve this issue on database level.

Oracle Versin: 11.2.0.2
Error: ORA-29275: partial multibyte character

View 1 Replies View Related

PL/SQL :: Partial Multibyte Character

Sep 12, 2012

I'm trying to

select name from test1@remote;and hit ORA-29275: partial multibyte character. I also tried

select CONVERT(name,'AL32UTF8','UTF8') from test1@remote;and

select UTL_RAW.CAST_TO_RAW(name) from test1@remote;but still hitting the same error.

My database is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production.

View 1 Replies View Related

Add Partial Primary Key Into Oracle Table?

Oct 17, 2006

how to add a primary key into the oracle table but how do i add a partial primary key?

View 2 Replies View Related

ORA-29275 / Partial Multibyte Character

Jul 12, 2013

I am getting "ORA-29275: partial multibyte character" error when I try to read records from a table.

How can I identify the effected record(s) and how can I fix the data in the effected record(s)?

View 1 Replies View Related

Application Express :: Partial Page Refresh PL/SQL Region

Aug 13, 2013

I have an APEX page with a master and a detail. The master is a classic report with a column link which sets an hidden item on my

page:javascript:$s('P280_DAK_ID','#DAK_ID#'); 

I have a dynamic action which reacts on change of the item P280_DAK_ID.It then refreshes the detail region which is a PL/SQL region with the following:

begin apex_p280.show_xml(p_dak_id => v('P280_DAK_ID'));end;

This prints a few pieces of text to the screen, nothing special. Everything works except the refresh of the detail region.This is due to the fact that the new value of P280_DAK_ID is not in the session state yet. So my question is: what would be a nice solution to set the item in the session state without a submit? With a normal region I could fill in 'Page items to submit', but with a pl/sql region I can't. 

View 1 Replies View Related

Server Utilities :: Sql-524 / Partial Record Found At End Of Datafile

Aug 1, 2010

I have an excel table whose data i have to transfer in oracle.

I did the following steps:

1) converted excel data in CSV format.

2) Created a control file test.ctl

LOAD DATA
INFILE 'C:IDB Price List.csv'
BADFILE 'C:IDB Price List.bad'
DISCARDFILE 'C:IDB Price List.dsc'
INSERT INTO TABLE idb_price_list
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(group_no,type,description,amt,flag)

3) when i run the sql loader utility, i get an error "sql-524: partial record found at end of datafile"

View 5 Replies View Related

SQL & PL/SQL :: Partial Data Inserted When Reading From Global Temporary Table?

Jun 25, 2012

I have a complex sql query that fetches 88k records. This query uses a global temporary table which is the replica of one of our permanent tables. When I do Create table..select... using this query it inserts only fewer records. But when I make the query point to the permanent table it inserts all 88k records.

1. I tried running the select query separately using temp and perm table. Both retrieves 88k records.

2. From debugging I found that this problem occurred when we were trying to perform a left outer join on an inline view.

However this problem got resolved when I used the /*+ FIRST_ROWS */ hint.

From my limited oracle knowledge I assume that it is the problem with the query and how it is processed in the memory.

View 1 Replies View Related

Server Utilities :: IMP-00018 - Partial Import Of Previous Table Completed

Oct 11, 2011

I am trying to import the schema into 11g database, which i took on Oracle 9i database. While import is running, data file is full as auto extension was not enabled.

I got the following error:

. . importing table "WO_GL_ACCOUNT_SUMMARY"
IMP-00058: ORACLE error 1653 encountered
ORA-01653: unable to extend table PWRPLANT.WO_GL_ACCOUNT_SUMMARY by 1024 in tabl
espace PWRPLANT
IMP-00018: partial import of previous table completed: 7055845 rows imported.

Then I increased the datafile size and finally Import terminated successfully with warnings. At this point, I want to know whether WO_GL_ACCOUNT_SUMMARY Table was imported with out missing any rows .

View 7 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

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 :: 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 :: 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 :: 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

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

SQL & PL/SQL :: XML Query For Tree

Sep 20, 2013

I need to form XML for hirarchy data,

CREATE TABLE TESTING (
EMPID VARCHAR2(1),
ENAME VARCHAR2(10),
CHILD_ID NUMBER,
PARENT_ID NUMBER);

INSERT INTO TESTING VALUES ('A', 'AText', 1001,0);
INSERT INTO TESTING VALUES ('B', 'BText', 1002,1001);
INSERT INTO TESTING VALUES ('C', 'CText', 1003,1002);
insert into testing values ('D', 'DText', 1004,1003);

SELECT LEVEL, EMPID,ENAME,CHILD_ID, PARENT_ID
FROM TESTING
START WITH PARENT_ID = 0
CONNECT BY NOCYCLE PRIOR CHILD_ID = PARENT_ID;

Need select query to return output like below

<ITEM ID="A" TEXT="AText" IM0="folderClosed.gif" CHILD="1">
<ITEM ID="B" TEXT="BText" IM0="folderClosed.gif" CHILD="1">
<ITEM ID="C" TEXT="CText" IM0="folderClosed.gif" CHILD="1">
<ITEM ID="D" TEXT="DText" IM0="folderClosed.gif" CHILD="1">
</ITEM>
</ITEM>
</ITEM>
</ITEM>

View 1 Replies View Related

Structure In Tree Query

Sep 8, 2012

Explain the following query to me:

in particular Lpad (' ', Level-1)*3.

select lpad(' ' , (level-1)*3) || emp_last_name employee
from emp
connect by supervisor_emp_id = prior emp_id
start with supervisor_emp_id is null;

View 1 Replies View Related

SQL & PL/SQL :: Bitmap Or B-Tree Index?

Apr 9, 2010

I was executing following query and it was taking about 20 sec's to execute before i decided to create B-Tree(Normal) index on column DeliVery.

Select
DeliVery,Code,Sum(Units),Sum(Loads),Count(units),
Count(Loads)
Where

[code]...

After creating B-Tree index on Column Delivery the execution time has been reduced to one second ,thats what i wanted. But If i create Bitmap index on the same column then execution time is not getting reduced and is still same around 20 sec.I think theoratically "Delivery" column is the right candidate for Bitmap index? whether should i create bitmap index or stick with B-Tree index?

View 14 Replies View Related

Forms :: Hierarchical Tree In 6i

Dec 24, 2012

Hierarchical Tree in oracle form 6i .i am trying to build tree in form 6i but it give error

SELECT 1,
level,
job
,
'',
ename
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
order by level

i want to show job wise employees ...

View 4 Replies View Related







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