Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 1E2082E0348 for ; Wed, 13 Feb 2008 13:16:26 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 35222-06 for ; Wed, 13 Feb 2008 13:16:19 -0400 (AST) Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3]) by postgresql.org (Postfix) with ESMTP id 4D0502E036C for ; Wed, 13 Feb 2008 13:16:19 -0400 (AST) Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise; Wed, 13 Feb 2008 18:16:17 +0100 Message-Id: <47B333EA.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Date: Wed, 13 Feb 2008 18:16:11 +0100 From: "Bart Degryse" To: Subject: Re: return field from different table conditionally References: <47B2DA66.A3DD.0030.0@indicator.be> In-Reply-To: <47B2DA66.A3DD.0030.0@indicator.be> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part72548BCB.0__=" X-imss-version: 2.049 X-imss-result: Passed X-imss-approveListMatch: *@indicator.be X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/104 X-Sequence-Number: 30195 --=__Part72548BCB.0__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable I gave up trying to do this is a single SQL statement and went over to = writing a stored procedure. Unfortunately I get stuck here too. This function gets me the data I need from tables accounts_new, account_ran= ges and salesunits. CREATE OR REPLACE FUNCTION fnc_test ( INOUT p_dataareaid accounts.dataareaid%TYPE, INOUT p_accnbr accounts.accountnumber%TYPE, INOUT p_lineamount account_ranges.amountmst%TYPE, OUT p_dimension accounts.dimension%TYPE,=20 OUT p_itemid accounts.itemid%TYPE, OUT p_salesunit accounts.salesunit%TYPE, OUT p_poolid accounts.poolid%TYPE, OUT p_pg_interval salesunits.pg_interval%TYPE) RETURNS record AS $body$ BEGIN SELECT A.dimension, A.itemid, COALESCE(AR.salesunit, A.salesunit), A.poolid, S.pg_interval INTO STRICT p_dimension, p_itemid, p_salesunit, p_poolid, p_pg_interval FROM salesunits S, accounts_new A left join (select * from account_ranges where dataareaid= =3D p_dataareaid AND accountnumber =3D p_accnbr AND amountmst =3D = p_lineamount) AR USING (dataareaid, accountnumber) WHERE A.dataareaid =3D p_dataareaid AND A.accountnumber =3D p_accnbr AND S.salesunit =3D COALESCE(AR.salesunit, A.salesunit); RETURN; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; =20 So when I do this I really get what I want. select * from fnc_test('lil', 700010, 2) =20 However I still need to link that to the billing_exact (and exact_files) = table. Using the function in the FROM clause gets me an error: ERROR: function expression in FROM may not refer to other relations of = same query level =20 Using the function in the SELECT clause gets me closer: SELECT fnc_test(E.dataareaid, B.reknr, B.lineamountmst) FROM public.billing_exact B, public.exact_files E WHERE B.fileid =3D E.id AND B.transactionid BETWEEN 53965 AND 53980 =20 the data is still there, but it is now a record type fnc_test "(lil,700011,159.0000000000000000,fracimal-2,fracimal,6m,ALRN,""6 mons"")" "(lil,700011,139.0000000000000000,fracimal-2,fracimal,1y,ALRN,""1 year"")" "(lil,700011,159.0000000000000000,fracimal-2,fracimal,6m,ALRN,""6 mons"")" "(lil,700011,139.0000000000000000,fracimal-2,fracimal,1y,ALRN,""1 year"")" and I can't insert it in a table as individual fields. I've tried all sorts of variations (return a table type or a user defined = type from the=20 stored procedure, type casting the result) but to no avail. =20 Can anyone help me out? Thanks >>> "Bart Degryse" 2008-02-13 11:54 >>> I'm having trouble getting the values I want from my tables. Below you find the DDL's for all tables concerned and some data to test = with. =20 What I would like to get as a result is: for each billing_exact record the = salesunit from account_ranges if there is a match between billing_exact.lineamountmst and account_ranges.= amountmst and otherwise the salesunit from accounts_new.=20 =20 Some joins that would certainly have to be made billing_exact.fileid =3D exact_files.id=20 exact_files.dataareaid =3D accounts_new.dataareaid billing_exact.reknr =3D accounts_new.accountnumber accounts_new.accountnumber =3D account_ranges.accountnumber=20 accounts_new.dataareaid =3D account_ranges.dataareaid=20 =20 And then there must be something like if billing_exact.lineamountmst =3D account_ranges.amountmst then return account_ranges.salesunit else return account_new.salesunit (consider this some kind of default = value) So the result would have to be 3 records (as there are 3 records in = billing_exact) and the salesunit from the appropriate table:=20 =20 62, 700011, 53972, '2005-08-01', NULL, 139, '1y'=20 62, 700011, 53973, '2005-08-01', NULL, 159, '6m' 62, 700011, 53974, '2005-08-01', NULL, 278, 'pcs' =20 It can't be very hard to do this with a stored procedure but can anyone help me out on how to do this with SQL alone? Many thanks in advance. Bart =20 ------------------------------------------------------------ INSERT INTO "exact_files" ("id", "fname", "fdate", "fsize", "crc32", = "statusid", "dataareaid") VALUES (62, 'FR2005.DBF', '2008-02-01 09:06:00', 2326318, 'E2E1C53C', 3, = 'lil'); INSERT INTO "billing_exact" ("fileid", "reknr", "transactionid", "transacti= ondate", "invoiceid", "lineamountmst") VALUES (62, 700011, 53972, '2005-08-01', NULL, 139); =20 INSERT INTO "billing_exact" ("fileid", "reknr", "transactionid", "transacti= ondate", "invoiceid", "lineamountmst") VALUES (62, 700011, 53973, '2005-08-01', NULL, 159); =20 INSERT INTO "billing_exact" ("fileid", "reknr", "transactionid", "transacti= ondate", "invoiceid", "lineamountmst") VALUES (62, 700011, 53974, '2005-08-01', NULL, 278); =20 INSERT INTO "public"."salesunits" ("salesunit", "pg_interval") VALUES ('pcs', '00:00:00'); =20 INSERT INTO "public"."salesunits" ("salesunit", "pg_interval") VALUES ('6m', '6 mons'); =20 INSERT INTO "public"."salesunits" ("salesunit", "pg_interval") VALUES ('1y', '1 year'); INSERT INTO "accounts_new" ("accountnumber", "dataareaid", "pool", = "account", "dimension", "itemid", "salesunit", "poolid", "ignore") VALUES (700011, 'lil', 'RN', 'A&C IMPOTS REABO', 'fracimal-2', 'fracimal', = '1y', 'ALRN', False); INSERT INTO "public"."account_ranges" ("dataareaid", "accountnumber", = "amountmst", "startdate", "enddate", "salesunit") VALUES ('lil', 700011, 159, '2005-01-01', NULL, '6m'); =20 INSERT INTO "public"."account_ranges" ("dataareaid", "accountnumber", = "amountmst", "startdate", "enddate", "salesunit") VALUES ('lil', 700011, 278, '2005-08-01', '2005-08-31', 'pcs'); =20 CREATE TABLE "public"."exact_files" ( "id" SERIAL,=20 "fname" TEXT NOT NULL,=20 "fdate" TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,=20 "fsize" INTEGER NOT NULL,=20 "crc32" VARCHAR(8) NOT NULL,=20 "statusid" INTEGER NOT NULL,=20 "dataareaid" VARCHAR(3) NOT NULL,=20 CONSTRAINT "exact_files_pkey" PRIMARY KEY("id"),=20 CONSTRAINT "exact_files_unq" UNIQUE("fname"),=20 CONSTRAINT "exact_files_file_status_fkey" FOREIGN KEY ("statusid") REFERENCES "public"."file_status"("id") ON DELETE NO ACTION ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED ) WITHOUT OIDS; CREATE TABLE "public"."billing_exact" ( "fileid" INTEGER NOT NULL,=20 "reknr" INTEGER NOT NULL,=20 "transactionid" INTEGER NOT NULL,=20 "transactiondate" DATE NOT NULL,=20 "invoiceid" INTEGER,=20 "lineamountmst" NUMERIC(32,16) NOT NULL,=20 "creationdate" DATE DEFAULT ('now'::text)::date NOT NULL,=20 CONSTRAINT "billing_exact_pkey" PRIMARY KEY("fileid", "transactionid"),= =20 CONSTRAINT "billing_exact_exact_files_fkey" FOREIGN KEY ("fileid") REFERENCES "public"."exact_files"("id") ON DELETE NO ACTION ON UPDATE CASCADE NOT DEFERRABLE ) WITHOUT OIDS; =20 CREATE INDEX "billing_exact_creationdate_idx" ON "public"."billing_exact" USING btree ("creationdate") WITH (fillfactor =3D 100); =20 CREATE UNIQUE INDEX "billing_exact_unq" ON "public"."billing_exact" USING btree ("transactionid", (fnc_idx_dataareaid(fileid))); CREATE TABLE "public"."salesunits" ( "salesunit" VARCHAR(10) NOT NULL,=20 "pg_interval" INTERVAL(65535) NOT NULL CONSTRAINT "salesunits_pkey" PRIMARY KEY("salesunit") ) WITHOUT OIDS; =20 CREATE TABLE "public"."accounts_new" ( "accountnumber" INTEGER NOT NULL,=20 "dataareaid" VARCHAR(3) NOT NULL,=20 "pool" TEXT NOT NULL,=20 "account" TEXT NOT NULL,=20 "dimension" VARCHAR(16) NOT NULL,=20 "itemid" VARCHAR(20) NOT NULL,=20 "salesunit" VARCHAR(10) NOT NULL,=20 "poolid" VARCHAR(10) NOT NULL,=20 "ignore" BOOLEAN DEFAULT false NOT NULL,=20 CONSTRAINT "accounts_new_pkey" PRIMARY KEY("dataareaid", "accountnumber")= ,=20 CONSTRAINT "accounts_new_salesunits_fkey" FOREIGN KEY ("salesunit") REFERENCES "public"."salesunits"("salesunit") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE ) WITHOUT OIDS; CREATE TABLE "public"."account_ranges" ( "dataareaid" VARCHAR(3) NOT NULL,=20 "accountnumber" INTEGER NOT NULL,=20 "amountmst" NUMERIC(32,16) NOT NULL,=20 "startdate" DATE NOT NULL,=20 "enddate" DATE,=20 "salesunit" VARCHAR(10) NOT NULL,=20 CONSTRAINT "account_ranges_pkey" PRIMARY KEY("dataareaid", "accountnumber= ", "amountmst", "startdate"),=20 CONSTRAINT "account_ranges_salesunits_fkey" FOREIGN KEY ("salesunit") REFERENCES "public"."salesunits"("salesunit") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE ) WITHOUT OIDS; =20 CREATE OR REPLACE FUNCTION "public"."fnc_idx_dataareaid" (p_fileid = integer) RETURNS varchar AS $body$ DECLARE v_dataareaid exact_files.dataareaid%TYPE; BEGIN SELECT dataareaid INTO v_dataareaid FROM exact_files WHERE id =3D = p_fileid; RETURN v_dataareaid; END; $body$ LANGUAGE 'plpgsql' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER; --=__Part72548BCB.0__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Description: HTML
I gave up trying to do this is a single SQL statement and went over = to writing a stored procedure.
Unfortunately I get stuck here too.
This function gets me the data I need from tables accounts_new, = account_ranges and salesunits.

