SQL & PL/SQL :: PLS-00653 - Aggregate / Table Functions Not Allowed In Scope?

Jul 8, 2010

I 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]...

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Stored Procedure With Multiple Table Join With Aggregate Functions

Jun 15, 2013

I 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.

View 2 Replies View Related

SQL & PL/SQL :: Aggregate Functions On A Join

Nov 7, 2012

I 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.

View 3 Replies View Related

SQL & PL/SQL :: Using Rank Clause With Window Aggregate Functions

Jan 5, 2012

I 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.

View 11 Replies View Related

Reports & Discoverer :: How To Add Aggregate Functions Manually

Oct 30, 2010

i 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.

View 1 Replies View Related

SQL & PL/SQL :: Changing Rows To Columns Without Using Pivot And Aggregate Functions?

Aug 13, 2013

Changing rows to columns without using pivot and aggregate functions?

View 10 Replies View Related

SQL & PL/SQL :: Sub-program Or Cursor (C1) Reference Is Out Of Scope

Feb 19, 2013

When I am executing below code it is working fine

DECLARE
CURSOR C1 IS
SELECT EMPLOYEE_ID FROM EMPLOYEES;
TYPE EMP_ID_TYPE IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE INDEX BY BINARY_INTEGER;
TABLE_EMP_ID EMP_ID_TYPE;
BEGIN

[code].....

But when i am executing below code it is showing error as subprogram or cursor 'C1' reference is out of scope

DECLARE
CURSOR C1 IS
SELECT EMPLOYEE_ID FROM EMPLOYEES;
TYPE EMP_ID_TYPE IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE INDEX BY BINARY_INTEGER;
TABLE_EMP_ID EMP_ID_TYPE;
BEGIN

[code].....

The only difference in above two blocks is that EXIT WHEN Statement.

View 2 Replies View Related

PLS-00222 - No Function With Name Exists In Scope / ORA-06512 No Data Found?

Apr 22, 2009

I am developing a 3-tier based website having Oracle 10g DB at back end. Firstly, i created a pl/sql stored procedure as:

create or replace procedure CheckLogin(u_name in varchar2, u_pwd in varchar2,is_admin in number) is
uname varchar2(30);
upwd varchar2(30);
begin

[code]...

View 1 Replies View Related

Determining What Procedures / Triggers / Functions Update A Table

Jul 2, 2010

I'm trying to determine which procedures (in or out of packages), triggers or functions insert to or update particular tables in Oracle 10.2.0.4.I've got a far as looking at user_dependencies, and have written the following sql based on that:

CODEselect o.name, decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE',
8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY', 22, 'LIBRARY',
[code]....

This isn't how I'd write the code for production, it's just research code at the moment.

This tells me most of what I need to know, but it doesn't allow me to remove those procedures etc that just select from the table I'm interested in. The vast majority of the dependencies are just selects. In a previous role I used SQL Server 2000 and I could get this information from sysdepends, filtering on the resultobj column to remove the select only dependencies - so I'll be a little surprised if Oracle doesn't have this facility.

View 1 Replies View Related

SQL & PL/SQL :: Update Salary In Employees Table With Analytical Functions?

Feb 16, 2013

i have employee table i want to update salary with all employee 5 percent

View 4 Replies View Related

SQL & PL/SQL :: Aggregate Between Two Dates

Jul 13, 2010

I 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

View 14 Replies View Related

Aggregate Two Columns

Oct 25, 2011

I 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 ?

View 1 Replies View Related

Tuning Aggregate Queries?

May 1, 2011

I 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

View 5 Replies View Related

Constraint Specification Not Allowed Here

Aug 4, 2011

SQL> create table emp_column_level_cons(empno number(4) constraint empcol_empno_pk primary key
2 ename varchar2(15) unique,
3 job varchar2(20) not null,
4 sal number(7,2) check (sal between 1500 and 25000),
5 deptno number(4) constraint empcol_deptno_fk foreign key re
ferences dept(deptno) on delete cascade);
deptno number(4) constraint empcol_deptno_fk foreign key references dept(deptno) on delete c
*
ERROR at line 5:
ORA-02253: constraint specification not allowed here

================================================

View 1 Replies View Related

SQL & PL/SQL :: Sequence Number Not Allowed

Mar 2, 2013

I have a following select statement where I am not generating a sequence in any of these WHERE clause, GROUP BY or ORDER BY clause, DISTINCT clause, along with a UNION or INTERSECT or MINUS or sub-query.

I have query like this

"SELECT
AER_ID_TEMP.AER_ID,
AER_ID_TEMP.D__PRNO,
AER_ID_TEMP.D__PCNT,
CAST((select char_value from aris_param_data where param_id = 101)||seq_record_id.nextval AS INT) as RECORD_ID
FROM AER_ID_TEMP"

but still encountering a error "ORA-02287 sequence number not allowed"

View 2 Replies View Related

SQL & PL/SQL :: Further Changes To Block By Transaction Not Allowed

Jun 6, 2011

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE10.2.0.4.0Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

We are running a code through informatica and are frequently getting the ORA-08007. The error and the code causing it are mentioned below. (The '?' implies a variable which is supplied by informatica). The source is a flat file.

ORA-08007: Further changes to this block by this transaction not allowed

UPDATE table_20
SET capxuser = ?,
capxtimestamp = ?,
capxaction = ?,

[code]...

1. Tried initially by reducing the commit interval to commit at 100 rows. But still getting the same error.
2. DBA has installed the patch # 6790768. But it still fails.

View 10 Replies View Related

