Our Client requested additional functionality for their DataBase.
They wanted to inform Document owners that their documents needed to be renewed. Our Client stores two types of documents in their DataBase.
1. Documents that were initially released ( e.g. Document A) and
2. the Renewed version of the same document (e.g. Document A-Renew).
FWI You can compare these documents to the Driver license issued by the Dutch CBR.
Our Client initially put the onus on the Document owners to renew their own documents, but that did not really benefit the processes in their organization.
Our Client requested therefore a functionality that automates the process to inform Document owners to renew their documents.
The DataBase holds the metadata about these documents, including the date these documents were issued and the period of validity of those document. It was quickly understood that you cannot inform Document owners to renew a document that was already indicated as a renewal of their initial document. Because both renewed and initial issues documents carry the exact same weight, it was decided to just communicate about a "BasicDocument (e.g. Document A) to cover both the initial and the '-Renew'. Because the Table holding the metadata had a record type record that held both the information, 'A' and 'A-Renew', it became a challenge to trim '-Renew' from the one record. The solution became to add a new column to the record, holding just '-Renew' and the likes. Our Client now communicates to the Document owners using the "BasicDocument", while for other purposes the Query for "Document" is used. See below.
Code: Select all
SELECT
"tTypeOfDocument"."TypeOfDocument" ||
COALESCE ( ' ' || "tTypeOfDocumentCoding"."TypeOfCertificateCoding", '' ) "BasicDocument"
FROM
"tTypeOfDocument",
{
oj "tDocumentQualification"
LEFT OUTER JOIN "tTypeOfDocumentCoding" ON
"tDocumentQualification"."FKTypeOfDocumentCodingID" = "tTypeOfDocumentCoding"."TypeOfDocumentCodingID"
}
WHERE
"tDocumentQualification"."FKTypeOfDocumentID" = "tTypeOfDocument"."TypeOfDocumentID"
for the full document info:
Code: Select all
SELECT
"tTypeOfDocument"."TypeOfDocument" ||
COALESCE ( ' ' || "tTypeOfDocumentCoding"."TypeOfCertificateCoding", '' ) ||
COALESCE ( '-' || "tTypeOfDocumentCoding"."TypeOfDocumentCodingStatus", '' ) "Document"
FROM
"tTypeOfDocument",
{
oj "tDocumentQualification"
LEFT OUTER JOIN "tTypeOfDocumentCoding" ON
"tDocumentQualification"."FKTypeOfDocumentCodingID" = "tTypeOfDocumentCoding"."TypeOfDocumentCodingID"
}
WHERE
"tDocumentQualification"."FKTypeOfDocumentID" = "tTypeOfDocument"."TypeOfDocumentID"
Hopes this explains our solution.
Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.