I need to get data from several tables, but the last piece of data I need can come from one of three tables, and each table uses a unique column name. I've tried setting up a CASE statement in the select, but I've obviously made a syntax error. Am I on the right track? Is there a better way? This is a DB2 database. I have limited access and do not think dyanmic SQL or creating a table is an option.
SELECT M1.MESSAGE_ID, M1.MESSAGE_NAME, M1.CREATED_DATETIME, M1.MESSAGE_SIZE, M2.PATH, case when select EXTRACTABLE_COUNT from MBX_EXTRACT_COUNT where exists (select M1.MESSAGE_ID from MBX_EXTRACT_COUNT) then select EXTRACTABLE_COUNT from MBX_EXTRACT_COUNT when select EXTRACTABLE_UNTIL from MBX_TIL_COUNT where exists (select M1.MESSAGE_ID from MBX_TIL_COUNT) then select EXTRACTABLE_UNTIL from MBX_TIL_COUNT when select EXTRACTABLE from MBX_EXTRACTABLE where exists (select M1.MESSAGE_ID from MBX_EXTRACTABLE) then select EXTRACTABLE from MBX_EXTRACTABLE end as EXTRACT_VALUE FROM MBX_MESSAGE M1 left JOIN MBX_MAILBOX M2 ON M1.MAILBOX_ID = M2.MAILBOX_ID left JOIN MBX_EXTRACT_COUNT M3 ON M1.MESSAGE_ID = M3.MESSAGE_ID left JOIN MBX_EXTRACT_TIL M4 ON M1.MESSAGE_ID = M4.MESSAGE_ID left JOIN MBX_EXTRACTABLE M5 ON M1.MESSAGE_ID = M4.MESSAGE_ID