August 18, 2014 at 9:04 am
I was wondering if there was a more simple way to accomplish the following. Currently I have a standard query with a join to several tables. There are two additional tables MAS_CTB and MAS_STB. I would like to do a union between those two tables to get FIELDVALUE which will exist in either the CTB table or the STB table and then have that value be returned with the results of the original query.
I can of course write a UNION from the main query to the CTB and then to the STB table, however it's about 80 dummy fields I would have to replicate in the union which is why I was wondering if there was a more simple way. Thanks!
Main Query:
SELECT Field1, Field2...Field80
From Table1
Join Table2
Union Portion:
Select FieldValue <------
From MAS_CTB
UNION
Select FieldValue <-----Return to main query above
From MAS_STB
August 18, 2014 at 11:12 am
I guess this is "simpler":
WITH addedFieldValue
AS (
SELECT
FieldValue
FROM
MAS_CTB
UNION
SELECT
FieldValue
FROM
MAS_STB
)
SELECT
*
FROM
original_query
UNION
SELECT
*,
[additional null columns to match original query list]
FROM
addedFieldValue;
It does seem odd to me that you'd want to add this as rows to a result set and not as an additional column on the existing rows.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 18, 2014 at 2:15 pm
Thanks for your response! Actually I would just like the field to be added as a column but was thinking that the union could combine the data from the other two tables for me. Am I incorrect in my logic there?
August 18, 2014 at 2:34 pm
mnovosel (8/18/2014)
Thanks for your response! Actually I would just like the field to be added as a column but was thinking that the union could combine the data from the other two tables for me. Am I incorrect in my logic there?
Not sure exactly what you mean by "was thinking that the union could combine the data from the other two tables for me.". Yes you could union the MAS_CTB and MAS_STB tables, but you'd need to include the columns you would use to JOIN back to the main tables. So something like this:
WITH addedFieldValue
AS (
SELECT
FieldValue,
JoinColumn(s)
FROM
MAS_CTB
UNION
SELECT
FieldValue,
JoinColumn(s)
FROM
MAS_STB
)
SELECT
OQ.*,
AFV.FieldValue
FROM
original_query AS OQ JOIN
addedFieldValue AS AFV ON
OQ.JoinColumn(s) = AFV.JoinColumn(s)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply