table column containing list of other table column

Question

create table cdi(comp_id varchar2(3),pk_key varchar2(2000));
insert into cdi values('abc','empno,ename,job');
insert into cdi values('pqr','empno,job,mgr');
insert into cdi values('cde','empno,mgr,sal');
commit;

create table emp_test(empno integer,ename varchar2(200),job varchar2(200),mrg  integer,sal integer);
insert into emp_test values(1,'Gaurav Soni','DB',12,12000);
insert into emp_test values(2,'Niharika Saraf','Law',13,12000);
insert into emp_test values(2,'Niharika Saraf','Law',13,12000);
insert into emp_test values(3,'Saurabh Soni',null,12,12000);
commit;

In cdi table comp_id is primary key

create or replace procedure test(p_comp_id IN cdi.comp_id%TYPE
                            ,p_empno   IN emp_test.empno%TYPE
                            )
IS
TYPE ref_cur is ref cursor;
v_cur ref_cur;
v_pk_key cdi.pk_key%TYPE;


BEGIN
  OPEN v_cur is select pk_key from cdi where comp_id =p_comp_id;
  fetch v_cur into v_pk_key;

 --now this list v_pk_key is primary key for that comp_id
 --so following things need to be done 
 --1.check the emp_test table with this column list (v_pk_key )
 --2. whether for that emp_no the primary key is null eg.
   -- incase of comp_id cde ...empno,mgr,sal  value should be not null
   --if any of the value is null raise an error 
 --3.If there are two rows for that primary also raise an error.
   -- for eg comp_id=abc  two rows are fetched from emp_test 
 close v_cur;
END;

I am not sure of the approach what should i do to,first i think of concatenating the v_pk_key like empno||ename||job and then used this in select query ,but not able to check for null values ,i am confused what to do .

EDIT

what i have tried was to convert the list v_pk_key to

NVL(empno,'$')||NVL(ename,'$')||NVL(job,'$') and then

select v_pk_list from emp_test where empno=p_empno;

and then check for $ in the result if there is no $ in the result i ll check for more than one row,but i am not finding this as an efficient solution

if anyone give me a jist of it ,i will solve this .

Surely you can find a more descriptive title for your question. — Matt Ball, Mar 08 '12 at 16:27
maybe I haven't had enough coffee, but whats the question/problem again? — tbone, Mar 08 '12 at 16:35
@tbone:the problem is that how can i check all 3 condition with less statement. — Gaurav Soni, Mar 08 '12 at 16:37
@tbone:what i have tried was to convert the list to `v_pk_key to NVL(empno,'$')||NVL(ename,'$')||NVL(job,'$')` and the `select v_pk_list from emp_test where empno=p_empno;` and then check for $ in the result ,but i am not finding this as an efficient solution — Gaurav Soni, Mar 08 '12 at 16:41

score 1 · Accepted Answer · edited May 23 '17 at 12:29

1

I would split out that list of values, which really represents 3 columns ('empno, ename, job'). Use instr function, or create a separate function to split and return a pl/sql table, but either way it would be much more clear what is intended in the code.

See here for a SO link to some examples using instr to split csv fields.

Once you have 3 separate local variables with these values (l_empno, l_ename, l_job), then you can use much easier in your various SQL statements (where l_empno = blah and l_ename not in (blahblah)), etc...

edited May 23 '17 at 12:29

Community

1
1

answered Mar 08 '12 at 17:22

tbone

15,107
3
33
40

:column is not always 3 it may vary 10+ ,we need to use more than 10 local vairable – Gaurav Soni Mar 08 '12 at 17:28
@GauravSoni if it varies, how do you know what field in the string maps to which field in another table? – tbone Mar 08 '12 at 17:43
:i just want to check for nullability of column values and count of rows ,i dint want the value of columns ,it can be done by what i have expained in question but need a more optimized solution thats why i am here – Gaurav Soni Mar 08 '12 at 17:46
1

@GauravSoni in your post, you say "1.check the emp_test table with this column list (v_pk_key )". If "v_pk_key" is a csv list that represents 3 columns of emp_test, I'm saying split out the list so that you can do this: select * from emp_test where empno=p_empno and ename=l_ename and job=l_job...; Every time I see elements of a csv list trying to be used in a meaningful way it causes problems, but I assume you can't change the schema (what everyone seems to say anyway), so I'll avoid that argument. – tbone Mar 08 '12 at 18:05

table column containing list of other table column

1 Answers1