error,again

tentaively, got:
create #table(a,b,c,d)
insert into #table
select A,B,C,D
from s,t,x,y,z where y.price > 20

select *,
(select max(D) from
(select distinct top 2 with ties from #temp v where
v.B=W.B and v.C = W.c),
(select min(D) from
(select distinct top 2 with ties from #temp v where
v.B=W.B and v.C = W.c)
from #temp w
drop Table #temp

This works, but if y.price > 20 condition is not met,
unsupported error message gets produced within crystal
report. i expected blank field report instead in this
case,

included If exists around the select statement ,
still same error.

BTW, is there a way to find max(D) and min(D),
two highests, can be improved?.

--
Sent by 3 from yahoo subdomain of com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.comalexqa2003@.yahoo.com (u128845214@.spawnkill.ip-mobilphone.net) writes:
> tentaively, got:
> create #table(a,b,c,d)
> insert into #table
> select A,B,C,D
> from s,t,x,y,z where y.price > 20
> select *,
> (select max(D) from
> (select distinct top 2 with ties from #temp v where
> v.B=W.B and v.C = W.c),
> (select min(D) from
> (select distinct top 2 with ties from #temp v where
> v.B=W.B and v.C = W.c)
> from #temp w
> drop Table #temp
> This works, but if y.price > 20 condition is not met,
> unsupported error message gets produced within crystal
> report. i expected blank field report instead in this
> case,

If the condition is not met, the temp table is empty, and you will
get no rows back at all.

You could do:

IF EXISTS (SELECT * FROM #temp)
SELECT *, ...
ELSE
SELECT NULL, NULL, NULL, NULL, NULL, NULL -- As many as needed.

Although, it would be better to rewrite the report to handle an
empty result set...

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp