SQL & PL/SQL :: Grouping List Of Elements With One Or More Common?

Oct 6, 2011

I have a one column table that store lists of elements :

create table test_table (c1 VARCHAR2(4000));
insert into test_table values ('1,23');
insert into test_table values ('1,2');
insert into test_table values ('3,4,5');

[code]...

The output column would be something like that:

output_column
1,2,7,23
6,9,0
3,4,5

I'm grouping columns that have at least one element in common.

(1,23) and (1,2) merge into : (1,2,23)
(1,2,23) and (7,2) merge into : (1,2,7,23) --> Output
(6,9) and (9,0) merge into : (6,9,0) --> Output
(3,4,5) and (5,5) merge into : (3,5,5) --> Output

I have made this logic using only PL/SQL, with loops and nested tables using function memberof, but I suppose that there is a way to improve the performance using only SQL.

View 9 Replies


ADVERTISEMENT

Forms :: How To Avoid (no List Elements Defined For List Item) Error

Sep 27, 2011

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

View 1 Replies View Related

Forms :: FRM-30351 / No List Elements Defined For List Item

Oct 30, 2011

DECLARE
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

View 4 Replies View Related

SQL & PL/SQL :: Get The Distinct Elements Of A Collection Of Objects

Oct 7, 2010

I have a collection of objects built in a PL/SQL program.

I want to keep only the distinct elements of this collection for the rest of the program.

I failed to find the correct way to do it

For instance (the actual object is far more complex containing object attributes itself), with the following types:

drop type t1b
/
drop type t1a
/
create or replace type t1a as object (
v1 integer,
v2 integer
)
/
create or replace type t1b as table of t1a
/
I have the following variable:

v t1b := t1b(t1a(1,2),t1a(1,2));

And I want to get only one "t1a(1,2)" in my collection.

My first idea (actually the second one but it does not matter) was to use DISTINCT:

SQL> declare

2 v t1b := t1b(t1a(1,2),t1a(1,2));
3 begin
4 select distinct t1a(v1,v2) bulk collect into v from table(v);
5 dbms_output.put_line(v.count);
6 end;
7 /
declare
*
ERROR at line 1:

ORA-22950: cannot ORDER objects without MAP or ORDER method ORA-06512: at line 4

As I said the object is far more complex and builting a MAP function is quite tedious (but I will do it if there is no other way).

The next idea was to use multiset operators:

SQL> declare
2 v t1b := t1b(t1a(1,2),t1a(1,2));
3 begin
4 select v multiset intersect distinct v into v from dual;
5 dbms_output.put_line(v.count);
6 end;
7 /
1

PL/SQL procedure successfully completed.

This works well but I suspect this is not the correct way and there is one to do it in PL/SQL but currently failed to find it.

View 2 Replies View Related

SQL & PL/SQL :: Select All The Elements Of A Record Type

May 24, 2011

Is there any way that I can check what are the elements present in a pl sql record type by querying in table?

For example if I want to check what are elements present in oe_order_pub.header_rec_type and I don't want to open the package, then in which table I should query? Is it possible?

View 2 Replies View Related

SQL & PL/SQL :: Count Number Of Elements In Array?

Mar 13, 2013

I need to count number of elements in the same catagory of an array.. For example, an array consists of {'a','b','c','c','a','d','c'} means, i need to display like a=2, b=2, c=3, d=1.

I have written the below code.

declare
type array_val is varray(10) of varchar2(15000);
counter number:=0;
SMQ_NAME ARRAY_VAL:=ARRAY_VAL();
begin

[code]....

But its not showing exact output as my requirement..

View 6 Replies View Related

SQL & PL/SQL :: Raise Exception With All Elements In Collection

Nov 17, 2011

PROCEDURE test_data(
)
IS
TYPE test_tab IS TABLE OF NUMBER(15);
t1 test_tab;
BEGIN

I have missing elements in a collection that should be reported to the terminal while running the procedure

IF t1.COUNT > 0 THEN
RAISE_APPLICATION_ERROR('Missing Elements: ' || elments from collection t1);

How can I raise an exception with all the missing elements from collection t1 here?

