The forum will be down for maintenance at some point between Friday, September 25, 2020 at 5pm PDT and Sunday, September 27, 2020 at 11:59 PDT. Downtime is unknown but will be minimized.

Take a simple select that contains two LIST functions, inside each of which we use the COALESCE function.

select 
 "list"(if "coalesce"("row_num",0) > 5 then ' (p)' else '' endif, ',' order by "coalesce"("row_num",0) desc),
 "list"(if "coalesce"("row_num",0) >= 5 then ' (p)' else '' endif, ',' order by "coalesce"("row_num",0) desc) 
FROM sa_rowgenerator(1,10);
In 16.0.0.2546, this select is performed fine.
In 17.0.10.6057, this select causes an error:
Illegal ORDER BY in aggregate function
SQLCODE=-904, ODBC 3 State="42000"
There is a bug 17th version?

asked 27 Apr, 05:20

Ilia63's gravatar image

Ilia63
1.1k414970
accept rate: 44%

1

Funnily enough is does work with 17.0.10.6057 when using isnull instead of coalesce in the list aggregate (when using coalesce in the order by):

select 
 "list"(if "isnull"("row_num",0) > 5 then ' (p)' else '' endif, ',' order by "coalesce"("row_num",0) desc),
 "list"(if "isnull"("row_num",0) >= 5 then ' (p)' else '' endif, ',' order by "coalesce"("row_num",0) desc) 
FROM sa_rowgenerator(1,10);

or when using isnull only in the order by - but it also fails when replacing all 4 appearances of coalesce with isnull... So I guess the answer to your question is "yes".

(27 Apr, 05:54) Volker Barth
4

This appears to be a bug that was introduced between 17.0 Build 4935 (PL39) and 17.0 Build 5745 (PL41). It is being investigated as Engineering Case# 821576.

(29 Apr, 08:34) Chris Keating

This is solved in 17.0.0.6175, cf. the note on Engineering Case #821576 in the newest readme file:

    The server may incorrectly return the SQL code -904 "Illegal ORDER BY 
    in aggregate function" if a query contains two or more LIST functions 
    in the same query block, the LIST functions have the same or subsumed ORDER 
    BY clauses, and an order by expression is used in an IF expressions in the 
    first parameter of the LIST 
    function.

    For example: In the following query the expression "coalesce(a1,0)" 
    is in both LIST functions as part of the IF
    expressions and the ORDER BY.

    select 
    list( if coalesce(a1,0) < 1 then b1 else b1*(c1/a1) endif, ',' order 
    by coalesce(a1,0) desc ) as col1,
    list( if coalesce(a1,0) <= 1 then b1 else b1*(c1/a1) endif, ',' order 
    by coalesce(a1,0) desc ) as col2
    from T1

    This has been fixed.
permanent link

answered 18 Aug, 03:40

Volker%20Barth's gravatar image

Volker Barth
36.7k343505761
accept rate: 34%

edited 18 Aug, 03:46

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×173

question asked: 27 Apr, 05:20

question was seen: 211 times

last updated: 18 Aug, 03:46