Hi,
i am struggling with getting my query to include desired data, basicly i want to JOIN tag with document when the query finds a match with TAG and if document title in CLIENT_Document contains the tag_no. its working perfectly when it finds an exact match however some of the data in TAG column is prefixed with 60 and is exluding that data. i have tried making a case statement with substr or regex replace but i havent been able to make it work. Would appreciate help from anyone , attached is a picture that shows desired data from tables and result/ desired result
SELECT DISTINCT
tag.project_id,
tag.tag_no,
tag.fac_suffix,
tag.tag_suffix ,
technical_document.project_no AS "PROJ_DOC_PROJ_NO",
technical_document.doc_no AS "PROJ_DOC_DOC_NO",
client_document.doc_title,
tag.func_domain_id
FROM technical_document, tag
JOIN client_document ON client_document.doc_title LIKE '%'||TAG_NO||'%'
WHERE tag.project_no LIKE '%029P%'
AND client_document.doc_no LIKE '%'||technical_document.doc_no||'%'