SQL & PL/SQL :: User-defined Aggregate Calculation For Z Score

Jun 29, 2011

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

View 6 Replies


ADVERTISEMENT

SQL & PL/SQL :: User Defined Type

Jun 19, 2012

I am trying to create a new data type but keeping getting a ORA-0902 invalid data type error. I am using the example show at:

Guess I cannot post a link until I have posted more than 5 messages so here is the link another way:

www(dot)psoug.org/reference/type.html

Specifically, all the steps work up to Create Table which fails with the error.

By work -- I mean the type gets created & the 3 select statements prior to Create Table confirm the existence of the ssn_t data type.

View 5 Replies View Related

User Defined Data Type

Jun 19, 2012

I am trying to create & use a new data type but keep getting a ora-0902 invalid data type error running on 10g express. The create type and select statements execute fine and select confirms the ssn_t is a type. The create table statement fails with the invalid data type error.

Here is an example.

CREATE TYPE ssn_t AS OBJECT ssn_type CHAR(11));
SELECT object_name, object_type
FROM user_objects WHERE object_type = 'TYPE';
CREATE TABLE Z (A CHAR(4), B SSN_T);

View 14 Replies View Related

How To Use User Defined Type While Creating New Table

Oct 13, 2009

That is I have created the User Defined Data Type as following. CREATE OR REPLACE TYPE Bit_Type AS OBJECT(Bit NUMBER(1,0));

After completing this creation of new UDT, I am trying to create the table with this UDT as follows, CREATE OR REPLACE TABLE Sample_Bit ( RegID Bit_Type);

I received an Error Message like:
SQL Error: ORA-22913: must specify table name for nested table column or attribute
22913. 00000 - "must specify table name for nested table column or attribute"
*Cause: The storage clause is not specified for a nested table column or attribute.
*Action: Specify the nested table storage clause for the nested table column or attribute.

View 1 Replies View Related

Possible To Easily Set Up A Single User Defined Metric

Dec 28, 2010

Is it possible to easily set up a single User Defined Metric, to monitor all the users quotas? I know I can do it, if I make individual metrics for each user. But was hoping there was a simple way of combining them all into a single metric.

I am using Oracle 10G2 Standard Edition on a windows 2003 Server.

View 1 Replies View Related

Forms :: User Defined Login Form

May 3, 2011

I have designed a Login form which takes username, password and connect string is hardcoded.I have compiled it, made an fmx file. created a shortcut to desktop. given the BIN path in shortcut.

when i want to lauch it, oracle's default login popup is shown.It can be by passed while defining username/password@string in shortcut properties but it reveals the password. every one can access it. I want to launch my own form without logging in to oracle. how can i avoid default login popup?

View 8 Replies View Related

SQL & PL/SQL :: Declare A User Define Exception Name As Per Defined Name?

Jan 25, 2011

Can we able to declare a user define exception name as per defined exception name?Ex: we have predefined exception called :"NO_DATA_FOUND "

Now in my PL/SQL block can i able to declare "NO_DATA_FOUND" like

Begin
no_data_found exception;
//statements
raise no_data_found;
end

View 6 Replies View Related

SQL & PL/SQL :: Procedure To Call User Defined Function?

Apr 12, 2012

I have made a function to add two number create or replace function add_num(a in number, b in number) return number

as
begin
return a+b;
end;
/

i run it via
select add_num(2,5) from dual;

my question is how to call add_num in procedure.

View 9 Replies View Related

SQL & PL/SQL :: Does Oracle Log Exception In User Defined Function

Jan 12, 2012

If a user defined function in Oracle may throw and catch exceptions depending on input, now does the Oracle logs the exception? Fundamentally we are asking if Oracle logs any random DML errors (not bulk operation). We hope not, as we do not want our client DBA get concerned. There might be many levels of settings in Oracle to log or not log. So if Oracle may log it depending on setting, how do we suppress it either at Oracle specific schema level, or function level?

This is my user defined function:

create or replace FUNCTION ISNUMERIC(char_in CHAR) RETURN NUMBER IS
n NUMBER ;
BEGIN
n := TO_NUMBER(char_in);
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;

View 2 Replies View Related

