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

SQL error in the database when accessing a table for queries

Former Member — Thu, 21 Aug 2008 03:13:04 GMT

Hi experts,

I'm encountering a problem in the select statement on the standard program it is an upgrade from ECC 6.0 for queries. The error encountered is "SQL error in the database when accessing a table". It has a problem in the SELECT statement while in other box that is not upgraded into ECC 6.0 it encounters no dump, does ECC 6.0 affect the SELECT statement? Can anybody help me with this.

This is from transaction st22:

Runtime Errors: DBIF_RSQL_SQL_ERROR

Exception : CX_SY_OPEN_SQL_DB

How to correct the error

Database error text........: "ORA-01445: cannot select ROWID from, or sample, a

join view without a key-preserved table"

Internal call code.........: "[RSQL/FTCH/LIKP ]"

Please check the entries in the system log (Transaction SM21).

The dump points here:

-->SELECT LIKP_{KUNAG LIKP}KUNNR LIKP_{LFART LIKP}VBELN LIKP_{VSTEL LIKP}WADAT_IST VBUK_{VBELN VBUK}WBSTK LIPS_{CHARG LIPS}LFIMG LIPS~MATNR

LIPS_{POSNR LIPS}VBELN LIPS_{VRKME VBFA}VBELN VBFA_{VBELV VTTP}TKNUM VTTP_{VBELN KNA1}ADRNR KNA1_{KUNNR KNA1}NAME1 KNA1~NAME2

KNA1_{ORT01 KNA1}STRAS MAKT_{MAKTX MAKT}MATNR ADRC_{ADDRNUMBER ADRC}SORT2 VBAK_{AUART VBAK}SPART VBAK_{VBELN VBAK}VKORG

VBAK_{VTWEG KNVV}KDGRP KNVV_{KUNNR KNVV}SPART KNVV_{VKORG KNVV}VTWEG

into (LIKP-KUNAG , LIKP-KUNNR , LIKP-LFART , LIKP-VBELN , LIKP-VSTEL , LIKP-WADAT_IST , VBUK-VBELN , VBUK-WBSTK , LIPS-CHARG

, LIPS-LFIMG , LIPS-MATNR , LIPS-POSNR , LIPS-VBELN , LIPS-VRKME , VBFA-VBELN , VBFA-VBELV , VTTP-TKNUM , VTTP-VBELN

, KNA1-ADRNR , KNA1-KUNNR , KNA1-NAME1 , KNA1-NAME2 , KNA1-ORT01 , KNA1-STRAS , MAKT-MAKTX , MAKT-MATNR , ADRC-ADDRNUMBER

, ADRC-SORT2 , VBAK-AUART , VBAK-SPART , VBAK-VBELN , VBAK-VKORG , VBAK-VTWEG , KNVV-KDGRP , KNVV-KUNNR , KNVV-SPART

, KNVV-VKORG , KNVV-VTWEG )

from ( LIKP

inner join VBUK

on VBUK_{VBELN = LIKP}VBELN

inner join LIPS

on LIPS_{VBELN = LIKP}VBELN

inner join VBFA

on VBFA_{VBELN = LIPS}VBELN

left outer join VTTP

on VTTP_{VBELN = LIKP}VBELN

inner join KNA1

on KNA1_{KUNNR = LIKP}KUNNR

inner join MAKT

on MAKT_{MATNR = LIPS}MATNR

left outer join ADRC

on ADRC_{ADDRNUMBER = KNA1}ADRNR

inner join VBAK

on VBAK_{VBELN = VBFA}VBELV

inner join KNVV

on KNVV_{KUNNR = LIKP}KUNAG

and KNVV_{SPART = VBAK}SPART

and KNVV_{VKORG = VBAK}VKORG

and KNVV_{VTWEG = VBAK}VTWEG )

where LIKP~LFART in SP$00009

and LIKP~VBELN in SP$00003

and LIKP~VSTEL in SP$00002

and LIKP~WADAT_IST in SP$00005

and VBUK~WBSTK in SP$00004

