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).

Can anyone help? Does this make sense?

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.

Hope this helps.

OH GREAT!! Thank you. I apologize, I do have:

SQL SQLMSS
SELECT QUESTION....
...
END

not just

SQL
SELECT QUESTION...
...
END

That for some reason does not work. I am unsure if it is because of the JOINS in the second table, but I will try the second option using MATCH.

Thank you so much. I will let you know if this works!!

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

TABLE FILE HOLD
PRINT *
END

Thanks so much!!