Solved

Union SQL Syntax

Posted on 2013-06-07
11
537 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
  • 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
 
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now