If you have 3 files: afile bfile cfile
Each record in afile has: <1> bfile_id <2> cfile_id Both bfile and cfile have the nationality code(s) in attribute 1. Given the above schema, this program and dictionary will return "0" when the nationality in bfile is not in any of the values of cfile and "1" when it is: 001 SUBROUTINE nationality 002 COMMON /nationality/ b_filevar, c_filevar 003 INCLUDE qbasiccommonpick 004 EQU a_record TO access(3) 005 EQU record_count TO access(4) 006 IF record_count = 1 THEN 007 OPEN "bfile" TO b_filevar ELSE ABORT 008 OPEN "cfile" TO c_filevar ELSE ABORT 009 END 010 bfile_id = a_record<1> 011 cfile_id = a_record<2> 012 READ b_record FROM b_filevar, bfile_id ELSE b_record = "" 013 READ c_record FROM c_filevar, cfile_id ELSE c_record = "" 014 b_nationality = b_record<1> ;* single value 015 c_nationality = c_record<1> ;* multi-valued 016 b_nationality_found_in_c_nationality = @FALSE 017 c_nationality_count = DCOUNT(c_nationality, @VM) 018 FOR value = 1 TO c_nationality_count UNTIL b_nationality_found_in_c_nationality 019 b_nationality_found_in_c_nationality = (b_nationality = c_nationality<1,value>) 020 NEXT value 021 newpick(12) = b_nationality_found_in_c_nationality 022 RETURN 001 A 002 0 003 004 005 006 007 008 CALL nationality 009 R 010 5 For information on dictionary subroutines, see this page <https://docs.jbase.com/46350-jql/317985-calling-subroutine-from-dictionary-items> . Daniel Klein "Wise men speak because they have something to say; fools because they have to say something." -- Plato On Mon, Oct 15, 2018 at 1:03 PM JOSE L MARTINEZ-AVIAL <[email protected]> wrote: > Hello guys, > > I have three tables A, B and C. The records in table A contain a field > ID.B and a field ID.C, that indicates the ID of an associated record in > tables B and C. Each record in B and C also contain a field called > NATIONALITY, which is single value for B, and multivalue for C. We want to > retrieve all records in A such that the field nationality in B is not among > the values of the fields nationality in C. So it would be something like > this(pseudocode) > > SELECT A WITH EVAL 'TRANS("B",ID.B,NATIONALITY,"X") IN > TRANS("C",ID.C,NATIONALITY,"X") ' EQ 0 > > What will be the best option to do this? > > Thanks > > JL > > > -- > -- > IMPORTANT: T24/Globus posts are no longer accepted on this forum. > > To post, send email to [email protected] > To unsubscribe, send email to [email protected] > For more options, visit this group at > http://groups.google.com/group/jBASE?hl=en > > --- > You received this message because you are subscribed to the Google Groups > "jBASE" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- -- IMPORTANT: T24/Globus posts are no longer accepted on this forum. To post, send email to [email protected] To unsubscribe, send email to [email protected] For more options, visit this group at http://groups.google.com/group/jBASE?hl=en --- You received this message because you are subscribed to the Google Groups "jBASE" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
