topic Re: Select query help in Application Development and Automation Discussions

Select query help

Former Member — Fri, 16 Jun 2006 10:05:34 GMT

I neede a select query between two tables in to ITAB where the common database entries of both tables to be excluded

Re: Select query help

Former Member — Fri, 16 Jun 2006 10:10:26 GMT

Hi raj,

1. the sql will depend upon

the MEANING OF 'common database entries'

ie. is there only one primary key column,

which will signify COMMON,

or more than one column.

or all fields.

2. Can u give the tables names.

regards,

amit m.

Re: Select query help

Former Member — Fri, 16 Jun 2006 10:14:38 GMT

If I understand you correctly, you can use INNER JOIN for this

Check this query below.

  
SELECT kb~bukrs bi~gjahr bi~belnr INTO TABLE itab_cust 
         FROM knb1 AS kb
         INNER JOIN bsid AS bi ON kb~bukrs NE bi~bukrs.

Believe this helps you.

rgds,

Re: Select query help

Former Member — Fri, 16 Jun 2006 10:18:22 GMT

Hai

Go through the following Code

TABLES: EKKO,EKPO.

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-000.

SELECT-OPTIONS:

S_LIFNR FOR EKKO-LIFNR,

S_BUKRS FOR EKKO-BUKRS.

SELECTION-SCREEN END OF BLOCK B1.

DATA: BEGIN OF IT_EKKO OCCURS 0,

BUKRS LIKE EKKO-BUKRS,

LIFNR LIKE EKKO-LIFNR,

EBELN LIKE EKKO-EBELN,

KNUMV LIKE EKKO-KNUMV,

EBELP LIKE EKPO-EBELP,

MATNR LIKE EKPO-MATNR,

INFNR LIKE EKPO-INFNR,

MENGE LIKE EKPO-MENGE,

MEINS LIKE EKPO-MEINS,

NETPR LIKE EKPO-NETPR,

END OF IT_EKKO.

SELECT

A~BUKRS

A~LIFNR

A~EBELN

A~KNUMV

B~EBELP

B~MATNR

B~INFNR

B~MENGE

B~MEINS

B~NETPR

INTO TABLE IT_EKKO

FROM EKKO AS A INNER JOIN EKPO AS B

ON EKKO-EBELN = EKPO-EBELN

WHERE A~LIFNR IN S_LIFNR AND

A~BUKRS IN S_BUKRS.

IF SY_SUBRC = 0.

SORT IT_EKKO BY EBELN.

ENDIF.

IF NOT IT_EKKO[] IS INITIAL.

CLEAR IT_EKKO.

LOOP AT IT_EKKO.

WRITE : /IT_EKKO-BUKRS,

IT_EKKO-LIFNR,

IT_EKKO-EBELN,

IT_EKKO-KNUMV,

IT_EKKO-EBELP,

IT_EKKO-MATNR,

IT_EKKO-INFNR,

IT_EKKO-MENGE,

IT_EKKO-MEINS,

IT_EKKO-NETPR.

ENDLOOP.

ENDIF.

Thanks & regards

Sreenivasulu P

BUKRS LIFNR EBELN KNUMV

FROM EKKO

INTO TABLE IT_EKKO

WHERE LIFNR IN S_LIFNR

AND BUKRS IN S_BUKRS.

IF NOT IT_EKKO[] IS INITIAL.

SELECT EBELN EBELP MATNR INFNR MENGE MEINS NETPR

FROM EKPO

INTO TABLE IT_EKPO

FOR ALL ENTRIES IN IT_EKKO

WHERE EBELN = IT_EKKO-EBELN.

ENDIF.

SORT IT_EKKO BY BUKRS LIFNR EBELN.

LOOP AT IT_EKKO.

READ TABLE IT_EKPO WITH KEY EBELN = IT_EKPO-EBELN.

WRITE:/1 SY-VLINE,

(10) IT_EKKO-EBELN ,

SY-VLINE,

(10) IT_EKKO-LIFNR,

SY-VLINE,

(15) IT_LFA1-NAME1,

SY-VLINE,

(10) IT_EKKO-BUKRS HOTSPOT ON,

SY-VLINE,

(15) V_BUTXT,

75 SY-VLINE.

HIDE: IT_EKKO-EBELN,

IT_EKKO-LIFNR.

WRITE:/1(75) SY-ULINE.

AT END OF LIFNR.

LINNO = SY-LINCT - SY-LINNO - 1.

SKIP LINNO.

ENDAT.

ENDLOOP.

Re: Select query help

Former Member — Fri, 16 Jun 2006 10:29:34 GMT

Hi,

Please you explain your requirement more

precisely.

Regards

Amole

Re: Select query help

Former Member — Fri, 16 Jun 2006 11:24:54 GMT

Hi Raj,

Please refer to this program which is given as an example in 'abapdocu' transaction code.

REPORT demo_select_inner_join.

DATA: BEGIN OF wa,

carrid TYPE spfli-carrid,

connid TYPE spfli-connid,

fldate TYPE sflight-fldate,

bookid TYPE sbook-bookid,

END OF wa,

itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY carrid connid fldate bookid.

SELECT p_{carrid p}connid f_{fldate b}bookid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( spfli AS p

INNER JOIN sflight AS f ON p_{carrid = f}carrid AND

p_{connid = f}connid )

INNER JOIN sbook AS b ON b_{carrid = f}carrid AND

b_{connid = f}connid AND

b_{fldate = f}fldate )

WHERE p~cityfrom = 'FRANKFURT' AND

p~cityto = 'NEW YORK' AND

f_{seatsmax > f}seatsocc.

LOOP AT itab INTO wa.

AT NEW fldate.

WRITE: / wa-carrid, wa-connid, wa-fldate.

ENDAT.

WRITE / wa-bookid.

ENDLOOP.

Inner join works like an intersection and Outer join works like union.

Say if u have two tables TAB1 and TAB2.

TAB1 :

FIELD1 FIELD2

A TEXT1

B TEXT2

C TEXT5

TAB2 :

FIELD3 FIELD4

A TEXT3

B TEXT4

INNER JOIN :

FIELD1 FIELD2 FIELD4

A TEXT1 TEXT3

B TEXT2 TEXT4

The entry for c got delected, because it is an inner join.

OUTER JOIN :

FIELD1 FIELD2 FIELD4

A TEXT1 TEXT3

B TEXT2 TEXT4

C TEXT5

The entry for c got selected, because it is an Outer join.

Hope this will help u in some way.