SELECT statement gives short dump

[former_member194099]

Hello Experts,

I have a select statement which is something like:

SELECT DISTINCT * FROM t5ux1

WHERE tyear = gp_udate(4)

AND txcmp = units-txcmp

AND pernr IN gp_tstpr.

ee_index-pernr = t5ux1-personid.

ee_index-txcmp = t5ux1-txcmp.

COLLECT ee_index.

ENDSELECT.

"gp_tstpr" is a select option here. When gp_tstpr is huge with say 30000 entries it gives a dump with CX_SY_OPEN_SQL_DB exception, otherwise this statement works fine.

Different possibilities to solve the problems are:

1. Use FOR ALL ENTRIES IN clause. But to use this clause I would need to declare a new internal table having all the key fields from t5ux1 and populate it from gp_tstpr.

2. Use CHECK statement inside select which would be like-

SELECT DISTINCT * FROM t5ux1

WHERE tyear = gp_udate(4)

AND txcmp = units-txcmp.

CHECK ee_index-pernr IN gp_tstpr.

APPEND ee_index.

ENDSELECT.

Are there any more options to solve this problem? Which option is better also from performance perspective?

Thanks

Sagar

[former_member194099]

Hello Experts,

I have a select statement which is something like:

SELECT DISTINCT * FROM t5ux1

WHERE tyear = gp_udate(4)

AND txcmp = units-txcmp

AND pernr IN gp_tstpr.

ee_index-pernr = t5ux1-personid.

ee_index-txcmp = t5ux1-txcmp.

COLLECT ee_index.

ENDSELECT.

"gp_tstpr" is a select option here. When gp_tstpr is huge with say 30000 entries it gives a dump with CX_SY_OPEN_SQL_DB exception, otherwise this statement works fine.

Different possibilities to solve the problems are:

1. Use FOR ALL ENTRIES IN clause. But to use this clause I would need to declare a new internal table having all the key fields from t5ux1 and populate it from gp_tstpr.

2. Use CHECK statement inside select which would be like-

SELECT DISTINCT * FROM t5ux1

WHERE tyear = gp_udate(4)

AND txcmp = units-txcmp.

CHECK ee_index-pernr IN gp_tstpr.

APPEND ee_index.

ENDSELECT.

Are there any more options to solve this problem? Which option is better also from performance perspective?

Thanks

Sagar

[Former Member]

Hi,

SELECT DISTINCT * FROM t5ux1 
WHERE tyear = gp_udate(4) 
AND txcmp = units-txcmp 
AND pernr IN gp_tstpr. 
ee_index-pernr = t5ux1-personid. 
ee_index-txcmp = t5ux1-txcmp. 
COLLECT ee_index. 
ENDSELECT.

I feel usage of FOR ALL ENTRIES is a better option. As an internal table automatically gets created for the Select Option Field with the values as high, low. etc..

So better use for all entries on the select option and try it out.

Pls reward if useful.

Thanks,

Sirisha.

[former_member194099]

Thanks for the suggestion, but I would need to create internal table with fields txcmp, tyear as they have to be part of WHERE clause. Then I would have to populate it from gp_tstpr.