SQL & PL/SQL :: Why Commit Not Allowed In Trigger

May 17, 2010

why commit is not allowed in trigger?

View 3 Replies View Related

SQL & PL/SQL :: ORA-00984 / Column Not Allowed Here

Oct 30, 2010

I have created the following package-

create or replace PACKAGE TABLE_PKG1 AS
PROCEDURE make(table_name VARCHAR2, colspec varchar2);
PROCEDURE add_row(table_name VARCHAR2, value_s VARCHAR2,cols VARCHAR2);
END TABLE_PKG1
CREATE OR REPLACE PACKAGE BODY TABLE_PKG1 AS

[code]....

The package and the package body compiles successfully. When I add table-

BEGIN
TABLE_PKG1.make('EMP_new','country varchar2(20), currency varchar2(20)');
END;

The table is created successfully.However when I invoke the package using the following anonymous block for adding the row-

SET SERVEROUTPUT ON
BEGIN
TABLE_PKG1.add_row('EMP_new','"India","Rs"','Country,Currency');
END;

The it generates the following error-

Error report:

ORA-00984: column not allowed here
ORA-06512: at "SCOTT.TABLE_PKG1", line 14
ORA-06512: at line 2
00984. 00000 - "column not allowed here"

View 3 Replies View Related

SQL & PL/SQL :: List Function (Aggregate)

Dec 13, 2010

I 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

View 7 Replies View Related

PL/SQL :: Group Function Is Not Allowed Here

Mar 3, 2013

Am running the below query and getting the error below :

ORA-00934: group function is not allowed here
SELECT   CONTRNO,
            SUBNO AS USER_CODE,
            TOTAL_POINTS AS AMOUNT,
            STARTDATE + 30 AS DATE_F,
            'SUB' USER_TYPE

[Code]....

View 4 Replies View Related

Aggregate Function - Combine Results

Nov 24, 2009

I 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?

View 4 Replies View Related

SQL & PL/SQL :: De-Aggregate Year To Date Figures?

Apr 27, 2011

I 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]...

View 9 Replies View Related

SQL & PL/SQL :: Special Character Not Allowed In Column Value?

Aug 8, 2012

I have a table stud_mst In which i have following columns STUD_NAME , ROLL_NO , COURSE

Table values are for e.g

STUD_NAME , ROLL_NO , COURSE
Harshita 1323 BSC
Atul 1734 BCOM
L'Pradhan 3739 BA

Still today user can enter any special character value in stud_name like L'Pradhan . I want to put a restriction on stud_name so that user can enter only alphabetical value into stud_name column.

View 6 Replies View Related

Forms :: Frm-41009 Function Key Not Allowed

May 1, 2012

i create form,when i press F11, it displays this error:Frm-41009 function key not allowed ,press ctrl+k for list of valid values.And i can't enter value into fields to query records.

View 11 Replies View Related

SQL & PL/SQL :: ORA-02253 / Constraint Specification Not Allowed Here

Jan 6, 2011

I've got the following SQL script that I'm trying to run against a database: -

CREATE TABLE jdp_parameters (
id NUMBER(9) CONSTRAINT pk_jdp_parameters
PRIMARY KEY
CONSTRAINT ck_jdp_parameters_id
CHECK (id = 0),

[code]....

On running it I get the following errors: -

pricelevel_id NUMBER(9) CONSTRAINT fk_jdp_pricelevel_id
*
ERROR at line 10:
ORA-02253: constraint specification not allowed here
Sequence created.
REFERENCES jdp_parameters(id)

[code]....

View 4 Replies View Related

SQL & PL/SQL :: Aggregate Contiguous Range Of Days?

Oct 21, 2010

I 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

View 2 Replies View Related

SQL & PL/SQL :: Materialized View - Zero Length Not Allowed?

Dec 8, 2010

I am trying to create one Materialized view from a Select Statement and I am getting the following error

SQL Error: ORA-01723: zero-length columns are not allowed

I have checked the table and found nothing like varchar2(0) or char(0) , but the column in my select statment may get a Null value. Will it result in error like above ?

Normal View : I have created this because I think a materialized view would not be created if we have a Sub query.

CREATE OR REPLACE VIEW mx_test
AS
SELECT t."EXTN_SERVICE_REQUEST_NO",
t."EXTN_SERVICE_TYPE",

[Code]....

Materialized View :

CREATE materialized VIEW mx_ashok
AS
SELECT * FROM mx_test

Error :

Error starting at line 1 in command:
CREATE materialized VIEW mx_ashok
AS
SELECT * FROM mx_test
Error at Command Line:3 Column:16
Error report:
SQL Error: ORA-01723: zero-length columns are not allowed
01723. 00000 - "zero-length columns are not allowed"
*Cause:
*Action:

Do I need to use NVLs for all columns which may result in NULL value ?

View 9 Replies View Related

SQL & PL/SQL :: How To Write Analytic Function Without Aggregate

Mar 8, 2012

Do 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

View 4 Replies View Related

SQL & PL/SQL :: ORA-01733 - Virtual Column Not Allowed Here

Nov 23, 2011

Does know why I am getting ORA-01733- virtual column not allowed here Following :

CREATE TABLE EMPL
(
EMP_ID NUMBER(10),
CNT NUMBER(10)
)
insert into empl(EMP_ID)
values(1)

[code]....

ORA-01733- virtual column not allowed here

View 33 Replies View Related

PL/SQL :: XML Aggregate Function And Out Of Process Memory

Aug 8, 2012

I 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]...

View 0 Replies View Related







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