I have this query
SELECT DISTINCT
i.id1,
length(s.family_id) len,
s.family_id
FROM
tabel1 i,
tabel2 s
WHERE
i.id1 = s.member1
OR i.id1 = s.member2
OR i.id1 = s.member3
OR i.id1 = s.member4
OR i.id1 = s.member5
OR i.id1 = s.member6
OR i.id1 = s.member7
OR i.id1 = s.member8
OR i.id1 = s.member9
OR i.id1 = s.member10
OR i.id1 = s.member11
OR i.id1 = s.member12
OR i.id1 = s.member14
OR i.id1 = s.member13
OR i.id1 = s.member15
OR i.id1 = s.member16
OR i.id1 = s.member17
OR i.id1 = s.member18;
but the number of members is not permanent (every run it can be changed this run it was 18 and the next run it can be 3 ) their is any way to make this where statement automatic ? (the members are coming from TABEL2 ) I thought about doing it with a for loop, but I couldn't make it happen.
CodePudding user response：
Shortly: that's an awful data model. Discard it, as soon as possible. Table - that contains MEMBER information - should be in master-detail relationship to tabel1 and current tabel2 (which doesn't have any member columns).
Mind you - currently, you have from member1 up to member18 columns. What if member19 has to be added? Will you modify all queries you've written, in all procedures, functions, views, forms, reports, ...? You can, but you should not.
I don't know what those tables contain, but - consider something like this:
SQL> CREATE TABLE tabel1
2 (
3 id1 NUMBER PRIMARY KEY
4 );
Table created.
SQL> CREATE TABLE tabel2
2 (
3 id2 NUMBER PRIMARY KEY,
4 id2_1 NUMBER REFERENCES tabel1 (id1),
5 family_id NUMBER
6 );
Table created.
SQL> CREATE TABLE tabel3
2 (
3 id3 NUMBER PRIMARY KEY,
4 id3_2 NUMBER REFERENCES tabel2 (id2),
5 member NUMBER REFERENCES tabel1 (id1)
6 );
Table created.
Query would then look like this:
SQL> SELECT DISTINCT a.id1, LENGTH (b.family_id) len, b.family_id
2 FROM tabel1 a
3 JOIN tabel2 b ON b.id2_1 = a.id1
4 JOIN tabel3 c
5 ON c.id3_2 = b.id2
6 AND c.member = a.id1
7 WHERE a.id1 = 1;
no rows selected
SQL>
Of course there's nothing returned, my tables are empty. But, with such a data model, you'd just add another row into tabel3, without modifying any code you've written so far.
CodePudding user response：
With this data structure and my understanding of the question...
- There is no link between data in those tables
- Table with member id always has just one row with different number of columns after every run
- The question is how to define Where clause to get just the member ids that are present in the particular run
There is no loop in Oracle's Where clause, so we have to think of workaround.
Simulated data in table containing member ids (1 row with 18 member id columns)
CREATE TABLE A_TABLE_OF_FAMILY_MEMBERS AS
SELECT 'XXX' "FAMILY_ID", 'This is dummy column' "DUMMY",
1 "MEMBER1", 2 "MEMBER2", 3 "MEMBER3", 4 "MEMBER4", 5 "MEMBER5", 6 "MEMBER6", 7 "MEMBER7", 8 "MEMBER8", 9 "MEMBER9",
10 "MEMBER10", 11 "MEMBER11", 12 "MEMBER12", 13 "MEMBER13", 14 "MEMBER14", 15 "MEMBER15", 16 "MEMBER16", 17 "MEMBER17", 18 "MEMBER18"
FROM Dual
Simulated data in table 1
WITH
a_table AS
(
Select 1 "ID", 'Name 1' "NAME" From Dual Union All
Select 2 "ID", 'Name 2' "NAME" From Dual Union All
Select 3 "ID", 'Name 3' "NAME" From Dual Union All
Select 4 "ID", 'Name 4' "NAME" From Dual Union All
Select 5 "ID", 'Name 5' "NAME" From Dual Union All
Select 6 "ID", 'Name 6' "NAME" From Dual
),
Workaround for your WHERE clause - create function to get all the member id values in particular run
Just for demonstration there are three options of the function's return value. See the results in code below:
create or replace Function get_member_columns(p_What VarChar2 := 'cols') RETURN VarChar2 IS
BEGIN
Declare
mRet VarChar2(512);
cols VarChar2(512);
Begin
If Lower(p_What) = 'len' Then -- return list of column names
SELECT To_Char(Count(*)) INTO cols
FROM
(
SELECT tc.COLUMN_NAME
FROM all_tab_cols tc
WHERE tc.TABLE_NAME = 'A_TABLE_OF_FAMILY_MEMBERS' AND tc.COLUMN_NAME LIKE('MEMBER%')
);
mRet := cols;
ElsIf Lower(p_What) = 'cols' Then -- return number of member columns
SELECT LISTAGG(tc.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY To_Number(Replace(tc.COLUMN_NAME, 'MEMBER', '')))
INTO cols
FROM all_tab_cols tc
WHERE tc.TABLE_NAME = 'A_TABLE_OF_FAMILY_MEMBERS' AND tc.COLUMN_NAME LIKE('MEMBER%');
mRet := cols;
ElsIf Lower(p_What) = 'vals' Then -- return list of column values
Declare
CURSOR c IS
SELECT COLUMN_NAME
FROM all_tab_cols
WHERE TABLE_NAME = 'A_TABLE_OF_FAMILY_MEMBERS' AND COLUMN_NAME LIKE('MEMBER%')
ORDER BY To_Number(Replace(COLUMN_NAME, 'MEMBER', ''));
sq VarChar2(1) := ''''; -- single quote character
sql_txt VarChar2(512);
col VarChar2(32);
vals VarChar2(512) := '';
val VarChar2(32) := '';
Begin
OPEN c;
LOOP
FETCH c INTO col;
EXIT WHEN c%NOTFOUND;
sql_txt := 'SELECT ' || col || ' FROM A_TABLE_OF_FAMILY_MEMBERS';
Execute Immediate(sql_txt) INTO val;
vals := vals || sq || val || sq || ', ';
END LOOP;
CLOSE c;
mRet := SubStr(vals, 1, Length(vals) - 2);
End;
End If;
RETURN mRet;
Exception
WHEN NO_DATA_FOUND THEN RETURN 'ERR - [function: get_member_columns()] --> ' || SQLERRM;
WHEN OTHERS THEN RETURN 'ERR - [function: get_member_columns()] --> ' || SQLERRM;
End;
END get_member_columns;
--
-- ------------------------------------------------------
-- Test function return options
SET SERVEROUTPUT ON
Begin
DBMS_OUTPUT.PUT_LINE('Number of member_id columns ---> ' || get_member_columns('len'));
DBMS_OUTPUT.PUT_LINE('List of names of member_id columns ---> ' || get_member_columns('cols'));
DBMS_OUTPUT.PUT_LINE('List of values in member_id columns ---> ' || get_member_columns('vals'));
End;
--
-- TEST RESULTS
--
-- anonymous block completed
-- Number of member_id columns ---> 18
-- List of names of member_id columns ---> MEMBER1, MEMBER2, MEMBER3, MEMBER4, MEMBER5, MEMBER6, MEMBER7, MEMBER8, MEMBER9, MEMBER10, MEMBER11, MEMBER12, MEMBER13, MEMBER14, MEMBER15, MEMBER16, MEMBER17, MEMBER18
-- List of values in member_id columns ---> '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18'
For unification of your WHERE clause we will need just the 'vals' option.
Create a CTE containing the values of member ids in the particular run and rewrite the main SQL
WITH
member_vals AS
( Select get_member_columns('vals') "MEMBER_IDS" From Dual )
--
-- Main SQL
SELECT DISTINCT
i.ID,
s.FAMILY_ID,
get_member_columns('len') "NUMBER_OF_MEMBERS"
FROM
a_table i
INNER JOIN
A_TABLE_OF_FAMILY_MEMBERS s ON( 1 = 1 )
INNER JOIN
member_vals m ON (INSTR(m.MEMBER_IDS, '''' || To_Char(i.ID) || '''') > 0)
ORDER BY i.ID
It is not a good way to do it at all, but if your situation is like described and you can't change the structure and logic of the data, it could help.
This way the function in every run would give you either list of member id column names or values, as well as the number of those columns if you need it. The values will be compared with the ID from table 1 and if there is a match a row will be returned.
With the data from this answer the result is as follows
--
-- R e s u l t :
--
-- ID FAMILY_ID NUMBER_OF_MEMBERS
-- ---------- --------- --------------------
-- 1 XXX 18
-- 2 XXX 18
-- 3 XXX 18
-- 4 XXX 18
-- 5 XXX 18
-- 6 XXX 18