topic Re: Help with a select in Application Development and Automation Discussions

Help with a select

Former Member — Tue, 24 Apr 2007 18:46:32 GMT

I am getting a syntax error in the following code. The error is in second to last line (WHERE sa_num = adrc-addrnumber). The error I get is "Field SA_NUM is unknown"

*&---------------------------------------------------------------------*
*& Report  ZPARTNER
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT  zpartner.

TABLES: vbak,
        vbpa,
        adrc.


TYPES: BEGIN OF ty_detail,
         vbeln TYPE vbak-vbeln,
         erdat TYPE vbak-erdat,
         vdatu TYPE vbak-vdatu,
         sa_num TYPE vbpa-adrnr,
         sa_name TYPE adrc-name1,
         sa_city TYPE adrc-city1,
         sa_state TYPE adrc-region,
         sa_zip TYPE adrc-post_code1,
         sa_tel TYPE adrc-tel_number,
         ag_num TYPE vbpa-kunnr,
         ag_name TYPE kna1-name1,
         ag_street TYPE kna1-stras,
         ag_city TYPE kna1-ort01,
         ag_state TYPE kna1-regio,
         ag_zip TYPE kna1-pstlz,
         ag_tel TYPE kna1-telf1,
         ag_sales TYPE vbak-vkbur,
         we_num TYPE likp-kunnr,
         we_name TYPE kna1-name1,
         we_name2 TYPE kna1-name2,
         we_street TYPE kna1-stras,
         we_city TYPE kna1-ort01,
         we_state TYPE kna1-regio,
         we_zip TYPE kna1-pstlz,
         we_tel TYPE kna1-telf1,
       END OF ty_detail.

DATA: it_detail TYPE SORTED TABLE OF ty_detail WITH UNIQUE KEY vbeln sa_num ag_num we_num,
      wa_detail LIKE LINE OF it_detail.

DATA addressnum TYPE vbpa-adrnr.






**----------------------------------------------------------------------
* S E L E C T I O N - S C R E E N
**----------------------------------------------------------------------
SELECTION-SCREEN BEGIN OF BLOCK required WITH FRAME TITLE text-002.
SELECT-OPTIONS:  s_vdatu FOR vbak-vdatu OBLIGATORY.    "Req delivery date
SELECTION-SCREEN END OF BLOCK required.

SELECTION-SCREEN BEGIN OF BLOCK temp WITH FRAME TITLE text-001.
SELECT-OPTIONS:  s_auart FOR vbak-auart OBLIGATORY.
PARAMETER:  p_parfun TYPE vbpa-parvw DEFAULT 'Z5' OBLIGATORY.
SELECTION-SCREEN END OF BLOCK temp.

INITIALIZATION.
  REFRESH s_auart.
  CLEAR s_auart.
  s_auart-sign = 'I'.
  s_auart-option = 'EQ'.
  s_auart-low = 'OR'.
  APPEND s_auart.
  s_auart-low = 'ZDT'.
  APPEND s_auart.
**----------------------------------------------------------------------
* END OF SELECTION SCREEN
**----------------------------------------------------------------------

**----------------------------------------------------------------------
* M A I N - B O D Y
**----------------------------------------------------------------------
*Main select statement.
  SELECT a~vbeln a~erdat a~vdatu c~adrnr c~kunnr
      INTO TABLE it_detail
      FROM vbak AS a
        INNER JOIN vbap AS b
            ON b~vbeln = a~vbeln
        INNER JOIN vbpa AS c
            ON c~vbeln = b~vbeln
            AND c~posnr = b~posnr
                 WHERE a~auart IN s_auart
                 AND a~vdatu IN s_vdatu
                 AND c~parvw = p_parfun.


  LOOP AT it_detail INTO wa_detail.
    SELECT name1 street city1 region post_code1 tel_number
        FROM adrc INTO CORRESPONDING FIELDS OF wa_detail
        WHERE sa_num = adrc-addrnumber.
    ENDLOOP.

