How to pass value as parameter to join tables in Oracle SQL-CodePudding

We have situation like each project has separate / unique table. and each table has unique column name.

For ex Project AAA is having table A1_table, in this table the column name will be A1_APP, A1_DOCUMENT, A1_Pages and so on.

Similarly for Project BBB will have table B1_Table and this table will have column name like B1_APP, B1_DOCUMENT, B1_Pages.

I am trying to join the table by passing the column name value as parameter. Since it will be difficult to change the column name for each project

Since we have different column name i could not able to join the table.

Kindly advise

Note : The table is already created by vendor. i am just trying to extract data for all studies. so it will be difficult for me to rename the column one by one

Sql Script :

DECLARE 

V_IMG_DOC_ID INT := '12345';
V_SHORT_DESC  NVARCHAR2(100) :=  'B18' ;
v_sql        VARCHAR2(5000);

BEGIN 
Select C.PROJECT "PROJECT", D.SUBJECT "SUBJECT_NO",D.SITE_NUMBER, E.IMAGE_ID,E.IMG_DOC_ID,F. 
DTYPE_DESC "DOCUMENT_TYPE",E.IMG_FILENAME,E.IMG_NAT_FILE_ORG "FILE_LOCATION" 

from APPLICATION A 
inner join  B18_DOCUMENT B on A. APP_ID = B.||V_SHORT_DESC||_APP_ID
inner join PROJECT_IMAGE E on E.IMG_DOC_ID = B.||V_SHORT_DESC||D_DOC_ID
inner join SUBJECT D on D.SJ_ID = B.||V_SHORT_DESC||D_SJ_ID
Inner join PROTOCOL C on  C.APP_ID = A.APP_ID
inner join DOCUMENTTYPE F on F. DT_APP_ID = A. APP_ID and F. DT_ID  = 
B.||V_SHORT_DESC||D_DT_ID
where E.IMG_DOC_ID = 5877630
ORDER BY E.IMG_DOC_ID  DESC;

END;

CodePudding user response：

That looks like a terribly wrong data model. If you want to pass table/column names and use them in your queries, you'll have to use dynamic SQL which is difficult to maintain and debug.

By the way, do you really plan to duplicate, triplicate, ... all your tables to fit all new projects? That's insane!

Should be something like this (table_1 has a foreign key constraint, pointing to project table).

SQL> create table project
  2    (id_project    number       constraint pk_proj primary key,
  3     name          varchar2(20) not null
  4    );

Table created.

SQL> create table table_1
  2    (id            number       constraint pk_t1 primary key,
  3     id_project    number       constraint fk_t1_proj references project (id_project),
  4     app           varchar2(20),
  5     document      varchar2(20),
  6     pages         number
  7    );

Table created.

Sample rows:

SQL> insert into project (id_project, name) values (1, 'Project AAA');

1 row created.

SQL> insert into project (id_project, name) values (2, 'Project BBB');

1 row created.

SQL> insert into table_1 (id, id_project, app) values (1, 1, 'App. 1');

1 row created.

SQL> insert into table_1 (id, id_project, app) values (2, 1, 'App. 2');

1 row created.

SQL> insert into table_1 (id, id_project, app) values (3, 2, 'App. 3');

1 row created.

Sample queries:

SQL> select * from project;

ID_PROJECT NAME
---------- --------------------
         1 Project AAA
         2 Project BBB

SQL> select a.id, p.name project_name, a.app
  2  from table_1 a join project p on p.id_project = a.id_project
  3  order by p.name, a.id;

        ID PROJECT_NAME         APP
---------- -------------------- --------------------
         1 Project AAA          App. 1
         2 Project AAA          App. 2
         3 Project BBB          App. 3

SQL>

CodePudding user response：

Refactor your code so that the projects are all in the same table and add a project_name column.

CREATE TABLE project_documents (
  project_name VARCHAR2(10),
  app_id       NUMBER,
  document     CLOB,
  pages        VARCHAR2(50)
  -- ,... etc.
)

If you want to restrict users to only seeing their own projects then you can use a virtual private database.

Then you do not need to use dynamic SQL to build queries with lots of different table names and can just use the one table for all projects and add a filter for the specific project using the added project_name column.

If you cannot do that then you are going to have to either:

use dynamic SQL to build the queries and dynamically set the table and column names each time you run the query; or

create a view of all the projects:

CREATE VIEW all_projects (project_name, app_id, document, pages /*, ... */) AS
  SELECT 'A1', a1_app_id, a1_document, a1_pages /*, ... */ FROM a1_table UNION ALL
  SELECT 'A2', a2_app_id, a2_document, a2_pages /*, ... */ FROM a2_table UNION ALL
  SELECT 'B1', b1_app_id, b1_document, b1_pages /*, ... */ FROM b1_table UNION ALL
  SELECT 'B18', b18_app_id, b18_document, b18_pages /*, ... */ FROM b18_table

and then you can query the view using the normalised column names rather than the project-specific names.

(Note: You will have to update the view when new projects are added.)