SAP SQL Anywhere

Question

When using the wonderful LIST aggregate with a rather complex expression, how can I specify the order within the list?

I want to have the list ordered by the complex expression itself but would not want to repeat the whole term.

Something like

select list(expr1 || ' ' || expr2 || ' ' || expr3 || ' ' || expr4, ', ' order by 1)

would be sufficient, but unfortunately, order-by ordinal (here: 1) is not allowed for list. Neither is it allowed to give the complex expression an alias and use that as order-by expression. As to the docs, I seem to be allowed to use an integral variable ("order by MyInt") but as I'm using a view, that possibility seems to fail, too. Repeating the whole expression does work, but I would like to avoid that.

Any hints for a "shorter syntax" are highly appreciated:) - I'm using SA 11.0.1.2427.

EDIT:

If no such possibility exists, I would suggest to add that in future version - cf. Breck's answer.

Accepted Answer

I vote "yes" for an "ORDER BY SELF" enhancement, or ORDER BY 1, or simply ORDERED.

The examples in the Help, such as SELECT LIST( EmployeeID ORDER BY Surname ), simply do not exist in the real world... I cannot imagine ever needing to see '1013,191,750,921,868,1658,...'

Yes, I know what the Help is trying to show, and yes, I often use ORDER BY other-columns-not-in-the-list-expression, but in the simple cases ORDER BY SELF is what is needed: SELECT LIST( EmployeeID ORDER BY EmployeeID )

http://dcx.sybase.com/index.html#1200en/dbreference/list-function.html

The Help DOES seem to imply that SELECT LIST( Street ) FROM Employees without an ORDER BY is implicitly ordered by Street: '487 Kennedy Court, 547 School Street' which is in fact just an accident.

Answer 2

You could wrap the complex expression into a derived table.

A quick little example with the demo database:

SELECT list(emp_record ORDER BY emp_record) 
FROM (SELECT '(' || EmployeeID || ') ' || GivenName || ' ' || SurName as emp_record FROM Employees) AS a_table;

Edit: This could also be made into a function of its own...

CREATE OR REPLACE FUNCTION fn_list(expr VARCHAR(1000),delimiter VARCHAR(10), source
VARCHAR(255), sortorder VARCHAR(4))
RETURNS LONG VARCHAR
BEGIN
DECLARE ret LONG VARCHAR;
EXECUTE IMMEDIATE 
    'SELECT list(expr' || 
        IF (delimiter <> '') THEN ', ''' || delimiter || '''' ELSE '' END IF || 
        IF (sortorder <> '') THEN ' ORDER BY expr ' || sortorder ELSE '' END IF || 
    ') into ret FROM (' || '
        SELECT ' || expr || ' as expr FROM ' || source || 
            IF (sortorder <> '') THEN ' ORDER BY expr ' || sortorder ELSE '' END IF || 
        ') as a_tab';
RETURN ret;
END;

Resulting in a fairly friendly syntax:

SELECT fn_list('GivenName || '' '' || SurName',', ','Employees','ASC');

And depending on when you expect this list to change, you could make the function deterministic, so the result may end up being cached.

SAP SQL Anywhere

How to use LIST with a complex expression and ORDER the list with that expression?

Follow this question

Related questions