Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id BB0FE9F968E for ; Fri, 7 Sep 2007 07:02:04 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 37208-09 for ; Fri, 7 Sep 2007 06:57:01 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3]) by postgresql.org (Postfix) with ESMTP id 850029F9818 for ; Fri, 7 Sep 2007 06:52:07 -0300 (ADT) Received: from webaccess.indicator.be ([10.100.1.7]) by interscan.indicator.com with InterScan Message Security Suite; Fri, 07 Sep 2007 11:34:45 +0200 Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise; Fri, 07 Sep 2007 11:52:04 +0200 Message-Id: <46E13B58.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Date: Fri, 07 Sep 2007 11:51:53 +0200 From: "Bart Degryse" To: Subject: Failing join with set returning function Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__PartF9DE5329.0__=" X-imss-version: 2.048 X-imss-result: Passed X-imss-approveListMatch: *@indicator.be X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.982 tagged_above=0 required=5 tests=AWL=-0.638, BAYES_40=-0.185, HTML_MESSAGE=0.001, LONGWORDS=1.803, UNPARSEABLE_RELAY=0.001 X-Spam-Level: X-Archive-Number: 200709/62 X-Sequence-Number: 29275 --=__PartF9DE5329.0__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable I've written a function that calculates the number of days for every month = in a given range and returns that as a set of records. CREATE OR REPLACE FUNCTION general_daysinmonth( date1 IN date, date2 IN date, month OUT date, days OUT integer) RETURNS SETOF record AS $body$ DECLARE startdate date; enddate date; BEGIN IF date1 >=3D date2 THEN startdate :=3D date2; enddate :=3D date1; ELSE startdate :=3D date1; enddate :=3D date2; END IF; month :=3D date_trunc('month', startdate); WHILE month <=3D enddate LOOP days :=3D LEAST(general_lastdayofmonth(month), enddate) - GREATEST(star= tdate, month) + 1; RETURN NEXT; month :=3D month + interval '1 month'; END LOOP; RETURN; END; $body$ LANGUAGE 'plpgsql' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER; Now I want to use that resultset as part of a join with a table called = billing (see definition below).=20 This join should return a record per month that is between salesstartdate = and salesenddate and lineamountmst should be divided pro rata the number = of days in each month =20 An example If table billing holds a record like id invoiceid inventtransid invoicedate dataareaid lineamountmst dimension = itemid salesunit issues salesstartdate salesenddate salesstopcode = salespoolid 1170 22428431 735706 2006-02-28 hlm 89,89 nlaatoal-6 nlaatoal 3m 0 = 2006-02-24 2006-05-23 SWI-TRM-1Y aans Then select * from general_daysinmonth(date '2006-02-24', date '2006-05-23'= ) would return month days 2006-02-01 5 2006-03-01 31 2006-04-01 30 2006-05-01 23 =20 So my join should return 4 records like invoiceid billingmonth revenuemonth revenue 22428431 2006-02-01 2006-02-01 5,05=20 22428431 2006-02-01 2006-03-01 31,31=20 22428431 2006-02-01 2006-04-01 30,30 22428431 2006-02-01 2006-05-01 23,23 where 89,89 / number of days between 2006-02-24 and 2006-05-23 * number of = days in 2006-02 =3D 5,05 for the first record =20 I thought I could do that quite easily like this SELECT B.invoiceid, date_trunc('month', B.invoicedate) as billingmonth,= C.month as revenuemonth,=20 B.lineamountmst * C.days / (B.salesenddate - B.salesstartda= te + 1) as revenue FROM billing B, general_daysinmonth(B.salesstartdate, B.salesenddate) = C WHERE B.dataareaid =3D 'hlm' AND=20 B.issues =3D 0 AND=20 B.salesenddate >=3D date_trunc('year', current_timestamp) - = interval '1 year'; or like this SELECT B.invoiceid, date_trunc('month', B.invoicedate) as billingmonth,= C.month as revenuemonth,=20 B.lineamountmst * C.days / (B.salesenddate - B.salesstartda= te + 1) as revenue FROM billing B, (select * from general_daysinmonth(B.salesstartdate, = B.salesenddate)) C WHERE B.dataareaid =3D 'hlm' AND=20 B.issues =3D 0 AND=20 B.salesenddate >=3D date_trunc('year', current_timestamp) - = interval '1 year'; But I get an error message in both cases: respectively ERROR: function expression in FROM may not refer to other relations of = same query level ERROR: subquery in FROM may not refer to other relations of same query = level =20 Can anyone please explain me why I'm getting this error message and how I = do what I'm trying to do? Thanks for your help, advice and time... =20 CREATE TABLE public.billing ( id serial PRIMARY KEY, invoiceid varchar(20) DEFAULT NULL, inventtransid varchar(20) DEFAULT NULL, invoicedate date NOT NULL, dataareaid varchar(3) NOT NULL, lineamountmst numeric(32,16) NOT NULL, dimension varchar(16) NOT NULL, itemid varchar(20) NOT NULL, salesunit varchar(10) NOT NULL, issues numeric(2) DEFAULT 0, salesstartdate date NOT NULL, salesenddate date NOT NULL, salesstopcode varchar(16) DEFAULT NULL, salespoolid varchar(10) NOT NULL ) WITH (fillfactor=3D75); =20 =20 --=__PartF9DE5329.0__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Description: HTML
I've written a function that calculates the number of days for = every month in a given range and returns that as a set of records.
CREATE OR REPLACE FUNCTION general_daysinmonth(
  date1 IN = date,
  date2 IN date,
  month OUT date,
  days = OUT integer) RETURNS SETOF record AS
