Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Joining 2 tables in focus
Go
New
Search
Notify
Tools
Reply
  
Joining 2 tables in focus
 Login/Join
 
<ort>
posted
Hello,

I don't know how to translate the following 2 sql queries to focus language:

1. using minus between 2 tables:
select student_id
from students
minus
select student_id
from students_diploma

2. using union all between 2 tables:
SELECT CLASS_ID, STUDENT_ID,
FROM C_CLASS_STUDENTS_HISTORY
UNION ALL
SELECT CLASS_ID, STUDENT_ID,
FROM C_CLASS_STUDENTS

Can you write me the code in focus please ?

Thanks
 
Reply With QuoteReport This Post
<pranasH>
posted
Union:

TABLE FILE CAR
PRINT CAR
ON TABLE HOLD
END

TABLE FILE CAR
PRINT CAR
MORE
FILE HOLD
END

For minus use "MERGE ... ON MATCH ..." dont remermber exactly :-)

Pranas
 
Reply With QuoteReport This Post
Expert
posted Hide Post
ort, its probably a good idea to start with MATCH syntax. Have a read of Chapter 15 in the text "Creating Reports with WF Language".
The Match command accomplishes the lateral merger of two files, giving all 6 outcome possibilities. (A, B, A and B, A or B, A not B, B not A); its a good idea to master the MATCH concept, before you move on the mastering JOINS.
MATCH is very powerful, and easy. In your example, it seems you want a list of all the students who haven't gotten their diplomas yet.
so you'ld be using a A not B scenario.
something like:
MATCH FILE STUDENTS
WRITE ... BY STUDENT_ID
RUN
FILE STUDENTS_DIPLOMA
WRITE ... BY STUDENT_ID
AFTER MATCH HOLD OLD-NOT-NEW
END
..of course you'll need a master for that diploma file that has a shorter name.
And in your second example, you'll
be wanting the union, thats A OR B
so your command will be
AFTER MATCH HOLD OLD-OR-NEW
Got it?
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
Ort,

You can always resort to 'good old' SQL passthru -

1. using minus between 2 tables:
SQL
select student_id
from students
minus
select student_id
from students_diploma
;
TABLE FILE SQLOUT
PRINT *
END


2. using union all between 2 tables:
SQL
SELECT CLASS_ID, STUDENT_ID,
FROM C_CLASS_STUDENTS_HISTORY
UNION ALL
SELECT CLASS_ID, STUDENT_ID,
FROM C_CLASS_STUDENTS
;
TABLE FILE SQLOUT
PRINT *
END
 
Posts: 5601 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Joining 2 tables in focus

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.