Unable to use "<" for inDB Query to Microsoft SQL Database

[KristenB]

I'm hitting a wall trying to use < inDB, and I'm wondering if this is a "bug"/known issue for this set of Alteryx tools/database connections. Normally, I get around this issue by keeping my Connect InDB/Dynamic Input InDB queries simple and using InDB filters to narrow down my data before pulling it out of the database and continuing to manipulate it.

In this case, I am needing to input a dynamic date (numerical field) and filtering it for value less than or equal to the dynamic date. I can't currently think of any work arounds to avoid the issue.

Both = and > work without issue, it is only < and <= that cause the error to pop up.

It appears to be an issue with Alteryx reading the xml and potentially not escaping it properly? But since my SQL server won't accept "&lt;", I can't try manually escaping it to see if it will work. Does anyone have suggestions or work arounds?

Alteryx Version: 2023.1.1.361 Patch: 6

Database: Microsoft SQL Server

Here's an example query:

The error:

"Error: Connect In-DB (11): Error parsing xml: Message=expected =, Location=</Query>
</FormatSpecificOpti..."

The xml view showing the escaped character which should be correct?

[alexnajm]

Can you connect to actual tables and see if this error persists?

[KristenB]

Yes. Actual tables are where the issue started, and then I stuck in the generic example for the post. When I played with it, it got angry at the semicolon once I'd switched the "<" to a ">", and after I'd removed the semicolon was when it started getting angry because there's not a "table_name" in my database connection. :)

It does look like the error is slightly different with my data, but I believe it amounts to the same thing:

Here is the exact same query as above but with an "=" and no error:

[alexnajm]

I had the same thought about the semi colon lol, that was going to be my next suggestion!

Can you try doing just the "select columns from table" in the connect In-DB tool, then replicate the where clause in a filter In-DB tool afterwards?

[DataNath]

Hey @KristenB, when you tried manually escaping, did you have the escape in quotes i.e. exactly "&lt;"? I came across the same issue when querying Snowflake whilst building this macro: https://community.alteryx.com/t5/Community-Gallery/SQL-Join/ta-p/1204659

And managed to get around it by using a query like “A” &lt;= “B” (without quotes around the escaped section), as shown in this supplementary info.

[KristenB · Accepted solution]

@alexnajm : I'm bringing in the query with a dynamic input inDB, since I want to be able to schedule it to run on the Gallery each month with a specific, dynamic date. Normally the answer would be yes (and I believe that's why I haven't had an issue before), but I'm not sure how to get a dynamic input into a downstream filter tool.

@DataNath : Yes, I tried "column b &lt;= 20241231", no quotes anywhere. This was something I tried after seeing how it was translated to the xml, not because of existing coding knowledge. I think the issue here is that the query is read literally to the SQL database, but Alteryx is seeing the first "<" as the start to the final "</query>" and I don't know how to bypass each issue.

It occurred to me last night (trying to fall asleep, haha) that I could pick an arbitrary start date (say, a month before my dynamic date) and use a "between 20241130 and 20241231". That should capture all the data I want and circumvent the "<" issue. There's the slim possibility with my data set that this may not catch everything, but at that point there's something very wrong with the old, missing data that it should be throwing up red flags in other places. :)