can we use concatenate in where clause of a select query

[Former Member]

I have a select query as follows....

SELECT ebeln ebelp belnr vgabe bwart DMBTR

FROM ekbe

INTO TABLE i_ekbe

FOR ALL ENTRIES IN i_blck

WHERE ebeln = i_blck-ebeln AND ebelp =

i_blck-ebelp.

Now i want to retrieve data from BKPF ..can i write a select query something like....

select * from BKPF into itab

FOR ALL ENTRIES IN i_ekbe

where awkey = ( conatenate i_ekbe-belnr i_ekbe-gjahr )

OR ELSE...is there any other way to link table ekbe and bseg ???

[peter_ruiz2]

hi,

it is not possible.

the best thing to do is to concatenate those two fields into one variable and use that variable in your select statement

regards,

Peter

[Former Member]

Hi Poonam,

SELECT ebeln ebelp belnr vgabe bwart DMBTR
FROM ekbe
INTO TABLE i_ekbe
FOR ALL ENTRIES IN i_blck
WHERE ebeln = i_blck-ebeln 
AND ebelp = i_blck-ebelp.

select * 
from BKPF
 into  table itab
FOR ALL ENTRIES IN i_ekbe
where belnr = i_ekbe-belnr.

If u want to check with AWKEY concatenate into seperate field and then use to check in where condition.

data: lw_awkey(20) type c.

CONCATENATE  i_ekbe-belnr  i_ekbe-gjahr  INTO lw_awkey.

select * 
from BKPF
 into  table itab
FOR ALL ENTRIES IN i_ekbe
where awkey = lw_awkey

Best regards,

raam

[Former Member]

ekbe-belnr is not equal to bkpf-belnr......it is a material doc number....i actually have to retrieve bkpf-belnr....conatenating ekbe-belnr and ekbe-gjahr gives me awkey so that i can retrieve bkpf-belnr..

[Former Member]

hi poonam,

add 1 more field(temp) in interna table where concatenated vale get stored.

SELECT ebeln ebelp belnr vgabe bwart DMBTR

FROM ekbe

INTO TABLE i_ekbe

FOR ALL ENTRIES IN i_blck

WHERE ebeln = i_blck-ebeln AND ebelp =

i_blck-ebelp.

loop at i_bclk into wa_bclk.

concatenate i_ekbe-belnr i_ekbe-gjahr into i_ekbe-temp.

modify i_ekbe from wa_ekbe TRANSPORTING temp.

endloop.

select * from BKPF into itab

FOR ALL ENTRIES IN i_ekbe

where awkey = iekbe-temp.

regards,

sandeep patel

[Former Member]

i wanted to avoid looping i_blck as this can slow the performance of the report

[Former Member]

hi poonam,

add 1 more field(temp) in interna table where concatenated vale get stored.

SELECT ebeln ebelp belnr vgabe bwart DMBTR

FROM ekbe

INTO TABLE i_ekbe

FOR ALL ENTRIES IN i_blck

WHERE ebeln = i_blck-ebeln AND ebelp =

i_blck-ebelp.

loop at i_bclk into wa_bclk.

concatenate i_ekbe-belnr i_ekbe-gjahr into i_ekbe-temp.

modify i_ekbe from wa_ekbe TRANSPORTING temp.

endloop.

select * from BKPF into itab

FOR ALL ENTRIES IN i_ekbe

where awkey = iekbe-temp.

regards,

sandeep patel

[Sidh_M]

Hi Poonam,

I guess we cant use concatenate in select query. But befor calling 2nd query declare one field in i_ekbe ie belnr_gjahr. and get both fields n this one field as follows.

loop at i_ekbe.

concatenate i_ekbe-belnr i_ekbe-gjahr into i_ekbe-belnr_gjahr.

modify i_ekbe transporting belnr_gjahr.

clear i_ekbe.

endloop.

Now write your 2nd query

select * from BKPF into itab

FOR ALL ENTRIES IN i_ekbe

where awkey = i_ekbe-belnr_gjahr

Regards,

SUDHIR MANJAREKAR

[Former Member]

Hi Poonam,

You cannot write the concatenate in the WHERE condition.

First you Concatenate and put it in a seperate variable and use that variable in where condition.

Hope this helps you.

Regards,

Chandra Sekhar

[former_member195383]

in stead of the secod select quer try the following loop statement....

declare wf_char1, wf_char2, wf_char3 as character variables..

and wf_awkey of the type of BKPF-awkey ,

and an work area wa_itab of the type BKPF

loop at i_ekbe.

wf_char1 = i_ekbe-belnr.

wf_char2 = i_ekbe-gjahr .

conctenate wf_char1 wf_char2 into wf_char3.

wf_awkey = wf_char3.

select single * from BKPF into wa_itab

where awkey = wf_awkey.

append wa_itab to itab.

clear : wf_char1 , wf_char2, wf_char3, wf_awkey, wa_itab .

endloop.

regarding the performance....it wont affect greatly unless u have a huge number of entries...

second thing is u cant concatenate in where condition..

reward points if useful..

Edited by: Rudra Prasanna Mohapatra on Jul 8, 2008 12:50 PM

[Former Member]

you can't do it exactly the way you described...but it is possible to construct the where clause dynamically...

see the following code for reference...

DATA: ls_where(72) TYPE c,

lt_where LIKE TABLE OF ls_where.

concatenate 'AWKEY = ' i_ekbe-belnr i_ekbe-gjahr into ls_where. (basically each row of the lt_where table consists of conditions that you specify after the where clause).

append ls_where to lt_where.

select * from BKPF into itab

FOR ALL ENTRIES IN i_ekbe

where (lt_where).

see how to modify this to suit your requirement.

hope it helps...

(I haven't done a syntax check on the code...please do and check for minor errors...if any)

Edited by: Priyank Jain on Jul 8, 2008 6:50 AM

[vikash_pathak]

Hi Poonam,

if you have matching keys in both of table you should avoid using for all entries either should you join.

how ever the suggested appraoch you can use for the solution but i would like to correct a little on this.

instead creating temp field in ekbe prapare a range table and that you can use in where clause no need to use for all entries.

like:

types:ty_combined_value type range of awkey.

data(combined_value) = value ty_combined_value( for ls_ekbe in i_ekbe 

                                                                                 sign = if_fsbp_const_range=>sign_include

                                                                                 option = if_fsbp_const_range=>option_equal

                                                                                 low =  ls_ekbe-belnr &&  ls_ekbe-gjahr ).

select * from BKPF into itab

where awkey in combined_value.

Thanks

[ThorstenHoefer]

Hi,
have you tried someting like this?

select fldate
, left(  fldate ,4 ) as y
, substring(  fldate , 5 ,2  ) as m
from sflight
where left(  fldate ,4 ) = '2016'
  and substring(  fldate , 5 ,2  ) = '11'