topic Appropriate select querry needed in Application Development and Automation Discussions

Appropriate select querry needed

Former Member — Fri, 08 Jan 2010 06:47:52 GMT

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

Re: Appropriate select querry needed

Former Member — Fri, 08 Jan 2010 06:53:56 GMT

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

Re: Appropriate select querry needed

GauthamV — Fri, 08 Jan 2010 06:55:34 GMT

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'.

Re: Appropriate select querry needed

SuhaSaha — Fri, 08 Jan 2010 06:56:12 GMT

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

Re: Appropriate select querry needed

Former Member — Fri, 08 Jan 2010 07:06:56 GMT

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

Re: Appropriate select querry needed

SuhaSaha — Fri, 08 Jan 2010 07:21:47 GMT


> 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

Re: Appropriate select querry needed

Former Member — Fri, 08 Jan 2010 07:35:11 GMT

HI Suhas,

Thanx for your valueble advice

Rgds

Karthik