Regards,

Davis

Re: Help with a select

Former Member — Tue, 24 Apr 2007 18:53:12 GMT

Hi,

Please try this.


...

LOOP AT it_detail INTO wa_detail.
  SELECT name1 street city1 region post_code1 tel_number
    FROM adrc INTO CORRESPONDING FIELDS OF wa_detail
   WHERE addrnumber = wa_detail-sa_num.
   
  MODIFY it_detail FROM wa_detail.
ENDLOOP.

...

Regards,

Ferry Lianto

Re: Help with a select

Former Member — Tue, 24 Apr 2007 18:54:03 GMT

Hi,

I think the sequence must be reversed. Flip the fields and that should work.

WHERE addrnumber = wa_detail-sa_num.

Regards,

Sumant.

Re: Help with a select

RichHeilman — Tue, 24 Apr 2007 18:54:13 GMT

your code should be like so. You may want to use the SINGLE in your SELECT statement and also do a MODIFY statement also.




  LOOP AT it_detail INTO wa_detail.
    SELECT Single name1 street city1 region post_code1 tel_number
        FROM adrc INTO CORRESPONDING FIELDS OF wa_detail
        WHERE  addrnumber = wa_detail-sa_num.
     modify it_detail from wa_detail.
    ENDLOOP.

Regards,

Rich Heilman

Message was edited by:

Rich Heilman

Re: Help with a select

Former Member — Tue, 24 Apr 2007 18:55:01 GMT

Hi!


  LOOP AT it_detail INTO wa_detail.
    SELECT name1 street city1 region post_code1 tel_number
        FROM adrc INTO CORRESPONDING FIELDS OF wa_detail
        WHERE addrnumber = wa_detali-sa_num.   "<<< correct here
    ENDLOOP.

Regards

Tamá

Re: Help with a select

Former Member — Tue, 24 Apr 2007 18:56:31 GMT

Hi Davis

LOOP AT it_detail INTO wa_detail.

SELECT name1 street city1 region post_code1 tel_number

FROM adrc INTO CORRESPONDING FIELDS OF wa_detail

WHERE sa_num = adrc-addrnumber.

ENDLOOP.

I think there is no field called sa_num in table adrc.

it should be

where it_detail-sa_num = adrc-addrnumber.

Thanks

Re: Help with a select

Former Member — Tue, 24 Apr 2007 18:56:44 GMT

dont use select inside a loop it will cause performance issue. use for all entries.

Your select query is wrong.

SELECT name1 street city1 region post_code1 tel_number

FROM adrc INTO CORRESPONDING FIELDS OF wa_detail

WHERE adrc-addrnumber = wa_detail-sa_num .

Re: Help with a select

Former Member — Tue, 24 Apr 2007 19:03:00 GMT

Rich, thanks a lot for your reply (and helping a fellow PA resident). Thanks to all for the help, I am always amazed at how helpful people are on SDN.

Regards,

Davis.

Re: Help with a select

Former Member — Tue, 24 Apr 2007 19:05:10 GMT

phanindra, thanks for the tip. I'll have to try that out.

Regards,

Davis

Re: Help with a select

Former Member — Tue, 24 Apr 2007 19:06:50 GMT

Hi Davis,

Did you check my reply?

Regards,

Ferry Lianto

Re: Help with a select

Former Member — Tue, 24 Apr 2007 19:09:38 GMT

Ferry, yes I did. I saw it after I posted the reply to Rich. For some reason I got an email with his reply before I got yours. Thanks for your help!

Davis

Re: Help with a select

RichHeilman — Tue, 24 Apr 2007 19:16:42 GMT

That's weird. Anyway, not sure that Ferry's solution is as it was when initially posted(neither is mine), plus there is no suggestion for the SINGLE in SELECT statement in Ferry's post. But it doesn't matter, we are all here to work and learn together. Right?

Regards,

Rich Heilman