topic Re: SELECT in Application Development and Automation Discussions

SELECT

Former Member — Thu, 24 Jan 2008 11:57:27 GMT

HOW TO WRITE A SINGLE SELECT STATEMENT TO RETRIEVE "PERNR FROM PA0001", "NACHN , VORNA FROM PA0002"?

Re: SELECT

Former Member — Thu, 24 Jan 2008 12:01:22 GMT

performancewise it is not advisable to use single select for fetching data from these 2 tables...

use 2 selects

select pernr

from pa0001 into table it_0001

where pernr in s_pernr

and begda le s_date-high

and endda ge s_date-low.

if sy-subrc = 0.

sort it_0001 by pernr endda descending.

delete adjacent duplicates from it_0001 comparing pernr.

endif.

if not it_0001[] is initial.

select vorna nachn from pa0002 into table it_0002 for all entries in it_0001 where pernr = it_0001-pernr and endda = '99991231'.

if sy-subrc = 0.

sort it_0002 by pernr.

endif.

Re: SELECT

Former Member — Thu, 24 Jan 2008 12:04:10 GMT

Hi KRITHIGA APPADURAI,

Do You want to join for these two tables?

Plzz reward if it is useful,

Mahi.

Re: SELECT

Former Member — Thu, 24 Jan 2008 12:04:13 GMT

Hi Krithiga

I belive if you want to retrieve just NACHN and VORNA, it is already present in PA0001 itself. The ENAME is the concatenation of VORNA, NACHN and TITEL from PA0002, may be you can look at the option of splitting after getting from PA0001.

Regards

Ranganath

PS : Reward points for all useful answers !

Re: SELECT

Former Member — Thu, 24 Jan 2008 12:05:58 GMT

Hi krithiga,

use joins as we hav only two tables...

select p1_{pernr p2}nachn p2_{vorna from pa0001 as p1 join pa0002 as p2 on p1}pernr = p2~pernr .

pls reward if helps,

regards.

Re: SELECT

rainer_hbenthal — Thu, 24 Jan 2008 12:17:53 GMT

Thats not as easy as you might thought. You have to take the date ranges into account, personal subarea may have changed more often then your last name. Thats why the logical database PNP is used very often doing a provide on the records.

if you just need actual lastname and firstname you dont need to read anything from pa0001, you can just do a select on pa0002 using a where clause with pernr and endda = 99991231

Your statement is a little bit unclear, you do not need pa0001 for pernr, its in pa0002 too.

Re: SELECT

rainer_hbenthal — Thu, 24 Jan 2008 12:20:39 GMT

> select p1_{pernr p2}nachn p2_{vorna from pa0001 as p1 join pa0002 as p2 on p1}pernr =

> p2~pernr .

What sense does this join makes having pernr in pa0002 and selecting nothing else but pernr from pa0001?