Appropriate select querry needed

[Former Member]

Hi Experts,

I need appropriate select query for the below :

SELECT  PLNNR
             PLNKN
             ARBID
             WERKS
             BMSCH
             VGE02
             VGW02 FROM PLPO
       INTO TABLE IT_PLPO
      FOR ALL ENTRIES IN IT_CRHD
      WHERE WERKS EQ IT_CRHD-WERKS    AND ARBID EQ IT_CRHD-OBJID     
         AND PLNTY EQ 'N'.

Please advice a appropriate select querry for the above as,

If use the above querry it will fetch all 50-70 records for a single wa_crhd-objid

but I need only a single line where plpo~vgw02 is maximum.

ie: my where conditon should satisfy max ( vgw02 ) from PLPO.

Regds,

R.Karthik

[Former Member]

Hi,

Check with this one....

SELECT  PLNNR
             PLNKN
             ARBID
             WERKS
             BMSCH
             VGE02
             max(VGW02)
       FROM PLPO
       INTO TABLE IT_PLPO
     up to 1 rows
      FOR ALL ENTRIES IN IT_CRHD
      WHERE WERKS EQ IT_CRHD-WERKS    AND ARBID EQ IT_CRHD-OBJID     
         AND PLNTY EQ 'N'.

Thanks,

Sid

[GauthamV]

Try this.

SELECT  Single PLNNR
             PLNKN
             ARBID
             WERKS
             BMSCH
             VGE02 
      max(VGW02)
       FROM PLPO
       INTO TABLE IT_PLPO
      FOR ALL ENTRIES IN IT_CRHD
      WHERE WERKS EQ IT_CRHD-WERKS    AND ARBID EQ IT_CRHD-OBJID     
         AND PLNTY EQ 'N'.

[Former Member]

Hi Gautham,

I tried the above logic already but I am getting error message

the addition FOR ALL ENTRIES excludes all aggregate functions with the exception
of COUNT(*) as the single element of the SELECT clause

Even I tried by using

loop at it_crhd into wa_crhd.
 SELECT  PLNNR
            PLNKN
            ARBID
            WERKS
            BMSCH
            VGE02
       MAX( VGW02 )
      FROM PLPO
      INTO CORRESPONDING FIELDS OF TABLE IT_PLPO
WHERE WERKS EQ WA_CRHD-WERKS AND ARBID EQ WA_CRHD-OBJID
        AND PLNTY EQ 'N'.

append wa_plpo into it_plpo.
endloop.

Regds

Karthik

[SuhaSaha]

>

> the addition FOR ALL ENTRIES excludes all aggregate functions with the exception
> of COUNT(*) as the single element of the SELECT clause
>

Hello Karthick,

I have already pointed it out MAX will not work with FAE. Please take the pain to read the SAP documentation.

AFAIK you have no shortcut technique, you gotto select all records & then fetch the MAX value.

BR,

Suhas

[Former Member]

HI Suhas,

Thanx for your valueble advice

Rgds

Karthik

[SuhaSaha]

Hello Karthick,

Unfortunately aggregate functions (with exception to COUNT * ) are not supported with FOR ALL ENTRIES.

SAP documentation says:

If the addition FOR ALL ENTRIES is used in front of WHERE, or if cluster or pool tables are listed after FROM, no other aggregate expressions apart from COUNT( * ) can be used. 

Also:

The addition FOR ALL ENTRIES is only possible before WHERE conditions of the SELECT statement. 

You have to select all the records, then sort based on the column you want & read the record. I donot think there is any alternative to this

BR,

Suhas

Edited by: Suhas Saha on Jan 8, 2010 12:28 PM