Re: [SQL] JOIN and aggregate problem

2009-02-20 Thread Bob Henkel
How about this? CREATE TABLE t1(d INT,s INT, c INT); CREATE UNIQUE INDEX idx01_t1 ON t1 USING btree (d, s, c); INSERT INTO t1 (d, s, c) VALUES (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5); CREATE TABLE t2(d INT,s INT, c INT, x boolean); INSERT INTO t2(d, s, c, x) VALUES (1,1,1,TRUE),(1,1,1,FALSE),

Re: [SQL] JOIN and aggregate problem

2009-02-20 Thread Bob Henkel
Scratch this one won't work for you. On Fri, Feb 20, 2009 at 1:03 PM, Bob Henkel wrote: > I might be missing something but does this solve your issue? > > CREATE TABLE t1(d INT,s INT, c INT); > > CREATE UNIQUE INDEX idx01_t1 > ON t1 USING btree (d, s, c); > > INSERT INTO t1 (d, s, c) > VALUES (1,

Re: [SQL] JOIN and aggregate problem

2009-02-20 Thread Stephan Szabo
On Fri, 20 Feb 2009, Tarlika Elisabeth Schmitz wrote: > I have 2 tables T1 and T2 > > T1 has the columns: D, S, C. The combination of D,S,C is unique. > T2 has the columns: D, S, C, and boolean X. The combination of D,S,C is > not unique. > > I need to produce the following result for every occurr

Re: [SQL] JOIN and aggregate problem

2009-02-20 Thread Richard Huxton
Tarlika Elisabeth Schmitz wrote: > I have 2 tables T1 and T2 > > T1 has the columns: D, S, C. The combination of D,S,C is unique. > T2 has the columns: D, S, C, and boolean X. The combination of D,S,C is > not unique. > > I need to produce the following result for every occurrence of T1: > D,S,C

Re: [SQL] JOIN and aggregate problem

2009-02-20 Thread Bob Henkel
I might be missing something but does this solve your issue? CREATE TABLE t1(d INT,s INT, c INT); CREATE UNIQUE INDEX idx01_t1 ON t1 USING btree (d, s, c); INSERT INTO t1 (d, s, c) VALUES (1,1,1),(2,2,2),(3,3,3),(4,4,4); CREATE TABLE t2(d INT,s INT, c INT, x boolean); INSERT INTO t2(d, s, c, x

[SQL] JOIN and aggregate problem

2009-02-20 Thread Tarlika Elisabeth Schmitz
I have 2 tables T1 and T2 T1 has the columns: D, S, C. The combination of D,S,C is unique. T2 has the columns: D, S, C, and boolean X. The combination of D,S,C is not unique. I need to produce the following result for every occurrence of T1: D,S,C, COUNT COUNT is the number of matching D,S,C co

Re: [SQL] How concat 3 strings if 2 are not empty?

2009-02-20 Thread M.P.Dankoor
Andreas, Kretschmer was quite close, try following: case when trim(coalesce(s1,'')) = '' and trim(coalesce(s3,'')) = '' then '' when trim(coalesce(s1,'')) != '' and trim(coalesce(s2,'')) != '' and trim(coalesce(s3,'')) != '' then s1 || s2 || s3 else trim(coalesce(s1,'')) || trim(coalesce

Re: [SQL] Creating a function with single quotes

2009-02-20 Thread Leif B. Kristensen
On Friday 20. February 2009, Adrian Klaver wrote: >On Friday 20 February 2009 6:29:43 am Leif B. Kristensen wrote: >> About twenty years ago I wrote a lot of Turbo Pascal code, and IIRC >> semicolon after an END was allowed but considered bad style. The rules concerning ENDs and semicolons in Pasc

Re: [SQL] Creating a function with single quotes

2009-02-20 Thread Adrian Klaver
On Friday 20 February 2009 6:29:43 am Leif B. Kristensen wrote: > On Friday 20. February 2009, Adrian Klaver wrote: > >Actually you need both semicolons. One after the RETURN statement and > > one after the END statement > >See below for full details: > >http://www.postgresql.org/docs/8.3/interacti

Re: Fwd: Re: [SQL] Creating a function with single quotes

2009-02-20 Thread Shawn Tayler
Ah.. Missed that one. Thank you Adrian! Shawn On Fri, 2009-02-20 at 06:27 -0800, Adrian Klaver wrote: > Actually you need both semicolons. One after the RETURN statement and > one after > the END statement > See below for full details: > http://www.postgresql.org/docs/8.3/interactive/plp

Re: [SQL] Creating a function with single quotes

2009-02-20 Thread Leif B. Kristensen
On Friday 20. February 2009, Adrian Klaver wrote: >Actually you need both semicolons. One after the RETURN statement and > one after the END statement >See below for full details: >http://www.postgresql.org/docs/8.3/interactive/plpgsql-structure.html I see the documentation, but empirically you d

Fwd: Re: [SQL] Creating a function with single quotes

2009-02-20 Thread Adrian Klaver
Memo to self: Remember hit reply all. -- Forwarded Message -- Subject: Re: [SQL] Creating a function with single quotes Date: Friday 20 February 2009 From: Adrian Klaver To: "Leif B. Kristensen" On Friday 20 February 2009 6:13:03 am you wrote: > On Friday 20. February 2009, S

Re: [SQL] Creating a function with single quotes

2009-02-20 Thread Shawn Tayler
Hi Leif! Thank you to you and the group. Worked like a charm. The SQL language was the key Shawn On Fri, 2009-02-20 at 15:12 +0100, Leif B. Kristensen wrote: > You should place a semicolon at the end of the RETURN line, and > remove > the one after END, > > BTW, simple functions as th

Re: [SQL] Creating a function with single quotes

2009-02-20 Thread Leif B. Kristensen
On Friday 20. February 2009, Shawn Tayler wrote: >Hello Jasen and the List, > >I tried the $$ quote suggestion: > >create function f_csd_interval(integer) returns interval as >$$ >BEGIN >RETURN $1 * interval '1 msec' >END; >$$ >LANGUAGE 'plpgsql'; > >Here is what I got: > >edacs=# \i 'f_csd_interva

Re: [SQL] Creating a function with single quotes

2009-02-20 Thread Shawn Tayler
Hello Jasen and the List, I tried the $$ quote suggestion: create function f_csd_interval(integer) returns interval as $$ BEGIN RETURN $1 * interval '1 msec' END; $$ LANGUAGE 'plpgsql'; Here is what I got: edacs=# \i 'f_csd_interval.sql' psql:f_csd_interval.sql:7: ERROR: syntax error at or n

Re: [SQL] Creating a function with single quotes

2009-02-20 Thread Jasen Betts
On 2009-02-19, Shawn Tayler wrote: > Hello, > > This has me befuddled. I am trying create a simple experiment, rather > new to SQL and I am running into an issue with single quotes. All I can > find on creating a function states the procedure should be contained > within single quotes. My probl