Using union in Execute SQL snap

[clarrivey]

I have an Execute SQL where I wan to add column names to my output file before adding the data. For example:
select ‘Last_Name’||chr(09)||‘First_Name’ from sys.dual
union
select rtrim(spriden_last_name) from spriden||chr(09)||select rtrim(spriden_first_name) from spriden
If I do this, I get an output of:
Alastname Afirstname
Blastname Bfirstname
.
.
.
Last_Name First_Name
.
.
.
Zlastname Zfirstname
when in fact, what I want is
Last_Name First_Name
Alastname Afirstname
Blastname Bfirstname
.
.
.
Zlastname Zfirstname

Anyone have a clue as to how to get this???

[koryknick]

@clarrivey - most databases have “union” and “union all”. “Union” will typically merge the results by removing any duplicates between the results sets, which is probably why they are being sorted prior to output. “Union all” generally returns all results as they are returned from each result set.

With that said, you can try “union all” and may see the results you are expecting. However, I will caution that the “header” still has a chance that it will not be returned as the first record if the database performs the separate queries concurrently.

Just from a design perspective, I’m curious why you are trying to manufacture the header record separately. Why not provide an alias name for the return values and use a CSV Formatter to generate the header row in the output?

[clarrivey]

Hi Kory,

Thanks, the union all worked. As for the design perspective, I am not sure I understand how to do that. Given the example I supplied, would I break that into 2 separate SQL Execute statements with the first being select ‘Last_Name’||chr(09)||‘First_name’ alias_name from sys.dual and the second being select rtrim(spriden_last_name) from spriden||chr(09)||select rtrim(spriden_first_name) from spriden and the just having the second one as input into the CSV Formatter and using the alias_name and clicking the show header box? If that is not what you meant, could you please further elaborate?

Thanks,

Chris

[koryknick]

Here is a sample pipeline to show what I am trying to describe. This is using a SQL Server Execute snap - I don’t believe you specified which database you are using.
Community Post 12674_2022_06_10.slp (5.3 KB)

[clarrivey]

Hi Rory,

I’m using Oracle, but I could not get it to work because it is basically creating a single record in the second query.

Chris