topic Re: HELP WITH SELECT STATEMENT.. in Application Development and Automation Discussions

HELP WITH SELECT STATEMENT..

Former Member — Mon, 25 Jun 2007 20:55:21 GMT

Hi gurus,

Need some help...I am writing this select statement but it is picking some duplicate values ..could u tell me if this is correct...

SELECT DISTINCT A_{EBELP A}DMBTR A_{MENGE D}MEINS D~TXZ01 FROM EKBE AS A

INNER JOIN EKPO AS D

ON A_{EBELN = D}EBELN

INTO INT_ITEM

WHERE A~EBELN = INT_HEADER-EBELN.

APPEND INT_ITEM.

CLEAR INT_ITEM.

ENDSELECT.

Your help is required..

Thnx Jim

Re: HELP WITH SELECT STATEMENT..

Former Member — Mon, 25 Jun 2007 20:58:05 GMT


SELECT DISTINCT A~EBELP A~DMBTR A~MENGE D~MEINS D~TXZ01 
FROM EKBE AS A
INNER JOIN EKPO AS D
ON A~EBELN = D~EBELN
INTO TABLE INT_ITEM
WHERE A~EBELN = INT_HEADER-EBELN.

SORT INT_ITEM BY EBELP.
DELETE ADJACENT DUPLICATES FROM INT_ITEM.

Never use SELECT-ENDSELECT -;) Also, DISTINCT should work....I put DELETE ADJACENT just in case.... -:P

Greetings,

Blag.

Re: HELP WITH SELECT STATEMENT..

Former Member — Mon, 25 Jun 2007 21:03:04 GMT

Hi Jimmy ,

Better way to write this code is ;

SELECT  A~EBELP 
                       A~DMBTR 
                       A~MENGE 
                       D~MEINS 
                       D~TXZ01 
    FROM EKBE AS A
    INNER JOIN EKPO AS D
           ON A~EBELN = D~EBELN
   INTO TABLE INT_ITEM
   WHERE A~EBELN = INT_HEADER-EBELN.

REMOVE ADJACENT DUPLICATES COMPARING EBELN.

Reward points if helpfull..****************************

Re: HELP WITH SELECT STATEMENT..

Former Member — Mon, 25 Jun 2007 21:03:33 GMT

Hey Blag,

Thnx for ur reply...

I tried this also but when I debug i see that for SORT INT_ITEM BY EBELP.

sy-subrc = 4..

so it really not doing anything..

what else we can try...

Thnx for ur prompt support

Cheers:Jim

Re: HELP WITH SELECT STATEMENT..

Former Member — Mon, 25 Jun 2007 21:09:40 GMT

I am getin duplicate values..I am running this select for a PO which has only 4 items but in my internal table i am getin 16 items ...every line item is repeated 4 times..

Please suggest..

Re: HELP WITH SELECT STATEMENT..

Former Member — Mon, 25 Jun 2007 21:11:10 GMT

If INT_ITEM contains a field called EBELP, it should work....How you defined your internal table???

Greetings,

Blag.

Re: HELP WITH SELECT STATEMENT..

Former Member — Mon, 25 Jun 2007 21:15:36 GMT

Hey Blag,

I will show the entire code...Look at it and suggest...

REPORT ZDEMO_PROGRAM .

*********************************************************

D A T A B A S E T A B L E S D E C L A R A T I O N

************************************************************************

TABLES:EKBE, "History per Purchasing Document

RBKP, "Document Header: Invoice Receipt

LFA1, "Vendor Master

EKPO. "Purchasing Document Item

************************************************************************

T Y P E S D E C L A R A T I O N S

************************************************************************

TYPES:BEGIN OF I_H,

EBELN TYPE EBELN,

BELNR TYPE RE_BELNR,

XBLNR TYPE XBLNR1,

LIFNR TYPE LIFRE,

NAME1 TYPE NAME1_GP,

END OF I_H.

TYPES:BEGIN OF I_T,

EBELP TYPE EBELP,

DMBTR TYPE DMBTR,

