help in selecting statements

[Former Member]

Hi

what is the wrong in this select statement

I have to fetch users from alm_me_d997 and i have to compare it with the functional location of another table iflot

data: begin of t_floc occurs 0,

userid like ALM_ME_D997-userid, " Mobile users id

Funcloc like ALM_ME_D997-funcloc, " Functional location

tplnr like iflot-tplnr, " Functional location

tplma like iflot-tplma, " Superior Func location

end of t_floc.

SELECT userid funcloc

FROM alm_me_d997

INTO CORRESPONDING FIELDS OF TABLE t_floc

WHERE funcloc = t_floc-tplnr.

Regards

Nanda

[Former Member]

Hi

what is the wrong in this select statement

I have to fetch users from alm_me_d997 and i have to compare it with the functional location of another table iflot

data: begin of t_floc occurs 0,

userid like ALM_ME_D997-userid, " Mobile users id

Funcloc like ALM_ME_D997-funcloc, " Functional location

tplnr like iflot-tplnr, " Functional location

tplma like iflot-tplma, " Superior Func location

end of t_floc.

SELECT userid funcloc

FROM alm_me_d997

INTO CORRESPONDING FIELDS OF TABLE t_floc

WHERE funcloc = t_floc-tplnr.

Regards

Nanda

[Former Member]

Hi,

Try the INNER JOIN clause:

SELECT a_{userid a}funcloc

INTO CORRESPONDING FIELDS OF TABLE t_floc

FROM alm_me_d997 as a

inner join iflot as b on a_{funcloc = b}tplnr.

if you have functional location from iflot in an internal table, then

SELECT userid funcloc

FROM alm_me_d997

INTO CORRESPONDING FIELDS OF TABLE t_floc

for all entries in IT_FLOC

WHERE funcloc = IT_FLOC-tplnr.

IT_FLOC is an internal table containing all the functional locations.

Regards,

Subramanian

[Former Member]

Hi

it dont contain any values in that table

[Former Member]

In that case, you have to change your where clause, you need to give another option. (you may define select-option on screen and use it in where or derive it in program and pass in where clause), but current select query does not make any sense.

regards,

Mohaiyuddin

[Former Member]

hi

can i have sample coding plz

Regards

Nanda

[Former Member]

Hi Nanda kishore,

Select query does not make any sense, you are fetching data in same table and specifying condition WHERE funcloc = t_floc-tplnr, i.e. using same internal table to check in where condition. (that too without FOR ALL ENTRIES ).

Specify your requirement more clearly.

Are there entries already avaialble in t_floc ?

Regards,

Mohaiyuddin

[Former Member]

Hi

I have already tried by using iner join query but its not working .

[Former Member]

Hi Mohaiyuddin Soniwala

Can i Have a sample coding please.

My Requirement is to fetch the userid and functional location from that table alm_me_d997 and if the functional in that table and functional location in iflot table are same then i have to fetch all values in field tplma for the same table iflot

Regards

Nanda

[Former Member]

data: begin of t_floc occurs 0,

userid like ALM_ME_D997-userid, " Mobile users id

Funcloc like ALM_ME_D997-funcloc, " Functional location

tplnr like iflot-tplnr, " Functional location

tplma like iflot-tplma, " Superior Func location

end of t_floc.

SORT IFLOT BY FUNCLOC.

SELECT userid funcloc

FROM alm_me_d997

INTO CORRESPONDING FIELDS OF TABLE t_floc

FOR ALL ENTRIES IN IFLOT

WHERE funcloc = IFLOT-FUNCLOC.

LOOP AT T_FLOC.

READ TABLE IFLOT WITH KEY FUNCLOC = T_FLOC-FUNCLOC BINARY SEARCH.

IF SY-SUBRC = 0.

T_FLOC-TPLMA = IFLOT-TPLMA.

MODIFY T_FLOC TRANSPORTING TPLMA,

ENDIF.

ENDLOOP.

I am assuming there will be 1 value in IFLOT for each TPLMA.. if there are multiple, you will need to have nested loop and append values to new internal table.

Regards,

Mohaiyuddin

[Former Member]

Hi,

Ur code as now will fetch no records............

how ll funcloc = t_floc-tplnr in ur select query's get any value?

SELECT userid funcloc

FROM alm_me_d997

INTO CORRESPONDING FIELDS OF TABLE t_floc

WHERE funcloc = t_floc-tplnr.

cheers,

Will.

[Former Member]

Try using two internal tables:

select TPLNR

from IFLOT

into table t_iflot.

(Specify Where Clause if any)....

if not it_iflot[] is initial.

select userid

funcloc

into table t_floc

from alm_me_d997

for all entries in t_iflot

where funcloc = t_iflot-tplnr.

endif.

If using Inner Join : Try to Specify IFLOT as the first table and ALM_ME_D997 as the second.

SELECT

b~userid

a~tplnr

INTO CORRESPONDING FIELDS OF TABLE t_floc

FROM IFLOT as a

inner join alm_me_d997 as b

on a_{tplnr = b}funcloc.