SAP Connector: Calling multiple fields with the same name using Join

[jesper_bagge]

Hi,

As stated in the topic, I'm trying to call multiple fields with the same name from two tables in SAP using a JOIN statement. However, I get a errormessage saying i have already stated the fieldname once in my query. Would this have been an ordinary MS SQL query i would have used alias to differentiate the two fields. Is there a similar technique I can use when querying SAP using the SAP Connector?

Example as follows:

SQL SELECT
 EKBE~EBELN
 EKBE~EBELP
 EKBE~EINDT
 EKES~EINDT
FROM EKBE
 JOIN EKES ON EKBE~EBELN = EKES~EBELN AND EKBE~EBELP = EKES~EBELP;

The fields EINDT from the tables EKBE and EKES contain different dates, both of great use to my client. However, I cannot seem to load the both in the same query. I know i can join them in the QV part of my script, but these two tables contain a lot of rows and loadtime is an issue.

[disqr_rm]

I never had success joining SAP tables in QV like these. I think it's not supported by connector.

You would be better off create DB View in SAP, say Z_EKBE_EKES and using that view in QV to download data from.

[#3]

The SAP connector is able to execute joins, as it simply executes OPENSQL-statements (http://help.sap.com/saphelp_470/helpdata/de/fc/eb3b8b358411d1829f0000e829fbfe/content.htm) against the SAP-system!

1) ensure that you have a valid openSql-statement

2) ensure that you have a unique alias for every field in your statement. QlikView complains if the (Open-)SQL-Statement returns multiple fields with the same name.

[jesper_bagge]

Yes, well. Now I think we're getting somewhere. You mention "unique alias for every field". Since my original question was if there was a way to set unique aliases to fields that originally has the same name - perhaps you can try to answer my question? Or at least point me in the general direction?

Cheers!

[#5 · Accepted solution]

You are right; SAPConnector seems to ignore aliasing at all;

I have been playing around with QV functions FieldName() in the LOAD-Statement, but they don't worked for me as expected. I don't really have any more ideas how this could be done, beside joining the data in QV...

[jesper_bagge]

So its the SAPConnector that refuses to accept aliasing?

I took some time reading trough the OPEN SQL documentation you linked in your previous reply and found the following: http://help.sap.com/erp2005_ehp_04/helpdata/DE/62/10a423384746e8bf5f15ccdd36e8b1/content.htm

This would imply that you can use the AS keyword when retrieving data from SAP - but not by using the SAPConnector?

If that is the case, maybe its time for a feature request?

[Anonymous]

I have SAP Connector version 16.0.0.400 and it still does not allow aliasing however it will allow a join from tables with fields with the same name. It automatically appends 01 to the end of the second field.