Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Please help me with the following.
In need to select values from SAP table based on field values from another SAP table, i.e. select from select
Script part underneath in bold is the part I am needing assistance with?
//////////////////////////////////////////////////////////
[VBAK]: //Sales Document Header Data
LOAD VBELN AS [Sales Document],
ERNAM AS [Created by],
AUDAT AS [Document Date],
KNUMV AS [Doc. condition no.],
where
AUDAT > '20160501'
LEFT JOIN ([VBAK])
LOAD KNUMV AS [Doc. condition no.],
KSCHL AS [Condition type],
KBETR AS [Condition rate]
;
SQL SELECT
KNUMV,
KSCHL,
KBETR
FROM KONV
WHERE
KNUMV =
(Load [Doc. condition no.],
Resident VBAK);
//////////////////////////////////////////////////////////
Field KNUMV is an indexed field, if I only use a left join the query runs for hours instead of seconds.
Thanks in advance
Hi Mome,
Try this,
//////////////////////////////////////////////////////////
[VBAK]: //Sales Document Header Data
LOAD VBELN AS [Sales Document],
ERNAM AS [Created by],
AUDAT AS [Document Date],
KNUMV AS [Doc. condition no.],
where
AUDAT > '20160501'
LEFT JOIN ([VBAK])
LOAD KNUMV AS [Doc. condition no.],
KSCHL AS [Condition type],
KBETR AS [Condition rate];
Temp:
Load Concat(Distinct Chr(39) & [Doc. condition no.] & Chr(39),', ') as List resident VBAK;
LET vList = Peek('List', 0, 'Temp');
Drop table Temp;
SQL SELECT
KNUMV,
KSCHL,
KBETR
FROM KONV
WHERE
Match(KNUMV, $(vList));
//////////////////////////////////////////////////////////
try this
[VBAK]: //Sales Document Header Data
LOAD VBELN AS [Sales Document],
ERNAM AS [Created by],
AUDAT AS [Document Date],
KNUMV AS [Doc. condition no.],
where
AUDAT > '20160501';
Temp:
Load Distinct Concat(Chr(39) & [Doc. condition no.] & Chr(39),', ') as List
resident VBAK;
LET vList = Peek('List', 0, 'Temp');
Drop table Temp;
LEFT JOIN ([VBAK])
LOAD KNUMV AS [Doc. condition no.],
KSCHL AS [Condition type],
KBETR AS [Condition rate];
SQL SELECT
KNUMV,
KSCHL,
KBETR
FROM KONV
WHERE
Match(KNUMV, $(vList));
Hi Tamil & Kushal, thank you for the feedback,
I see you have similar solutions,
I have tried both in the exact format you send them, but I get the same script error:
i.e.
////////////////////////////////////////////
/QTQVC/OPEN_STREAM failed after 00:00:00 Key = SQL_ERROR (ID:00 Type:E Number:001 "(" is invalid here (due to grammar). contains an invalid character or it is a keyword. (It might be possible to escape it using "!"). Or a space is missing or there is one space too many.)
SQL SELECT
KNUMV,
KSCHL,
KBETR
FROM KONV
WHERE
Match (KNUMV, '0117978213', '0117978214', '0117979191')
////////////////////////////////////////////
Am I making an obvious syntax error or may it be that SAP Connector doesn't recognize this command?
Regards
I have replaced the statement Load Distinct Concat(Chr(39) & [Doc. condition no.] & Chr(39),', ') as List
with
Load Distinct Concat('KNUMV = ' & Chr(39) & [Doc. condition no.] & Chr(39),' or ') as List
Works perfectly!
Thanks again
Morne,
Ah!! I forgot to see the SQL statement. Match is a Qlikview function and it won't work in Sql script. Glad that you found the solution!
You can try like below
Where KNUMV IN ($(vList));
try this instead
[VBAK]: //Sales Document Header Data
LOAD VBELN AS [Sales Document],
ERNAM AS [Created by],
AUDAT AS [Document Date],
KNUMV AS [Doc. condition no.],
where
AUDAT > '20160501';
Temp:
Load Distinct Concat(Chr(39) & [Doc. condition no.] & Chr(39),', ') as List
resident VBAK;
LET vList = Peek('List', 0, 'Temp');
Drop table Temp;
LEFT JOIN ([VBAK])
LOAD KNUMV AS [Doc. condition no.],
KSCHL AS [Condition type],
KBETR AS [Condition rate];
SQL SELECT
KNUMV,
KSCHL,
KBETR
FROM KONV
WHERE KNUMV in ($(vList))