topic Re: sql query error in Application Development and Automation Discussions

sql query error

Former Member — Thu, 31 May 2012 08:22:08 GMT

Hi Everybody,

Im working on a smartform. Im having error in this join query...

SELECT T1~VBELN

T1~VKORG

T1~VTWEG

T1~SPART

T1~FKDAT

T1~KUNAG

T2~POSNR

T2~MATNR

T2~ARKTX

T2~FKLMG

T2~NETWR

INTO TABLE IT_TAB

FROM VBRK AS T1

INNER JOIN VBRP AS T2 ON T1~VBELN EQ T2~VBELN

WHERE T1~VBELN eq P_VBELN.

Im getting error as "IT_TAB is a table without a header line and therefore has no component called KUNAG". Please help...

Re: sql query error

former_member222709 — Thu, 31 May 2012 08:25:08 GMT

Hi Vasantha,

Please add the data declaration code of it_tab.

Regards, Pranav.

Re: sql query error

Former Member — Thu, 31 May 2012 08:39:16 GMT

Possible reason:

1.) Check your TYPES declaration and DATA declaration of internal table IT_TAB.

It looks like you miss out to include the field KUNAG.

2.) Also, I suspect that it maybe not on your query, is there any READ TABLE statement or LOOP .. END LOOP process to table IT_TAB?

3.) How you declare your IT_TAB table? Please check this. INTERNAL TABLE TYPES.

Or please paste here your data declaration of it_tab.

Regards,

Jake

Re: sql query error

Former Member — Thu, 31 May 2012 09:03:52 GMT

Hi Pranav,

I have declared IT_TAB like this...check if im wrong...

In the types tab...i have declared types_table like this...

TYPES: BEGIN OF TYPES_TABLE,

VBELN TYPE VBRK-VBELN,

VKORG TYPE VBRK-VKORG,

VTWEG TYPE VBRK-VTWEG,

SPART TYPE VBRK-SPART,

FKDAT TYPE VBRK-FKDAT,

KUNAG TYPE VBRK-KUNAG,

POSNR TYPE VBRP-POSNR,

MATNR TYPE VBRP-MATNR,

ARKTX TYPE VBRP-ARKTX,

FKLMG TYPE VBRP-FKLMG,

NETWR TYPE VBRP-NETWR,

END OF TYPES_TABLE.

Re: sql query error

former_member222709 — Thu, 31 May 2012 09:11:26 GMT

Hi Vasantha,

Your reference structure TYPES_TABLE is a locally declared structure and your are referring to that in Global data.

Solution: Create the structure as ZTYPES_TABLE using T. Code SE11 and in your Global Data Declaration change types_table to ZTYPES_TABLE.

Regards, Pranav.

Re: sql query error

RaymondGiuseppi — Thu, 31 May 2012 09:19:16 GMT

Did you declare a work-area to read the data, and did you use it in

- abap code nodes

look for LOOP AT and READ TABLE statements in abap nodes.

- Loops in table nodes

Look at data table of table nodes;

Regards,

Raymond

Re: sql query error

Former Member — Thu, 31 May 2012 09:51:39 GMT

Hi Raymond,

I did declared IT_TAB1 and used it as you have shown in the screenshots....

Regards

Vasanth

Re: sql query error

former_member209818 — Thu, 31 May 2012 09:56:49 GMT

HI Vasantha..

Seems your above code the not having any issue but issue is at some other node...

Activate the smartform and while displaying the error, it will mention node.. in that node.. check all the places where you are using KUNAG from your internal table

Re: sql query error

RaymondGiuseppi — Thu, 31 May 2012 10:00:04 GMT

Check also that no text node and no condition tab of any node use a IT_ITAB-KUNAG but the WA_ITAB-KUNAG.

And just open the form via transaction SMARTFORMS and execute a check,

then click on errot to reach the bad statement

Regards,

Raymond

Re: sql query error

former_member282968 — Thu, 31 May 2012 10:05:47 GMT

Hi Vasanth,

You cant declare internal table in the Global data in the way you have declared.

In the tab TYPES Declare as:

it_TYPES_TABLE type table of TYPES_TABLE.

Now in the tab GLOBAL DATA declare as:

it_itab type it_TYPES_TABLE

With regrads,

Re: sql query error

Former Member — Thu, 31 May 2012 13:19:16 GMT

Hi everyone,

I have identified the error.

SELECT T1~VBELN

T1~VKORG

T1~VTWEG

T1~SPART

T1~KUNAG

