topic Re: SELECT statement gives short dump in Application Development and Automation Discussions

SELECT statement gives short dump

former_member194099 — Tue, 06 May 2008 05:40:59 GMT

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

Re: SELECT statement gives short dump

Former Member — Tue, 06 May 2008 05:54:42 GMT

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.

Re: SELECT statement gives short dump

former_member194099 — Tue, 06 May 2008 06:01:57 GMT

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.