![]() |
||||||||||||
Go ![]() | New ![]() | Search ![]() | Notify ![]() | Tools ![]() | Reply ![]() | ![]() |
Member |
Hi all, I have tables like this.. Table name :store Prodid Items value 1 Rice A 2 vanilla B 3 Bru C 4 Wheat A Table name : Refer F1 F2 x A Y B Z C From the Table Store , i need to lookup Table Refer I did code like this.. TABLE FILE REFER PRINT F2 BY F1 ON TABLE HOLD AS X1 FORMAT ALPHA END DEFINE FILE STORE PLK/A50=DB_LOOKUP(X1,VALUE,F2,F1); END TABLE FILE HOLD VALUE ITEM PLK PRODID END WHEN I RUN THIS CODE... It shows the error as Data retrieval has been killed & job has been stopped. Require solution.. Any suggestions ?????????? Thanks, DeviThis message has been edited. Last edited by: Kerry, WebFOCUS 7.6.4,7.6.9 OS: Windows XP HTML,EXCEL,PDF | ||
|
Expert |
Devi, Tell us what kind of files they are, like flat, DB2, etc. Also, please update your profile signature with your product suite, release, and platform so that we can better help you. Ginny --------------------------------- Prod: WF 7.7.01 Dev: WF 7.6.9-11 Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google | |||
|
Virtuoso |
I would suggest going with a straight JOIN. JOIN VALUE IN STORE TO (ALL) F2 IN REFER AS JOIN1 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 | |||
|
Virtuoso |
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 | |||
|
Expert |
Duh! I missed that. Good eye, Darin. Ginny --------------------------------- Prod: WF 7.7.01 Dev: WF 7.6.9-11 Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google | |||
|
Member |
Hi all, 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 | |||
|
<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 | ||
|
Virtuoso |
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 | |||
|
Member |
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 | |||
|
Virtuoso |
Devi, Go back to your very first post. You posted that there is an error generated:
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
| |||||||||
|
<JG> |
The error suggests you are trying to join com files Try SET PCOMMA=ON which enables support for true csv files | ||
|
Member |
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 | |||
|
Virtuoso |
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 | |||
|
Powered by Social Strata |
![]() | Please Wait. Your request is being processed... |
|