Focal Point Banner


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 myibi@ibi.com


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Pass Thru SQL using MINUS converted to WebFocus Code

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Pass Thru SQL using MINUS converted to WebFocus Code
 Login/Join
 
Platinum Member
posted
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, 2005Report This Post
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, 2004Report This Post
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, 2005Report This Post
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, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Pass Thru SQL using MINUS converted to WebFocus Code

Copyright © 1996-2020 Information Builders