[SQL] return field from different table conditionally
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"),
[SQL] Like problem
8.2.5 I am having an issue with trying to use 'LIKE' so that I can match on a string with an underscore in it. What is the proper way to find the following string? Table t1 Column c1 String I want to match on 'abc_' SELECT c1 FROM t1 WHERE c1 LIKE '%abc\_%'; This gives me the following message: WARNING: nonstandard use of escape in a string literal LINE 1: ...ct c1 from t1 where c1 like '%abc\_%'; Remember I want an actual underscore. Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu
Re: [SQL] Like problem
Richard Huxton <[EMAIL PROTECTED]> writes: > Campbell, Lance wrote: >> WARNING: nonstandard use of escape in a string literal >> LINE 1: ...ct c1 from t1 where c1 like '%abc\_%'; > Either indicate you are using an escaped string: LIKE E'%abc\_%' Actually that's wrong, what he'd need is LIKE E'%abc\\_%' (or omit the E and ignore the warning). Alternatively, set standard_conforming_strings to TRUE and write LIKE '%abc\_%' ... but beware that that might break other parts of your app that are expecting backslash to be special. > Or, change the escape character: LIKE '%abcQ_%' ESCAPE 'Q' Yeah, this might be the easiest localized solution. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Like problem
Richard, The first example you gave me does not work. Below is the test example I used (this example should NOT return 'matched'): SELECT 'matched' WHERE 'abcgxyz' LIKE E'%abc\_x%'; ?column? -- matched (1 row) The second example you gave me does work: SELECT 'matched' WHERE 'abcgxyz' LIKE '%abcQ_x%' ESCAPE 'Q'; ?column? -- 0 rows returned SELECT 'matched' WHERE 'abc_xyz' LIKE '%abcQ_x%' ESCAPE 'Q'; ?column? -- matched 1 row Why does the first example not work? I have also tried the following (the below should not work if they are correct): SELECT 'matched' WHERE 'abcgxyz' LIKE '%abc' || E'\_' || 'x%'; ?column? -- matched (1 row) SELECT 'matched' WHERE 'abcgxyz' LIKE '%abc' || E'_' || 'x%'; ?column? -- matched (1 row) Do you have any thoughts on why none of these examples work with the 'E'? Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 13, 2008 10:42 AM To: Campbell, Lance Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Like problem Campbell, Lance wrote: > 8.2.5 > > I am having an issue with trying to use 'LIKE' so that I can match on a > string with an underscore in it. What is the proper way to find the > following string? > WARNING: nonstandard use of escape in a string literal > > LINE 1: ...ct c1 from t1 where c1 like '%abc\_%'; Either indicate you are using an escaped string: LIKE E'%abc\_%' Or, change the escape character: LIKE '%abcQ_%' ESCAPE 'Q' -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Like problem
Campbell, Lance wrote: 8.2.5 I am having an issue with trying to use 'LIKE' so that I can match on a string with an underscore in it. What is the proper way to find the following string? WARNING: nonstandard use of escape in a string literal LINE 1: ...ct c1 from t1 where c1 like '%abc\_%'; Either indicate you are using an escaped string: LIKE E'%abc\_%' Or, change the escape character: LIKE '%abcQ_%' ESCAPE 'Q' -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] return field from different table conditionally
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
the data is still there, but it is now a record type
fnc_test
"(lil,700011,159.,fracimal-2,fracimal,6m,ALRN,""6 mons"")"
"(lil,700011,139.,fracimal-2,fracimal,1y,ALRN,""1 year"")"
"(lil,700011,159.,fracimal-2,fracimal,6m,ALRN,""6 mons"")"
"(lil,700011,139.,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" <[EMAIL PROTECTED]> 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
Re: [SQL] Like problem
Tom Lane wrote: Richard Huxton <[EMAIL PROTECTED]> writes: Campbell, Lance wrote: WARNING: nonstandard use of escape in a string literal LINE 1: ...ct c1 from t1 where c1 like '%abc\_%'; Either indicate you are using an escaped string: LIKE E'%abc\_%' Actually that's wrong, what he'd need is LIKE E'%abc\\_%' (or omit the E and ignore the warning). need to switch to a better coffee... -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Like problem
Tom, From your comments the recommended approach moving forward would be to use ESCAPE and define your escape character? Thanks for your help, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 13, 2008 10:53 AM To: Richard Huxton Cc: Campbell, Lance; pgsql-sql@postgresql.org Subject: Re: [SQL] Like problem Richard Huxton <[EMAIL PROTECTED]> writes: > Campbell, Lance wrote: >> WARNING: nonstandard use of escape in a string literal >> LINE 1: ...ct c1 from t1 where c1 like '%abc\_%'; > Either indicate you are using an escaped string: LIKE E'%abc\_%' Actually that's wrong, what he'd need is LIKE E'%abc\\_%' (or omit the E and ignore the warning). Alternatively, set standard_conforming_strings to TRUE and write LIKE '%abc\_%' ... but beware that that might break other parts of your app that are expecting backslash to be special. > Or, change the escape character: LIKE '%abcQ_%' ESCAPE 'Q' Yeah, this might be the easiest localized solution. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Like problem
Tom Lane answered this question in a prior post. Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Campbell, Lance Sent: Wednesday, February 13, 2008 10:59 AM To: Richard Huxton Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Like problem Richard, The first example you gave me does not work. Below is the test example I used (this example should NOT return 'matched'): SELECT 'matched' WHERE 'abcgxyz' LIKE E'%abc\_x%'; ?column? -- matched (1 row) The second example you gave me does work: SELECT 'matched' WHERE 'abcgxyz' LIKE '%abcQ_x%' ESCAPE 'Q'; ?column? -- 0 rows returned SELECT 'matched' WHERE 'abc_xyz' LIKE '%abcQ_x%' ESCAPE 'Q'; ?column? -- matched 1 row Why does the first example not work? I have also tried the following (the below should not work if they are correct): SELECT 'matched' WHERE 'abcgxyz' LIKE '%abc' || E'\_' || 'x%'; ?column? -- matched (1 row) SELECT 'matched' WHERE 'abcgxyz' LIKE '%abc' || E'_' || 'x%'; ?column? -- matched (1 row) Do you have any thoughts on why none of these examples work with the 'E'? Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 13, 2008 10:42 AM To: Campbell, Lance Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Like problem Campbell, Lance wrote: > 8.2.5 > > I am having an issue with trying to use 'LIKE' so that I can match on a > string with an underscore in it. What is the proper way to find the > following string? > WARNING: nonstandard use of escape in a string literal > > LINE 1: ...ct c1 from t1 where c1 like '%abc\_%'; Either indicate you are using an escaped string: LIKE E'%abc\_%' Or, change the escape character: LIKE '%abcQ_%' ESCAPE 'Q' -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
