Re: return field from different table conditionally - Mailing list pgsql-sql

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,
  INOUT p_accnbr accounts.accountnumber%TYPE,
  INOUT p_lineamount account_ranges.amountmst%TYPE,
  OUT p_dimension accounts.dimension%TYPE, 
  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 = p_dataareaid AND accountnumber = p_accnbr AND amountmst = p_lineamount) AR USING (dataareaid, accountnumber)
  WHERE
    A.dataareaid = p_dataareaid AND
    A.accountnumber = p_accnbr AND
    S.salesunit = 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_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

 

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 = E.id AND B.transactionid BETWEEN 53965 AND 53980

 

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

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_ranges.amountmst and otherwise

the salesunit from accounts_new.

 

Some joins that would certainly have to be made

  billing_exact.fileid = exact_files.id 

  exact_files.dataareaid = accounts_new.dataareaid

  billing_exact.reknr = accounts_new.accountnumber

  accounts_new.accountnumber = account_ranges.accountnumber

  accounts_new.dataareaid = account_ranges.dataareaid

 

And then there must be something like

  if billing_exact.lineamountmst = 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")
VALUES ('pcs', '00:00:00');

 

INSERT INTO "public"."salesunits" ("salesunit", "pg_interval")
VALUES ('6m', '6 mons');

 

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');

 

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_unq" UNIQUE("fname"),
  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,
  "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", "transactionid"),
  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_exact"
  USING btree ("creationdate")
  WITH (fillfactor = 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"."salesunits"("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("dataareaid", "accountnumber", "amountmst", "startdate"),
  CONSTRAINT "account_ranges_salesunits_fkey" FOREIGN KEY ("salesunit")
    REFERENCES "public"."salesunits"("salesunit")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    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 = p_fileid;
  RETURN v_dataareaid;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER;