Query Builder Listagg within issue

Toad for Oracle & DevOps Toolkit
1

I am trying to do view/create the following query in Query Builder and it seems to be dropping the within syntax

here is my query:

SELECT distinct my_KEY, LISTAGG (my_field, ‘|’)
WITHIN GROUP (ORDER BY my_field)
OVER (PARTITION BY My_KEY)
AS my_fields
FROM (select distinct tbl1.my_field, tbl2.KEY as assoc_KEY from tbl2
left join tbl1
on tbl1.my_KEY = tbl2.tbl_1_my_KEY);

Here is what Query Builder converts it to

SELECT distinct my_KEY, LISTAGG (my_field, '|') AS my_fields

FROM ( select distinct tbl1.my_field, tbl2.KEY as assoc_KEY from tbl2
left join tbl1
on tbl1.my_KEY = tbl2.tbl_1_my_KEY);

As you can see, it dropped my within group… and the Over Partition… parts, which won’t run in Query Builder.

I know this seems a bit simple, but this is part of a much larger complex query that I am trying to model out. My Toad version is 12.12.0.39

2

Hi Paul,

Thanks for letting us know about this. Normally, I’d suggest giving it a shot in the next beta, but we’re past our beta cycle for this release. However, this issue has been fixed for the next GA version of Toad (13.1), which will be released here within the next couple months. Feel free to give it a shot when it’s released and see if that fixes your issue.

And if you decide to upgrade to Toad 13.1, feel free to also download the beta and join our beta forum. That way, if you have any suggestions for improvements, or find anything else during your normal work process, you can let us know and/or test out new features and keep up-to-date with the current development cycle.

Thanks again!

-John

3

John, thanks for the info, I may pull down the 13.1 beta