SQL & PL/SQL :: List Function (Aggregate)
Dec 13, 2010I have one table emp with columns id , name and dept. i want query to get the list like this........
ID name did
1 ajay 10,30,40,60
2 sree 10,30,70
ADVERTISEMENT
Aggregate Function - Combine Results
Nov 24, 2009I have a query more or less like this:
SELECT field1,
COUNT(x) AS COUNT
FROM my_table
GROUP BY field1;
For field1 I want to get a count, but if field1 is like 'ABC%' then I want to combine all of those.
So if I have the following:
ABC1 | 5
ABC2 | 10
XYZ1 | 3
I want results like this:
ABC | 15
XYZ1 | 3
I've tried using some case statements like
SELECT CASE
WHEN field1 LIKE 'ABC%' THEN
'ABC'
ELSE
field1
END AS field1,
COUNT(x) AS COUNT
FROM my_table
GROUP BY CASE
WHEN field1 LIKE 'ABC%' THEN
'ABC'
ELSE
field1
END;
but this just gives me
ABC | 5
ABC | 10
XYZ1 | 3
How can I combine record 1 and 2 from the last record set example above?
SQL & PL/SQL :: How To Write Analytic Function Without Aggregate
Mar 8, 2012Do we have analytic function equivalent of following?
select object_type,owner from dba_objects group by object_type,owner ;
I am trying to get a unique srno for a combination of a 2 fields - here object_type and owner
OWNEROBJECT_TYPESRNO
SYSVIEW1
SYSTABLE2
SYSPROCEDURE3
SYSTEMVIEW4
SYSTEMTABLE5
SYSTEMFUNCTION6
SYSTEMPROCEDURE7
SCOTTTABLE8
SCOTTVIEW9
.......................
also how can I get the SRNO?
I can' use sequence in the group by function and if I get equivalent analytic for above group by even then I can't write row_number as the order by gives detail record
I don't want to wrap this select inside other select
PL/SQL :: XML Aggregate Function And Out Of Process Memory
Aug 8, 2012I am getting most of the time 'out of process memory' when i run the 'ord' procedure.here i am providing the tables and the procedure.
i have 2 table orders which holds distinct values, and departments table has ordvalue column holds long string of values for a particular record from orders table. for example if the values in orders table as follows:
ord_code ord_level ordid ordstatus ord_num user utimestamp
SR11 1 2 A 101 V SYSDATE
SR11 1 2 A 102 V SYSDATE
SR11 1 2 A 103 V SYSDATE
SR11 1 2 A 104 V SYSDATE
SR11 1 2 A 105 V SYSDATE
SR11 1 2 A 106 V SYSDATE
SR11 1 1 B 101 R SYSDATE
SR11 1 1 B 102 R SYSDATE
SR11 1 1 B 103 R SYSDATE
SR11 1 1 B 104 R SYSDATE
SR11 1 1 B 105 R SYSDATE
SR11 1 1 B 106 R SYSDATE
ETC...
AT TABLE departments data will be like this
ord_code ord_level ordid ordstatus ord_num user utimestamp
SR11 1 2 A 101,102,103,104,105,106 V sysdate
SR11 1 2 B 101,102,103,104,105,106 R sysdate
from the get_ord procedure the data aggreates using the xmlelement and gets as a long string value into departments table of ord_num column.
CREATE TABLE test.orders
(
ord_CODE VARCHAR2(4 BYTE) NOT NULL,
ord_LEVEL VARCHAR2(1 BYTE) NOT NULL,
ordID NUMBER(5) NOT NULL,
ordstatus VARCHAR2(1 BYTE) NOT NULL,
ord_num NUMBER(3) not null,
user VARCHAR2(8 BYTE),
UTIMESTAMP DATE
[code]...
PL/SQL :: How Database Handles Record Locking When Aggregate Function Called
Jul 17, 2012how the DB handles record locking when an aggregate function is called? For instance:
...
select count(*)
into v_count
from x;
...Is there a lock maintained on table x for the duration of the transaction so no rows can be inserted or deleted?
PL/SQL :: Analytic Function To List Max
Oct 23, 2012I have oracle table has records like this clnt pno beg_dt end_dt load_ts
502852 02 01-NOV-93 31-OCT-94 01-AUG-12
502852 01 01-OCT-93 31-AUG-94 01-SEP-12
502866 01 01-JUN-90 31-DEC-90 01-AUG-12
256974 01 01-JAN-90 31-MAY-90 01-SEP-12
280441 01 01-JUN-96 31-MAY-97 01-AUG-12
[Code]....
I am writing an oracle query using analytic funtion to list that has max(load_ts)
But my query returns bad results and retrieve all the records for some reason.
select a.*
from
(
select
CLNT,
[Code]....
SQL & PL/SQL :: List Of Arguments Of A Function Or Procedure
Oct 18, 2011I would like to know if it is possible to get the arguments of a function (or procedure), in the same way as - for example - the "arguments" object in JavaScript.
I have seen the table ALL_ARGUMENTS, for example, that is providing a lot of interesting details, but I still miss the most important for me: the values of the arguments, not only the name, or type, etc...
Something that could be called as soon as the function (or procedure) is run, at the beginning of its process, and that would give a kind of array of arguments values.
PL/SQL :: Trim Extra Space In Function Parameter List
May 9, 2013I am calling a function in front end, from front end i am passing space, now i want to trim space in parameter itself, it's psssible, assume the below is the function defination, i want to trim the p_region_name parameter like this trim(p_region_name), is this possible?
FUNCTION add_country_region_column (
p_s_country_code_iso_2 IN varchar2_table_type,
p_type IN d_country.c1_type%TYPE,
p_name IN d_country.c1_name%TYPE,
p_desc IN d_country.c1_desc%TYPE,
[Code]....
SQL & PL/SQL :: Aggregate Between Two Dates
Jul 13, 2010I need to aggregate the data based the two dates criteria.
I have two tables
1. Table1
Number Date1 Date2
1 10-Jun-2010 30-Jun-2010
2 10-Feb-2010 30-Feb-2010
----------------------------------
2. Table2
Number Date Revenue
1 11-Jun-2010 100
1 09-Jun-2010 100
1 12-Jun-2010 100
2 11-Feb-2010 100
2 12-Feb-2010 100
2 13-Feb-2010 100
......................
......................
So on
Output:
Number Revenue Date2
1 200 30-Jun-2010
2 300 30-Feb-2010
Aggregate Two Columns
Oct 25, 2011I have two table and I want to merge them
TERMS_TABLE
ID | TERMS
309 | 'hardware'
309 | 'software'
309 | 'computer'
TFIDF_TABLE
ID | TERMS
309 |'computer,phone,mp3....'
Now I want to add TERMS column of TERMS_TABLE to terms column of TFIDF_TABLE but If TFIDF_TABLE already contains TERMS of TERMS_TABLE then I should not insert this term to the NEW_TFIDF_TABLE , like that
result should be:
NEW_TFIDF_TABLE
ID | TERMS
309 |'computer,phone,mp3....,hardware,software'
How can I do that ?
Tuning Aggregate Queries?
May 1, 2011I have performance problem with 7 queries involving groupby clauses in OLAP database.These are queries triggered during siebel DAC run
kumar[size="4"][/size][color="#0000FF"][/color]kumardba
SQL & PL/SQL :: Aggregate Functions On A Join
Nov 7, 2012I am trying to Show all Customers (custId, last name, first name) who bought cars at the Arctic AND Pacific branches and Order it by CustId.
These are the tables:
VIN CARTYP BRNAME BUYERID SALEDATE
------- ------ ---------- ---------- -----------
1JJ0001 CT9015 Arctic
1NN0001 CT9014 Arctic 10010 18-mar-2012
1PP0001 CT7013 Arctic 10007 25-sep-2012
1QQ0001 CT7012 Atlantic 10014 22-dec-2009
1RR0001 CT7011 College 10013 30-sep-2012
2JJ0001 CT9015 Pacific
2NX0001 CT9014 Pacific
2PP0001 CT7013 Arctic
2QQ0001 CT7012 Arctic
[Code]...
CUSTID LNAME FNAME BALANCE_DUE
---------- ------------ ------- -----------
10001 Disney Daisy 9000.5
10010 Dupont Caryn 40000
10006 Gordon Diane 75000.5
10005 Gordon Tracy 3000.25
10013 Gross Henry 50000
10015 Hilton Kate 40000
10011 Lea Diane 12000
10003 Lopez Athena
[Code]...
No matter how I write the query, I get the message that no rows have been selected.
SQL & PL/SQL :: De-Aggregate Year To Date Figures?
Apr 27, 2011I have a table as below:
Year Month Value
2011 01 15
2011 02 26
2011 03 34
[code]...
The value is an aggregate Year to Date Figure And I was wondering what the best method of splitting this data out into a Monthly Figure so that it would look like below:
Year Month Mth Value
2011 01 15
2011 02 11
2011 03 8
2011 04 9
[code]...
SQL & PL/SQL :: Aggregate Contiguous Range Of Days?
Oct 21, 2010I intend to get for every client the start date and end of a contiguous range of days. Example for the same client have two records, in the first goes from day 1 to day 5 and the second from day 3 to day 9, i intend to get a record for this client where indicated that the start date is on day 1 and ending on Day 9.
SELECT 123 as CLI_ID, TO_DATE('20100101', 'YYYYMMDD') as DT_START, TO_DATE('20100105', 'YYYYMMDD') as DT_END FROM DUAL UNION
SELECT 123 as CLI_ID, TO_DATE('20100208', 'YYYYMMDD') as DT_START, TO_DATE('20100321', 'YYYYMMDD') as DT_END FROM DUAL UNION
SELECT 123 as CLI_ID, TO_DATE('20100219', 'YYYYMMDD') as DT_START, TO_DATE('20100228', 'YYYYMMDD') as DT_END FROM DUAL UNION
SELECT 123 as CLI_ID, TO_DATE('20100227', 'YYYYMMDD') as DT_START, TO_DATE('20100405', 'YYYYMMDD') as DT_END FROM DUAL UNION
SELECT 123 as CLI_ID, TO_DATE('20100901', 'YYYYMMDD') as DT_START, TO_DATE('20101013', 'YYYYMMDD') as DT_END FROM DUAL
Expected :
CLI_ID,DT_START,DT_END
123,01/01/2010,05/01/2010
123,08/02/2010,05/04/2010
123,01/09/2010,13/10/2010
SQL & PL/SQL :: User-defined Aggregate Calculation For Z Score
Jun 29, 2011I'm calculating a Z score based on some simple numerical data thus:
create table t (id number, val number);
insert into t values(1, 1795);
insert into t values(2, 1753);
insert into t values(3, 1743);
insert into t values(4, 1876);
insert into t values(5, 1848);
[Code] .....
the logic is quite simple - calculate a moving average over the previous 12 rows, and a stdev over the same window. Then subtract the prior row's moving average from the current value, and divide by the prior row's stdev.
The issue is I want to expose this logic in a BI tool (OBI EE v10g), meaning I can't use the nested analytic functions. How to achieve this logic in a single analytic pass? The sql above took about 2 minutes to write this morning, then I've spent all day looking at user-defined aggregate functions, but haven't even been able to get the first step, the moving average, working. I can understand that I can probably create an udaf to replicate the avg(val) over (order by id ROWS BETWEEN 11 PRECEDING AND 0 FOLLOWING) functionality, but I can't see how to bundle the logic for the other three steps in the calculation into this.
From what I've read, the ODCIAggregateMerge should allow me to combine different threads that can return the different values I need for the current row calculation. Is this correct?
The only example udafs I can find are either not relevant (STRAGG) or very simple (ie don't appear to invoke multiple passes). I've also had a look at the COLLECT function, but again I can't see a way to use this.
SQL & PL/SQL :: Materialized View With Aggregate And FAST REFRESH
Feb 8, 2012I'm trying to build up a materialized view with aggregate and FAST REFRESH for INSERT and UPDATE, DELETE with no success. But the web doesn't deny it ?
--build up needed schema objects
CREATE TABLE empt
( ename VARCHAR2(20),
empno INTEGER,
[Code]....
SQL & PL/SQL :: SELECT Query For Displaying Aggregate Values?
Jul 1, 2011i have table with following data.
yearquartersales Revenue
2004Q145678
2004Q287456
2004Q356732
2004Q4120986
2005Q12398
2005Q23900
2005Q36522
2005Q42763
I want the output in following way.tell me the select query for this
yearquarterSales Revenue
2004 Q145678
Q287456
Q356732
Q4120986
2004 total Sales310852
2005 Q12398
[code]....
SQL & PL/SQL :: Maintaining Aggregate Column Data Integrity?
Jun 8, 2012What is the best practices to maintain aggregate columns? Suppose I have the following 2 tables:
DROP TABLE TEST.ORDERS_DET_T;
DROP TABLE TEST.ORDERS_T;
CREATE TABLE TEST.ORDERS_T (
ID NUMBER NOT NULL PRIMARY KEY,
ORDER_CODE VARCHAR2(100) NOT NULL,
[code]....
I want the following test script to act int the way, described in comments. Basically this means that the sum of TEST.ORDERS_DET_T.ORDER_DET_SUMA must be equal to TEST.ORDERS_T.ORDER_SUMA after transaction commits.
INSERT INTO TEST.ORDERS_T(ID, ORDER_CODE, ORDER_SUMA) VALUES(1,'FRUITS',100);
INSERT INTO TEST.ORDERS_DET_T(ID, ORDERS_T_ID, ORDER_DET_CODE, ORDER_DET_SUMA) VALUES(2,1,'APPLES',40);
INSERT INTO TEST.ORDERS_DET_T(ID, ORDERS_T_ID, ORDER_DET_CODE, ORDER_DET_SUMA) VALUES(3,1,'PEAT',60);
COMMIT; --SHOULD BE OK, 40+60=100
[code]....
P.S. Creating views based on ORDERS_T and ORDERS_DET_T are not an option, if the user would still be allowed to modify data in tables (as in test scenarios). This is because of current business situation, where the client has 2 teams (outsourcing for back-office solution and insourcing for web solution) that are accessing/modifying the data in the same tables.
SQL & PL/SQL :: Using Rank Clause With Window Aggregate Functions
Jan 5, 2012I am trying to use RANK() clause with a window clause...is that possible to use both together?
like
select col1, col2, col3,
RANK() OVER (ORDER BY col3 desc RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RK
from table t
but getting error in SQL Developer
ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:
Error at Line: 2 Column: 33
The reason why i need to rank in window clause because i have data like this
Name Marks Quiz
Ali 10 1
John 20 1
Sara 30 1
John 40 2
Sara 50 2
Ali 20 2
...
...
and so on
I want to rank them based on their cumulative sum marks after every test..ranking should be in such a way that it should look current row and preceding rows
like this
Name Marks Quiz cumulative_marks rk
Ali 10 1 10 4
John 20 1 20 3
Sara 30 1 30 2
Peter 100 1 100 1
John 40 2 60 3 ==> becuase John has now third most overall cumulative marks (60) after quiz 2.
Sara 50 2 90 2 ==> becuase Sara has now 2nd most overall cumulative marks (90) after quiz 2.
Ali 20 2 30 4 ==> becuase Ali has now fouth most overall cumulative marks (30) after quiz 2.
Reports & Discoverer :: How To Add Aggregate Functions Manually
Oct 30, 2010i want to create a blood donor report, which consists of his profile, donations_n_tests details, donor_to_patient tracking. each is a different table which i have imported to the layout.
now the problem is that for the 1st ever layout that i make via the report builder wizard, it offers me to choose aggregate functions (like sum, cout, avg, etc). but for the rest of the tables that i import into this layout via ADDITIONAL LAYOUT OPTION (report block. it does not offer me these summary functions.
actually i can only configure aggrigate functions via the report layout wizard, perhaps report builder is not that friendly like form developer where we can put a display item set calculation mode to summary and specify the column for the aggregate function.
Forms :: How To Avoid (no List Elements Defined For List Item) Error
Sep 27, 2011I am creating the Dynamic list but when i am compiling the form it gives the compilation error "No list elements defined for the list item".
I can eliminate it by entering the dummy list element but this dummy value will be displayed at form run time.
List Products List Of Client Grouped By Type Of Product?
Dec 14, 2011Im trying to list the products list of a client grouped by type of the product. Ex:
product type
prod.A acid
prod.B flavour
prod.C acid
prod.D cleaner
prod.E flavour
I want to list something as:
Acid
Prod.A
Prod.C
Cleaner
prod.D
Flavour
prod.B
prod.E
Forms :: FRM-30351 / No List Elements Defined For List Item
Oct 30, 2011DECLARE
CURSOR GRP IS
SELECT RowNum rn, Letter_Group_ID||'-'||A_Desc AName,Letter_Group_ID
FROM Hrs_Group;
BEGIN
Clear_list('Letter_Group_ID');
FOR I IN GRP LOOP
Add_List_Element('Letter_Group_ID',I.rn,I.AName,I.Letter_Group_ID);
end loop;
END;
FRM-30351: No list elements defined for list item.
List LETTER_GROUP_ID
Server Administration :: Materialized Aggregate View Index?
Jun 27, 2012When you create a MAV, you automatically get a hidden column and an index. Here's an example,drop user jon cascade;
grant dba to jon identified by jon;
conn jon/jon
create table emp as select * from scott.emp;
create materialized view mv1 enable query rewrite as
select deptno,sum(sal) from emp group by deptno;
select object_name,object_type from user_objects;
select index_name,column_name from user_ind_columns where table_name='MV1';
select column_name,hidden_column from user_tab_cols where table_name='MV1';
select deptno,"SUM(SAL)",sys_nc00003$ from mv1;
SQL & PL/SQL :: PLS-00653 - Aggregate / Table Functions Not Allowed In Scope?
Jul 8, 2010I have a function that returns the situation for one month for some database. I need to implement it in some report medium for one year. The one year function works ok.
My problem is when trying to make another function that runs the monthly function 12 times and that error is "PLS-00653: aggregate/table functions are not allowed in PL/SQL scope".am trying to get around some restrictions and somehow until this part things seem to be ok.
I tried to use a union with 12 blocks but it works very slow in the reporting environment and now i want to try to make another function that runs another function 12 times depending on the parameter.
here is the code (there might be some name misuse since i had to change the names of the original code -
CREATE OR REPLACE
FUNCTION anual(monthh in varchar2, year IN VARCHAR2)
return anual_REP_var PIPELINED is
BR anual_REP:=anual_REP(NULL,NULL, NULL,NULL);
contor INT(2);
[code]...
Application Express :: Interactive Report - Aggregate Sum Color
Jul 19, 2012In Interactive report, I want the sum of the column to be displayed in different colours if total is negative.
View 1 Replies View RelatedSQL & PL/SQL :: Changing Rows To Columns Without Using Pivot And Aggregate Functions?
Aug 13, 2013Changing rows to columns without using pivot and aggregate functions?
View 10 Replies View RelatedSQL & PL/SQL :: Stored Procedure With Multiple Table Join With Aggregate Functions
Jun 15, 2013I have 2 tables order_items and items.
Order_items Items
Item_id Item_id
Quantity Price
In normal sql statement: select sum(order_items.quantity*items.price) sales_price
from order_items,items
where order.item_id=items.item_id;
I have to put this logic in either a stored procedure or Function just to calculate sum(order_items.quantity*items.price) and store the aggregated value as Sales_price in DB. Then we have to call this from Informatica Stored procedure Transformation where we will have only one output port as Sales_price,this is to load into summary table. All the aggregate calculations and joining of 2 tables should be done on DB side and only one output should be populated when we execute this procedure.
Application Express :: Applying Aggregate To All Columns In Interactive Report?
Jun 25, 2012I have few reports that I want to have totals at the bottom of the report.
Is there any way I could apply totals to all columns without doing it one by one?
Ratio Between Global Memory Bound And Aggregate PGA Auto Target?
Aug 5, 2013SQL> select name,decode(unit,'bytes',value/1024/1024,value) as mb from v$pgastat; NAME MB---------------------------------------------------------------- ----------aggregate PGA target parameter 25600aggregate PGA auto target 2724.14648global memory bound 1024total PGA inuse 22601.7333total PGA allocated 26653.6230maximum PGA allocated
[code]....
I understand I have soft limit( aggregate PGA target parameter) which was overlimited (maximum PGA allocated = 35374.4638) hence we have over allocation count>0.Extra bytes read/written=13GB,hence we have excessive 13Gb that we had to flush on disk(excessive I/O operations) cause of limitation in 1024MB(global memory bound)(it's not enough to join or to sort something so we must do onepassor multipass) ,which defines the size of single operation of sort or join(so does it mean that it's some kind of sort_area_size and hash_area_size for automatic workarea_size_policy? and in this case what about _smm_max_size?)aggregate PGA auto target - is the amount of space(total) that Oracle can give for work areas running in automatic mode.
So I can't understand ratio between global memory bound and aggregate PGA auto target - why does the aggregate PGA auto target such tiny?(relatively process count)?Is the global memory bound static for particular aggregate PGA target parameter?
I can change it only by redefining aggregate PGA target parameter?What would be with aggregate PGA auto target if I started 10 sort operation and each of them takes about 1Gb of memory.How huge it would be? 10Gb?