MENGE TYPE MENGE_D,

MEINS TYPE BSTME,

TXZ01 TYPE TXZ01,

END OF I_T.

************************************************************************

D A T A D E C L A R A T I O N S

************************************************************************

DATA: INT_HEADER TYPE STANDARD TABLE OF I_H WITH HEADER LINE,

INT_ITEM TYPE STANDARD TABLE OF I_T WITH HEADER LINE.

************************************************************************

S E L E C T I O N S C R E E N *

************************************************************************

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-011.

SELECT-OPTIONS:S_EBELN FOR EKBE-EBELN OBLIGATORY.

SELECTION-SCREEN END OF BLOCK b1.

AT SELECTION-SCREEN.

PERFORM VALIDATE_SCREEN.

START-OF-SELECTION.

PERFORM FETCH_DATA.

TOP-OF-PAGE.

INCLUDE ZHEADER.

END-OF-PAGE.

ULINE.

END-OF-SELECTION.

*PERFORM DISPLAY_DATA.

FORM VALIDATE_SCREEN.

CLEAR EKBE-EBELN.

IF S_EBELN IS NOT INITIAL.

SELECT EBELN UP TO 1 ROWS

INTO EKBE-EBELN

FROM EKBE

WHERE EBELN IN S_EBELN.

ENDSELECT.

ENDIF.

ENDFORM.

FORM FETCH_DATA.

SELECT DISTINCT A_{EBELN B}BELNR B_{XBLNR B}LIFNR C~NAME1

INTO INT_HEADER

FROM EKBE AS A

INNER JOIN RBKP AS B

ON A_{BELNR = B}BELNR

INNER JOIN LFA1 AS C

ON B_{LIFNR = C}LIFNR

WHERE A~EBELN IN S_EBELN.

APPEND INT_HEADER.

CLEAR INT_HEADER.

ENDSELECT.

LOOP AT INT_HEADER.

WRITE:/40 TEXT-001,INT_HEADER-LIFNR,

60 TEXT-002,INT_HEADER-NAME1,

/30 TEXT-003, INT_HEADER-EBELN,

60 TEXT-004, INT_HEADER-BELNR,

85 TEXT-005, INT_HEADER-XBLNR.

SELECT distinct A_{EBELP A}DMBTR A_{MENGE D}MEINS D~TXZ01 FROM EKBE AS A

INNER JOIN EKPO AS D

ON A_{EBELN = D}EBELN

INTO table INT_ITEM

WHERE A~EBELN = INT_HEADER-EBELN.

WRITE:/20 TEXT-006,

40 TEXT-007,

60 TEXT-008,

80 TEXT-009,

100 TEXT-010.

ULINE.

LOOP AT INT_ITEM.

WRITE:/20 INT_ITEM-EBELP,

30 INT_ITEM-DMBTR,

50 INT_ITEM-MENGE,

80 INT_ITEM-MEINS,

100 INT_ITEM-TXZ01.

ENDLOOP.

ULINE.

ENDLOOP.

Your help is really appreciated...Buddy

Thnx

Re: HELP WITH SELECT STATEMENT..

Former Member — Mon, 25 Jun 2007 21:23:43 GMT

Can Anyone suggest something...

Thnx

Cheers:Jim

Re: HELP WITH SELECT STATEMENT..

Former Member — Mon, 25 Jun 2007 21:29:46 GMT

Not sure about your requirement....But I manage to make some fixes to the code...


*********************************************************
* D A T A B A S E T A B L E S D E C L A R A T I O N
************************************************************************
TABLES:EKBE, "History per Purchasing Document
RBKP, "Document Header: Invoice Receipt
LFA1, "Vendor Master
EKPO. "Purchasing Document Item

************************************************************************
* T Y P E S D E C L A R A T I O N S
************************************************************************
TYPES:BEGIN OF I_H,
EBELN TYPE EBELN,
BELNR TYPE RE_BELNR,
XBLNR TYPE XBLNR,
LIFNR TYPE LIFRE,
NAME1 TYPE NAME1_GP,
END OF I_H.

