Skip site navigation (1) Skip section navigation (2)

return field from different table conditionally

From: "Bart Degryse" <Bart(dot)Degryse(at)indicator(dot)be>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: return field from different table conditionally
Date: 2008-02-13 10:54:14
Message-ID: 47B2DA66.A3DD.0030.0@indicator.be (view raw or flat)
Thread:
Lists: pgsql-sql
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;

Responses

pgsql-sql by date

Next:From: Campbell, LanceDate: 2008-02-13 16:30:31
Subject: Like problem
Previous:From: Steve MidgleyDate: 2008-02-12 18:11:39
Subject: Re: Usage of UUID with 8.3 (Windows)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group