Just curious - is it possible to union two normal SQL queries (from separate databases/resources) into one table? It appears the way to do it is with Query JSON with SQL, but I am getting blank rows for what is being unioned. For example:
select *, '1' as [Server] from {{ formatDataAsArray(GetJobStatus01.data )}}
UNION ALL
select *, '2' as [Server] from {{ formatDataAsArray(GetJobStatus02.data )}}
This query will show the correct data for the query "above" the UNION ALL, but the data below the UNION ALL will be blank (with the exception of hard-coded values like Server in this example). It doesn't matter which query goes on top, it will pull the correct data.
Hey @RCarlson32 and welcome to the forum! You are on the right track - I’d definitely suggest using a Query JSON with SQL query here (or a transformer, but this is easier). The library that we use here can be a bit confusing documentation-wise, and is very dependent on the structure of the original data that you’re pulling in.
Strangely enough, I’ve tried to reproduce this locally and with my own datasets your query works fine. Is it possible that the fact that you’re aliasing them with the same name ([Server]) the issue?
This is, indeed an issue with AlaSQL, the underlying library we use mentioned in @justin's post. It can be resolved using the CORRESPONDING keyword. In the case of the example given in the original post that would look like
select *, '1' as [Server] from {{ formatDataAsArray(GetJobStatus01.data )}}
UNION ALL CORRESPONDING
select *, '2' as [Server] from {{ formatDataAsArray(GetJobStatus02.data )}}