Doubt in select query

[Former Member]

HI,

In my requirement i want to fetch a field by giving the where codn such as

1)fields from different tables

2)using the value which i have already placed in an internal table.

Please help me how to code

[bpawanchand]

Hi

USe INNER JOIN to join different tables based on the key you.

Regards

Pavan

[Former Member]

Cant i use like this in the where codn

where name1 = t001w-name1 and belnr = bseg-belnr

[Former Member]

Yes,

you can do something like that if you put BSEG & T001W in the join.

( join on .... )

Check the F1 help.

Erwan

[bpawanchand]

hi

Regards

Pavan

[Former Member]

This is the reason why i asked you to elaborate your requirement. That would give us an opportunity to come up with more precise solution.

You can do one thing, Go for a select endselect. First pick up the root document, based on that within that select endselect call a perform and select the other value. Once you get the required documents, go for the last select where in you will specify the WHERE condition and fetch the data(EBELNR) from the table.

In one of the performs (where the selected data can be compared to that in the internal table) do a READ with KEY and you will have the required field value too.

Some sample code to give you a clear idea.

    SELECT lifnr                         " Acc. No. of Vendor
      FROM vbpa
      INTO (w_lifnr)
     WHERE vbeln EQ w_vbeln
       AND parvw EQ c_patnr_func_sp.

* To fetch the subsequent invoice for a delivery document.
      PERFORM fetch_subsequent_invoice.

    ENDSELECT.
*----------------------------------------------------------------------*
*  FORM FETCH_SUBSEQUENT_INVOICE                                       *
*----------------------------------------------------------------------*
*  Subroutine for fetching the subsequent invoice for a delivery from  *
*  VBFA                                                                *
*----------------------------------------------------------------------*
*  There are no interface parameters to be passed to this subroutine.  *
*----------------------------------------------------------------------*
FORM fetch_subsequent_invoice .

  CLEAR w_invc.

  SELECT SINGLE
         vbeln                         " Subsequent SD document
    FROM vbfa
    INTO (w_invc)
   WHERE vbelv   EQ w_vbeln
     AND vbtyp_n EQ c_doc_type_m.

  IF sy-subrc EQ 0.

* To fetch the company code based on the Invoice number.
    PERFORM fetch_company_code.

  ENDIF.                               " IF SY-SUBRC NE 0

ENDFORM.                               " FETCH_SUBSEQUENT_INVOICE
*----------------------------------------------------------------------*
*  FORM FETCH_COMPANY_CODE                                             *
*----------------------------------------------------------------------*
*  Subroutine for picking up the company code based on the subsequent  *
*  Invoice for a delivery document from VBRK                           *
*----------------------------------------------------------------------*
*  There are no interface parameters to be passed to this subroutine.  *
*----------------------------------------------------------------------*
FORM fetch_company_code .

  SELECT bukrs                         " Company Code
    FROM vbrk
    INTO (w_bukrs)
   WHERE vbeln EQ w_invc.

* To fetch the freight variance amount from the table BSIS.
    PERFORM fetch_freight_variance_amount.

  ENDSELECT.

ENDFORM.                               " FETCH_COMPANY_CODE

..........and so on.

[Former Member]

Can u please tell a sample code for this.I dont know how to use join codn

[Former Member]

Well, it depends on the selection criteria that the user inputs and the final data that you are going to fetch. can you tell the business requirement.

[Former Member]

In my requirement i want to fetch belnr based on the i/ps bukrs,anlkl,brdatu and wbs element.Already have fetched the wbs element and placed in an internal table.The other fields which i had mentioned above are in different tables

[Former Member]

This message was moderated.

[Former Member]

Hi,

Use the INNER JOIN statement ( several table )

& the "FOR ALL ENTRIES" statement for your input internal table.

Check the F1 help.

Hope this helps,

Erwan

[Former Member]

If you have to select one record use

READ itab1 with key field = itab2-field TRANSPORTING field.

If you have to select more than one record use

LOOP at itab2 where field = itab1-field.
ENDLOOP.

If the values to be fetched are form DB use INNER JOIN or use statement FOR ALL ENTRIES IN

Hope this helps.

A

[Former Member]

What is the table that you want to fetch the values from and what are the other table fields that you are placing in the WHERE condition. Without knowing these it wouldnt be appropriate to give any solution.

[Former Member]

actually i want to fetch the document number(belnr) by using the where codn from different tables like lfa1,anlav and based on the wbs elements which i have already fertched and placed in the internal table

[Former Member]

Hi,

First select the fields which u are going to pass in WHERE condition from a table.

Then use SELECT .. FOR ALL ENTRIES statement.

Eg.,

Select matnr from mara
into table it_mara
where matnr in s_matnr.

if it_mara[] is not initial.
  select matnr maktx from makt
  into table it_makt
  for all entries in it_mara
  where matnr = it_mara-matnr.
endif.

Regards,

Prem

[Former Member]

Hi,

You use Inner Join for the Databse Table and use FOR ALL ENTRIES in Internal Table.

Thanks & Regards

Nabendu

[Former Member]

example is here

select kna1_{kunnr vbak}vbeln vbak_{erdat vbak}netwr into itab from kna1 inner join vbak on kna1_{kunnr = vbak}kunnr where kna1~kunnr in <parameter name>

[Former Member]

Hi its better to use FOR ALL ENTRIES in place of INNERJOIN.

When u r clubbing 2 tables(1 is DBtable and itab) in that case u want to use FOR ALL ENTRIES.

[Former Member]

Hi,

In this case you have to go for like this.

begin of itab1 occurs 0,

field1

field2

end of itab1.

begin of itab2 occurs 0,

field3

field4

end of itab2.

begin of itab3 occurs 0,

field1

field3

field5

end of itab3.

these two internal tables are not having common fields.

now u want to select data from someother table with these two interanl tables itab1 and itab2 .

in this case use For All Entries for itab1.

select * from XXXX into itab3

For All Entries in itab1

where field1 = itab1-field1.

here dont check itab2 values or fields.

then put loop for itab3 and read itab2 with key field3 = w_itab3-field3. if sy-subrc <> 0 then delete the corresponding record from itab3.

loop at itab3 into w_itab3.

read table itab2 with key field3 = w_itab3-field3.

if sy-subrc <> 0.

delete corresponding record from table itab3.

endif.

endloop.

after this you will get the record in itab3 with corresponding to tables itab1 and itab2

regards,

Raja

[Former Member]

The best thing to do first would be to learn how to write SQL from the beginning.

A good place to start is [W3 Schools|http://www.w3schools.com/sql/default.asp].

They have good tutorials where you can learn how to JOIN and do other simple things that you should probably know.