topic Re: Doubt in select query in Application Development and Automation Discussions

Doubt in select query

Former Member — Wed, 27 Aug 2008 12:35:37 GMT

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

Re: Doubt in select query

bpawanchand — Wed, 27 Aug 2008 12:36:36 GMT

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

Regards

Pavan

Re: Doubt in select query

Former Member — Wed, 27 Aug 2008 12:37:59 GMT

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

Re: Doubt in select query

Former Member — Wed, 27 Aug 2008 12:39:47 GMT

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.

Re: Doubt in select query

Former Member — Wed, 27 Aug 2008 12:39:51 GMT

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.

Re: Doubt in select query

Former Member — Wed, 27 Aug 2008 12:40:44 GMT

Cant i use like this in the where codn

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

Re: Doubt in select query

Former Member — Wed, 27 Aug 2008 12:43:28 GMT

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

Re: Doubt in select query

Former Member — Wed, 27 Aug 2008 12:43:47 GMT

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

Re: Doubt in select query

Former Member — Wed, 27 Aug 2008 12:44:00 GMT

Yes,

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

( join on .... )

Check the F1 help.

Erwan

Re: Doubt in select query

bpawanchand — Wed, 27 Aug 2008 12:44:23 GMT

Regards

Pavan

Re: Doubt in select query

Former Member — Wed, 27 Aug 2008 12:44:49 GMT

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.

Re: Doubt in select query

Former Member — Wed, 27 Aug 2008 12:49:49 GMT

Hi,

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

Thanks & Regards

Nabendu

Re: Doubt in select query

Former Member — Wed, 27 Aug 2008 12:54:30 GMT

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

Re: Doubt in select query

Former Member — Wed, 27 Aug 2008 12:59:24 GMT

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>

Re: Doubt in select query

Former Member — Wed, 27 Aug 2008 13:03:00 GMT

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.

Re: Doubt in select query

Former Member — Wed, 27 Aug 2008 13:03:17 GMT

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.

Re: Doubt in select query

Former Member — Wed, 27 Aug 2008 13:10:04 GMT

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

Re: Doubt in select query

Former Member — Wed, 27 Aug 2008 13:14:15 GMT

This message was moderated.

Re: Doubt in select query

Former Member — Wed, 27 Aug 2008 19:11:22 GMT

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

Re: Doubt in select query

Former Member — Wed, 27 Aug 2008 22:44:08 GMT

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.