Hi,

I'm trying to join a flat file to some tables in a DB cause I need data out of both the tables and flat files. Here's the code:

-*****************Define flat file*****************
-SET &LOC = '/home/dppeda8/ibi/srv43/wfs/catalog';
FILEDEF ytf0656 DISK &LOC/ytf0656.txt
-**************************************************
-*********Join PO_HDR table to flat file************
JOIN
PO_HDR.PO_NBR TO ytf0656.PO_NBR AS J1
END
-***************************************************
-***************Join Oracle Tables********************
JOIN IPC AND PO_NBR IN PO_HDR TO IPC AND PO_NBR IN PO_LINE AS J3
*****************************************************
-IF &XPRT_SP EQ 'NO' THEN GOTO SCREEN ELSE GOTO EXCEL;
-SCREEN
TABLE FILE PO_HDR
HEADING CENTER
"Header Text Here"
PRINT
PO_NBR
PO_LINE_NBR AS 'PO LINE'
PO_LINE.DATE_CREATED AS 'LINE CREATED'
VENDOR_CODE AS 'VEND'
RECORDING_CO AS 'REC CO'
ITEM_ID
MR_REFERENCE_NBR
MR_LINE_NBR AS 'MR LINE'
DATE_NEEDED AS 'NEEDED'
DATE_RECEIVED AS 'RECEIVED'
QTY_PURCHASED AS 'QTY PURCH'
INVC_QTY
INVC_AMT
UNIT_PRICE
EXPEDITE_MESSAGE1
COST_ELEM_CODE AS 'CEC'
ORG_CODE
LOC_REMOTE
WORK_ORDER_NBR AS 'WO NUMB'
CHG_TO_ACCOUNT AS 'ACCT'
PO_LINE_STATUS AS 'LINE STAT'
DET_MSG1 AS 'Sub Labor Ops Region Code'

Thanks as always....

pbv,

What issue are you having? Getting errors?

Dan

Getting no data. I know the values I'm inputting are fine because I'm using a PO_NBR from the flat file in my search...

quote:

JOIN
PO_HDR.PO_NBR TO ytf0656.PO_NBR AS J1
END

I don't think your join is correct you need to say IN ... TO ... IN ...

Your join syntax is incorrect for WebFOCUS.

Also, and of more importance, you should NEVER join flat files to DB2 or any relational engine. One technique is to extract the foreign keys from the flat file and put them in a SAVE file. Then use the WHERE IN FILE syntax against the DB2 file and hold that result in a FOCUS file with an index.

The SAVE file must be 16000 bytes max.

For example:

TABLE FILE flatfile
PRINT DST.PO_NBR
WHERE screening conditions
ON TABLE SAVE
END
TABLE FILE db2table
PRINT/SUM  whatever
WHERE DB2_PO_NBR IN FILE SAVE
etc.
END  

Now I did notice that you where using 2 fields to join on the DB2 table.

You might want to give us more information on that.

Leah's comment would definitely cause your problem. Apart from that, there is one other issue I have run into when joining to flat files. If the field I am joining to is not in proper sort order in the flat file, I found that the join doesn't work sometimes. In other words, if you are joining to PO_NBR in ytf0656, then ytf0565 would need to be sorted in PO_NBR sequence.

May or may not be the problem - you'll have to fix the join first as Leah suggests, then see what you get.

Found the reference from the manual that mentions the problem I was trying to describe:

In fixed sequential files, the cross-referenced field can be any field. However, both the host and cross-referenced file must be retrieved in ascending order on the named (key) field. If the data is not in the same sort order, errors are displayed. If the cross-referenced file contains only one segment, the host file must have a segment declaration.

Hi,
You can join a flat file to a relational table. It is the opposite that you cannot do. However, remember that when you join a flat file to a DB WebFocus does the JOIN and not the DB. So if your flat file is very big it will be slow.

In reality you can join from Flat to Relational or Relation to Flat.

There are some things that need to be taken into account.

1. For joining to a flat file, you file MUST be in the right order, if the join field is out of sequence, it will not match. A simple TABLE HOLD will fix this.

2. Joining from a flat file will cause many "Calls" to the realtional DB, which may be inefficient, always have extra WHERE clauses that filter on a column in the relational table, this will improve the performance.

I would suggest for your problem, do the following.

• Check the order of the flat file join field, if it is not sorted ascending, pre sort it.
  
• Remove the second JOIN and test, see if you get info with the one join to the flat file.
  
• Set ALL to PASS, and see what data come out.
  

After this you should have more info on pinpointing the problem.