END IF;
END;

View 8 Replies View Related

SQL & PL/SQL :: How To Access The Record Type Elements Of Varray

Jun 11, 2013

i have created one varray whose elements are of record type. Now how can i access those record type elements?

structure of table t1:
select * from t1;

IDDESCRIPTION

1a
2b
3c

select * from t2;

ID1DESCRIPTION1

4aa
5bb
1cc

declare
type r1 is record (id t1.id%type);
type r2 is record (id1 t2.id1%type);
type r3 is record (id1 r1, id2 r2);
type var1 is varray(20) of r3;
[code].......

View 13 Replies View Related

XML DB :: Optional Validation Of Some Complex Type Elements

Jul 31, 2012

There is XMLType table with structural storage. Is there a way to make schema validation disabled on some elements of complex type?

It is unpractical to maintain a schema for the element due to high volatility. Ideally it will be stored in CLOB and extracted as is a whole branch without validation, none of the elements under this complex type will be extracted separately.

View 3 Replies View Related

SQL & PL/SQL :: Getting Most Common Value

Mar 31, 2010

I am using this query:

Select
t1.col1 as A,
t2.col1 as B,
t3.col1 as C,
t4.col1 as D

[Code]...

with this above query i am getting result like this:

A B C D

2 null 2 null
6 6 null 6
8 null null null

However i need the result like this:

A B C D

6 6 null 6

Note:

I am looking for common value that presents in most of these 4 tables for different t1.col2 values like 'E','G','K',...etc. I cant use the NOT NULL condition in the where clause,since i need to check for different values in t1.col2,for each of this there may be null value in any of the 4 tables.

View 3 Replies View Related

SQL & PL/SQL :: How To Get Common Records

Feb 10, 2013

I have a situation as follows.

Table1 has 10 columns as col1,col2,col3,col4,...col10

Table2 has 5 columns as col1,col2,...col5.

col3 and col4 are common in both tables.I am trying to get common records from both tables.How can I get them.

View 3 Replies View Related

SQL & PL/SQL :: How To Use Common Group By Function While Using Union All

Aug 12, 2010

i am having two sql query which i have made union of it but i need group by common.

select
distinct ac.CUSTOMER_NAME cust_name,
ac.CUSTOMER_NUMBER cust_no,
ps.CUSTOMER_SITE_USE_ID,
raa_ship_ps.PARTY_SITE_NAME loc,
sum(days_30.AMOUNT_DUE_REMAINING) D_30,

[code]........

View 9 Replies View Related

SQL & PL/SQL :: How To Calculate Highest Common Factor

Oct 5, 2005

Is there any function avaialble in SQL that can return the highest common factor among a bunch of numbers. For example
10,20,25 have a highest common factor of 5.

View 9 Replies View Related

Forms :: Host Common On Linux

Oct 27, 2010

My database as well as application on linux. I want to execute this command on linux. I need equivalent code.

