topic subquery error in Application Development and Automation Discussions

subquery error

Former Member — Thu, 16 Apr 2009 10:32:37 GMT

hi Experts....

I am writting a subquery where i want to select S1 and s5

and in my main query i want to select s5

my criteria is such that i want to take the resigned and joined date

am taking the s5 - Begda where i can get the

resigned employee date of type(S5) and joined date (BEGDA) in S1

PERNR      ENDDA      BEGDA      MASSN
                                      
00000009   31.01.2007 22.03.1993 S1   
00000009   31.12.9999 01.02.2007 S5

i have written the query as below but getting an error

select pernr begda endda massn from pa0000 into corresponding fields  of table it_pa0000
     where begda <= sy-datum and 
          endda  >= sy-datum  and 
          massn = 'S1' ( select pernr massn from pa0000 into corresponding fields of table it_pa0000
                                           where massn = 'S5' )

thanx in advance

RachelArun

Re: subquery error

Former Member — Thu, 16 Apr 2009 10:37:17 GMT


select pernr begda endda massn 
from pa0000 
into corresponding fields  of table it_pa0000
where begda <= sy-datum and 
endda  >= sy-datum  and 
massn = 'S1'

select pernr massn 
from pa0000 
into corresponding fields of table it_pa0000
where massn = 'S5'

Re: subquery error

Former Member — Thu, 16 Apr 2009 10:43:48 GMT

hi kemmer

thnx for the response....but i think its almost the same expect than the brackets

but then i still get the error

Incorrect expression "SELECT" in logical condition.

Re: subquery error

Former Member — Thu, 16 Apr 2009 10:58:34 GMT

HI,

Check this code... there is no need to subquery

select pernr begda endda massn from pa0000 into corresponding fields  of table it_pa0000
     where begda <= sy-datum and 
          endda  >= sy-datum  and 
         ( massn = 'S1'  OR massn = 'S5' ).

Re: subquery error

Former Member — Thu, 16 Apr 2009 11:07:14 GMT

hi ,

select pernr begda endda massn from pa0000 into corresponding fields of table it_pa0000

where begda <= sy-datum and

endda >= sy-datum and

massn = 'S1' and

massn = 'S5' .

Re: subquery error

Former Member — Thu, 16 Apr 2009 11:08:58 GMT

if i give so ....my condition itself will get changed .......i want to take S1 for corresponding S5

not S1 or S5

Re: subquery error

Former Member — Thu, 16 Apr 2009 11:15:19 GMT

thanx for the reply but i am pretty sure that subquery will satisfy my requirememnt

but iam not sure abt the subquery i write

so i need help in subquery

select pernr begda endda massn
           from pa0000 
       into corresponding fields  of table it_pa0000
      where begda <= sy-datum and
      endda  >= sy-datum  having
      massn = 'S5' ALL ( select massn
                                           from pa0000
                                                  wher massn = 'S5' and massn = 'S1' ).

Re: subquery error

Former Member — Thu, 16 Apr 2009 11:16:44 GMT

oh well sry ic forgot the dots behind the select statements.

Re: subquery error

Former Member — Thu, 16 Apr 2009 11:21:08 GMT

need not feel sorry ....thanx for the help actually....

i kept the period and tried .....i was able to do that ...but then thats not the problem....

first i have writen like 2 select queries and have named it as subquery and u have also modified the same

but jus now i found from f1 that thats not the way to do so,......

firs in my subquery i have selected both s1 and s1 and passed that to my main query as input but again iam facing the problem

select pernr begda endda massn
           from pa0000 
       into corresponding fields  of table it_pa0000
      where begda <= sy-datum and
      endda  >= sy-datum  having
      massn = 'S5' ALL ( select massn
                                           from pa0000
                                                  wher massn = 'S5' and massn = 'S1' ).

Re: subquery error

Former Member — Thu, 16 Apr 2009 11:39:02 GMT

Hi Rachel,

As per your condition you can try these selct statements:

select pernr massn from pa000 into corresponding fields of table it_pa0000_1

where begda <= sy-datum and

endda >= sy-datum and

massn = 'S5'.

select pernr begda endda massn from pa0000 into corresponding fields of table it_pa0000

for all entries in it_pa0000_1

where massn = 'S1' and

pernr = it_pa0000_1-pernr.

Cheers,

Surinder

Re: subquery error

Former Member — Thu, 16 Apr 2009 11:43:37 GMT

HI,

Check this query..

DATA it_pa0000 LIKE STANDARD TABLE OF pa0000.
DATA it1_pa0000 LIKE STANDARD TABLE OF pa0000.

* Get the resigned employees here
SELECT pernr begda endda massn FROM pa0000
   INTO CORRESPONDING FIELDS  OF TABLE it_pa0000
     WHERE begda <= sy-datum AND
          endda  >= sy-datum  AND
          massn = 'S5'.

* get the joinig dates of employees who has got retired
SELECT * FROM pa0000
   INTO CORRESPONDING FIELDS  OF TABLE it1_pa0000
    FOR ALL ENTRIES IN it_pa0000
         WHERE pernr EQ it_pa0000-pernr
           AND massn = 'S5'.

" club the entries
LOOP AT it_pa0000.
  LOOP AT it1_pa0000 WHERE pernr EQ it_pa0000-pernr.
    APPEND it_p0000 TO it1_pa0000.
  ENDLOOP.
ENDLOOP .