SQL error in the database when accessing a table

[Former Member]

I have an inner join on 3 tables which works perfectly well in development & quality but the same fails in production. I get the error 'SQL error in the database when accessing a table'. These 3 tables have 99,770, 88,408 & 93,937 records in production. The amount of data in development & quality is less.

[Former Member]

Hi,

Check if all 3 tables are present in PRD. Also check if the columns are the same.

Regards,

Jovito

Edited by: dsouzajovito on Jan 28, 2011 12:43 PM

[Former Member]

Yes, all three tables are present in production & have the same columns.

[Former Member]

Hi,

Did you activate the tables ?

If yes, Can you give more details of the dump (ST22).

Regards,

Jovito

[Former Member]

Inner join on more than 2 tables is not recommended, and it will not work if we have huge amounts of data.

Better try to use the inner join on two tables and try to use for all entries on the third table which will have a better performace than using the inner join on 3 tables.

Regards,

ravindra.

[Former Member]

Here is my query where it failed:

SELECT /1om/reworwoh010~ordid

/1om/reworwoh010~ordno

/1om/reworwoh010~prtidfr

/1om/reworwoh010~poordno

ztscm_custpo_spl~order_id

ztscm_custpo_spl~customer_po

ztscm_custpo_spl~delivery_no

ztscm_custpo_spl~customer_model

ztscm_custpo_spl~quantity

ztscm_custpo_spl~VEN_CONF_DATE

ztscm_add_data~order_id

ztscm_add_data~customer

ztscm_add_data~product_no

ztscm_add_data~product_id

FROM /1om/reworwoh010

INNER JOIN ztscm_custpo_spl ON

ztscm_custpo_spl_{order_id = /1om/reworwoh010}ordid

INNER JOIN ztscm_add_data ON

ztscm_add_data_{order_id = /1om/reworwoh010}ordid

INTO CORRESPONDING FIELDS OF TABLE t_/1om/reworwoh010

WHERE /1om/reworwoh010~prtidfr IN t_prtid AND

/1om/reworwoh010~poordno IN t_poordno AND

ztscm_custpo_spl~customer_po IN t_custpo AND

ztscm_custpo_spl~VEN_CONF_DATE in t_vcd and

ztscm_add_data~product_no IN t_matnr AND

ztscm_add_data~customer IN t_cust .

[Former Member]

what is the error you are getting?

[Former Member]

Hi shpava,

We do know much of ur custom tables so wont be able to help with the query.

Can you post the dump that you receive.

All the contents that are relevant.

Regards,

Jovito

[Former Member]

The dump says SQL error in the database when accessing a table. I am not able to figure out why it is failing in PRD. Could it be because of the huge amount of data?

[Former Member]

Short text

SQL error in the database when accessing a table.

How to correct the error

Database error text........: "Resource limit exceeded. MSGID=

Job=410440/SN302/WP00"

Information on where terminated

Termination occurred in the ABAP program "/1BCWDY/JZKQIYPSL2MIDBTNNIQR==CP" -

in "GET_DATA".

The main program was "SAPMHTTP ".

In the source code you have the termination point in line 1414

of the (Include) program "/1BCWDY/B_JZKQIN8R6S91THNZXKPH".

Termination occurred in a Web Dynpro application

Web Dynpro Component ZSNC_SHIPPING_GR_INV_NEW

Web Dynpro Controller COMPONENTCONTROLLER

The termination is caused because exception "CX_SY_OPEN_SQL_DB" occurred in

procedure "GET_DATA" "(METHOD)", but it was neither handled locally nor

declared

in the RAISING clause of its signature.

The procedure is in program "/1BCWDY/JZKQIYPSL2MIDBTNNIQR==CP "; its source

code begins in line

693 of the (Include program "/1BCWDY/B_JZKQIN8R6S91THNZXKPH ".

[Former Member]

Database error text........: "Resource limit exceeded. MSGID=Job=410440/SN302/WP00

Looks like a perfect point to start, don't you think so too?

[Former Member]

What does 'Resource limit exceeded' exactly mean? What is your suggestion to fix it?

[Former Member]

Hi,

This error is thrown by the SQL database when it doesnt have more space to place the data.

Look at it this way.. the join created by your program has a very large cartesian product which requires a large amount of memory and the database is not able to provide that much memory.

Your option would be to query one table at a time using the clause FOR ALL ENTRIES.

Then inside a loop, you can simulate the join using READ TABLE clause.

Regards,

Jovito.

[Former Member]

Thanks! I will try it & will let you know if this works.

[Former Member]

>

> Inner join on more than 2 tables is not recommended, and it will not work if we have huge amounts of data.
> 
> Better try to use the inner join on two tables and try to use for all entries on the third table which will have a better performace than using the inner join on 3 tables.
> 
> Regards,
> ravindra.

Absolutely and positively incorrect.

Rob

[Former Member]

That's rubbish.

[Former Member]

Yes, even I don't agree to this. Why wouldn't a join work?

As a matter of fact something strange happened. Without any modification the join on 3 tables and the 2nd one which I had on 4 tables starting working in production as well.

[Former Member]

When you run out of memory the best solution is to use package processing.

Instead of SELECT * INTO TABLE ... use the following:

OPEN CURSOR...FOR SELECT ...

DO.

FETCH NEXT CURSOR...PACKAGE SIZE ...INTO...

IF sy-subrc <> 0.

EXIT.

ENDIF.

ENDDO.

CLOSE CURSOR...