CREATE OR REPLACE FUNCTION fnc_test (
  INOUT p_dataareaid accounts.dataareaid%TYPE,
  INO= UT p_accnbr accounts.accountnumber%TYPE,
  INOUT p_lineamount = account_ranges.amountmst%TYPE,
  OUT p_dimension accounts.dime= nsion%TYPE, 
  OUT p_itemid accounts.itemid%TYPE,
&nbs= p; OUT p_salesunit accounts.salesunit%TYPE,
  OUT = p_poolid accounts.poolid%TYPE,
  OUT p_pg_interval salesunits.= pg_interval%TYPE) RETURNS record AS
$body$
BEGIN
  SELECT
=     A.dimension,
    A.itemid,
 &n= bsp;  COALESCE(AR.salesunit, A.salesunit),
    = A.poolid,
    S.pg_interval
  INTO STRICT
&nbs= p;   p_dimension, p_itemid, p_salesunit, p_poolid, p_pg_interval<= BR>  FROM
    salesunits S,
    = accounts_new A left join (select * from account_ranges where dataareaid = =3D p_dataareaid AND accountnumber =3D p_accnbr AND amountmst =3D = p_lineamount) AR USING (dataareaid, accountnumber)
  WHERE
 = ;   A.dataareaid =3D p_dataareaid AND
    = A.accountnumber =3D p_accnbr AND
    S.salesunit =3D = COALESCE(AR.salesunit, A.salesunit);
  RETURN;
