Hey all,

So, I have a colleague that is trying to make a UPC description (present in a permanent FOCUS datamart) available with another MSSQL data source that has UPCs only.

His dilemma is that he wants to have a report that lets him use that description among all the other MSSQL fields around halfway through the columns being included in the final request. I'm not too expert on joining differing data source types together to do the job. I've always used a KU join to tack another data source onto the end of a parent data source as its child. (We are joining the FOCUS file to the MSSQL file by UPC fields of the same format A14V.) We tried such, but we aren't getting any data back when we sample it. It's just blank. We sample the FOCUS file and get field values just fine. It's worked in every other scenario we did this type of join in before. Not sure why it's having issues. If we can't get a FOCUS file to join to a MSSQL file at the master file level, how could one whip up something in a fex? We've looked at MATCH, MORE, etc. Not finding any luck. Probably because we're so new to this end of things still (BI layer joins).

We've been spinning our wheels all day with documentation, looking around in the forums, etc. with no luck.

Any help/guidance will be much appreciated.

Thanks in advance!

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

Cool,
Is your UPC a 12-digit number?

Could you copy and paste the UPC field's USAGE and ACTUAL from both master files? Does the table that you are joining have a unique number of UPC/UPC Descriptions (i.e. no repetition of a UPC).

CoolGuy,

My advice is don't...

With that being said let me clarify. Don't join a FOCUS table directly with an MSSQL table. It is never efficient and depending on the size of the request will take forever to come back. Cross Platform joins are never pretty.

What I would suggest is to pull your data from MSSQL to the reporting server and hold it in a FOCUS table, then you can join the two tables together without any issues as they will be both be on the Reporting Server in the same format

 
TABLE FILE MY_ITEMS
PRINT FIELD_1 FIELD_2
BY UPC
WHERE THIS
WHERE THAT
ON TABLE HOLD AS MY_SQL_DATA FORMAT FOCUS INDEX UPC
END

JOIN UPC IN MY_SQL_DATA TO UPC IN MY_FOCUS_DATA AS J0
TABLE FILE MY_SQL_DATA
PRINT FIELD_1 FIELD_2 FOCUS_DESC
BY UPC
END

The key thing to pay attention to when joining FOCUS tables is the Index. I believe you can only join on one field for a FOCUS file and that parent field must be indexed. XFOCUS I think will allow you to join on more fields and can grow to be larger (I think FOCUS has a file size limit of 4GB where XFOCUS is 16GB... Worth double checking though).

Hopefully you aren't talking about doing this in a synonym. If thats the case, then I would suggest posting your master files and how you are doing your join. Also might be worth running a trace to see the SQL being generated.

Hi Cool Guy

and make sure that the index name is not more than 8 char or the index will not work...

ON TABLE HOLD AS TABLE_NAME FORMAT XFOCUS INDEX ABC BCD

Also try using the GUI to build the JOIN that may help you since there is a lot of logic in the backend code. I do it and that always helps with joins.

Best of Luck,

Barry Solomon

@eric.woerle

Thank you so much. That worked like a charm. I really appreciate you taking the time to help me out.

@CoolGuy

Thanks for posting this to help out a WF noob.

To all,

Thank you so much for your willingness to chime in and help us on this. Thank you Eric for your suggestion too that ended up being the thing we needed!

I'd write to everyone individually but I'm in the middle of a server crisis.

Thanks again everyone!

Another option, depending on the number of descriptions, is to hold the data as a temporary SQL table.

I wouldn't suggest this if there are many records though.

Waz,

Thanks for your suggestion. This description HOLD file is taken from an item table with zillions (seemingly) records in it detailing all the items our company distributes I believe. So, we are going to be scheduling it to update daily early in the morning so we can join to it where desired. It would be nice to have a temp table in SQL but not sure we want to do it that way. Thanks though.