dynamic select field

[Former Member]

Hello,

I have to perform a dynamic select statement with im_field variable.

select single max(im_field)....

I know that I have to use brackets in order to use variables inside the select statements, but what about in this case? I have a short dump with wrong fieldname.

I also tried select single max((im_field))...., but same result.

[Sandeep_Panghal]

Hi George,

The Aggregate functions work perfectly with numeric fields .

U can try this:

select single max( im_field ) ..

there is space before and after the varible in braces.

Hope this helps.

[Former Member]

hi George,

Check out this code

tables: rsrd1.
DATA: LineType TYPE string,
      ItabRef  TYPE REF TO DATA.
FIELD-SYMBOLS: <fs>  TYPE STANDARD TABLE.
 
parameter tbl like RSRD1-TBMA_VAL.
 
linetype = tbl.
CREATE DATA ItabRef TYPE STANDARD TABLE OF (LineType).
ASSIGN ItabRef->* to <fs> .
SELECT * FROM (tbl) INTO table <fs>.

Regards,

Santosh

[Former Member]

Hi George,

Try this...

DATA v_matnr LIKE marc-matnr.
DATA v_list(100).
v_list = 'MAX( MATNR )'.

SELECT (v_list)
  FROM marc
  INTO v_matnr.
  WRITE v_matnr.
ENDSELECT.

[Former Member]

Thank you for this tip!

[Former Member]

check this example to get an idea...

MAX( [DISTINCT] fdescriptor )

Addition:

... AS alias

Effect

Returns the largest value for the selected lines in the column identified by the Field descriptor fdescriptor.The DISTINCT specification does not alter the result. NULL values are ignored in the calculation, except when all of the values in a column are NULL. In this case, the result is NULL.

Example

Output a list of all customers on Lufthansa flight 0400 in 1995, along with the highest price paid, sorted by customer name:

TABLES: SCUSTOM, SBOOK.

SELECT SCUSTOM_{NAME SCUSTOM}POSTCODE SCUSTOM~CITY

MAX( SBOOK~LOCCURAM )

INTO (SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY,

SBOOK-LOCCURAM)

FROM SCUSTOM INNER JOIN SBOOK

ON SCUSTOM_{ID = SBOOK}CUSTOMID

WHERE SBOOK~FLDATE BETWEEN '19950101' AND '19951231' AND

SBOOK~CARRID = 'LH ' AND

SBOOK~CONNID = '0400'

GROUP BY SCUSTOM_{NAME SCUSTOM}POSTCODE SCUSTOM~CITY

ORDER BY SCUSTOM~NAME.

WRITE: / SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY,

SBOOK-LOCCURAM.

ENDSELECT.

Addition

... AS alias

Effect

As in variant 1. You can also use an alternative column name in variants 2 - 6 to sort the result by an aggregate expression. Unlike aggregate expressions, you can use alternative column names in the ORDER-BY clause.

Example

Output a list of all customers on Lufthansa flight 0400 in 1995, along with the highest price paid, sorted by price and customer name:

TABLES: SCUSTOM, SBOOK.

SELECT SCUSTOM_{NAME SCUSTOM}POSTCODE SCUSTOM~CITY

MAX( SBOOK~LOCCURAM ) AS MAX

INTO (SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY,

SBOOK-LOCCURAM)

FROM SCUSTOM INNER JOIN SBOOK

ON SCUSTOM_{ID = SBOOK}CUSTOMID

WHERE SBOOK~FLDATE BETWEEN '19950101' AND '19951231' AND

SBOOK~CARRID = 'LH ' AND

SBOOK~CONNID = '0400'

GROUP BY SCUSTOM_{NAME SCUSTOM}POSTCODE SCUSTOM~CITY

ORDER BY MAX DESCENDING SCUSTOM~NAME.

WRITE: / SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY,

SBOOK-LOCCURAM.

ENDSELECT.

Ramesh.