topic Re: SQL error in the database when accessing a table in Application Development and Automation Discussions

SQL error in the database when accessing a table

Former Member — Fri, 28 Jan 2011 11:35:16 GMT

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.

Re: SQL error in the database when accessing a table

Former Member — Fri, 28 Jan 2011 11:43:26 GMT

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

Re: SQL error in the database when accessing a table

Former Member — Fri, 28 Jan 2011 11:53:03 GMT

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

Re: SQL error in the database when accessing a table

Former Member — Fri, 28 Jan 2011 12:00:23 GMT

Hi,

Did you activate the tables ?

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

Regards,

Jovito

Re: SQL error in the database when accessing a table

Former Member — Fri, 28 Jan 2011 12:02:16 GMT

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.

Re: SQL error in the database when accessing a table

Former Member — Fri, 28 Jan 2011 12:04:49 GMT

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 .

Re: SQL error in the database when accessing a table

Former Member — Fri, 28 Jan 2011 12:07:14 GMT

what is the error you are getting?

Re: SQL error in the database when accessing a table

Former Member — Fri, 28 Jan 2011 12:07:43 GMT

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

Re: SQL error in the database when accessing a table

Former Member — Fri, 28 Jan 2011 12:12:00 GMT

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?

Re: SQL error in the database when accessing a table

Former Member — Fri, 28 Jan 2011 12:15:30 GMT

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 ".

Re: SQL error in the database when accessing a table

Former Member — Fri, 28 Jan 2011 12:20:59 GMT

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

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

Re: SQL error in the database when accessing a table

Former Member — Fri, 28 Jan 2011 12:28:51 GMT

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

Re: SQL error in the database when accessing a table

Former Member — Fri, 28 Jan 2011 12:38:39 GMT

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.

Re: SQL error in the database when accessing a table

Former Member — Fri, 28 Jan 2011 13:08:54 GMT

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

Re: SQL error in the database when accessing a table

Former Member — Fri, 28 Jan 2011 14:12:48 GMT


> 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

Re: SQL error in the database when accessing a table

Former Member — Fri, 28 Jan 2011 16:27:24 GMT

That's rubbish.

Re: SQL error in the database when accessing a table

Former Member — Fri, 28 Jan 2011 16:32:50 GMT

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...

Re: SQL error in the database when accessing a table

Former Member — Mon, 31 Jan 2011 06:56:13 GMT

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.