PL/SQL :: How To Copy A User Defined Type From Other Schema

Aug 25, 2012

Through sql*plus, how can I copy a user-defined type from other schema to mine?

I've granted the execute object privilege over the user-defined type to my user, so I can use the type.

I know I can manually write and create the type, but I wanna learn (if exist) a statement to copy the user-defined type, something like:
CREATE TABLE x2 AS
(SELECT *
FROM x1);

I'm testing ORACLE DATABASE 11g R2 over Windows O. S.

View 3 Replies View Related

SQL & PL/SQL :: User Defined Data Types In Cast Functions

Jun 22, 2010

I am migrating sybase to oracle database. A Java developer needs money datatypes.I said to them, please change the cast(<value> as number(19,4) in java code side. but they are not accepted because money data type is used most of the places.

select cast(0 as money) from bank_trans; this sql statements present in java code. I need to create user defined type is equivalent to money datatype.

My steps

I have create user defined data types

create or replace type money as object(money as numbeer(19,4)

select cast(0 as money) from dual;

it shows inconsistent datatypes error.

create or replace type money is table of numbeer(19,4);

select cast(0 as money) from dual;

it shows inconsistent datatypes error.

View 10 Replies View Related

Enterprise Manager :: User Defined Metric Using SQL Code

Nov 25, 2012

How can I apply a user metric for DB (single database) using a script or cli?

clear my question:

Can I create a User Defined Metric using SQL code, and not via the graphical interface?

View 1 Replies View Related

User-defined Metric Doesn't Send Email

Dec 18, 2010

I'm attempting configure User-defined Metric to send mails when happen warning or critical alerts .My environment is Cluster Database and I'm using OEM Grid Control

I created User-defined Metric for monitoring invalid object for example and scheduled this collect to every 5 minutes.On initial OEM page, I see alert violation, but don't receive mail for this. In my Notification Rules page, I added "User-Defined Numeric Metric" and includes object Check_Invalid_Objects, wich is my UDM.I receive several alerts violations, but don't UDM.

 UDM_Violation.png ( 116.68K )
Number of downloads: 0

 NotificationRule.jpg ( 218.78K )
Number of downloads: 0

 ScheduleNotification.jpg ( 373.64K )
Number of downloads: 0

View 6 Replies View Related

SQL & PL/SQL :: User Defined Exceptions For Insertion Update Queries

Sep 7, 2012

how to define user defined exceptions for cases like, ==> when anyone tries to insert string values without using single quotation marks " '...' "? ==> update the column which is not present in table.

how can I define user defined exceptions for such cases?

View 3 Replies View Related

PL/SQL :: Writing User Defined Function The Same Way As The Oracle Functions

Sep 26, 2012

In one of the interviews , I have attended the guy asked me to write a user defined function which will take the column name and list all the values .

For example

Table Name:Employees
Column Name: Employee_Name

Employee_name
Scott
Ivgun
Jack
Shane

The query should be in this fromat

SELECT col_agg(Employee_name) emp_name from Employees;

The output should be

Emp_names
scott,ivgun,jack,shane

View 6 Replies View Related

Forms :: How To Create User - Defined Search In Runtime Oracle 6i

Sep 24, 2012

Is it Possible to create User - defined Search forms in runtime oracle forms 6i . If One Having sample Forms

View 1 Replies View Related

SQL & PL/SQL :: Mapping User Defined Object Types On REMOTE Databases

Dec 2, 2010

I need to transfer PL/SQL or NESTED table from LOCAL database to REMOTE.I need to be able to use that transfered table in SELECT statement.I tried:

--ON LOCAL
SELECT SYS_OP_GUID() FROM DUAL;
--966D2DFEAEEB80D6E0430A0166CB80D6
create or replace type varchar_number_oid OID '966D2DFEAEEB80D6E0430A0166CB80D6' as object (
v varchar2(10),
i number);
/
Type created
SELECT SYS_OP_GUID() FROM DUAL;
--966D2DFEAEFB80D6E0430A0166CB80D6
[code]....

I tried using PL/SQL types it worked, but then i have a problem that i can not use local collection in SQL.

View 2 Replies View Related

Forms :: How To Hide / Suppress User Defined Alert Messages

Dec 19, 2011

Is it possible to hide/suppress an user defined alert message in Oracle Forms application?

View 1 Replies View Related

Precompilers, OCI & OCCI :: Retrieve Collection Of User-defined Object?

May 9, 2003

I am having a OCCI problem, I try to retrieve a collection of user-define objects.

The major steps I am using are
1. define database object

CREATE OR REPLACE TYPE my_obj_t AS OBJECT
( id number, name varchar2(10) )
CREATE OR REPLACE
TYPE my_obj_tab_t as table of my_obj_t;

2. A stored procedure return a collection as out paramerter
my_stored_procedure(obj_list out my_obj_tab_t);

3. Using OTT to generate the *.cpp and header files.

4. Register out parameter like
vector<my_obj_t> vect;
stmt->registerOutParam(1, OCCIVECTOR,
sizeof (vect),
"MY_OBJ_TAB_T");

5. stmt->execute();

6. Try to get the results by
getVector(stmt, 1, vect);

I am getting compilation error:

Could not find a match for
oracle::occi::getVector(oracle::occi::Statement*,
int, std::vector<my_obj_t, std::allocator<my_obj_t>>).

Am I doing the right things ?What steps and function should I use ?

View 1 Replies View Related

PL/SQL :: Assign Variables To User Defined Data Type Within Package

Oct 16, 2013

I have this requirement, I have a following Record type within a package, instead of selecting datas into this i need to assign variables to this record type  

TYPE xx_delivery_detail_rectype IS RECORD (      p_delivery_id                    NUMBER,      p_ultimate_dropoff_location_id   NUMBER,      p_creation_date                  DATE,      p_last_update_date               DATE,      p_container_name                 VARCHAR2 (30),      p_inventory_item_id              NUMBER,      p_shipped_quantity               NUMBER,      p_shipment_line_id               NUMBER   );     TYPE xx_delivery_table_rectype IS TABLE OF xx_delivery_detail_rectype      INDEX BY BINARY_INTEGER; 

Say i have variables to assign to all the values within the record type, How do i do it within the package

View 4 Replies View Related

Semantic Technologies :: User-defined Function In FILTER Clause?

Apr 28, 2013

can i create the user-defined functions and use them in the FILTER clause in the sem_match function? there are some built-in functions for the FILTER clasue. however, only one function (DATATYPE(literal)) support for date/time in the built-in functions. i want to implement some user-defined funcitons in the FILTER clause which can check time intervals in ontology. there are some functions about valid time in the WorkSpace Manager such as WM_OVERLAPS, WM_CONTAINS,WM_MEETS, etc. so, can i write some functions using the these valid time functions in WM and use them in the FILTER clause?

View 2 Replies View Related

User-defined Functions In Sql Loader Control File With DIRECT Path Mode

Jan 22, 2013

I am using oracle 10g version 10.2.0.1.0.

Can I use user-defined functions in my sql*loader control file and load in DIRECT path mode?

View 1 Replies View Related

ORA-22804 Remote Operations Not Permitted On Object Tables Or User-defined Type Columns?

Jul 5, 2013

I have a two different Databases. I created a db link in DB 1 to connect to DB 2 and it is working fine when I select data from any table. but I have one table in the DB2 which has a column with user defined data type . so when I try to select this column from DB 2 by using the DB link it gives me this error :ORA-22804 remote operations not permitted on object tables or user-defined type columns.

View 1 Replies View Related

SQL & PL/SQL :: How To Implement (score-proc) Using Bulk Collect

May 20, 2013

In my below code the procedures "total_score_proc" and "CopyInternalScores" are calling "score_proc" procedure 50 times
for different variable values.

Instead of calling the "score_proc" procedure 50 times.I want to hold the values in to collection , defining it in package and call that procedure only once.

how to implement "score_proc" using bulk collect.

CREATE OR REPLACE PACKAGE total_score_pkg
IS
PROCEDURE total_score_proc(pBUID IN STAGING_ORDER_DATA.BUID%TYPE,
OrderNum IN STAGING_ORDER_DATA.ORDER_NUM%TYPE,

[code]....

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

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

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







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