T1~FKDAT

T2~POSNR

T2~MATNR

T2~ARKTX

T2~FKLMG

T2~NETWR

INTO TABLE IT_TAB

FROM VBRK AS T1

INNER JOIN VBRP AS T2 ON T1~VBELN EQ T2~VBELN

WHERE T1~VBELN eq P_VBELN.

SELECT SINGLE KUNNR NAME1 ORT01 LAND1 PSTLZ INTO FS_TAB

FROM KNA1

WHERE KUNNR = IT_TAB-KUNAG.

The error comes from the second SELECT query. Please tell me how to correct it.

Re: sql query error

RaymondGiuseppi — Thu, 31 May 2012 13:35:11 GMT

Better add the KNA1 table to first query.

Else you will have to select data from KNA1 in a internal table INTO TABLE it_kna1 using a FOR ALL ENTRIES IN IT_TAB (on first internal table, if not empty), sort the table and then update the fist internal table in a LOOP reading complementary information from the second table using a READ TABLE statement (binary search)

And dont' put a SELECT SINGLE in a LOOP, or be ready to start a discussion in a performance forum/area.

Regards,

Raymond

Re: sql query error

former_member282968 — Thu, 31 May 2012 19:54:57 GMT

Hi Vasanthakumar,

You cant assign an internal table in the where condition of the select query unless you declare that with FOR ALL ENTRIES of that internal table.Here you need only one entry so for all entries doesn't arise.

What you can do is that if KUNAG is common for VBELN then you can read the index 1 and pass the value to the select query.

In your declaration IT_ITAB is the internal table and IT_TAB1 is the work area so,

Read table itab into itab1 index 1.

Now,

SELECT SINGLE KUNNR NAME1 ORT01 LAND1 PSTLZ INTO FS_TAB

FROM KNA1

WHERE KUNNR = IT_TAB1-KUNAG.

Now it will not give that error.

With regards,

Re: sql query error

Former Member — Fri, 01 Jun 2012 02:07:03 GMT

Hi,

Just as suggested, it is better to add your second query to your first query.

SELECT T1~VBELN
      T1~VKORG
      T1~VTWEG
      T1~SPART
      T1~KUNAG
      T1~FKDAT
      T2~POSNR
      T2~MATNR
      T2~ARKTX
      T2~FKLMG
      T2~NETWR
      T3~NAME1
       T3~ORT01 
      T3~LAND1 
      T3~PSTLZ      
      INTO TABLE IT_TAB
      FROM VBRK AS T1
      INNER JOIN VBRP AS T2 ON T1~VBELN EQ T2~VBELN
      INNER JOIN KNA1 AS T3 ON T1~KUNAG EQ T3~KUNNR
      WHERE T1~VBELN eq P_VBELN.

And add this to your TYPES tab:

TYPES: BEGIN OF types_table,
          vbeln TYPE vbrk-vbeln,
          vkorg TYPE vbrk-vkorg,
          vtweg TYPE vbrk-vtweg,
          spart TYPE vbrk-spart,
          fkdat TYPE vbrk-fkdat,
          kunag TYPE vbrk-kunag,
          posnr TYPE vbrp-posnr,
          matnr TYPE vbrp-matnr,
          arktx TYPE vbrp-arktx,
          fklmg TYPE vbrp-fklmg,
          netwr TYPE vbrp-netwr,
          name1 TYPE kna1-name1,
          ort01 TYPE kna1-ort01,
          land1 TYPE kna1-land1,
          pstlz TYPE kna1-pstlz,
        END OF types_table.

Regards,

Jake

Re: sql query error

Former Member — Mon, 04 Jun 2012 12:29:16 GMT

Hi Raymond,

As you have mentioned I declared a workarea for KNA1 and used FOR ALL ENTRIES and checked it and it is now working...this is how i used the SELECT statement.

SELECT POSNR MATNR ARKTX FKLMG NETWR INTO TABLE T_VBRP

FROM VBRP

WHERE VBELN = P_VBELN.

SELECT VBELN VKORG VTWEG SPART FKDAT KUNAG INTO TABLE T_VBRK

FROM VBRK WHERE VBELN = P_VBELN.

IF SY-SUBRC = 0.

SELECT KUNNR NAME1 ORT01 LAND1 PSTLZ INTO TABLE T_KNA1

FROM KNA1

FOR ALL ENTRIES IN T_VBRK

WHERE KUNNR = T_VBRK-KUNAG.

ENDIF.

thx everyone for your suggestions.