select query

[Former Member]

Hi all

We are having requirment in which i've to take partial field data from a field in database table and use it in where clause with select option.

i am writting the select query :

select bldat budat mblnr mjahr

from mkpf

into corresponding fields of table i_mkpf

for all entries in i_vbfa_so

where mblnr = i_vbfa_so-vbelv

and mjahr = i_vbfa_so-erdat(4)

and bldat+4(2) in so_month

and bldat in so_year.

Error is coming : field bldat+4(2) unknown.

I know this won't work but is there a way to do it in some other way.

[Former Member]

In my case select option is mandatory field.

so after using select query. We compared that internal table value with select option using check statement.

this is the code :

SELECT a_{fkdat a}erdat a_{bukrs a}waerk a~vbtyp

b_{vbeln b}posnr b_{fklmg b}vrkme b_{netwr b}matnr b_{bwtar b}werks

INTO TABLE i_vbrk

FROM vbrk AS a JOIN vbrp AS b

ON a_{vbeln = b}vbeln

FOR ALL ENTRIES IN i_vbfa_so

WHERE b~vbeln = i_vbfa_so-vbelv

AND b~posnr = i_vbfa_so-posnv

AND b~werks IN so_plt

AND b~matnr IN so_matnr

AND b~bwtar IN so_vltyp

AND a~bukrs IN so_cocod .

LOOP AT i_vbrk INTO wa_vbrk .

CHECK wa_vbrk-erdat+4(2) IN so_month .

IF sy-subrc = 0 .

CHECK wa_vbrk-erdat+0(4) IN so_year .

IF sy-subrc = 0 .

CHECK wa_vbrk-fkdat+4(2) IN so_mon1 .

IF sy-subrc = 0 .

CHECK wa_vbrk-fkdat+0(4) IN so_year1 .

IF sy-subrc = 0 .

APPEND wa_vbrk TO i_vbrk_so.

CLEAR wa_vbrk.

ENDIF .

ENDIF .

ENDIF.

ENDIF.

ENDLOOP .

[jayanthi_jayaraman]

Hi,

It's not possible in select.

Alternative way is

select bldat budat mblnr mjahr

from mkpf

into corresponding fields of table i_mkpf

for all entries in i_vbfa_so

where mblnr = i_vbfa_so-vbelv

and mjahr = i_vbfa_so-erdat(4)

and bldat in so_month

and bldat in so_year.

Then delete the unwanted entries from the internal table by comparing bldat+4(2) and so_month.

Message was edited by:

Jayanthi Jayaraman

[Former Member]

since ur prime req is selecting based on month so ...why dont u ...

1. Calculate the start date and end date from the month's parameter selection screen.......create a range and append it ther in low and high.

2. use this range and replace the code as

and bldat in r_range....

Regards

Gunjan

[Former Member]

select bldat budat mblnr mjahr
from mkpf
into corresponding fields of table i_mkpf
for all entries in i_vbfa_so
where mblnr = i_vbfa_so-vbelv
and mjahr = i_vbfa_so-erdat(4)
and bldat in so_year.

loop at i_mkpf.
 
   if not i_mkpf+4(2) in so_month.
        delete table i_mkpf index sy-tabix.
   endif.

endloop.

[Former Member]

first take the first 6 character of your date field suppose

data : spmon(6).

spmon = sy-datum(6).

select bldat budat mblnr mjahr

from mkpf

into corresponding fields of i_mkpf

for all entries in i_vbfa_so

where mblnr = i_vbfa_so-vbelv

and mjahr = i_vbfa_so-erdat(4).

if i_mkpf-bldat cs spmon.

append i_mkpf.

clear i_mkpf.

endif.

endselect.

or you may try

select bldat budat mblnr mjahr

from mkpf

into corresponding fields of table i_mkpf

for all entries in i_vbfa_so

where mblnr = i_vbfa_so-vbelv

and mjahr = i_vbfa_so-erdat(4)and

bldat like spmon.

but i am not sure whether it will work or not.

Message was edited by:

SHIBA DUTTA

[Former Member]

Small modification in Chandra's logic. Hope Chandra wouldnt mind.

