SWQL function ARRAYVALUEAT / SPLITSTRINGTOARRAY working in SWQL Studio, but fail in web based reporting

equalswql

Hello Thwack Team,

For the below working query - which works in custom swql dashboard resources and in swql studio,

SELECT TOP 1 ARRAYVALUEAT(SPLITSTRINGTOARRAY(Replace('Part1-Part2-Part3-Part4','-','|§|§|')),0) as Field1,
ARRAYVALUEAT(SPLITSTRINGTOARRAY(Replace('Part1-Part2-Part3-Part4','-','|§|§|')),1) as Field2,
CONCAT(
ARRAYVALUEAT(SPLITSTRINGTOARRAY(Replace('Part1-Part2-Part3-Part4','-','|§|§|')),0),
'-',
ARRAYVALUEAT(SPLITSTRINGTOARRAY(Replace('Part1-Part2-Part3-Part4','-','|§|§|')),1)
) as Concatenated
FROM Orion.Nodes n
WHERE n.Caption LIKE '%-%-%-%'

it fails in web based reporting. I'm not sure if this is due to some caviat (for instance with UNION ALL in certain cases, ORDER BY needs to be removed or added to get the query to work properly), or some other unknown.

Result:

The ability to split strings based on specific characters is extremely useful and is shorter than other queries using different functions, so the ability to use this in reporting is a loss at the moment.

Thank you,

= swql

brscott

This may be related.  You also cannot do a ArrayValueAt in a subselect

The error is: "Attempting to access an element as a type incompatible with the array", if that is any help.

Like:

select q.Field1, q.Field2, q.Concatenated from (
--    SELECT TOP 1 
        ARRAYVALUEAT(SPLITSTRINGTOARRAY(Replace('Part1-Part2-Part3-Part4','-','|§|§|')),0) as Field1,
        ARRAYVALUEAT(SPLITSTRINGTOARRAY(Replace('Part1-Part2-Part3-Part4','-','|§|§|')),1) as Field2,
        CONCAT(
            ARRAYVALUEAT(SPLITSTRINGTOARRAY(Replace('Part1-Part2-Part3-Part4','-','|§|§|')),0),
            '-',
            ARRAYVALUEAT(SPLITSTRINGTOARRAY(Replace('Part1-Part2-Part3-Part4','-','|§|§|')),1)
            ) as Concatenated
    FROM Orion.Nodes n
    WHERE n.Caption LIKE '%-%-%-%'
) q

NOTE: Seems I cannot even post a subselect like this.  I had to add the "--" comment before it would allow me to save it.  So remove that if you want to try it.