TYPES:BEGIN OF I_T,
EBELP TYPE EBELP,
DMBTR TYPE DMBTR,
MENGE TYPE MENGE_D,
MEINS TYPE BSTME,
TXZ01 TYPE TXZ01,
END OF I_T.

************************************************************************
* D A T A D E C L A R A T I O N S
************************************************************************
DATA: INT_HEADER TYPE STANDARD TABLE OF I_H WITH HEADER LINE,
INT_ITEM TYPE STANDARD TABLE OF I_T WITH HEADER LINE.

************************************************************************
* S E L E C T I O N S C R E E N *
************************************************************************
SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-011.

SELECT-OPTIONS:S_EBELN FOR EKBE-EBELN OBLIGATORY.

SELECTION-SCREEN END OF BLOCK B1.


AT SELECTION-SCREEN.

  PERFORM VALIDATE_SCREEN.


START-OF-SELECTION.

  PERFORM FETCH_DATA.


TOP-OF-PAGE.

INCLUDE ZHEADER.


END-OF-PAGE.
  ULINE.


END-OF-SELECTION.

*PERFORM DISPLAY_DATA.

FORM VALIDATE_SCREEN.

  CLEAR EKBE-EBELN.
  IF NOT S_EBELN IS INITIAL.
    SELECT SINGLE EBELN
    INTO EKBE-EBELN
    FROM EKBE
    WHERE EBELN IN S_EBELN.
  ENDIF.

ENDFORM.

*---------------------------------------------------------------------*
*       FORM FETCH_DATA                                               *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
FORM FETCH_DATA.

  SELECT DISTINCT A~EBELN B~BELNR B~XBLNR B~LIFNR C~NAME1
  INTO TABLE INT_HEADER
  FROM EKBE AS A
  INNER JOIN RBKP AS B
  ON A~BELNR = B~BELNR
  INNER JOIN LFA1 AS C
  ON B~LIFNR = C~LIFNR
  WHERE A~EBELN IN S_EBELN.

  SORT INT_HEADER BY EBELN.
  DELETE ADJACENT DUPLICATES FROM INT_HEADER.

  LOOP AT INT_HEADER.
    WRITE:/40 TEXT-001,INT_HEADER-LIFNR,
    60 TEXT-002,INT_HEADER-NAME1,
    /30 TEXT-003, INT_HEADER-EBELN,
    60 TEXT-004, INT_HEADER-BELNR,
    85 TEXT-005, INT_HEADER-XBLNR.

    SELECT DISTINCT A~EBELP A~DMBTR A~MENGE D~MEINS D~TXZ01
    FROM EKBE AS A
    INNER JOIN EKPO AS D
    ON A~EBELN = D~EBELN
    INTO TABLE INT_ITEM
    WHERE A~EBELN = INT_HEADER-EBELN.

    WRITE:/20 TEXT-006,
    40 TEXT-007,
    60 TEXT-008,
    80 TEXT-009,
    100 TEXT-010.
    ULINE.

    LOOP AT INT_ITEM.
      WRITE:/20 INT_ITEM-EBELP,
      30 INT_ITEM-DMBTR,
      50 INT_ITEM-MENGE,
      80 INT_ITEM-MEINS,
      100 INT_ITEM-TXZ01.
    ENDLOOP.

    ULINE.
  ENDLOOP.

ENDFORM.

Sorry I have no more time to make more changes -:)

Greetings,

Blag.

Re: HELP WITH SELECT STATEMENT..

Former Member — Mon, 25 Jun 2007 21:31:23 GMT

Hi Jimmy,
The Problem in your code is you are printing the data inside teh loop and endloop using select -- endselect. 
This would be better is you get all the data inside one final table and then print that table.

Get final table.
sort final table by ebeln.
then loop.. endloop. to display it to the output.

Re: HELP WITH SELECT STATEMENT..

Former Member — Mon, 25 Jun 2007 21:40:29 GMT

Allright...I will try that...

Thnx for ur help...