Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

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


Shawn Andrews 2

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...