HOW TO FETCH DATA FROM CLUSTER table with reference to other table

[#1]

my code is this:

load * ;

SQL SELECT * FROM BSEG WHERE ( KUNNR IN (SQL SELECT KUNNR FROM KNA1 ));

After reloading , i am getting error: Open Stream Failed.

[#2]

load * ;

SQL SELECT KUNNR FROM KNA1 ;

load *

where exists(KUNNR);

SQL SELECT * FROM BSEG ;

this is one way. i don't have sql server installed so i can't be sure if it's a syntax problem or the syntax is not supported. i think you don't have to put SQL word in the subquery.

[#3]

Hi.

You can also use the INNER KEEP (better than INNER JOIN in this case):

Table1:

load *;

SQL SELECT * FROM BSEG;

Table 2:

inner keep (Table1)

load KUNNR;

SQL SELECT KUNNR FROM KNA1;

That should reduce the data set to the intersection of those two tables.

Regards,

Montal.

[#4]

You need to remove the second "SQL".

load * ;

SQL SELECT * FROM BSEG WHERE KUNNR IN (SELECT KUNNR FROM KNA1 );

[#5]

a97mic is right, but I think you should compare the running time of all alternatives and see for yourself. (In AS400 db2 the QlikView inner keep is much more faster than the SQL running time)

Regards,

Montal.

[#6]

Load * ;

SQL SELECT * FROM BSEG WHERE KUNNR IN (SELECT KUNNR FROM KNA1 );

Its not working, and error is same

bcoz BSEG is a cluster table

Note: I want to reduce data while fetching data from database , not in qvd

[suniljain]

Reason behind this error is SAP R/3 is unable to give response in decided time frame.

and you can not extract record from this way through SAP Connector especilly in case of cluster table .

[#8]

Hi,

I am trying to implement the same thing, but getting the same OpenStream error.

Could you please guide if this issue is resolved at your end.

Thanks,

Madhuri.

[#9]

BSEG is a cluster table. The SAP connector manuel says that you should use subselect, as follows:

SQL SUBSELECT BUKRS BELNR GJAHR BUZEI BUZID AUGDT FROM BSEG WHERE BUKRS BELNR GJAHR IN ( SELECT BUKRS BELNR GJAHR FROM BKPF WHERE CPUDT = '20131023' );