NULL values in a SELECT in another SELECT

[Hiho]

Hi,

I have a query like this :

SELECT
x1,
x2,
( SELECT ... FROM ... WHERE ...
UNION
SELECT ... FROM ... WHERE ...) as x3
FROM ...
WHERE ...

The problem is that I don't want to return the results where x3 is
NULL.

Writing :

SELECT
x1,
x2,
( SELECT ... FROM ... WHERE ...
UNION
SELECT ... FROM ... WHERE ...) as x3
FROM ...
WHERE ... AND x3 IS NOT NULL

doesn't work.

The only solution I found is to write :

SELECT * FROM
(
(SELECT
x1,
x2,
( SELECT ... FROM ... WHERE ...
UNION
SELECT ... FROM ... WHERE ...) as x3
FROM ...
WHERE ...
) AS R1
)
WHERE R1.x3 IS NOT NULL

Is there a better solution? Can I use an EXISTS clause somewhere to
test if x3 is null without having to have a 3rd SELECT statement?
There's probably a very simple solution to do this, but I didn't find
it.

Thanks

[Serge Rielau]

SELECT

x1,
x2,

X.x3
FROM ... ,

( SELECT ... FROM ... WHERE ...
UNION
SELECT ... FROM ... WHERE ...) as X.x3

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Hiho]

Thanks for your answers

P. Ratchev :

Is this a valid query? I mean if the UNION of the two SELECT statements
returns more than a single value then you will get an error (at least on SQL
Server).

Fortunately, it's impossible that the UNION returns more than 1
value ! Thanks for your explanations...
S. Rielau :

SELECT
x1,
x2,

X.x3
FROM ... ,

( SELECT ... FROM ... WHERE ...
UNION
SELECT ... FROM ... WHERE ...) as X.x3

Unfortunately, this can't work in my case, because, in my case, I
would have :
SELECT ...
FROM X,
(SELECT ... FROM Y WHERE Y.a=X.a
UNION
............)

The 2nd "FROM" depends on the 1st.

Sorry, I shoud be more precise.

Hiho

[Serge Rielau]

>>SELECT

>> x1,
x2,

X.x3
FROM ... ,

>> ( SELECT ... FROM ... WHERE ...
UNION
SELECT ... FROM ... WHERE ...) as X.x3

WHERE Y.a = X.a

I fail to see the problem...
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Plamen Ratchev]

Perhaps you mean something like this (SQL Server 2005 required):

CREATE TABLE Foo (x CHAR(1));
CREATE TABLE Bar (x CHAR(1), y CHAR(1));

INSERT INTO Foo VALUES ('a');
INSERT INTO Foo VALUES ('b');

INSERT INTO Bar VALUES ('a', NULL);
INSERT INTO Bar VALUES ('b', '1');

SELECT x, y
FROM Foo AS F
CROSS APPLY (SELECT y FROM Bar AS B1 WHERE B1.x = F.x
UNION
SELECT y FROM Bar AS B2 WHERE B2.x = F.x) AS B
WHERE y IS NOT NULL;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Hiho]

SELECT x, y
FROM Foo AS F
* CROSS APPLY (SELECT y FROM Bar AS B1 WHERE B1.x = F.x
* * * * * * * * * * * UNION
* * * * * * * * * * * SELECT y FROM Bar AS B2 WHERE B2.x = F.x) AS B
WHERE y IS NOT NULL;

Yeah, that's it !
But I'm under SQL Server 2000.... I think I'll keep the first
solution.

S. Rielau : the problem is the "WHERE B1.x=F.x".
If I write SELECT X FROM F, (SELECT ... FROM B1 WHERE B1.x=F.x) AS F1
SQL Server doesn't recognize F.x.

[Serge Rielau]

Hiho wrote:

>SELECT x, y
FROM Foo AS F
CROSS APPLY (SELECT y FROM Bar AS B1 WHERE B1.x = F.x
UNION
SELECT y FROM Bar AS B2 WHERE B2.x = F.x) AS B
WHERE y IS NOT NULL;

Yeah, that's it !
But I'm under SQL Server 2000.... I think I'll keep the first
solution.

S. Rielau : the problem is the "WHERE B1.x=F.x".
If I write SELECT X FROM F, (SELECT ... FROM B1 WHERE B1.x=F.x) AS F1
SQL Server doesn't recognize F.x.

That's not what I wrote.
You can put the WHERE on the outer SELECT.

Teh SQL Standard to make what you tried work is:
FROM X, LATERAL(SELECT ..... WHERE X.blah = ...)
It's called lateral correlation.
Incidently CROSS APPLY Seems to be a funny invention to do SQL Standard:
CROSS JOIN LATERAL(...).

Vendor lock in SQL dialect for no good reason... grmbl grmbl...
Where is Celko when he's needed. ;-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Plamen Ratchev]

Just for the sake of replicating what you were trying to do, in SQL Server
2000 you can use UDF:

CREATE FUNCTION dbo.B
( @x CHAR(1) )
RETURNS CHAR
AS
BEGIN
SET @x = (SELECT y FROM Bar WHERE x = @x
UNION
SELECT y FROM Bar WHERE x = @x)
RETURN @x
END

Then you can write the query like:

SELECT x, dbo.B(x)
FROM Foo
WHERE dbo.B(x) IS NOT NULL

HTH,

Plamen Ratchev
http://www.SQLStudio.com