Link to home
Create AccountLog in
Avatar of Allan
AllanFlag for United States of America

asked on

Union SQL Syntax

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!
SOLUTION
Avatar of oleggold
oleggold
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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
let me know if it worked
Avatar of Allan

ASKER

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

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

Avatar of Allan

ASKER

Thanks DOSLover.

Ran the query and still returned the NULL as the first record:
User generated image
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!
SOLUTION
Avatar of David Todd
David Todd
Flag of New Zealand image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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 + '|'
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Allan

ASKER

Oh I see;  we'll try it later today and will let you know. Thanks!
Avatar of Allan

ASKER

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.