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
PRINT
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,
Devi

This message has been edited. Last edited by: Kerry,

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.

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.

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.

quote:

Also, DEFINE FILE STORE and TABLE FILE HOLD will never work.

Duh! I missed that. Good eye, Darin.

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

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

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.

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??????

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...

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

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..

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.....