Forum Discussion

clarrivey's avatar
clarrivey
New Contributor
4 years ago

Using union in Execute SQL snap

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???

5 Replies

  • koryknick's avatar
    koryknick
    Employee
    4 years ago

    @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's avatar
      clarrivey
      New Contributor
      4 years ago

      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

    • clarrivey's avatar
      clarrivey
      New Contributor
      4 years ago

      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

  • koryknick's avatar
    koryknick
    Employee
    4 years ago

    @clarrivey - perhaps I’m missing the goal you are trying to achieve here. From your original post, it appears that you are trying to create a header record for your output with the data coming from the second query. If you are trying to write those results to a tab-delimited file, then the example I provided will work - just copy the query from the SQL Server Execute snap in my example to an Oracle Execute snap. This works because SnapLogic’s CSV Formatter can use the field names of the input document as the header record in your file, which is how I configured the example pipeline. Also notice that the CSV Formatter snap is setup to use the tab character (\t or \u009) as the Delimiter.