Scribd
Upload
29 views1 page

SQL Query for Document Assignment Data

The document contains SQL queries for retrieving data from the 'IN_DOCUMENT' and 'ASSIGNED_IN_DOCUMENT' tables. It includes a selection of various fields such as IDs, priority, state, and creation dates, as well as a method to handle comma-separated values in 'IN_DOCUMENT_ID'. The queries also join with the 'department' table to include department names in the results.

Uploaded by

Nasr Mahmoud
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
29 views1 page

SQL Query for Document Assignment Data

The document contains SQL queries for retrieving data from the 'IN_DOCUMENT' and 'ASSIGNED_IN_DOCUMENT' tables. It includes a selection of various fields such as IDs, priority, state, and creation dates, as well as a method to handle comma-separated values in 'IN_DOCUMENT_ID'. The queries also join with the 'department' table to include department names in the results.

Uploaded by

Nasr Mahmoud
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

SELECT IN_DOCUMENT_ID

--replace(IN_DOCUMENT_ID, ',', '')


FROM "IN_DOCUMENT" WHERE "id"=41

(select DISTINCT(regexp_substr(IN_DOCUMENT.IN_DOCUMENT_ID, '[^,]+', 1, level)


)
from IN_DOCUMENT IN_DOCUMENT WHERE IN_DOCUMENT.IN_DOCUMENT_ID
IS NOT NULL
connect by level <= regexp_count(IN_DOCUMENT.IN_DOCUMENT_ID,
',') + 1
)

SELECT
"ASSIGNED_IN_DOCUMENT"."id" AS "ASSIGNED_IN_DOCUMENT_ID",
"IN_DOCUMENT"."id" AS "IN_DOCUMENT_ID",
"ASSIGNED_IN_DOCUMENT"."PRIORITY",
"ASSIGNED_IN_DOCUMENT"."STATE",
to_char( IN_DOCUMENT."created", 'rrrr-mm-dd' ) AS IN_DOCUMENT_DATA,
to_char( ASSIGNED_IN_DOCUMENT."created", 'rrrr-mm-dd' ) AS
ASSIGNED_IN_DOCUMENT_DATA,
to_char( ASSIGNED_IN_DOCUMENT.REPLYING_DATE, 'rrrr-mm-dd' ) AS REPLYING_DATE,
"ASSIGNED_IN_DOCUMENT"."ACTION_TAKEN",
"ASSIGNED_IN_DOCUMENT"."ACTION_DETAIL",
"ASSIGNED_IN_DOCUMENT"."DIRECTIVE",
"IN_DOCUMENT"."SUBJECT",
"department"."name" AS DEP_NAME
FROM
"ASSIGNED_IN_DOCUMENT",
"IN_DOCUMENT",
"department"
WHERE
"ASSIGNED_IN_DOCUMENT"."IN_DOCUMENT_ID" = "IN_DOCUMENT"."id"
AND "ASSIGNED_IN_DOCUMENT"."DEP_ID" = "department"."id"
AND "IN_DOCUMENT"."id" IN
( SELECT ' [' || "IN_DOCUMENT_ID" || ' ]' as IN_DOCUMENT_ID FROM "IN_DOCUMENT"
WHERE "IN_DOCUMENT"."id" = 41 )
--(1,2,21)

You might also like