Solved

SQL Union Query simple question

Posted on 2012-03-21
15
305 Views
Last Modified: 2012-04-06
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 DSD ON DS.ID = DSD.DATA_SOURCE_ID WHERE DSD.VC_FLAG = 0  
          UNION SELECT SOURCE_ID, COLUMN_NAME, 'Y' AS MAP_FLAG FROM CDIP.SV_DEFAULT_SOURCE_MAPPING

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_MAPPING table.  If there is a record in the CDIP.SV_DEFAULT_SOURCE_MAPPING table then I want to have a flag that says MAP_FLAG = 'Y" to signify I have a mapped record otherwise if it only exists in the top part have it be 'N' but right now of course based on if it exists in both the UNION will create 2 records for each.   There will be other records with different column names in the bottom part so those I want again flagged as 'Y' for the MAP_FLAG.

Hope this makes sense.
0
Comment
Question by:sbornstein2
  • 7
  • 6
15 Comments
 
LVL 11

Expert Comment

by:dougaug
ID: 37747909
See if this works:

SELECT DS.SOURCE_ID, DSD.COLUMN_NAME, 'N' AS MAP_FLAG
     FROM CDIP.SV_DATA_SOURCE DS INNER JOIN CDIP.SV_DATA_SOURCE_DETAIL DSD ON DS.ID = DSD.DATA_SOURCE_ID WHERE DSD.VC_FLAG = 0  
UNION
SELECT DSM.SOURCE_ID, DSM.COLUMN_NAME, 'Y' AS MAP_FLAG FROM CDIP.SV_DEFAULT_SOURCE_MAPPING DSM
 WHERE NOT EXISTS (SELECT *
                     FROM CDIP.SV_DATA_SOURCE DS INNER JOIN CDIP.SV_DATA_SOURCE_DETAIL DSD ON DS.ID = DSD.DATA_SOURCE_ID WHERE DSD.VC_FLAG = 0
                    WHERE DSM.SOURCE_ID = DS.SOURCE_ID
                      AND DSM.COLUMN_NAME = DSD.COLUMN_NAME)
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37747925
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 DSD ON
        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_MAPPING
) AS t2 ON
    t1.source_id = t2.source_id AND
    t1.column_name = t2.column_name
0
 

Author Comment

by:sbornstein2
ID: 37747961
this was perfect thanks
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:sbornstein2
ID: 37748040
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.
0
 

Author Comment

by:sbornstein2
ID: 37748047
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
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37748083
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.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37748093
[I have to admit, I kinda wondered how the statement you selected would actually do what you stated you wanted ... :-). ]
0
 

Author Comment

by:sbornstein2
ID: 37748099
Ya I will figure it out maybe I will request attention for the question, my bad sorry I should have better checked before awarding.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37748101
Sorry, I don't even have any version of Ora to check it on.

Which version of Ora?  I can check the syntax online.
0
 

Author Comment

by:sbornstein2
ID: 37748110
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
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37748161
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 DSD ON
        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_MAPPING
) t2 ON
    t1.source_id = t2.source_id AND
    t1.column_name = t2.column_name;
0
 

Author Comment

by:sbornstein2
ID: 37748296
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.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 150 total points
ID: 37748434
>> 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.  <<

That will work.  Technically DISTINCT will usu perform better just to remove dups:

FROM (
    SELECT DISTINCT DS.SOURCE_ID, DSD.COLUMN_NAME, 'N' AS MAP_FLAG
    FROM CDIP.SV_DATA_SOURCE DS
    INNER JOIN CDIP.SV_DATA_SOURCE_DETAIL DSD ON
        DS.ID = DSD.DATA_SOURCE_ID
    WHERE
        DSD.VC_FLAG = 0
) t1


Yeah, I really had forgotten that Ora didn't allow the "AS" before a derived table alias.  Kinda quirky, because it does allow the AS after a non-derived table:

FROM CDIP.SV_DATA_SOURCE AS DS
0
 

Author Closing Comment

by:sbornstein2
ID: 37817348
Thanks Scott looks like they reopened this for me.  Sorry it took so long for me to reward it right.  All set
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Pivot Rows To Columns 10 65
Sql query with where clause 2 34
SQL - format decimal in a string 5 38
SQL Recursion schedule 13 14
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question