and LIPS~MATNR in SP$00001

and VBAK~AUART in SP$00006

and VBAK~VBELN in SP$00007

and KNVV~KDGRP in SP$00008.

%dbacc = %dbacc - 1.

if %dbacc = 0.

stop.

endif.

check SP$00008.

check SP$00009.

check SP$00003.

check SP$00002.

check SP$00005.

check SP$00001.

check SP$00006.

check SP$00007.

check SP$00004.

add 1 to %count-LIKP.

%linr-LIKP = '01'.

extract %fg01.

%ext-LIPS01 = 'X'.

extract %fgwrLIPS01.

%linr-LIKP = '02'.

endselect.

And any idea on how to convert this using for all entries since it uses a standard table.

Re: SQL error in the database when accessing a table for queries

Former Member — Thu, 21 Aug 2008 03:23:28 GMT

Same problem here. Selecting from standard table for queries using inner joins and upon execution it will have a runtime error.

Is their any limitation or any relation in using inner joins for current version in ECC6?

Re: SQL error in the database when accessing a table for queries

peter_ruiz2 — Thu, 21 Aug 2008 03:39:36 GMT

hi,

SELECT KUNAG KUNNR LFART VBELN VSTEL WADAT_IST

INTO CORRESPONDING FIELDS OF TABLE I_LIKP

FROM LIKP

WHERE LFART IN SP$00009

AND VBELN IN SP$00003

AND VSTEL IN SP$00002

AND WADAT_IST IN SP$00005.

IF NOT i_likp[] IS INITIAL.

SELECT VBELN WBSTK

INTO CORRESPONDING FIELDS OF TABLE I_VBUK

FROM VBUK

FOR ALL ENTRIES IN i_likp

WHERE VBELN EQ i_likp-VBELN

AND WBSTK IN SP$00004.

SELECT CHARG LFIMG MATNR POSNR VBELN VRKME

INTO CORRESPONDING FIELDS OF TABLE I_LIPS

FROM LIPS

FOR ALL ENTRIES IN i_likp

WHERE vbeln EQ i_likp-vbeln

AND MATNR in SP$00001.

SELECT VBELN VBELV

INTO CORRESPONDING FIELDS OF TABLE I_VBFA

FROM VBFA

FOR ALL ENTRIES IN i_likp

WHERE vbeln EQ i_likp-vbeln.

SELECT TKNUM VBELN

INTO CORRESPONDING FIELD OF i_vttp

FROM VTTP

FOR ALL ENTRIES IN i_likp

WHERE vbeln EQ i_likp-vbeln.

SELECT ADRNR KUNNR NAME1 NAME2 ORT01 STRAS

INTO CORRESPONDING FIELDS OF i_kna1

FROM KNA1

FOR ALL ENTRIES IN i_likp

WHERE kunnr EQ i_likp-kunnr.

SELECT MAKTX MATNR

INTO CORRESPONDING FIELDS OF TABLE i_makt

FROM makt

FOR ALL ENTRIES IN i_likp

WHERE matnr EQ i_likp-matnr.

IF NOT i_kna1[] IS INITIAL.

SELECT ADDRNUMBER SORT2

INTO CORRESPONDING FIELDS OF TABLE i_adrc

FROM adrc

FOR ALL ENTRIES IN i_kna1

WHERE addrnumber EQ i_kna1-adrnr.

ENDIF.

IF NOT i_vbfa[] IS INITIAL.

SELECT AUART SPART VBELN VKORG VTWEG

INTO CORRESPONDING FIELDS OF TABLE i_vbak

FROM vbak

FOR ALL ENTRIES IN i_vbfa

WHERE vbeln EQ i_vbfa-vbelv

AND AUART in SP$00006.

ENDIF.

SELECT KDGRP KUNNR SPART VKORG VTWEG

INTO CORRESPONDING FIELDS OF TABLE i_knvv

FROM knvv

FOR ALL ENTRIES IN i_likp

WHERE kunnr EQ i_likp-kunag

AND KDGRP in SP$00008.

ENDIF.

regards,

Peter