topic Re: dynamic select field in Application Development and Automation Discussions

dynamic select field

Former Member — Thu, 14 Dec 2006 08:23:30 GMT

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.

Re: dynamic select field

Sandeep_Panghal — Thu, 14 Dec 2006 08:43:58 GMT

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.

Re: dynamic select field

Former Member — Thu, 14 Dec 2006 08:46:59 GMT

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

Re: dynamic select field

Former Member — Thu, 14 Dec 2006 08:47:53 GMT

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.

Re: dynamic select field

Former Member — Thu, 14 Dec 2006 08:56:45 GMT

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.

Re: dynamic select field

Former Member — Thu, 14 Dec 2006 10:08:25 GMT

Thank you for this tip!