HI, Adam

Thanks. It looks like I have to create a temparily table to store the data return from the stored procedure, and run the second query against it.

William

Adam <s3057043@...> wrote: --- 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







++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++




SPONSORED LINKS
Technical support Computer technical support Compaq computer technical support Compaq technical support Hewlett packard technical support Microsoft technical support

---------------------------------
YAHOO! GROUPS LINKS


Visit your group "firebird-support" on the web.

To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


---------------------------------





William, Yu

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

[Non-text portions of this message have been removed]