topic Re: SELECT POOLED TABLE IN SUBQUERY in Application Development and Automation Discussions

SELECT POOLED TABLE IN SUBQUERY

Former Member — Fri, 22 Jul 2005 02:02:02 GMT

Hi all,

how to select pooled table in subquery?

e.g. Pooled table: A017

SELECT * FROM A017

INTO CORRESPONDING FIELDS OF TABLE itab

WHERE exists ( SELECT * FROM EINA WHERE MATNR = A017~MATNR ).

it gives a syntax error and runtime error when I use A017-MATNR.

Re: SELECT POOLED TABLE IN SUBQUERY

jayanthi_jayaraman — Fri, 22 Jul 2005 03:44:30 GMT

Hi Macy Lo,

Try this.This compares the Matnr in A017 with EINA.

SELECT * FROM A017

INTO CORRESPONDING FIELDS OF TABLE itab

WHERE matnr in ( SELECT matnr FROM EINA ).

Hope this helps.

Re: SELECT POOLED TABLE IN SUBQUERY

Former Member — Fri, 22 Jul 2005 03:48:20 GMT

Hi,

If u want to use exists then u can try this out

DATA: WA_SFLIGHT TYPE SFLIGHT.

SELECT * FROM SFLIGHT AS F INTO WA_SFLIGHT

WHERE SEATSOCC < F~SEATSMAX

AND EXISTS ( SELECT * FROM SPFLI

WHERE CARRID = F~CARRID

AND CONNID = F~CONNID

AND CITYFROM = 'FRANKFURT'

AND CITYTO = 'NEW YORK' )

AND FLDATE BETWEEN '19990101' AND '19990331'.

WRITE: / WA_SFLIGHT-CARRID, WA_SFLIGHT-CONNID,

WA_SFLIGHT-FLDATE.

ENDSELECT.

Or u can check this sample code too

DATA: WA TYPE SFLIGHT.

SELECT * FROM SFLIGHT

INTO WA

WHERE SEATSOCC = ( SELECT MAX( SEATSOCC ) FROM SFLIGHT ).

WRITE: / WA-CARRID, WA-CONNID, WA-FLDATE.

ENDSELECT.

Hope this helps.

Thanks & Regards,

Judith.

Re: SELECT POOLED TABLE IN SUBQUERY

Former Member — Fri, 22 Jul 2005 04:19:18 GMT

Hi Macy,

Seems like it is not possible to use these subqueries in pooled tables. There'a the SAP note 151711 which talk about it for projection views, but I think that is applicable to pooled tables as well.

You might have to use alternative logic to meet your requirement - for example, consider using FOR ALL ENTRIES.

Regards,

Anand Mandalika.

Re: SELECT POOLED TABLE IN SUBQUERY

Former Member — Fri, 22 Jul 2005 04:21:05 GMT

Hi Jayanthi,

Have you executed this SQL atatement ? I see that it doesn't return me any values even when there are common MATNRs between EINA and A017.

Regards,

Anand Mandalika.

Re: SELECT POOLED TABLE IN SUBQUERY

Former Member — Fri, 22 Jul 2005 04:22:10 GMT

Hi Judith,

If you have observed the question carefully, the problem is when you use Pooled tables in the SQL statement. For normal transparent tables there's no problem.

Regards,

Anand Mandalika.