Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Union SQL Syntax

Posted on 2013-06-07
11
Medium Priority
?
545 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 200 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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 200 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 1600 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

Use Filtering Commands to Process Files in Linux

Learn how to manipulate data with the help of various filtering commands such as `cat`, `fmt`, `pr`, and others in Linux.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

722 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