sql query error

[Former Member]

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

[former_member222709]

Hi Vasantha,

Please add the data declaration code of it_tab.

Regards, Pranav.

[Former Member]

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.

[former_member222709]

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.

[Former Member]

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

[RaymondGiuseppi]

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

[Former Member]

Hi Raymond,

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

Regards

Vasanth

[former_member209818]

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

[RaymondGiuseppi]

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

[former_member282968]

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,

[Former Member]

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.

[RaymondGiuseppi]

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

[former_member282968]

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,

[Former Member]

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

[Former Member]

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.