topic Select statement where have 2 same field type. in Application Development and Automation Discussions

Select statement where have 2 same field type.

Former Member — Mon, 17 Aug 2009 03:08:19 GMT

hi abaper,

how to do the select statement where there are 2 same variables which have exactly same name , same data type but different table source. as below where both table TJ30T and TJ02T have variables name TXT04 with same data typer J_TXT04.

table TJ30T

MANDT MANDT

STSMA J_STSMA

ESTAT J_ESTAT

SPRAS SPRAS

TXT04 J_TXT04

TXT30 J_TXT30

LTEXT J_LANGTEXT

table TJ02T

ISTAT J_ISTAT

SPRAS SPRAS

TXT04 J_TXT04

TXT30 J_TXT30

both i have to select using select statement and choose the TJ30T_{txt04 and TJ02T}txt04. but the problems i faced, since both using txt04, one of it i have to rename with different name, ustxt04. when i did the select statement, the data for TJ30T~txt04 failed to find the ustxt04. here the select statement. thanks you


**********data types declaration*********
types: begin of stobj,
  pspnr type proj-pspnr,
  stspr type proj-stspr,
  objnr type proj-objnr,
  pspid type proj-pspid,
  psphi type prps-psphi,
  posid type prps-posid,
  aufnr type aufk-aufnr,
  pspel type aufk-pspel, 
  inact type jest-inact,
  stat type jest-stat,
  udate type jcds-udate,
  usnam type jcds-usnam, 
  utime type jcds-utime, 
  estat type tj30t-estat,
  TXT04 type TJ02T-txt04,
  USTXT04 type TJ30T-txt04,
end of stobj.

*******Internal and work area declaration*******
 data: itobj type table of stobj,
       waobj like line of itobj.

** select statement code*********
select
    a~pspnr a~stspr a~objnr a~pspid
    b~psphi b~posid
    c~aufnr c~pspel
    d~inact d~stat
    e~udate e~usnam e~utime "e~stat
   F~TXT04
    g~estat
    G~TXT04
    from proj as a
    inner join prps as b on a~pspnr = b~psphi
    inner join aufk as c on b~pspnr = c~pspel
    inner join jest as d on c~objnr = d~objnr
    inner join jcds as e on d~objnr = e~objnr
                         and d~stat = e~stat
    inner join tj02t as f on e~stat = f~istat
    inner join tj30t as g on a~stspr = g~stsma
    for all entries in itparm
    where  a~pspid = itparm-pspid "or e~stat = itparm-psy )
    or  b~posid = itparm-posid "or e~stat = itparm-wsy )
    or  c~aufnr = itparm-aufnr "or e~stat = itparm-nsy  )
    and ( d~inact <> 'X' or e~inact <> 'X')
    and f~spras = 'E' and g~spras = 'E'.

Re: Select statement where have 2 same field type.

Former Member — Mon, 17 Aug 2009 03:13:40 GMT

Hi,

Since you are using a~tablename, I think there would be no issues if there are fields having the same names.

Hope this helps.

Benedict

Re: Select statement where have 2 same field type.

guilherme_frisoni — Mon, 17 Aug 2009 03:15:07 GMT

Hi,

have you tried to use an alias to each field?


SELECT F~TXT04 as txt1
    G~TXT04 as txt2
...

But if you use INTO CORRESPONDIG FIELDS OF.. may fail to find correst field.

So you should declare your structure in the same order as your select statement.

Regards,

Frisoni

Re: Select statement where have 2 same field type.

Former Member — Mon, 17 Aug 2009 03:17:21 GMT

correctio0n on the select statement

select statement code*********

select

a_{pspnr a}stspr a_{objnr a}pspid

b_{psphi b}posid

c_{aufnr c}pspel

d_{inact d}stat

e_{udate e}usnam e_{utime "e}stat

F~TXT04

g~estat

G~TXT04

into corresponding fields of table itobj

from proj as a

inner join prps as b on a_{pspnr = b}psphi

inner join aufk as c on b_{pspnr = c}pspel

inner join jest as d on c_{objnr = d}objnr

inner join jcds as e on d_{objnr = e}objnr

and d_{stat = e}stat

inner join tj02t as f on e_{stat = f}istat

inner join tj30t as g on a_{stspr = g}stsma

for all entries in itparm

where a_{pspid = itparm-pspid "or e}stat = itparm-psy )

or b_{posid = itparm-posid "or e}stat = itparm-wsy )

or c_{aufnr = itparm-aufnr "or e}stat = itparm-nsy )

and ( d_{inact 'X' or e}inact 'X')

and f_{spras = 'E' and g}spras = 'E'.

Re: Select statement where have 2 same field type.

Former Member — Mon, 17 Aug 2009 03:21:10 GMT

Thanks Frisoni,

I used INTO CORRESPONDIG FIELDS OF.. i declare the internal table as i declare in the select statement.

Thanks you very much!

Re: Select statement where have 2 same field type.

daixiong_jiang3 — Mon, 17 Aug 2009 03:24:05 GMT

SOLUTION 1.:

select
    a~pspnr a~stspr a~objnr a~pspid
    b~psphi b~posid
    c~aufnr c~pspel
    d~inact d~stat
    e~udate e~usnam e~utime  g~estat
    F~TXT04 G~TXT04
INTO TABLE  itobj.

SOLUTION 2

select

a~pspnr a~stspr a~objnr a~pspid
    b~psphi b~posid
    c~aufnr c~pspel
    d~inact d~stat
    e~udate e~usnam e~utime "e~stat
   F~TXT04
    g~estat
    G~TXT04 as USTXT04
into corresponding fields of ....