[CLOSED] virtual field joins

Mark
is VFLD2 a defined field?
does it work if you put VFLD2 WITH RFLD2 in the guest bit of the join?
If not, how about making a real concatenated field in that HOLDFILE for your JOIN.

I don't join sql files, but do you need a VFLD1? eg: would this work?
JOIN REALA AND REALB IN SQLFILE TO etc
That's what i would do in ORACLE

In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID

Posts: 3811 | Location: Manhattan | Registered: October 28, 2003

Ignored post by susannah posted

Platinum Member

posted

Sorry, the VFLD2 in the HOLDFILE *is* a "real" field--it's a concatenation from two fields in another HOLD file. So...

TABLE FILE HOLDA
PRINT *
COMPUTE VFLD2/A10 = ID || '_' || NAME;
ON TABLE HOLD AS HOLDFILE
END

Thanks.

Mark
WF 7.6 Windows

Posts: 150 | Registered: July 26, 2007

Ignored post by MacLonghorn posted

Danny-SRL

Virtuoso

posted

Mark,

You are trying to join an SQL file to a HOLD file which is a sequential file. This can work only if you can retrieve the records from the SQL in the same order as they are in the HOLD. The chances for that are slim...

Since you are creating the HOLD file, I would suggest that you make it a FOCUS file with an index. To use your example:

  
TABLE FILE HOLDA
PRINT *
COMPUTE VFLD2/A10 = ID || '_' || NAME;
ON TABLE HOLD AS HOLDFILE FORMAT FOCUS INDEX VFLD2
END

Before you do this, find out how many records in the HOLD and if you really want to have all the fields of HOLDA. Consider also using TABLEF.

Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006

Ignored post by Danny-SRL posted

Platinum Member

posted

Thanks Danny, I thought it might have something to do with sorting/index. I'm actually not doing PRINT * that was just an example. I guess I need some more help with the conditional join b/c I thought I'd be able to join on virtual fields with the "new" syntax.

Thanks.

Mark
WF 7.6 Windows

Posts: 150 | Registered: July 26, 2007

Ignored post by MacLonghorn posted

Platinum Member

posted

Susannah
Yes, I need a single concatenated field b/c WF will not join multiple fields when one of the files is a FOCUS datasource. Very annoying.

Thanks.

Mark
WF 7.6 Windows

Posts: 150 | Registered: July 26, 2007

Ignored post by MacLonghorn posted

Platinum Member

posted

Danny, apparently the sorting doesn't work either. I'm just dumbfounded why WF is unable to match two fields on each other in a join.

Thanks.

Mark
WF 7.6 Windows

Posts: 150 | Registered: July 26, 2007

Ignored post by MacLonghorn posted

GamP

Virtuoso

posted

Mark,

I have no problem at all doing exactly what you describe.
I have created a focus hold file with an index on the field that I need to join to.
Then issued the join statement.
Then issued the define for the virtual field in the mssql table.
And lastly, coded the table request to show the records. They all came out fine.
My code to run this is:

DEFINE FILE mssqltable
VFLD2/A5=EDIT(SQLFIELD,'$$$99999');
END

TABLE FILE mssqltable
SUM REAL_FLD1 AS FLD1_BIG
    REAL_FLD2 AS FLD2_BIG
    REAL_FLD3 AS FLD3_BIG
BY  VFLD2
ON TABLE HOLD AS BIG FORMAT FOCUS INDEX VFLD2
ON TABLE SET ASNAMES ON
END

JOIN VFLD1 WITH SQLFIELD IN mssqltable TO ALL VFLD2 IN BIG AS J1

DEFINE FILE mssqltable
VFLD1/A5=EDIT(SQLFIELD,'$$$99999');
END

TABLE FILE mssqltable
PRINT REAL_FLD1
      REAL_FLD2
      REAL_FLD3 
      FLD1_BIG
      FLD2_BIG
      FLD3_BIG
BY    SQLFIELD
END

And this worked fine for me...
(of course this a simplified example, just to show you my basic code).

GamP

- Using AS 8.2.01 on Windows 10 - IE11.
	in Focus since 1988

Posts: 1961 | Location: Netherlands | Registered: September 25, 2007

Ignored post by GamP posted