SQL Query Text Parsing Bug

[barreram1989]

Hey all, I'm very new to Alteryx and slowly sorting my way through some of its intricacies.  I've been able to figure out most of them through community boards and such, but I'm running in an issue that I was hoping someone here internally could help with.I'm running a workflow that's extracting page urls from a smartsheet, cleansing them then feeding them as a dynamic input into a datalake connector with a sql query that's pulling web traffic data for our QB blog. I'm testing a relatively simple query to get a clean URL (without the CIDs) and its respective traffic, but every time I try to parse the URL, with this query:

select split(split(split(first_page_url, '\\?') [0], '#' ) [0], '\\%' ) [0] as clean_page, Count(ivid) as Traffic_ivid from dlprd.schema.table where first_page_url Like '%page_list%' group by 1

I get the following error "Error: Designer x64: The Designer x64 reported: You have found a bug.  The pipe has been ended."This bug doesn't come up when I change the query to a just have the url like so:

select first_page_url, Count(ivid) as Traffic_ivid from dlprd.schema.table where first_page_url Like '%page_list%' group by 1

The issue is that without the cleaned up URL, the aggregations happen at the individual url + CID level.I'll def need to parse the urls for this workflow and others, so I need to figure out how to get it to run - does anyone have any ideas or suggestions? Attaching the workflow file for reference.

Thanks in advance! 

[apathetichell]

this is off - no?

select split(split(split(first_page_url, '\\?') [0], '#' ) [0], '\\%' ) [0] as clean_page,

Maybe select split(split(split(first_page_url, '\\?') [0], '#' ) [0], '\\%' )  as clean_page?

What sql syntax are you using?

[barreram1989]

Thanks for jumping in so quickly here, @apathetichell.

Unfortunately that wasn't the issue - I tried running it without the indexing at the end, but I still get the same error.  Additionally, the split function returns an array, and with the other splits we do, it always returns a 1x1 array, so that indexing at the end just extracts the url string from the array. 

I'm fairly certain we're using MySQL syntax

[apathetichell]

Are you using In-DB here - Alteryx doesn't have native support for array/struct types - and I'd recommend casting this to a string.

[barreram1989]

this was really helpful - thanks! 

it looked liked casting to a string worked for a sec, but now I'm getting the same error 'you've found a bug'.  Super frustrating as it's a useless error message.  Will prob have to go to Alteryx support for this.

[apathetichell]

hmmm... you might want to enable logs in your odbc driver - and then run and check the logs to see what you are sending. It's possible that value still isn't fitting into the type of values Alteryx can parse. Are you In-DB or on canvas? You can try swapping to the other.

[barreram1989 · Accepted solution]

@apathetichell looks like a regex_replace does the trick - you were right that it was the array issue. thanks for the help!

select regexp_replace(first_page_url, '(\?|#)(.*)', '') as clean_page, 
Count(ivid) as Traffic_ivid 
from schema.table 
where first_page_url Like 'page_list%' 
group by 1