Hi all,
I'm looking to join 3 tables and can't seem to figure it out. Here is what my current syntax is (only 1 table, cannot get the others to join with aliased field):
select WI_ID as Workflow_ID, WI_CD as Creation_Date, WI_AED as Completed_Date, WI_AAGENT as AGENT, WI_TEXT as Description, WI_RHTEXT as Short,
CASE
when WI_RHTEXT = 'DP Document Dashboard' THEN substr(WI_TEXT,18,12)
End as DocID
from TBL_DS_ER_SWWWIHEAD;
I want to join 'TBL_DS_ER_SWWWIHEAD' to table 'DS_ER_OPT_VIM_1HEAD' using the alias DOCID from SWWWIHEAD table and "doc_num" field from the VIM_1HEAD table. The reason that I had to substring it was because the WI_TEXT field is something like 'DP Document Dashboard 990034564345' but I only want the # from that field. From the VIM_1HEAD table I want to bring back the "current_proc" field. Then I want to join the "current_proc" field to table 'TBL_DS_ER_TOTAL1' table using "current_proc" from the 'DS_ER_OPT_VIM_1HEAD' and the "exception_id" field from the TOTAL1 table to bring back "exception_description" field. I would like the output to look like:
WORKFLOW_ID CREATION_DATE COMPLETED_DATE AGENT DESCRIPTION SHORT DOCID CURRENT_PROC EXCEPTION_DESCRIPTION
I appreciate your help! Please let me know if you have any questions at all!