Pass Thru SQL using MINUS converted to WebFocus Code

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?

Posts: 178 | Registered: May 11, 2005

dhagen

Virtuoso

posted

Hide Post

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.

Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004

Ignored post by dhagen posted

Show Post

slfmr

Platinum Member

posted

Hide Post

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!!

Posts: 178 | Registered: May 11, 2005

Ignored post by slfmr posted

Show Post

slfmr

Platinum Member

posted

Hide Post

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!!

Posts: 178 | Registered: May 11, 2005

Ignored post by slfmr posted

Show Post

Please Wait. Your request is being processed...

Read-Only Topic

Focal Point

Focal Point Forums

WebFOCUS/FOCUS Forum on Focal Point

Pass Thru SQL using MINUS converted to WebFocus Code

Focal Point | Privacy Statement | Terms of Service