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

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

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?

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