Solved

Union SQL Syntax

Posted on 2013-06-07
11
543 Views
Last Modified: 2013-06-10
Hello Experts!

Thanks for reading this.

For MS SQL 2008, say you want the results to return:

1. All records from PTemp
2. All records from fTable that's not in PTemp
3. The first record returned should be '^SomeHeader' ..nekey is alpha (a-z).

The following sql returns NULL as the first record instead of '^SomeHeader'.
PTemp and fTable do not have a NULL field for nekey.

If you could help improve the efficiency with the sql that would be appreciated as well:

DECLARE @PTemp TABLE (nekey VARCHAR(30))

INSERT INTO @PTemp (nekey)
	SELECT nekey 
	FROM nTable  
	WHERE nekey <> ''  
	AND nekey IS NOT NULL 

SELECT  '^SomeHeader'   as ndcextract    

UNION

SELECT nekey + '|||' 	
FROM @PTemp

UNION

SELECT f.nekey + '|' + f.field02 + '|' + f.field03 + '|'
FROM DBO.[fTable] f
LEFT JOIN @PTemp p ON f.nekey = pb.nekey
WHERE p.nekey IS NULL
ORDER BY 1

Open in new window

TIA!
0
Comment
Question by:allanau20
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 21

Assisted Solution

by:oleggold
oleggold earned 50 total points
ID: 39230952
i think yr problem is in concat ,i'd try to get rid of it
DECLARE @PTemp TABLE (nekey VARCHAR(30))

INSERT INTO @PTemp (nekey)
      SELECT nekey
      FROM nTable  
      WHERE nekey <> ''  
      AND nekey IS NOT NULL

SELECT  '^SomeHeader'   as ndcextract    

UNION

SELECT nekey
FROM @PTemp

UNION

SELECT f.nekey + '|' + f.field02 + '|' + f.field03 + '|'
FROM DBO.[fTable] f
LEFT JOIN @PTemp p ON f.nekey = pb.nekey
WHERE p.nekey IS NULL
ORDER BY 1
0
 
LVL 21

Expert Comment

by:oleggold
ID: 39230958
also ,just in case, use the same where on temp

DECLARE @PTemp TABLE (nekey VARCHAR(30))

INSERT INTO @PTemp (nekey)
      SELECT nekey
      FROM nTable  
      WHERE nekey <> ''  
      AND nekey IS NOT NULL

SELECT  '^SomeHeader'   as ndcextract    

UNION

SELECT nekey
FROM @PTemp
      WHERE nekey <> ''  
      AND nekey IS NOT NULL

UNION

SELECT f.nekey + '|' + f.field02 + '|' + f.field03 + '|'
FROM DBO.[fTable] f
LEFT JOIN @PTemp p ON f.nekey = pb.nekey
WHERE p.nekey IS NULL
ORDER BY 1
0
 
LVL 21

Expert Comment

by:oleggold
ID: 39230959
let me know if it worked
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 5

Author Comment

by:allanau20
ID: 39230963
Thanks oleggold.

Other than the where clause on the temp; is there anything else you edited?
It looks the same as what was posted ...

This still returns NULL as the first record ..

DECLARE @PTemp TABLE (nekey VARCHAR(30))

INSERT INTO @PTemp (nekey)
      SELECT nekey 
      FROM nTable  
      WHERE nekey <> ''  
      AND nekey IS NOT NULL 

SELECT  '^SomeHeader'   as ext

UNION

SELECT nekey 
FROM @PTemp
      WHERE nekey <> ''  
      AND nekey IS NOT NULL 

UNION

SELECT f.nekey + '|' + f.field02 + '|' + f.field03 + '|'
FROM DBO.[fTable] f
LEFT JOIN @PTemp p ON f.nekey = p.nekey
WHERE p.nekey IS NULL
ORDER BY 1

Open in new window

0
 
LVL 5

Expert Comment

by:DOSLover
ID: 39231033
Please try this. Removed the use of PTemp.Also, since NULLs are excluded in both, we should see '^Someheader' at the top.

SELECT  '^SomeHeader'   as ndcextract    
UNION
SELECT nekey + '|||'   as ndcextract
	FROM nTable  
	WHERE nekey <> ''  
	AND nekey IS NOT NULL 
UNION
SELECT f.nekey + '|' + f.field02 + '|' + f.field03 + '|'  as ndcextract
FROM DBO.[fTable] f
where f.nekey is NOT NULL
  and not exists
      (Select nekey from nTable n
	    where n.nekey is NOT NULL
		  and nekey <> ''
		  and f.nekey = n.nekey)
ORDER BY 1

Open in new window

0
 
LVL 5

Author Comment

by:allanau20
ID: 39232181
Thanks DOSLover.

Ran the query and still returned the NULL as the first record:
NULL In First Record
I know we don't have null nekey in either the n or f tables.
Just to be sure ran this and got count 0:
select count(*)
from dbo.[fTable]
WHERE nekey IS NULL

Open in new window


Any ideas? thanks!
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 50 total points
ID: 39233542
Hi,

Thoughts:
Try each part of the union individually.

Check your ansi settings.

HTH
  David
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39233596
I suspect the following will return something:
select count(*)
from dbo.[fTable]
WHERE nekey IS NULL OR field02  IS NULL OR field03 IS NULL

In other words, if any of the values are NULL then the following concatenation will be NULL:
f.nekey + '|' + f.field02 + '|' + f.field03 + '|'
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 400 total points
ID: 39233602
If I am right, than do it this way:
SELECT ISNULL(f.nekey + '|', '') + ISNULL(f.field02 + '|', '') + ISNULL(f.field03 + '|', '')
0
 
LVL 5

Author Comment

by:allanau20
ID: 39233629
Oh I see;  we'll try it later today and will let you know. Thanks!
0
 
LVL 5

Author Closing Comment

by:allanau20
ID: 39234154
Bingo ackperkins...

oleggold, now I know what you meant "i think yr problem is in concat"

dtodd, I just assumed there's no NULL in field02 field03 b/c there's not supposed to be; it was a good idea to check .. should've thought of that ..

Thanks DOSLover for trying to help.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

617 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