$body$
DECLARE
  = startdate date;
  enddate date;
BEGIN
  IF date1 = >=3D date2 THEN
    startdate :=3D date2;
 &nb= sp;  enddate :=3D date1;
  ELSE
    = startdate :=3D date1;
    enddate :=3D date2;
  = END IF;
  month :=3D date_trunc('month', startdate);
  = WHILE month <=3D enddate LOOP
    days :=3D LEAST(gene= ral_lastdayofmonth(month), enddate) - GREATEST(startdate, month) + = 1;
    RETURN NEXT;
    month :=3D = month + interval '1 month';
  END LOOP;
  RETURN;
END;$body$
LANGUAGE 'plpgsql' IMMUTABLE RETURNS NULL ON NULL INPUT = SECURITY INVOKER;
Now I want to use that resultset as part of a join with a table = called billing (see definition below).
This join should return a record per month that is between salesstartd= ate and salesenddate and lineamountmst should be divided pro rata the = number of days in each month
 
An example
If table billing holds a record like
id invoiceid inventtransid invoicedate dataareaid&= nbsp;lineamountmst dimension itemid salesunit issues&nb= sp;salesstartdate salesenddate salesstopcode salespoolid
= 1170 22428431 735706 2006-02-28 hlm 89,89 nla= atoal-6 nlaatoal 3m 0 2006-02-24 2006-05-23 S= WI-TRM-1Y aans
Then select * from general_daysinmonth(date '2006-02-24', date = '2006-05-23')
would return
  month days
  2006-02-01 5
  = 2006-03-01 31
  2006-04-01 30
  2006-05-01 2= 3
 
So my join should return 4 records like
  invoiceid billingmonth revenuemonth revenue
  22428431 2006-02-01 2006-02-01 5,05 
  22428431 2006-02-01 2006-03-01 31,31
  22428431 2006-02-01 2006-04-01 30,30
  22428431 2006-02-01 2006-05-01 23,23
where 89,89 / number of days between 2006-02-24 and 2006-05-23 * = number of days in 2006-02 =3D 5,05 for the first record
 
I thought I could do that quite easily like this
    SELECT B.invoiceid, date_trunc('month', = B.invoicedate) as billingmonth, C.month as revenuemonth,
  &n= bsp;             = B.lineamountmst * C.days / (B.salesenddate - B.salesstartdate + 1) as = revenue
    FROM billing B, general_daysinmonth(B.sa= lesstartdate, B.salesenddate) C
    WHERE B.dataareaid = =3D 'hlm' AND
          = B.issues =3D 0 AND
        &nbs= p; B.salesenddate >=3D date_trunc('year', current_timestamp) - interval = '1 year';
or like this
    SELECT B.invoiceid, date_trunc('month', = B.invoicedate) as billingmonth, C.month as revenuemonth,
  &n= bsp;             = B.lineamountmst * C.days / (B.salesenddate - B.salesstartdate + 1) as = revenue
    FROM billing B, (select * from general_daysin= month(B.salesstartdate, B.salesenddate)) C
    WHERE = B.dataareaid =3D 'hlm' AND 
      &nb= sp;   B.issues =3D 0 AND
     &= nbsp;    B.salesenddate >=3D date_trunc('year', = current_timestamp) - interval '1 year';

But I get an error message = in both cases: respectively
ERROR:  function expression in FROM may not refer to other = relations of same query level
ERROR:  subquery in FROM may not = refer to other relations of same query level
 
Can anyone please explain me why I'm getting this error = message and how I do what I'm trying to do?
Thanks for your help, advice and time...
 
CREATE TABLE public.billing (
  id serial PRIMARY KEY,
&nbs= p; invoiceid varchar(20) DEFAULT NULL,
  inventtransid varchar(20) = DEFAULT NULL,
  invoicedate date NOT NULL,
  dataareaid = varchar(3) NOT NULL,
  lineamountmst numeric(32,16) NOT NULL,
&n= bsp; dimension varchar(16) NOT NULL,
  itemid varchar(20) NOT = NULL,
  salesunit varchar(10) NOT NULL,
  issues numeric(2)= DEFAULT 0,
  salesstartdate date NOT NULL,
  salesenddate = date NOT NULL,
  salesstopcode varchar(16) DEFAULT NULL,
  = salespoolid varchar(10) NOT NULL
) WITH (fillfactor=3D75);
 
 

--=__PartF9DE5329.0__=--