Hello, I am trying to convert my left join to a full join bu...

[Shawn Andrews 2]

Hello,

I am trying to convert my left join to a full join but encountered an error which is unclear to me and have not seen other threads with the same issue. Maybe someone can shed some light onto what needs to be changed.

My join is the following:

TABLE FILE ED_THIRD_PARTY_DISTRIBUTOR_BDL_DATA

SUM

BDL_TOTAL_PRODUCT_COST

BDL_TOTAL_PRODUCT_GST

BDL_TOTAL_PRODUCT_DEPOST

ED_THIRD_PARTY_DISTRIBUTOR_BDL_DATA.BDL_DATA.UNIT_SHIPPED

BY ED_THIRD_PARTY_DISTRIBUTOR_BDL_DATA.BDL_DATA.ORDER_NUMBER2

ON TABLE HOLD AS BDL_SUMMARY FORMAT SQLMSS

END

JOIN

FULL_OUTER

FILE FACT_RETAIL_RQ_RECIEVING_INVOICE AT FACT_RETAIL_RQ_RECIEVING_INVOICE.FACT_RETAIL_RQ_RECIEVING_INVOICE.VENDORINVOICENUMBER

TO MULTIPLE

FILE BDL_SUMMARY AT BDL_SUMMARY.SEG01.ORDER_NUMBER2 TAG J4 AS J4

WHERE FACT_RETAIL_RQ_RECIEVING_INVOICE.FACT_RETAIL_RQ_RECIEVING_INVOICE.VENDORINVOICENUMBER EQ J4.SEG01.ORDER_NUMBER2;

END

The error message is the following: (FOC32680) FULL/RIGHT JOIN IS NOT SUPPORTED FOR DIFFERENT SQL SUFFIXES/CONNECTIONS: FACT_RETAIL_RQ_RECIEVING_INVOICE.

Thanks,

Shawn Andrews

[Manoj Chaurasia]

Hi Shawn

I suspect in your server profile (edasprof.prf) you have more than one connection to sql server and the hold file is using the first one it finds which is a different connection than is used in the other table being joined.

[Martin Yergeau]

Seems (for me) that FACT_RETAIL_RQ_RECIEVING_INVOICE & BDL_SUMMARY are not using the same connection string to be accessed

Have you tried to force the destination for BDL_SUMMARY using

ENGINE SQLMSS SET DEFAULT_CONNECTION <ConnectionName>

TABLE FILE ED_THIRD_PARTY_DISTRIBUTOR_BDL_DATA

SUM

BDL_TOTAL_PRODUCT_COST

BDL_TOTAL_PRODUCT_GST

BDL_TOTAL_PRODUCT_DEPOST

ED_THIRD_PARTY_DISTRIBUTOR_BDL_DATA.BDL_DATA.UNIT_SHIPPED

BY ED_THIRD_PARTY_DISTRIBUTOR_BDL_DATA.BDL_DATA.ORDER_NUMBER2

 

ON TABLE HOLD AS BDL_SUMMARY FORMAT SQLMSS

END

Where ConnectionName must be the same as used to access FACT_RETAIL_RQ_RECIEVING_INVOICE

[Shawn Andrews 2]

Thanks for the input.

I located the master files for both tables and both appear to have the same connection name dev_data_warehouse. However, I still set the default connection as martin suggested using this connection name, but the same error is present. Any alternative ways to find the difference that WF is talking about in the error message

[Manoj Chaurasia]

Shawn

You may have one of the masters defined in multiple app folders and tha APP PATH controls which folder is searched first

[Shawn Andrews 2]

Hey Chuck,

There are 4 master copies of one of the tables and 1 copy of the other table. I confirmed they all have the same connection name. Also the tables are in different databases but on the same database server so i dont think that is an issue.

[Manoj Chaurasia]

Shawn - I agree with your assessment, you may need to open a case with techsupport and have them do a streaming session with you.

[Martin Yergeau]

Does the physical SQL table have the same format, structure, indexes, collation between each locations

Does the master files also have the same format, structure and same acx (except for connection string) between each locations

Be aware that when you hold as a SQLMSS the format may not be the one you think is.

Look at the BDL_SUMMARY table and the fields name, are they as in the TABLE FILE END or have generic name

[Shawn Andrews 2]

I managed to solve the issue and perform the full join but i cannot explain the reasoning why WebFocus did not complain this time. I will include the solution below for people with the same problem.

My solution was to hold both into HOLD files, in format SQLMSS, then perform the join such as the following:

TABLE FILE Table1

 

ON TABLE HOLD AS T1 FORMAT SQLMSS

END

TABLE FILE Table2

 

ON TABLE HOLD AS T2 FORMAT SQLMSS

END

JOIN

FULL_OUTER ORDERNUMBER IN T1 TO ORDERNUMBER IN T2

END

[Manoj Chaurasia]

I think Martin was right about how the tables were saved in to sql server and once you did both of them things matched up.

[Martin Yergeau]

What I found is that if you dont put any AS name then the SQL table fields are created with generic names that have no relation with the field name used (E01, E02, ).

If I remember well, sometime, depending of the field, it keeps its name but not always.

So each time I hold in a SQLMSS, I put AS name everywhere to avoid issue.

Even having the SET ASNAMES = ON do not solve the problem.