END;
$body$LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
 
So when I do this I really get what I want.
select * from fnc_test('lil', 700010, 2)
 
However I still need to link that to the billing_exact (and exact_file= s) table.
Using the function in the FROM clause gets me an error:
ERROR:  function expression in FROM may not refer to other = relations of same query level
 
Using the function in the SELECT clause gets me closer:
SELECT
  fnc_test(E.dataareaid, B.reknr, B.lineamountmst)
F= ROM
  public.billing_exact B, public.exact_files E
WHERE
&nbs= p; B.fileid =3D E.id AND B.transactionid BETWEEN 53965 AND 53980
 
the data is still there, but it is now a record type
fnc_test
"(lil,700011,159.0000000000000000,fracimal-2,fracimal,6m,A= LRN,""6 mons"")"
"(lil,700011,139.0000000000000000,fracimal-2,fracimal,1= y,ALRN,""1 year"")"
"(lil,700011,159.0000000000000000,fracimal-2,fracima= l,6m,ALRN,""6 mons"")"
"(lil,700011,139.0000000000000000,fracimal-2,frac= imal,1y,ALRN,""1 year"")"

and I can't insert it in a table as individual fields.
I've tried all sorts of variations (return a table type or a user = defined type from the
stored procedure, type casting the result) but to no avail.
 
