Home » SQL & PL/SQL » SQL & PL/SQL » How can I check this list of values are exists in database ?
How can I check this list of values are exists in database ? [message #186088] Sat, 05 August 2006 12:49 Go to next message
nasdaq
Messages: 8
Registered: April 2006
Location: bangkok
Junior Member
Please help me to solve it.
Example of data : Table1

ListID ListValue
1 A
1 B
2 A
2 C
3 A
4 A
4 B
4 C
Currently this table have 3 list ID as shown above. When I insert new list of data just like

ListID ListValue
5 A
5 B

I would like to check this group of list values is already exists in database but I don't know how to write sql to check it.

Thank you



Re: How can I check this list of values are exists in database ? [message #186091 is a reply to message #186088] Sat, 05 August 2006 13:04 Go to previous messageGo to next message
rampratap
Messages: 50
Registered: November 2004
Location: New Delhi
Member

1. You can create a compositive primary key on the table like:

alter table a1 add constraint a1_pk primay key(listid, listvalue);
this constraint will not allow u to enter duplicate group

second option is write a trigger on this table like:
create trigger a1_insert
before insert on a1
for each row
declare
ctr number := 0;
begin
select count(*) into ctr from a1
where listid = :new.listid
and listvalue = :new.listvalue;
if ctr <> o then
raise_application_error(-20001, 'group already exists');
end if;
end;
/

third option is
always insert values throw a procedure like
create procedure myproc( vlistid in number, vlistvalues in varchar2) is
ctr number := 0;
begin
select count(*) into ctr from a1
where listid = vlistid
and listvalue = vlistvalue;
if ctr = 0 then
insert into a1 values(vlistid, vlistvalue);
commit;
else
raise_application_error(-20001, 'duplicat values');
end if;
end;
/



Re: How can I check this list of values are exists in database ? [message #186094 is a reply to message #186088] Sat, 05 August 2006 14:33 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
A unique index on the columns would prevent duplicates, but that might not be an option due to your requirements.

Another option would be just to check when you do the actual insert.

SQL> select * from table1;

    LISTID L
---------- -
         1 A
         1 B
         2 A
         2 C
         3 A
         4 A
         4 B
         4 C

8 rows selected.

SQL> set verify off
SQL> define id=5
SQL> define value='A'
SQL> insert into table1 (listid, listvalue)
  2     select &id, '&value' from dual
  3     where (&&id, '&&value') not in
  4     (select listid, listvalue from table1);

1 row created.

SQL> /

0 rows created.

SQL> select * from table1;

    LISTID L
---------- -
         1 A
         1 B
         2 A
         2 C
         3 A
         4 A
         4 B
         4 C
         5 A

9 rows selected.

[Updated on: Sun, 06 August 2006 10:29]

Report message to a moderator

Re: How can I check this list of values are exists in database ? [message #186156 is a reply to message #186094] Sun, 06 August 2006 10:57 Go to previous messageGo to next message
nasdaq
Messages: 8
Registered: April 2006
Location: bangkok
Junior Member
I'm sorry if the question may be not clear.

Actually I have store procedure used for insert data just like Array Parameter therefore I must check value in Array Parameter is exists in database or not.

As I told you before structure and data stored in table as shown below.

Example of data : Table1

ListID ListValue
1 A
1 B

2 A
2 C

3 A

4 A
4 B
4 C

When user want to insert new data, they must pass value as array just like (5,5) and (A,B) but I listed as below.

ListID ListValue
5 A
5 B

Hence, I don't know how to check it that this group (A,B) is exist in database or not.

Thank you for your solution.It's my mistake that I don't tell you by detail. I don't want to check record by record just like create unique contraint on this table.I just want to check group of ListValue data is duplicated in database or not.

I will show another group of list value in order to explain it more clearly.

If I insert this group

ListID ListValue
5 B
5 C

This group can be inserted because they are not exist in table.

Re: How can I check this list of values are exists in database ? [message #186165 is a reply to message #186156] Sun, 06 August 2006 12:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Either your problem still isn't clear or you have not understood the answers. It looks like any of the solutions provided should work, except for the trigger (selecting from the table the trigger is on is likely to produce a mutating error). You should post your procedure code and how you are calling it and sql statements to create your table and sample data. Please read the sticky at the top of the forum for what we expect.
Re: How can I check this list of values are exists in database ? [message #186167 is a reply to message #186088] Sun, 06 August 2006 13:06 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
As I understand it, what you want is to check if the entire set of array parameter listvalues matches any set of listvalues already existing in the table.

Assuming that the order of listvalues does not matter i.e. (X,Y)=(Y,X), a solution:
- Concatenate the set of sorted input parameter values into a comma-separated string
- Concatenate the set of sorted listvalues for each listid in the table, into comma-separated strings
- Check if the input parameter string is equal to any of the listvalue strings in the table
SQL> select * from table1;

LISTID LISTVALUE
------ ----------
     1 A
     1 B
     2 A
     2 C
     3 A
     4 A
     4 B
     4 C

8 rows selected.

SQL> -- Convert sorted listvalues for each listid in the table into comma-separated strings
SQL> select listid,
  2         ltrim(sys_connect_by_path(listvalue,','),',') listvalues
  3  from  (select listid, listvalue,
  4                row_number() over (partition by listid order by listvalue) rn
,
  5                count(*) over (partition by listid) cnt
  6         from table1)
  7  where  rn = cnt
  8  start with rn = 1
  9  connect by prior listid = listid and prior rn = rn - 1
 10  order by listid;

LISTID LISTVALUES
------ --------------------
     1 A,B
     2 A,C
     3 A
     4 A,B,C

SQL> -- Group already exists: e.g. 'A,B'
SQL> select 'grpexists' from dual
  2  where 'A,B' in
  3    (select ltrim(sys_connect_by_path(listvalue,','),',') listvalues
  4  from  (select listid, listvalue,
  5                row_number() over (partition by listid order by listvalue) rn
,
  6                count(*) over (partition by listid) cnt
  7         from table1)
  8  where  rn = cnt
  9  start with rn = 1
 10  connect by prior listid = listid and prior rn = rn - 1)
 11  /

'GRPEXIST
---------
grpexists

SQL> -- Group does not exist: e.g. 'B,C'
SQL> select 'grpexists' from dual
  2  where 'B,C' in
  3    (select ltrim(sys_connect_by_path(listvalue,','),',') listvalues
  4  from  (select listid, listvalue,
  5                row_number() over (partition by listid order by listvalue) rn
,
  6                count(*) over (partition by listid) cnt
  7         from table1)
  8  where  rn = cnt
  9  start with rn = 1
 10  connect by prior listid = listid and prior rn = rn - 1)
 11  /

no rows selected
Re: How can I check this list of values are exists in database ? [message #186243 is a reply to message #186167] Mon, 07 August 2006 02:50 Go to previous message
nasdaq
Messages: 8
Registered: April 2006
Location: bangkok
Junior Member
Thank you very much for your advice.
I try to use by your suggestion and it can work now.
Razz
Previous Topic: NOT IN
Next Topic: External tables with System tablespace performance issue
Goto Forum:
  


Current Time: Fri Dec 09 15:16:00 CST 2016

Total time taken to generate the page: 0.20467 seconds