Solved

Union SQL Syntax

Posted on 2013-06-07
11
542 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

729 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