Can anyone help me out? Thanks

>>> "Bart Degryse" = <Bart.Degryse@indicator.be> 2008-02-13 11:54 >>>
I'm having trouble getting the values I want from my tables.
Below you find the DDL's for all tables concerned and some data to = test with.
 
What I would like to get as a result is: for each billing_exact = record the salesunit from account_ranges
if there is a match between billing_exact.lineamountmst and account_ra= nges.amountmst and otherwise
the salesunit from accounts_new.
 
Some joins that would certainly have to be made
  billing_exact.fileid =3D exact_files.id 
  exact_files.dataareaid =3D accounts_new.dataareaid
  billing_exact.reknr =3D accounts_new.accountnumber
  accounts_new.accountnumber =3D account_ranges.accountnumber =
  accounts_new.dataareaid =3D account_ranges.dataareaid
 
And then there must be something like
  if billing_exact.lineamountmst =3D account_ranges.amountmst = then
     return account_ranges.salesunit
  else
    return account_new.salesunit (consider this some = kind of default value)
So the result would have to be 3 records (as there are 3 records in = billing_exact)
and the salesunit from the appropriate table:
 
62, 700011, 53972, '2005-08-01', NULL, 139, '1y' 
62, 700011, 53973, '2005-08-01', NULL, 159, '6m'
62, 700011, 53974, '2005-08-01', NULL, 278, 'pcs'
 
It can't be very hard to do this with a stored procedure but
can anyone help me out on how to do this with SQL alone?
Many thanks in advance.
Bart
 
------------------------------------------------------------
INSERT INTO "exact_files" ("id", "fname", "fdate", "fsize", "crc32", = "statusid", "dataareaid")
VALUES (62, 'FR2005.DBF', '2008-02-01 = 09:06:00', 2326318, 'E2E1C53C', 3, 'lil');
INSERT INTO "billing_exact" ("fileid", "reknr", "transactionid", = "transactiondate", "invoiceid", "lineamountmst")
VALUES (62, 700011, = 53972, '2005-08-01', NULL, 139);
 
INSERT INTO "billing_exact" ("fileid", "reknr", "transactionid", = "transactiondate", "invoiceid", "lineamountmst")
VALUES (62, 700011, = 53973, '2005-08-01', NULL, 159);
 
INSERT INTO "billing_exact" ("fileid", "reknr", "transactionid", = "transactiondate", "invoiceid", "lineamountmst")
VALUES (62, 700011, = 53974, '2005-08-01', NULL, 278);
 
INSERT INTO "public"."salesunits" ("salesunit", "pg_interval")
VALU= ES ('pcs', '00:00:00');
 
INSERT INTO "public"."salesunits" ("salesunit", "pg_interval")
VALU= ES ('6m', '6 mons');
 
INSERT INTO "public"."salesunits" ("salesunit", "pg_interval")
VALU= ES ('1y', '1 year');
INSERT INTO "accounts_new" ("accountnumber", "dataareaid", "pool", = "account", "dimension", "itemid", "salesunit", "poolid", "ignore")
VALUE= S (700011, 'lil', 'RN', 'A&C IMPOTS REABO', 'fracimal-2', 'fracimal', = '1y', 'ALRN', False);

INSERT INTO "public"."account_ranges" = ("dataareaid", "accountnumber", "amountmst", "startdate", "enddate", = "salesunit")
VALUES ('lil', 700011, 159, '2005-01-01', NULL, '6m');
 
INSERT INTO "public"."account_ranges" ("dataareaid", "accountnumber", = "amountmst", "startdate", "enddate", "salesunit")
VALUES ('lil', = 700011, 278, '2005-08-01', '2005-08-31', 'pcs');
 
