Error DBIF_RSQL_INVALID_RSQL CX_SY_OPEN_SQL_DB in program

[Former Member]

Good Morning Wise People,

                                        I ask you for this type error. I saw by transaction ST22 for this error and I have these possible error causes:

	o The maximum size of an SQL statement was exceeded.
	o The statement contains too many input variables.
	o The input data requires more space than is available.
	o ...

and also it to point at this statements:

SELECT    pep,acao,conta_debito,ccusto_debito,conta_credito,ccusto_cretito

    FROM        zistps_mtcd_acao

    INTO TABLE    tl_mtcd_acao

    FOR ALL ENTRIES    IN tl_perc_fae

    WHERE    pep  IN     rl_pep_ran

        AND acao EQ tl_perc_fae-acao.

I think that could be the first cause, because:

-the table Z 'zistps_mtcd_acao' has 258 records

-the internal table 'tl_perc_fae' has 57.

-But, the 'Range' rl_pep_ran' has 5747 records. This is the problem I suppose.

My first idea is to separate the condition 'where'. First I'll take records by rl_pep_ran and then I'll take by tl_perc_fae_acao.

Would be correct to face this problem so?

I would appreciate your help.

Thanks in Advance.

Best Regards.

[ThomasZloch]

Since this problem comes up quite frequently (did you search before posting?), one of our contributors has written a blog about it:

Thomas

P.S. please rate the blog, not my link.

[Former Member]

Yes, I've already looked for here. But it has many variants.

       Thank you. I'll see your link.

[Former Member]

Note that FAEI (For All Entries In) gets handled differently by different DBMS systems.   For instance, in my DB2 installation, 100,000 entries in the FAEI referenced table results in 10,000 trips to the DBMS with ten entries in my where clause statement per trip (the 10 is a system variable).  I noticed in another Solaris/Oracle system that the where clause became this enormous string of where....eq :something and field eq :something, etc., a humongous where statement.

So, if you can, look at the size of your input table, and look at logs.... I would interpret this problem as the number of entries in my SQL-created where clause is so huge the DBMS saw a violation of the max number of entries it can handle... What would I do?  Package size addition to the Open SQL on the initial selection....extract FAEI for the smaller table obtained with package size, appending my target internal table....but that's just my take on how to deal with this.

There used to be a post pinned to the top of our old forum....FAEI is not the most efficient retrieval....and for DB2, that's very true....subquery is the fastest thing I've found (where it can be used) by far...

[ThomasZloch]

Once I find the time to do it, I will try to collect the old sticky threads, wrap them into a document and propose them as featured content on the overview page. Once I find...

Thomas