Обсуждение: multiple identical calc and function in single query

I have a query which includes the same calculation and function call
multiple times.

Is the calculation and function call evaluated one time or as many times
as it is in the query?

Example:

326/getdisplayconversionmultiplebypn(pnid) is in this query 6 times. How
many times is it evaluated?


select pnid, (326/getdisplayconversionmultiplebypn(pnid)) + case when
coalesce(mpq,0)=0 then 0 else
mpq-((326/getdisplayconversionmultiplebypn(pnid)) % mpq)end as qty,
        case when not translatempq then
(326/getdisplayconversionmultiplebypn(pnid)) + case when
coalesce(mpq,0)=0 then 0 else
mpq-((326/getdisplayconversionmultiplebypn(pnid)) % mpq)end
        else ((326/getdisplayconversionmultiplebypn(pnid)) + case when
coalesce(mpq,0)=0 then 0 else
mpq-((326/getdisplayconversionmultiplebypn(pnid)) % mpq)end)/mpq  end as
mpqqty
        from manufacturerpartpn a
        inner join leadstatemfgparts b on a.partid=b.partid and
a.manufacturerid=b.manufacturerid
        where compatibilitygradeid in (50,100,200) and lifecycleid not
in (-400,-500)
        and a.parentid is null and a.PartID=1108 and leadstateid = any
('{1,3}')
        and leadstateid <> case when lfbcount>0 then 4 else -1 end
        and leadstateid <> case when lpbcount>0 then 2 else -1 end

On Thu, Oct 29, 2009 at 12:05:54PM +0200, Sim Zacks wrote:
> 326/getdisplayconversionmultiplebypn(pnid) is in this query 6 times. How
> many times is it evaluated?

I'm pretty sure it'll evaluated multiple times.  Why don't you put it
into a sub-select, it'll at least save some typing.  Something like:

  SELECT pnid, calc + case when ... then .. else ... end AS qty
  FROM (
    SELECT pnid, 326/getdisplayconversionmultiplebypn(pnid) AS calc, ...
    FROM ...) x;

I have a feeling that it may still get evaluated several times in
certain cases, but you could put a "RAISE NOTICE" into your code to
figure out.

--
  Sam  http://samason.me.uk/