topic Re: help in selecting statements in Application Development and Automation Discussions

help in selecting statements

Former Member — Thu, 24 Jan 2008 04:55:08 GMT

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

Re: help in selecting statements

Former Member — Thu, 24 Jan 2008 05:01:10 GMT

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

Re: help in selecting statements

Former Member — Thu, 24 Jan 2008 05:02:53 GMT

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

Re: help in selecting statements

Former Member — Thu, 24 Jan 2008 05:04:52 GMT

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.

Re: help in selecting statements

Former Member — Thu, 24 Jan 2008 05:27:15 GMT

it dont contain any values in that table

Re: help in selecting statements

Former Member — Thu, 24 Jan 2008 05:28:34 GMT

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

Re: help in selecting statements

Former Member — Thu, 24 Jan 2008 05:31:19 GMT

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

Re: help in selecting statements

Former Member — Thu, 24 Jan 2008 05:32:02 GMT

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

Re: help in selecting statements

Former Member — Thu, 24 Jan 2008 05:35:06 GMT

can i have sample coding plz

Regards

Nanda

Re: help in selecting statements

Former Member — Thu, 24 Jan 2008 05:42:08 GMT

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.

Re: help in selecting statements

Former Member — Thu, 24 Jan 2008 05:44:05 GMT

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