How to make composite key in SAP Sql query

[asmitagg]

Hi All,

I m trying following subquery in SAP but its not working plz suggest.

SQL SELECT MBLNR,MJAHR,MATNR,CHARG,WERKS,MEINS,BUDAT_MKPF,BWART,MENGE FROM MSEG

WHERE MBLNR&MJHAR IN (SELECT MBLNR&MJHAR  FROM MKPF WHERE CPUDT > '$(vDate)' AND CPUDT < '$(vToday)');

"MBLNR&MJHAR" how to make composite key or concat these two fields to make key.

I m trying with '&' its not working.

[Ricardo_Gerhard]

Dear Asmita,

You can use this syntax below because I SAP database doesn´t allow subqueries as you tried:

- Load the query:

SELECT MBLNR&MJHAR  FROM MKPF WHERE CPUDT > '$(vDate)' AND CPUDT < '$(vToday);

- Then run the first query

SQL SELECT MBLNR,MJAHR,MATNR,CHARG,WERKS,MEINS,BUDAT_MKPF,BWART,MENGE FROM MSEG

WHERE MBLNR&MJHAR

with where exists statement

Exists ‒ QlikView

[Ricardo_Gerhard]

Anyway, SAP allows the composite key like you had tried FIELD1&FIELD2

[rupamjyotidas]

Suggestion would be to load the Data as it is and Use Preceding load to built your composite key

1)

Load MBLNR&MJHAR as Key;

SELECT MBLNR,MJHAR  FROM MKPF WHERE CPUDT > '$(vDate)' AND CPUDT < '$(vToday);

2)

Load * Where Exists(Key);

Load

SQL SELECT MBLNR,MJAHR,MATNR,CHARG,WERKS,MEINS,BUDAT_MKPF,BWART,MENGE FROM MSEG

[MarcoWedel]

MJHAR  -> MJAHR  Typo?