-
1. Re: I need to use two separate (not nested) "UNION ALL" SQL statements in order to import my data correctly in Tableau. How do I do this? I tried using a semicolon to separate the two statements but just got an error.
Cherie,
You shouldn't need a semi-colon. It would look something like this:
SELECT field1, field2, field 3
FROM table1
WHERE field2 >= 5
UNION ALL
SELECT field1, field2, field3
FROM table2
WHERE field1 = 'value'
What error are you getting?
-Joshua
-
2. Re: I need to use two separate (not nested) "UNION ALL" SQL statements in order to import my data correctly in Tableau. How do I do this? I tried using a semicolon to separate the two statements but just got an error.
Thanks for your response! So my first UNION ALL statement works just fine. However, the way my data is set up requires two separate SQL statements using UNION ALL that need to then be combined using UNION ALL. I just don't know how to combine the two statements using UNION ALL in a way that works. So the two statements look like this and I need to combine them:
First SQL statement:
SELECT [Sheet5$].[Beg MP] AS [Beginning MP],
[Sheet5$].[End MP] AS [End MP],
[Sheet5$].[CCI] AS [CCI],
[Sheet5$].[Point ID] AS [Point ID],
1 AS [Begin or End],
[Sheet5$].[Beg MP] AS [Milepoint]
FROM[Sheet5$]
UNION ALL
SELECT [Sheet5$].[Beg MP] AS [Beginning MP],
[Sheet5$].[End MP] AS [End MP],
[Sheet5$].[CCI] AS [CCI],
[Sheet5$].[Point ID] AS [Point ID],
2 AS [Begin or End],
[Sheet5$].[End MP] AS [Milepoint]
FROM[Sheet5$]
Second SQL statement:
SELECT [Sheet6$].[Begin] AS [Begin],
[Sheet6$].[Crash_VMT] AS [Crash_VMT],
[Sheet6$].[End] AS [End],
[Sheet6$].[Point_ID] AS [Point_ID],
1 AS [Begin or End],
[Sheet6$].[Begin] AS [Milepoint]
FROM [Sheet6$]
UNION ALL
SELECT [Sheet6$].[Begin] AS [Begin],
[Sheet6$].[Crash_VMT] AS [Crash_VMT],
[Sheet6$].[End] AS [End],
[Sheet6$].[Point_ID] AS [Point_ID],
2 AS [Begin or End],
[Sheet6$].[End] AS [Milepoint]
FROM [Sheet6$]
-
3. Re: I need to use two separate (not nested) "UNION ALL" SQL statements in order to import my data correctly in Tableau. How do I do this? I tried using a semicolon to separate the two statements but just got an error.
So the trick with a UNION statement is that all the aliases need to be the same and all the types need to be the same between the statements. You'e done that within the two statements, but it doesn't look like they match between the two. For example, the alias Beginning MP is not the same as Begin. I don't know if the type is the same or if the meaning is the same.
I'd probably need to see the data to understand exactly what you need, but here's an example as a guess:
SELECT field1 as field1, field2 as field2, field3 as field 3, CAST(NULL as INT) as field4
FROM
(
SELECT field1, field2, field 3
FROM table1
WHERE field2 >= 5
UNION ALL
SELECT field1, field2, field3
FROM table2
WHERE field1 = 'value'
) s1
UNION ALL
SELECT f1 as field1, f2 as field2, CAST(NULL as VARCHAR(30)) as field3, f4 as field4
FROM
(
SELECT f1, f2, f3
FROM table3
UNION ALL
SELECT f1, f2, f3
FROM table4
) s2
So, it's selecting from two sub-selects and then union those. Also, note that field3 is note applicable in the 2nd query and field4 is not applicable in the first. So, I used NULLs and explicitly cast them to match types between the two queries.
But, I'm just guessing, really. Are you sure you want to union? Is it the same type of data, just spread over multiple sheets? Or do you possibly want to have 2 data sources and use them both in Tableau? In that case, you could have 2 connections using the 2 queries separately.
Regards,
Joshua
-
4. Re: I need to use two separate (not nested) "UNION ALL" SQL statements in order to import my data correctly in Tableau. How do I do this? I tried using a semicolon to separate the two statements but just got an error.
Thank you!! That was super helpful.
I just have one more question, if you don't mind. You seem very knowledgeable about SQL, so I was wondering if you could tell me if it is possible to UNION two data sets together on only one field while leaving the remaining fields intact. For example, if I wanted to join the following two data sets together by "Mile" but leave the fields "Pavement" and "Bridge" intact as two separate fields in the final data set, how would I do it?
Data set 1:
Mile: 12, 15, 19
Pavement: 55, 52, 59
Data set 2:
Mile: 10, 6, 20
Bridge: 31, 35, 33
Desired Combined Data Set:
Mile: 12, 15, 19, 10, 6, 20
Pavement: 55, 52, 59
Bridge: 31, 35, 33
P.S. I've already tried joins on my data and they don't join my data in the way I need (because the Miles in the two data sets don't match up).
-
5. Re: I need to use two separate (not nested) "UNION ALL" SQL statements in order to import my data correctly in Tableau. How do I do this? I tried using a semicolon to separate the two statements but just got an error.
Cherie,
I feel like I should probably take a step back and understand what the data is. Are the numbers the measure of miles for Pavement and Bridge? What do the numbers for Mile mean? That might change my thinking.
But, to get the desired combined set above, I would do something like:
SELECT Mile, Bridge, CAST(NULL as INT) AS Pavement
FROM Table_With_Bridge
UNION ALL
SELECT Mile CAST(NULL as INT) as Bridge, Pavement
FROM Table_With_Pavement
Regards,
Joshua