SAP Join Syntax

[avastani]

I am using the following syntax and it doesn't work

Test:

LOAD VBRK~VBELN, VBRP~WERKS;

Sql Select VBRK~VBELN VBRP~WERKS

FROM VBRK

JOIN VBRP ON VBRK~VBELN = VBRP~VBELN

WHERE VBRK~FKDAT = '2012-04-01';

If I remove the Load statement it works fine. What am I missing here? How can we use the Preceding Load or is it that Preceding Loads are not possible in a join scenario when using SAP Connector.

[Sokkorn]

Hi amirvas,

amirvas wrote:

If I remove the Load statement it works fine. What am I missing here? How can we use the Preceding Load or is it that Preceding Loads are not possible in a join scenario when using SAP Connector.

Now let try

LOAD VBRK~VBELN As F1, VBRP~WERKS As F2;

Sql Select VBRK~VBELN VBRP~WERKS

FROM VBRK

JOIN VBRP ON VBRK~VBELN = VBRP~VBELN

WHERE VBRK~FKDAT = '2012-04-01';

Regards,

Sokkorn

[avastani]

Never mind. In the load statement I need to remove the table qualifier and it works fine.

[Sokkorn]

Good to hear that.

[avastani]

the odd part to think about is how does SAP Connector know which field is from which table if duplicate fieldnames like NETWR, VBELN etc are involved.

[suniljain]

DIRECTORY;

[VBAP - Order Items]:

LOAD

VBELN & '/'& POSNR as [%VBELN/POSNR],

[MEINS] as [Base Unit_MEINS],

[MATKL] as [Material Group_MATKL],

[WERKS] as [Plant_WERKS],

[MATNR] as [Material_MATNR],

[SPART] as [Division_SPART],

[KPEIN] as [Pricing unit_KPEIN],

[KMEIN] as [Unit of measure_KMEIN],

[ERNAM] as [Created by_ERNAM],

[ERDAT] as [Created on_ERDAT],

[AEDAT] as [Changed on_AEDAT],

[POSNV] as [Item_POSNV],

[VSTEL] as [Shipping Point_VSTEL],

[VBELN] as [Sales Document_VBELN],

[POSNR] as [Item_POSNR],

[NETWR] as [Net value_NETWR];

SQL SELECT MEINS MATKL WERKS MATNR SPART KPEIN KMEIN ERNAM ERDAT AEDAT POSNV VSTEL VBELN NETWR POSNR

FROM VBAP

WHERE VBELN IN (

SELECT VBELN FROM VBAK // Order Header

WHERE ERDAT > '$(vLastReload)'

OR AEDAT > '$(vLastReload)'

);

CONCATENATE ([VBAP - Order Items])

LOAD *

FROM VBAP.qvd (qvd)

WHERE not exists ([%VBELN/POSNR]);

[avastani]

Hi,

Thanks for the reply but that's not what I am looking for. I want to do a join between VBRK and VBRP and also being in both NETWR from VBRK and VBRP. One is a header amount, the other is item amount.

Hope this helps.

[suniljain]

You have to use qualify and unqualify for that.

[avastani]

So essentially the connector doesn't support aliasing and obtaining both fields in SQL but rather in LOAD statements once in QV side.

-afv.

Tel: 646.773.7936