How to implement "NOT EXISTS"?

M

mmarkows

New Member
Hi,
I have the following problem: I have to create a query (SELF-JOIN - f.e. using BUFFER) and write its output to file (f.e. using PUT UNFORMATTED). This is the SQL syntax. Can you help me with rewriting it to Progress 4GL (as I know this is the only way to write output to file, because Progress cannot 'spool' to file from simple SQL as oracle does)?

SELECT p1.emp-no, '0300', p1.dat-beg, p1.dat-end, p1.amount
FROM p_salary p1
WHERE p1.comp-no = 214
AND NOT EXISTS (SELECT *
FROM p_salary p2
WHERE p1.emp-no = p2.emp-no
AND p1.dat-end = p2.dat-end - day (p2.dat-end)
AND p2.comp-no = 210)

Thx in advance. Regards,
Maciej.
 
SELECT p1.emp-no, '0300', p1.dat-beg, p1.dat-end, p1.amount
FROM p_salary p1
WHERE p1.comp-no = 214
AND NOT EXISTS (SELECT *
FROM p_salary p2
WHERE p1.emp-no = p2.emp-no
AND p1.dat-end = p2.dat-end - day (p2.dat-end)
AND p2.comp-no = 210)
Click to expand...

Code: 
define buffer p1 for p_salary.
define buffer p2 for p_salary.
 
for each  p1
    where p1.comp-no = 214
 
      and not can-find(
          first p2
          where p2.emp-no = p1.emp-no
            and p2.comp-no = 210
            and p1.dat-end = p2.dat-end - day(p2.dat-end) )
 
    no-lock:
 
    display
        p1.emp-no '0300' p1.dat-beg p1.dat-end p1.amount.
 
end. /* each p1 */
 
Try Proogle at OE Hive http://oehive.org for a bit more focused search. It won't find what isn't there or what isn't indexed, but more of the hits are likely to be relevant.
 
This is the basic syntax rewite to apply.

FOR EACH tableName NO-LOCK WHERE
row attribute required criteria (fields) <> cVariableWhateverValue:
/* process here for the row selected eg assign row to tempTable */
END. /* EACH tableName */


Good luck.
 
You must log in or register to reply here.
Back
Top