CREATE TABLE "public"."exact_files" (
  "id" SERIAL, =
  "fname" TEXT NOT NULL,
  "fdate" TIMESTAMP(0) WITHOUT = TIME ZONE NOT NULL,
  "fsize" INTEGER NOT NULL,
  = "crc32" VARCHAR(8) NOT NULL,
  "statusid" INTEGER NOT NULL, =
  "dataareaid" VARCHAR(3) NOT NULL,
  CONSTRAINT = "exact_files_pkey" PRIMARY KEY("id"),
  CONSTRAINT "exact_files_un= q" UNIQUE("fname"),
  CONSTRAINT "exact_files_file_status_fkey" = FOREIGN KEY ("statusid")
    REFERENCES "public"."file_st= atus"("id")
    ON DELETE NO ACTION
   = ON UPDATE CASCADE
    DEFERRABLE
    = INITIALLY DEFERRED
) WITHOUT OIDS;
CREATE TABLE "public"."billing_exact" (
  "fileid" INTEGER = NOT NULL,
  "reknr" INTEGER NOT NULL,
  "transactionid" = INTEGER NOT NULL,
  "transactiondate" DATE NOT NULL,
  = "invoiceid" INTEGER,
  "lineamountmst" NUMERIC(32,16) NOT NULL, =
  "creationdate" DATE DEFAULT ('now'::text)::date NOT NULL, =
  CONSTRAINT "billing_exact_pkey" PRIMARY KEY("fileid", "transacti= onid"),
  CONSTRAINT "billing_exact_exact_files_fkey" FOREIGN KEY = ("fileid")
    REFERENCES "public"."exact_files"("id")    ON DELETE NO ACTION
    ON UPDATE = CASCADE
    NOT DEFERRABLE
) WITHOUT OIDS;
 
CREATE INDEX "billing_exact_creationdate_idx" ON "public"."billing_exa= ct"
  USING btree ("creationdate")
  WITH (fillfactor =3D = 100);
 
CREATE UNIQUE INDEX "billing_exact_unq" ON "public"."billing_exact"  USING btree ("transactionid", (fnc_idx_dataareaid(fileid)));
CREATE TABLE "public"."salesunits" (
  "salesunit" VARCHAR(10)= NOT NULL,
  "pg_interval" INTERVAL(65535) NOT NULL
  = CONSTRAINT "salesunits_pkey" PRIMARY KEY("salesunit")
) WITHOUT = OIDS;
 
CREATE TABLE "public"."accounts_new" (
  "accountnumber" = INTEGER NOT NULL,
  "dataareaid" VARCHAR(3) NOT NULL,
  = "pool" TEXT NOT NULL,
  "account" TEXT NOT NULL,
  = "dimension" VARCHAR(16) NOT NULL,
  "itemid" VARCHAR(20) NOT = NULL,
  "salesunit" VARCHAR(10) NOT NULL,
  "poolid" = VARCHAR(10) NOT NULL,
  "ignore" BOOLEAN DEFAULT false NOT NULL, =
  CONSTRAINT "accounts_new_pkey" PRIMARY KEY("dataareaid", = "accountnumber"),
  CONSTRAINT "accounts_new_salesunits_fkey" = FOREIGN KEY ("salesunit")
    REFERENCES "public"."salesu= nits"("salesunit")
    ON DELETE NO ACTION
  = ;  ON UPDATE NO ACTION
    NOT DEFERRABLE
) = WITHOUT OIDS;
CREATE TABLE "public"."account_ranges" (
  "dataareaid" = VARCHAR(3) NOT NULL,
  "accountnumber" INTEGER NOT NULL, =
  "amountmst" NUMERIC(32,16) NOT NULL,
  "startdate" = DATE NOT NULL,
  "enddate" DATE,
  "salesunit" VARCHAR(10= ) NOT NULL,
  CONSTRAINT "account_ranges_pkey" PRIMARY KEY("dataar= eaid", "accountnumber", "amountmst", "startdate"),
  CONSTRAINT = "account_ranges_salesunits_fkey" FOREIGN KEY ("salesunit")
  &= nbsp; REFERENCES "public"."salesunits"("salesunit")
    = ON DELETE NO ACTION
    ON UPDATE NO ACTION
 &nbs= p;  NOT DEFERRABLE
) WITHOUT OIDS;
 
CREATE OR REPLACE FUNCTION "public"."fnc_idx_dataareaid" (p_fileid = integer) RETURNS varchar AS
$body$
DECLARE
  v_dataareaid = exact_files.dataareaid%TYPE;
BEGIN
  SELECT dataareaid INTO = v_dataareaid FROM exact_files WHERE id =3D p_fileid;
  RETURN = v_dataareaid;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE RETURNS = NULL ON NULL INPUT SECURITY INVOKER;
--=__Part72548BCB.0__=--