Type of files, as Ginny suggests, will make a difference. Flat files have additional issues, keys on fields in RDBMS tables, etc.
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat
September 05, 2008, 12:01 PM
Darin Lee
Also, DEFINE FILE STORE and TABLE FILE HOLD will never work.
And while I'm at it, here are some other restrictions from the manual that should be adhered to:
The maximum number of pairs that can be used to match records is 63.
If the lookup file is a fixed format sequential file, it must be sorted and retrieved in the same order as the source file. The sequential file's key field must be the first lookup field specified in the DB_LOOKUP request. If it is not, no records will match.
In addition, if a DB_LOOKUP request against a sequential file is issued in a DEFINE FILE command, you must clear the DEFINE FILE command at the end of the TABLE request that references it or the lookup file will remain open. It will not be reusable until closed and may cause problems when you exit WebFOCUS. Other types of lookup files can be reused without clearing the DEFINE. They will be cleared automatically when all DEFINE fields are cleared.
If the lookup field has the MISSING=ON attribute in its Master File and the DEFINE or COMPUTE command specifies MISSING ON, the missing value is returned when the lookup field is missing. Without MISSING ON in both places, the missing value is converted to a default value (blank for an alphanumeric field, zero for a numeric field).
Source records display on the report output even if they lack a matching record in the lookup file.
Only real fields in the lookup Master File are valid as lookup and return fields.
If there are multiple rows in the lookup table where the source field is equal to the lookup field, the first value of the return field is returned.
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat
September 05, 2008, 12:09 PM
GinnyJakes
quote:
Also, DEFINE FILE STORE and TABLE FILE HOLD will never work.
Sorry for the insufficient information provided in my previous post.. Now I created below two tables by Excel & uploaded it.
Source table - Store Lookup table - Refer
In real life scenario , Source will be in the SAP & Lookup will be either in EXcel or DB2 , since we have no connectivity in the system, we are just doing a POC.
Table name :store { primary Key -Prodid }
Prodid Items value 1 Rice A 2 vanilla B 3 Bru C 4 Wheat A
Table name : Refer {Primary Key - F1 }
F1 F2
x A Y B Z C
I need to look up the fields - Value & F2 ; Returning field - F3
I NEED THE OUTPUT AS
PRODID ITEMS VALUE PLK 1 RICE A X 2 vanilla B Y 3 Bru C Z 4 Wheat A X
As suggested by Darin Lee, i removed the hold file .
and did the coding as below..
TABLE FILE REFer PRINT F2 BY F1 END DEFINE FILE STORE PLK/A50=DB_LOOKUP(Refer,VAL,F2,F1) END TABLE FILE STORE PRINT PLK VALUE ITEMS BY PRODID END
But I didn't get the output. Is ther any other way to get it??? Any suggestions??
Thanks DARIN LEE & GiNNYJAKES for ur suggestions for my previous post..
OS-Windows XP
WebFOCUS 7.6.4,7.6.9 OS: Windows XP HTML,EXCEL,PDF
September 08, 2008, 08:50 AM
<JG>
If the lookup file is a flat file the the source file must be sorted in the same order so you will need to sort that so it looks like
Prodid Items value 1 Rice A 4 Wheat A 2 vanilla B 3 Bru C
September 08, 2008, 11:19 AM
Darin Lee
Sorry for the confusion - what I was suggesting was that you can't do a DEFINE on STORE and TABLE on HOLD and get the desired results. Both the DEFINE and the TABLE must reference the same file.
As for the other way to get it, try the JOIN. There's not any difference in efficiency between what you're trying to do with the DB_LOOKUP function and using a JOIN and it appears that any requirements for sort order, etc. will be in effect for either one.
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat
September 09, 2008, 03:31 AM
Devi C
Hi Darin,
Thanks for your suggestion.
We have worked on join.. & did the coding as below..
JOIN VALUE IN STORE TO ALL F2 IN REFER AS J0 END
When we run the report ...it is showing the error as
(FOC1101) THIS INTERFACE DOES NOT SUPPORT THE GLOBAL JOIN
Any suggestions??????
WebFOCUS 7.6.4,7.6.9 OS: Windows XP HTML,EXCEL,PDF
September 09, 2008, 08:10 AM
GamP
Devi,
Go back to your very first post. You posted that there is an error generated:
quote:
Data retrieval has been killed & job has been stopped
This means that the order of the data is unexpectedly incorrect, in other words, that the file you use to look the values up in is out of order compared to the host file. Your files are now stored in excel - which means that you use the odbc connector to retrieve the data - and that means that is basicly nothing more than a fixed file. Thus, the records in refer must be in the same order as in store. Which they are not, when looked at it from the value/f2 fields. The following will work with this environment (at least it does so in my system):
TABLE FILE STORE
PRINT PRODID ITEMS
BY VALUE
ON TABLE HOLD
END
DEFINE FILE HOLD
PLK/A50=DB_LOOKUP(Refer,VAL,F2,F1)
END
TABLE FILE HOLD
PRINT PLK VALUE ITEMS
BY PRODID
END
Hope this helps...
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
September 09, 2008, 09:00 AM
<JG>
quote:
THIS INTERFACE DOES NOT SUPPORT THE GLOBAL JOIN
The error suggests you are trying to join com files
Try SET PCOMMA=ON which enables support for true csv files
September 09, 2008, 09:29 AM
Devi C
Hi all,
Thanks a lot for your suggestions.. I got the output . I did the coding as suggested by Gamp. Problem with lookup is resolved. Special thanks to GAMP..
WebFOCUS 7.6.4,7.6.9 OS: Windows XP HTML,EXCEL,PDF
September 09, 2008, 11:24 AM
Darin Lee
quote:
If the lookup file is a fixed format sequential file, it must be sorted and retrieved in the same order as the source file. The sequential file's key field must be the first lookup field specified in the DB_LOOKUP request. If it is not, no records will match.
Way back in the beginning.....
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat