--- In firebird-support@yahoogroups.com, "william_yuwei"
<william_yuwei@...> wrote:

>
> Hi, All
>
> I've built a stored procedure which returned the following result:
>
> ModelNo Color Size ColA ColB ColC
> 8080 Gold 48 2.5 2.2 2.4
> 8080 Gold 50 3.4 3.7 3.2
> 8080 Gun 48 0.8 0.9 0.9
>
> 8081 Blue 47 1.9 2.0 1.9
> 8081 Blue 49 2.7 3.0 2.8
>
> However there is another critera needs to be used against above
> results: if any of ColA, ColB or ColC are lower than 1.0, the whole
> group of model will be returned otherwise not, which means that

Model

> 8081 should not be returned to the final result, while whole records
> of Model 8080 should be returned.
>
> How can I implement this by SQL?
>
> Thanks
>
> William
>

William,

You have not provided source to your stored procedure, so it is a bit
hard to tell you the best way to do it. I suspect there may be some
normalisation issues with this data model.

select m.ModelNo, m.Color, m.Size, m.ColA, m.ColB, m.ColC
from mySP m
where exists
(
select *
from mysp m2
where m.ModelNo = m2.ModelNo
and (m2.ColA < 1 or m2.ColB < 1 or m2.ColC < 1)
)

The brackets are important because you are mixing ands and ors
together, and I always forget which takes precedence. This could be
made more efficient if there was a way of obtaining the sub query
result by reading a simple table, or even if your SP had an input
parameter where you could pass in the model no.

The above query should work anyway, it just may be slow as it will
have to run the SP n+1 times.

Adam