I have two synonyms that contain agency information. I need information from both synonyms so I match both on agency_id. I take the hold file, that is created, and I need to join this hold file to a fact table so that I can get additional. After I join to the fact table all the browser does is sits and spins and eventually times out. Here is the code I am using. Any thoughts?

MATCH FILE DIM_AGENCY
  PRINT DIM_AGENCY.DIM_AGENCY.DIM_AGENCY_KEY DIM_AGENCY.DIM_AGENCY.AGENCY_NAME DIM_AGENCY.DIM_AGENCY.AGENCY_PHYSICAL_ADDRESS_1 DIM_AGENCY.DIM_AGENCY.AGENCY_PHYSICAL_ADDRESS_2 DIM_AGENCY.DIM_AGENCY.AGENCY_STATE DIM_AGENCY.DIM_AGENCY.AGENCY_STATE_NAME 
  BY DIM_AGENCY.DIM_AGENCY.AGENCY_ID 
RUN
FILE COMM
  PRINT COMM.COMM.SUBDIVISION
  BY COMM.COMM.AGENCY_ID NOPRINT
AFTER MATCH HOLD AS AGENCY_COMM OLD-OR-NEW
END
-RUN

JOIN
CARDS_DATAMART_INTAKE.FACT_CARDS_INTAKE.DIM_AGENCY_KEY IN CARDS_DATAMART_INTAKE
TO UNIQUE AGENCY_COMM.AGENCY_C.DIM_AGENCY_KEY IN AGENCY_COMM TAG J0 AS J0
END

Thanks,

Daniel

This message has been edited. Last edited by: <Kathryn Henning>,

Daniel,

Your MATCH command created a sequential file.
What type of file is CARDS_DATAMART_INTAKE?
If it is also sequential then both file MUST BE SORTED by the JOIN field.
However, I can assume that it is not sequential.
So, either you join the AGENCY_COMM file to CARDS_DATAMART_INTAKE or you need another step to load the data of AGENCY_COMM into a FOCUS file with DIM_AGENCY_KEY declared as an INDEX. Or, if CARDS_DATAMART_INTAKE is an SQL table, load AGENCY_COMM into another SQL table.

Good luck!

The match created a sequential hold file in agency_id order and the join is on dim_agency_key. It is unclear if these are the same value.

I'm unsure of the file format of the host file in the join. Is it the same order as the sequential hold file you just created?

Lori

Danny, the CARDS_DATAMART_INTAKE is a resutset from a MSSQL table.

Lori, I matched on the agency_id because that is the common filed in both synonym. Both synonyms are sorted by the agency_id. One of the sysnonym that is used in the match contains the dim_agency_key field so when I join I have the dim_agency_key in the match file to join the the main fact synonym. The agency_id and dim_agency_key are not the same id. All host files come from a MSSQL result set.

Daniel

Just take your HOLD file and re-sort it again with:

ON TABLE HOLD AS xyz FORMAT FOCUS INDEX AGENCY_ID

If you do that it won't matter if your HOLD file isn't in the same sequence as the host file.

Either the host and the target have to be sorted in the same order or the target joined has to be a keyed file.

As other have indicated. Take the hold file from the match and hold it as a FOCUS file indexing the field you need.

George have tyou the syntax on the HOLD.

Lori

Daniel - Consider holding the CARDS_DATAMART_INTAKE data in FOCUS format with an index, and reversing the JOIN statement to JOIN from AGENCY_COMM to CARDS_DATAMART_INTAKE.

That worked thanks.