host('cmd /c E:data empITC1'.bat');

code of ITC1.bat is
echo off
sqlldr userid=user_name/pass@database control=E:data empITC1.ctl log=E:data emplogITC1.log

echo ........

control file as well as bat file exist in respective folders.

View 9 Replies View Related

Data Guard :: What Is The Most Common Mode

Feb 27, 2012

what is the most common mode in dataguard? maximize protection or maximize availability or maximize performance

View 6 Replies View Related

PL/SQL :: Find Common And Internal Calls?

Jul 19, 2013

create table call(id number(15) primary key, calling_no varchar2(15), called_no varchar2(15), calldate date, calltime timestamp, duration number(10) ,calltype varchar2(10));  1. CALL DETAILS OF 9891826547insert into call (id,calling_no,called_no,calldate,calltime,duration,calltype) values (1, 9891826547, 9891210785, to_date('01-Jun-13','dd-Mon-yy'), to_date('19:12:00','hh24:mi:ss'),10,'OUT'); insert into call (id,calling_no,called_no,calldate,calltime,duration,calltype) values (2,9891826547, 9899985476, to_date('01-Jun-13','dd-Mon-yy'), to_date('22:10:12','hh24:mi:ss'),50,'OUT'); insert into call (id,calling_no,called_no,calldate,calltime,duration,calltype) values (3, 9891826547, 9818415767, to_date('02-Jun-13','dd-

[code]...

View 3 Replies View Related

PL/SQL :: Find Common Data In 2 Columns In Two Different Tables

Oct 22, 2012

query to find out common data from 2 columns in two different tables??

View 6 Replies View Related

74 Privileges From Different Users On Different Tables To One Common User?

May 20, 2013

i have to give 74 privileges from different users on different tables to one common user,how can i do this?

View 7 Replies View Related

SQL & PL/SQL :: Oracle 11g / Windows 8 - Common Code In Package

Sep 13, 2013

In oracle 11g windows8 - OS

Suppose, there is a package p1 with proc A, Proc B and Func C.Now when any of the package proc or func gets invoked from a stored proc G then a common code which reads the cols of table xyz should be executed. How to do that?

View 8 Replies View Related

PL/SQL :: Compare Two Tables And Delete The Common Rows From Table 1

Jul 23, 2012

I want to compare two tables , and delete the common rows from the first table

Here is what i have done :

Create table test1(Test1C1 Number,
Test1C2 varchar2(50));

Create table test2(Test2C1 Number,
Test2C2 varchar2(50));

Insert into test1 values(1,'testdata1');
Insert into test1 values(2,'testdata2');
Insert into test1 values(3,'testdata3');
[code].......

it deletes all the records from Table test1. What should I modify here ? or should I write a different query ?

The desired contents in table test1 will be

2 testdata2
4 testdata4
6 testdata6
8 testdata8
10 testdata10

View 5 Replies View Related

Application Express :: How To Validate In Common PL/SQL Package And Show Custom Messages

Feb 11, 2013

Currently if i let required field empty, then APEX puts systematic error message "XXX must have some value." near the field and into the page header. I want to create custom business rule, that would show message for example "This action cannot be performed, because this and that...".

That kind of custom validation should be in my plSql package and somehow it should throw/send the phrase "This action cannot be performed, because this and that..." to the page header where was message "XXX must have some value.".

How can i create such custom error message to appear out using PlSql package function that i define myself? I plan to create a common validation package, which has lots of plsql functions to validate lot of APEX pages/forms, all those functions should throw/send somehow error messages to page header. I looked that this unofficial solution does not suit for me:

[URL].......

Because it disables APEX systematic automatic validations as i understand, and is unofficial.

View 4 Replies View Related

Forms :: PDE-PLI011 A Tools Common Schema Table Missing From Object Store

May 26, 2011

while trying to open an exisitng Form, an Error message appears as:

PDE-PLI011 A tools common schema table is missing from the object store.

and failed to open. what's the problem ? what i have to do ? it was working fine before.

View 2 Replies View Related

SQL & PL/SQL :: Grouping Of Data

Aug 19, 2013

I have a requirement to get the records group wise.Ex: For each departments, i need to get the employee details as a coma seperated.It means that the output must have the department name in first column and the second column must contain all the employees in that particular department (As a coma seperated).

View 3 Replies View Related

SQL & PL/SQL :: Generating A Key For Grouping?

Mar 27, 2013

In the below data, a container is moving from one city to another. 1,2 ,3 can be any number which i want to generate and use as keys to group the cities. Eg: AUH, JEB, CIW belong to the same key=2; SIN, IKT belong to a new group 4. The City where difference between the Seq# is greater than 1 (eg between S8W and AUH), a new group starts.

Conotainer #CitySeqI want this
-------------------------------------------
Container1S8W5251
Container1S8W5261
Container1AUH5362
Container1AUH5372
Container1JEB5382
Container1JEB5392

[code]....

View 4 Replies View Related

SQL & PL/SQL :: Grouping By Date

Oct 14, 2011

I have a table like this

Name Hours date
a810/11/2011
a 510/12/2011
a610/13/2011
a710/14/2011
a710/15/2011
a810/16/2011
a710/17/2011
a810/18/2011
a810/19/2011
a710/20/2011
a710/21/2011

If i want the sum of hours for 3 days range ,how should i do it.

E.g. say

name hrs startdate enddate

a 19 10/11/2011 10/13/2011
a 22 10/14/201110/16/2011
a 23 10/17/2011 10/19/2011

View 11 Replies View Related

PL/SQL :: Grouping Of Data

Feb 10, 2013

How can I get the grp_id for unique combination of manager and department, grp_id should be created on asc order of manager_id.
In this example manager_id 100 is minimum, so it should be grp 1 and all the employees with that manager_id should be in grp_id 1, for manager_id 114 grp_id should be 2.

If, there is manager_id 117, it should create grp_id 3.

To get grp_num ,I can use row_number() over (partition by department_id,manager_id order by employee_id) grp_num

I am looking for an update statement for this issue.

Oracle version : Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

CREATE TABLE HR.EMPLOYEES_2
(
  EMPLOYEE_ID         NUMBER(6),
  FIRST_NAME          VARCHAR2(20 BYTE),
  LAST_NAME           VARCHAR2(25 BYTE),
  EMAIL               VARCHAR2(25 BYTE),
  PHONE_NUMBER        VARCHAR2(20 BYTE),

[Code]....

Expected result
----------------
EMPLOYEE_ID    SALARY    MANAGER_ID    DEPARTMENT_ID    GRP_NUM    GRP_ID

114              11000        100          30              1              1
115              3100         100          30              2              1
116              2900         114          30              1              2
117              2800         114          30              2              2
118              2600         114          30              3              2
119              2500         114          30              4              2

View 8 Replies View Related

Grouping By Time Range

May 19, 2013

I am trying to break down the balance_date to display the following groupings:

7:00-17:30 CDT
18:00-4:30 CDT

I currently have the query setup to display by day instead of these time ranges. I would like the output to read

19 May Day
19 May Night
20 May Day
20 May Night

I am fairly new to this, but how would I go about making this change?

SELECT
TO_CHAR(TRUNC(balance_date,'D') + 4,'YYYY') || '-' ||
TO_CHAR(TRUNC(balance_date,'D') + 4,'IW') as year_wk,
TO_CHAR(TRUNC(balance_date,'D') + 4,'IW')as wk,

[Code] ........

View 1 Replies View Related

SQL & PL/SQL :: Oracle 9i - How To Use Grouping Query

May 31, 2012

Am getting an output like this in oracle9i,

S.No Column1 Column2 Column3 DateCol
1 A B C 10/2001
2 A B C 03/2001
3 B B C 02/2001
4 B B C 01/2001
5 A B C 03/2000

But My real scenario is , i need to populate the output in below structure

S.No Column1 Column2 Column3 DateCol

1 A B C 10/2001
A B C 03/2001
2 B B C 02/2001
B B C 01/2001
3 A B C 03/2000

I dont know how to form the query , to retrieve the structure..

View 2 Replies View Related

SQL & PL/SQL :: Grouping Splits Results?

Jun 6, 2013

SELECT
pas_code,
pas_profile,
count(sutp_id),
sum(sutp_price),

[code]...

And the problem is, that when i use sutp_price_proc and pbk_price in grouping, it splits my results by those rows. If i delete them from grouping, sql gives me error about not a single grouping in line 1.

pas_codepas_profilesutp_idsutp_pricex
2664good stuff310069< because pbk_price is like 67 from that period
2664good stuff310071< because pbk_price is like 50 from other period

how to get all results in a single line like:

pas_codepas_profilesutp_idsutp_pricex
2664good stuff6200140

View 3 Replies View Related

SQL & PL/SQL :: Query For Grouping And Count?

Feb 24, 2010

TASK_ID, TASK_STATUS, TASK_OWNER
================================
00001 , OPEN , ABC
00002 , OPEN , XYZ
00003 , WIP , ABC
00004 , CLOSED , XYZ
00005 , WIP , XYZ
00006 , CLOSED , XYZ
00007 , OPEN , XYZ

Output Required
Owner , Open , WIP, Closed
ABC 1 1 0
XYZ 2 1 2

View 9 Replies View Related







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