topic Re: In-DB warning using ORDER BY window function SQL in Alteryx Designer Desktop Discussions

In-DB warning using ORDER BY window function SQL

nakamott — Mon, 24 Jun 2019 23:09:36 GMT

I am receiving the warning "Using order by in initial In-DB query may result in a query error. Consider using the sort on the sample or stream out tools."

I do not find much information on this error. Was wondering if it also applies to the ORDER BY used in window functions and what the error will be?

Re: In-DB warning using ORDER BY window function SQL

AndrewBanh — Tue, 25 Jun 2019 04:08:38 GMT

Hi @nakamott

There are quite a few SQL queries that don't work in Alteryx, just as most Alteryx-formed SQL queries don't work in SSMS. Order by is one of said queries. I have only begun using In-DB tools as well 😊

If you are looking to sort your data, you'd have to configure your Sample In-DB like shown below. You want to change the top left box to Percent and type in 100 and tick the "Sample records based on order:" box.

Otherwise you'd have to stream your data out and use the normal Alteryx sort tool.

I have noticed that sometimes the Sample In-DB doesn't work as intended for me but the majority of the time it does. Haven't been able to work it out yet though.

Hope this helps mate.

- Andrew

Re: In-DB warning using ORDER BY window function SQL

Raj_007 — Tue, 19 Sep 2023 21:53:42 GMT

Hi,

I have few queries that has window functions like rank over parition by ,,,, and order by

when I try to run the query in indb connection i am getting an errror

This post is an old one but just wanted to confirm if it is still an iissue

Re: In-DB warning using ORDER BY window function SQL

apathetichell — Wed, 20 Sep 2023 02:37:30 GMT

tbh - order by usually works. It really depends upon your back end DB. Alteryx is basically saying use Order By as a sort when you are using datastream out.

Re: In-DB warning using ORDER BY window function SQL

Raj_007 — Wed, 20 Sep 2023 13:23:44 GMT

Hi, thank you so much - the way i have the queries are like derived tables

source is Oracle DB

WITH Test AS (

select from Test

something like this - the query structure is soemthing like this but too many queries - within that derived table or CTE i have the rank over partition by field and order by some field

so this is where i have the order by... so iam confused on how to get this as it is part of the query - I dont need any sort once i get the main dataset

Re: In-DB warning using ORDER BY window function SQL

apathetichell — Wed, 20 Sep 2023 13:59:55 GMT

You need to use subqueries vs CTE - WITH does not work In-DB because Alteryx wraps your entire statement in WITH command.

Re: In-DB warning using ORDER BY window function SQL

Raj_007 — Wed, 20 Sep 2023 14:29:14 GMT

Thank you so much - really helpful

Re: In-DB warning using ORDER BY window function SQL

simonaubert_bd — Fri, 29 Sep 2023 04:28:55 GMT

Hello @nakamott The warning is very conservative and even happen when you're doing rank with a windowing function directly in the connect in-db tool. But it's just that : a warning and not an error message. I have never encountered a case when it leads to an error.

Best regards,

Simon