As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you have any questions or need access: Contact email@example.com
I have tried this pass thru sql but it does not seem to be working. I am not asql expert, but perhaps it is because of the joins in one of the tables. Is there a way to do this in WF code? I have already taken a look at a June 6, 2004 entry posted here, but I did not understand the MATCH MERGE talk that was going on. This is what I have:
SQL SELECT QUESTION, QUESTION_ID FROM QUESTION_TABLE MINUS (SELECT QUES.QUESTION, ANS.QUEST_ID FROM T_PSQI_QUESTION_ID QUES, ANSWER_TABLE ANS WHERE QUES.QUESTION_ID = ANS.QUESTION_ID AND ANS.PERSON_NUM = '66030'); TABLE FILE SQLOUT PRINT * END
Basically I want all the questions and their id's that are available. Then I want to pull all the ones that were answered by person number 66030. In the ANSWER_TABLE, there will NOT be a log of the ID if the question was not answered so the MINUS should essentially take away all the questions that WERE answered leaving me with the UNANSWERED ones (which is what I want).
I don't believe the MINUS is ANSI SQL standard, therefore you could use SQL Passthrough. For example:
SQL SQLMSS SELECT QUESTION, QUESTION_ID FROM QUESTION_TABLE MINUS (SELECT QUES.QUESTION, ANS.QUEST_ID FROM T_PSQI_QUESTION_ID QUES, ANSWER_TABLE ANS WHERE QUES.QUESTION_ID = ANS.QUESTION_ID AND ANS.PERSON_NUM = '66030'); TABLE FILE SQLOUT PRINT * END
However, you can do the same thing with match, for example:
JOIN QUESTION_ID IN T_PSQI TO QUESTION_ID IN ANSWER_TABLE AS J1. END MATCH FILE T_PSQI PRINT QUESTION BY QUESTION_ID RUN FILE T_PQSI PRINT QUESTION BY QUESTION_ID WHERE PERSON_NUM = '66030'; AFTER MATCH HOLD OLD-NOT-NEW END TABLE FILE HOLD PRINT * END
The "AFTER MATCH HOLD OLD-NOT-NEW" tells focus to compare the two answer sets, and only keep the records in the first answer set (OLD) without a matching record in the second answer set (NEW). This is the same as a MINUS in SQL.
The MATCH worked great except it kept giving me 0 lines, so i was thinking it must be returning what WAS NOT matched in the SECOND TABLE. So I swithced around the tables in the JOIN line to be like the following:
(Note the PSQI table from earlier should have been the QUESTION_TABLE name... )
SQL SQLMSS SELECT QUESTION, QUESTION_ID FROM QUESTION_TABLE; TABLE FILE SQLOUT PRINT * ON TABLE HOLD AS HOLD1 END
SQL SQLMSS SELECT DISTINCT QUES.QUESTION, QUES.QUESTION_ID FROM QUESTION_TABLE QUES, ANSWER_TABLE ANS WHERE QUES.QUEST_ID = ANS.QUEST_ID AND ANS.PLAN_N = '66030'; TABLE FILE SQLOUT PRINT * ON TABLE HOLD AS HOLD2 END
JOIN QUESTION_ID IN HOLD1 TO QUESTION_ID IN HOLD2 AS J1 END MATCH FILE HOLD1 PRINT QUESTION BY QUESTION_ID RUN FILE HOLD2 PRINT QUESTION BY QUESTION_ID AFTER MATCH HOLD OLD-NOT-NEW END