I am using the following syntax and it doesn't work
LOAD VBRK~VBELN, VBRP~WERKS;
Sql Select VBRK~VBELN VBRP~WERKS
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.
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;
Never mind. In the load statement I need to remove the table qualifier and it works fine.
Good to hear that.
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.
[VBAP - Order Items]:
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
WHERE VBELN IN (
SELECT VBELN FROM VBAK // Order Header
WHERE ERDAT > '$(vLastReload)'
OR AEDAT > '$(vLastReload)'
CONCATENATE ([VBAP - Order Items])
FROM VBAP.qvd (qvd)
WHERE not exists ([%VBELN/POSNR]);
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.
You have to use qualify and unqualify for that.
So essentially the connector doesn't support aliasing and obtaining both fields in SQL but rather in LOAD statements once in QV side.