Nested Select

[#1]

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

[tamilarasu]

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));

//////////////////////////////////////////////////////////

[Kushal_Chawda]

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));

[#4]

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

[#5]

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

[tamilarasu]

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));

[Kushal_Chawda]

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))