Hello,

I am getting a list of unique records in one hold file and I want to join them up to my master file. This is a one to many relationship. How can we join hold files up to the synonym to filter down the records? The only way I am aware of is if I do a claims dump into a hold file and join from there (not efficient). Isn't IN FILE with 200,000 records not efficient either?

What is the most efficient way to do this? The records in the hold file would be about 200,000 and then records in the Master File are over 5,000,000.

Here's an example of the table structure I have

 

HOLD FILE 1
1
2
3
4
5
6
7
8
9
10
11
12

Synonym
1
1
1
2
2
3
4
4
4
4
4
5
5
6
6
6
7
7
7
8
9
9
10
 

This message has been edited. Last edited by: Brandon Andrathy,

Where is your data coming from ? RDBMS ?

Is the hold file from the same DB ?

You could try FORMAT SAME_DB, then just join them, if its all the same DB ?

There is always MATCH FILE. We aren't allowed to create temporary HOLD files in the database so all of ours are WebFOCUS hold files. Sometimes, when there is a lot of data in the HOLD file, MATCH FILE has better performance (runtime wise) than JOIN and TABLE. Thing is, if you look at what WebFOCUS is doing, a lot of times it converts JOIN and TABLE to MATCH FILE.

The most efficient way to do this is to make sure both files are in the same DBMS. Otherwise, you might unwittingly cause a scan of the first table before joining it to the second table. If this is an on the fly join, try to push everything into the same DBMS using HOLD FORMAT xyzdbms INDEX joinfieldname

Hey all,

Thank you very much for your responses!

Babak, I'm thinking your suggestion regarding index might be the way to go. Could you give me an example of what you're thinking with the CAR File code I have below?

 TABLE FILE CAR
BY COUNTRY
WHERE COUNTRY EQ 'FRANCE' OR 'ENGLAND'
ON TABLE HOLD AS 'CAR1'
END

TABLE FILE CAR
SUM
	SALES
BY COUNTRY
ON TABLE HOLD AS 'CAR2'
END

JOIN
COUNTRY IN CAR1 TO UNIQUE
COUNTRY IN CAR2 TAG J0 AS J0
END 

Waz, could you also give me an example of what you are thinking with: SAME_DB PERSISTENCE GLOBAL_TEMPORARY

I got the index created on my second hold file with this. But I'm not sure where to go from here.

Sounds like you are dealing with two relational tables.

Here is an example of creating a subquery to be used in a nested subquery SELECT:

http://forums.informationbuild...077033876#9077033876

You could also join to the table you created a 'SQL_SCRIPT' for.

David, DBINFILE creates exactly the type of SQL I want to see!

Wow, I have used DBINFILE but not with FORMAT SQL_SCRIPT. It's insane how DBINFILE works differently with SQL_SCRIPT.

Thanks a ton for this solution! My Data guy is gonna be happy I won't be causing him headaches with crazy queries lol.

My resulting query looks like something like the following for anyone this might help:

  TABLE FILE CAR
BY COUNTRY
WHERE COUNTRY EQ 'FRANCE' OR 'ENGLAND'
ON TABLE HOLD AS 'CAR1' FORMAT SQL_SCRIPT
END

TABLE FILE CAR
SUM
	SALES
BY COUNTRY
WHERE DBINFILE(CAR1, COUNTRY, COUNTRY)
END
  

Is there anything I might be able to do to speed up DBINFILE? Maybe make sure indexes are created within the database?

Assuming the data is all on the same DB, the first query (assuming it is coming direct from the DB, no intermediate hold files), you use FORMAT SAME_DB.

This will generate SQL to create a temp table and insert the records into it.
Then its a simple case of a join, how ever you like to do it.

e.g.

TABLE FILE CAR
BY COUNTRY
WHERE COUNTRY EQ 'FRANCE' OR 'ENGLAND'
ON TABLE HOLD AS 'CAR1' FORMAT SAME_DB
END

-RUN

JOIN
COUNTRY IN CAR1 TAG C1 TO UNIQUE
COUNTRY IN CAR  TAG C2 AS J0
END 

TABLE FILE CAR1
SUM
	C2.SALES
BY C1.COUNTRY
ON TABLE HOLD ....
END

-RUN

Thank you Waz. I appreciate your response. You've helped me a lot this week .

Closing this post.