sbornstein2
asked on
SQL Union Query simple question
Hello all,
I have the following Union query:
SELECT DS.SOURCE_ID, DSD.COLUMN_NAME, 'N' AS MAP_FLAG
FROM CDIP.SV_DATA_SOURCE DS INNER JOIN CDIP.SV_DATA_SOURCE_DETAIL
UNION SELECT SOURCE_ID, COLUMN_NAME, 'Y' AS MAP_FLAG FROM CDIP.SV_DEFAULT_SOURCE_MAP
What I am wanting to do is this:
There will be column names in the top part of the UNION that will exist also in the bottom CDIP.SV_DEFAULT_SOURCE_MAP
Hope this makes sense.
I have the following Union query:
SELECT DS.SOURCE_ID, DSD.COLUMN_NAME, 'N' AS MAP_FLAG
FROM CDIP.SV_DATA_SOURCE DS INNER JOIN CDIP.SV_DATA_SOURCE_DETAIL
UNION SELECT SOURCE_ID, COLUMN_NAME, 'Y' AS MAP_FLAG FROM CDIP.SV_DEFAULT_SOURCE_MAP
What I am wanting to do is this:
There will be column names in the top part of the UNION that will exist also in the bottom CDIP.SV_DEFAULT_SOURCE_MAP
Hope this makes sense.
SELECT
COALESCE(t1.source_id, t2.source_id) AS Source_Id,
COALESCE(t1.column_name, t2.column_name) AS Column_Name,
CASE WHEN t2.column_name IS NULL THEN 'N' ELSE 'Y' END AS Map_Flag
FROM (
SELECT DS.SOURCE_ID, DSD.COLUMN_NAME, 'N' AS MAP_FLAG
FROM CDIP.SV_DATA_SOURCE DS
INNER JOIN CDIP.SV_DATA_SOURCE_DETAIL
DS.ID = DSD.DATA_SOURCE_ID
WHERE
DSD.VC_FLAG = 0
) AS t1
FULL OUTER JOIN (
SELECT SOURCE_ID, COLUMN_NAME, 'Y' AS MAP_FLAG
FROM CDIP.SV_DEFAULT_SOURCE_MAP
) AS t2 ON
t1.source_id = t2.source_id AND
t1.column_name = t2.column_name
COALESCE(t1.source_id, t2.source_id) AS Source_Id,
COALESCE(t1.column_name, t2.column_name) AS Column_Name,
CASE WHEN t2.column_name IS NULL THEN 'N' ELSE 'Y' END AS Map_Flag
FROM (
SELECT DS.SOURCE_ID, DSD.COLUMN_NAME, 'N' AS MAP_FLAG
FROM CDIP.SV_DATA_SOURCE DS
INNER JOIN CDIP.SV_DATA_SOURCE_DETAIL
DS.ID = DSD.DATA_SOURCE_ID
WHERE
DSD.VC_FLAG = 0
) AS t1
FULL OUTER JOIN (
SELECT SOURCE_ID, COLUMN_NAME, 'Y' AS MAP_FLAG
FROM CDIP.SV_DEFAULT_SOURCE_MAP
) AS t2 ON
t1.source_id = t2.source_id AND
t1.column_name = t2.column_name
ASKER
this was perfect thanks
ASKER
I screwed up this actually did not work, it only returned a 'Y' flag for the record that existed in the DEFAULT MAPPING table that was not in the DETAIL table.
ASKER
Scott I may owe you some points :). Your solution is not working though its actually PL/SQL but is throwing the error;
SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
Error at Line: 50 Column: 2
SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
Error at Line: 50 Column: 2
Hmm, PL/SQL, been years since I did Oracle full time (Oracle 8 to be precise :-) ).
I think everything in that statement is OK for Oracle too, including CASE, which is now an ANSI standard.
I'm assuming you added the ";" at the end, always required for Ora.
I think everything in that statement is OK for Oracle too, including CASE, which is now an ANSI standard.
I'm assuming you added the ";" at the end, always required for Ora.
[I have to admit, I kinda wondered how the statement you selected would actually do what you stated you wanted ... :-). ]
ASKER
Ya I will figure it out maybe I will request attention for the question, my bad sorry I should have better checked before awarding.
Sorry, I don't even have any version of Ora to check it on.
Which version of Ora? I can check the syntax online.
Which version of Ora? I can check the syntax online.
ASKER
Looks like its throwing that error on this line:
) AS t1
like it's looking for another paren close or something ya the semi-colon is not the issue
) AS t1
like it's looking for another paren close or something ya the semi-colon is not the issue
OK, web search tells me that Ora does not the like "AS" in a derived table alias.
Well isn't that less clear :-) .
From what I can tell, everything else should be OK in Ora ( since I used the ANSI-compliant COALESCE() and not ISNULL() ):
So:
SELECT
COALESCE(t1.source_id, t2.source_id) AS Source_Id,
COALESCE(t1.column_name, t2.column_name) AS Column_Name,
CASE WHEN t2.column_name IS NULL THEN 'N' ELSE 'Y' END AS Map_Flag
FROM (
SELECT DS.SOURCE_ID, DSD.COLUMN_NAME, 'N' AS MAP_FLAG
FROM CDIP.SV_DATA_SOURCE DS
INNER JOIN CDIP.SV_DATA_SOURCE_DETAIL
DS.ID = DSD.DATA_SOURCE_ID
WHERE
DSD.VC_FLAG = 0
) t1
FULL OUTER JOIN (
SELECT SOURCE_ID, COLUMN_NAME, 'Y' AS MAP_FLAG
FROM CDIP.SV_DEFAULT_SOURCE_MAP
) t2 ON
t1.source_id = t2.source_id AND
t1.column_name = t2.column_name;
Well isn't that less clear :-) .
From what I can tell, everything else should be OK in Ora ( since I used the ANSI-compliant COALESCE() and not ISNULL() ):
So:
SELECT
COALESCE(t1.source_id, t2.source_id) AS Source_Id,
COALESCE(t1.column_name, t2.column_name) AS Column_Name,
CASE WHEN t2.column_name IS NULL THEN 'N' ELSE 'Y' END AS Map_Flag
FROM (
SELECT DS.SOURCE_ID, DSD.COLUMN_NAME, 'N' AS MAP_FLAG
FROM CDIP.SV_DATA_SOURCE DS
INNER JOIN CDIP.SV_DATA_SOURCE_DETAIL
DS.ID = DSD.DATA_SOURCE_ID
WHERE
DSD.VC_FLAG = 0
) t1
FULL OUTER JOIN (
SELECT SOURCE_ID, COLUMN_NAME, 'Y' AS MAP_FLAG
FROM CDIP.SV_DEFAULT_SOURCE_MAP
) t2 ON
t1.source_id = t2.source_id AND
t1.column_name = t2.column_name;
ASKER
That worked I just had to add a GROUP BY in the first part as there could be the same source and column name repeated actually. I am going to request attention for this question.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Scott looks like they reopened this for me. Sorry it took so long for me to reward it right. All set
SELECT DS.SOURCE_ID, DSD.COLUMN_NAME, 'N' AS MAP_FLAG
FROM CDIP.SV_DATA_SOURCE DS INNER JOIN CDIP.SV_DATA_SOURCE_DETAIL
UNION
SELECT DSM.SOURCE_ID, DSM.COLUMN_NAME, 'Y' AS MAP_FLAG FROM CDIP.SV_DEFAULT_SOURCE_MAP
WHERE NOT EXISTS (SELECT *
FROM CDIP.SV_DATA_SOURCE DS INNER JOIN CDIP.SV_DATA_SOURCE_DETAIL
WHERE DSM.SOURCE_ID = DS.SOURCE_ID
AND DSM.COLUMN_NAME = DSD.COLUMN_NAME)