471,853 Members | 1,770 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,853 software developers and data experts.

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.com
Jul 20 '05 #1
1 1467
al********@yahoo.com (u1********@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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Gizmo | last post: by
6 posts views Thread by Peter Frost | last post: by
7 posts views Thread by p | last post: by
2 posts views Thread by Chuck Ritzke | last post: by
3 posts views Thread by msnews.microsoft.com | last post: by
NeoPa
reply views Thread by NeoPa | last post: by
aboka
reply views Thread by aboka | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.