Greetings all, I have a question that I hope someone can help me with.

In my fex I have to retrieve my data in two Hold files. I would like to report off of just one of the hold files. Example below

 Select field 1, field 2, ... 
        from tables.

        table sqlout
        print LNKEY
              FIELD2
              FIELD3
              .
              .
              .
        ON TABLE HOLD AS LOANS1 FORMAT FOCUS   
  

 Select field 1, field 2, ... 
        from tables.

        table sqlout
        print LNKEY
              FIELD2
              FIELD3
              .
              .
              .
        ON TABLE HOLD AS LOANS2 FORMAT FOCUS 
        WHERE LOANS1.LNKEY EQ LOAN.LNKEY
[CODE]  

TABLE FILE LOANS2 [/CODE]


This does not work.

is there anyway that someone can think of to do what I'm attempting to do.

This message has been edited. Last edited by: FP Mod Chuck,

Are you getting an error? What did you expect and what are you getting instead?

this is what I have coded for the second query

 Select field 1, field 2, ... 
        from tables.

        table sqlout
        print LNKEY
              FIELD2
              FIELD3
              .
              .
              .
        ON TABLE HOLD AS LOANS2 FORMAT FOCUS 
        WHERE LOANS1.LNKEY2 EQ LOAN.LNKEY 

I did not get a error back, however when I did a -exit and such to see my data in Loans2 ...there was allot of other loans. I am looking to report off of the second hold file 'Only" when Loan2.lkey EQ Loans1.lnkey

I think you'll have to either join the two tables so you can get the effect you need or use MATCH FILE syntax. https://webfocusinfocenter.inf...ng/source/mrg159.htm

Thanks so much BabaNYC, I will take a look at the Match....

Hi,
If I understand correctly, you want to get from your second "select" only those records where the LNKEY field is equal to the LNKEY of the first "select". I would try the following:

  
Select field 1, field 2, ... 
        from tables.

        TABLE SQLOUT
        PRINT LNKEY
        ON TABLE HOLD AS LNKEY1
        END

Select field 1, field 2, ... 
        from tables.

        table sqlout
        print LNKEY
              FIELD2
              FIELD3
              .
              .
              .
        ON TABLE HOLD AS LOANS2 FORMAT FOCUS 
        WHERE LNKEY IN FILE LNKEY1

This should be more efficient than MATCH (unless there are more than 32K values for LNKEY in the first "select").

For small number of records DB_INFILE might be another solution.
https://webfocusinfocenter.inf...source/db_infile.htm