select bldat budat mblnr mjahr
from mkpf
into corresponding fields of table i_mkpf
for all entries in i_vbfa_so
where mblnr = i_vbfa_so-vbelv
and mjahr = i_vbfa_so-erdat(4)
and bldat in so_year.
 
*loop at i_mkpf.
 
*   if not i_mkpf+4(2) in so_month.
*        delete table i_mkpf index sy-tabix.
*   endif.
 
*endloop.

delete i_mkpf where not bldat+4(2) in so_month.

Kind Regards

Eswar

[Former Member]

hi

good

and bldat+4(2) instead of writing like this write in the below way

itab-bldat+4(2), here you have to mention the appropriate internal able name .

thanks

mrutyun^

[Former Member]

Hi Mandeep,

Good question.

A select option behaves as an internal table without a headerline.

I've tried some coding to meet your requirement.

It's worked fine. U can use s_month in ur select statement.

Just check it as a test program.

data: l_dats type dats,

month(2) type N.

selection-screen begin of block blk1.

select-options s_date for l_dats.

select-options s_date1 for l_dats no-extension no-display. "for internal use

select-options s_month for month no-display. "for internal use

select-options s_month1 for month no-extension no-display. "for internal use

selection-screen end of block blk1.

loop at s_date into s_date1.

s_month1-low = s_date1-low+4(2).

s_month1-high = s_date1-high+4(2).

s_month1-sign = s_date1-sign.

s_month1-option = s_date1-option.

append s_month1 to s_month.

write: / s_date1.

write: / s_month1.

endloop.

Message was edited by:

JITHENDRA CHADICHAL

[former_member69765]

My friend.. what u are trying is very much possible and is easy also ...

Basically if we are searching then we can search by specifying '*' this query works in a similar manner.

For eg search BUKRS with 10* it will list all the company code starting with 10.

A similar query can be written in ABAP also.

Refer this query .. .

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

data : itab type standard table of bkpf initial size 0,

date(8) type c.

concatenate '%' '03' '%' into date.

select * from bkpf into table itab where bukrs = '4000' and BLDAT like date.

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

Hope this helps... Plz reward points if it does.

[Former Member]

I think the best solution is to combine your selection for so_year and so_month into a range r_date. Use the particular range in your select query.

U can build your range in the below fashion.

lmth = so_month-high + 1.

concatenate so_year-high lmth 01 into lastdate.

lastdate = lastdate - 1.

concatenate so_year-low so_month-low 01 into r_date-low.

concatenate so_year-high so_month-high lastdate+6(2) into r_date-high.

r_date-sign = 'I'.

r_date-option = 'BT'.

append r_date.

select bldat budat mblnr mjahr

from mkpf

into corresponding fields of table i_mkpf

for all entries in i_vbfa_so

where mblnr = i_vbfa_so-vbelv

and mjahr = i_vbfa_so-erdat(4)

and bldat in r_date.

[Former Member]

In my case select option is mandatory field.

so after using select query. We compared that internal table value with select option using check statement.

this is the code :

SELECT a_{fkdat a}erdat a_{bukrs a}waerk a~vbtyp

b_{vbeln b}posnr b_{fklmg b}vrkme b_{netwr b}matnr b_{bwtar b}werks

INTO TABLE i_vbrk

FROM vbrk AS a JOIN vbrp AS b

ON a_{vbeln = b}vbeln

FOR ALL ENTRIES IN i_vbfa_so

WHERE b~vbeln = i_vbfa_so-vbelv

AND b~posnr = i_vbfa_so-posnv

AND b~werks IN so_plt

AND b~matnr IN so_matnr

AND b~bwtar IN so_vltyp

AND a~bukrs IN so_cocod .

LOOP AT i_vbrk INTO wa_vbrk .

CHECK wa_vbrk-erdat+4(2) IN so_month .

IF sy-subrc = 0 .

CHECK wa_vbrk-erdat+0(4) IN so_year .

IF sy-subrc = 0 .

CHECK wa_vbrk-fkdat+4(2) IN so_mon1 .

IF sy-subrc = 0 .

CHECK wa_vbrk-fkdat+0(4) IN so_year1 .

IF sy-subrc = 0 .

APPEND wa_vbrk TO i_vbrk_so.

CLEAR wa_vbrk.

ENDIF .

ENDIF .

ENDIF.

ENDIF.

ENDLOOP .