Обсуждение: Need help building SQL query

Hello,

I have a problem as to which I do not know
how to solve. My tables are built like this:

Timereportbreak consists of four breaks for
each timereport. They are ordered by the
indexnumber (0-3). They are connected to the
timereport table using the column timereportid.

[code]
CREATE TABLE timereportbreak
(
  timereportid int4 NOT NULL DEFAULT 0,
  starttime time NOT NULL DEFAULT '00:00:00'::time without time zone,
  endtime time NOT NULL DEFAULT '00:00:00'::time without time zone,
  indexnumber int4 NOT NULL DEFAULT 0
)
WITH OIDS;
ALTER TABLE timereportbreak OWNER TO postgres;
[/code]

Timereport consists of the following columns..

[code]
CREATE TABLE timereport
(
  employeesignature varchar(6) NOT NULL DEFAULT ''::character varying,
  startdate date,
  workedtimetotal time NOT NULL DEFAULT '00:00:00'::time without time zone,
  starttime time NOT NULL DEFAULT '00:00:00'::time without time zone,
  endtime time NOT NULL DEFAULT '00:00:00'::time without time zone,
  statuscode int2 NOT NULL DEFAULT 0,
  note text NOT NULL DEFAULT ''::text,
  internalinfo varchar(80) NOT NULL DEFAULT ''::character varying,
  id int4 NOT NULL DEFAULT nextval('timereport_id_seq'::text),
  CONSTRAINT "TimeReport_pkey" PRIMARY KEY (id)
)
WITH OIDS;
ALTER TABLE timereport OWNER TO postgres;
[/code]


What I would like to retrieve is all columns from timereport
and the four timereportbreaks connected to the timereport table.
However, since each break is not a separate column I am not
sure how to solve this. And since this database already has data
in it. I can't remodel the table. This is the output I want:

timereport.*, timereportbreak0start, timereportbreak0end,
timereportbreak1start, timereportbreak1end, ...



I did try something like this but using LIMIT makes it faulty and errornous:

[code]
CREATE OR REPLACE FUNCTION timereport_getbydateandemployee("varchar", date,
date, "varchar")
  RETURNS SETOF timereportandbreaks AS
$BODY$
SELECT DISTINCT
  timereport.employeesignature,
  timereport.startdate,
  timereport.workedtimetotal,
  timereport.starttime,
  timereport.endtime,
  timereport.statuscode,
  timereport.note,
  timereport.internalinfo,
  timereport.id
       ,(SELECT timereportbreak.starttime FROM timereportbreak, timereport,
employee
           WHERE
             (timereport.startdate BETWEEN $2 AND $3)
             AND
             (upper(timereport.employeesignature) = upper($4))
             AND
             upper(timereport.employeesignature) = upper(employee.signature)
             AND
             employee.companyid = $1
             AND
             timereport.id = timereportbreak.timereportid
             AND
             timereportbreak.indexnumber = '0'
           LIMIT '1'
        ) AS break1start
       ,(SELECT timereportbreak.endtime FROM timereportbreak, timereport,
employee
           WHERE
             (timereport.startdate BETWEEN $2 AND $3)
             AND
             (upper(timereport.employeesignature) = upper($4))
             AND
             upper(timereport.employeesignature) = upper(employee.signature)
             AND
             employee.companyid = $1
             AND
             timereport.id = timereportbreak.timereportid
             AND
             timereportbreak.indexnumber = '0'
           LIMIT '1'
        ) AS break1end
       ,(SELECT timereportbreak.starttime FROM timereportbreak, timereport,
employee
           WHERE
             (timereport.startdate BETWEEN $2 AND $3)
             AND
             (upper(timereport.employeesignature) = upper($4))
             AND
             upper(timereport.employeesignature) = upper(employee.signature)
             AND
             employee.companyid = $1
             AND
             timereport.id = timereportbreak.timereportid
             AND
             timereportbreak.indexnumber = '1'
           LIMIT '1'
        ) AS break2start
       ,(SELECT timereportbreak.endtime FROM timereportbreak, timereport,
employee
           WHERE
             (timereport.startdate BETWEEN $2 AND $3)
             AND
             (upper(timereport.employeesignature) = upper($4))
             AND
             upper(timereport.employeesignature) = upper(employee.signature)
             AND
             employee.companyid = $1
             AND
             timereport.id = timereportbreak.timereportid
             AND
             timereportbreak.indexnumber = '1'
           LIMIT '1'
        ) AS break2end
       ,(SELECT timereportbreak.starttime FROM timereportbreak, timereport,
employee
           WHERE
             (timereport.startdate BETWEEN $2 AND $3)
             AND
             (upper(timereport.employeesignature) = upper($4))
             AND
             upper(timereport.employeesignature) = upper(employee.signature)
             AND
             employee.companyid = $1
             AND
             timereport.id = timereportbreak.timereportid
             AND
             timereportbreak.indexnumber = '2'
           LIMIT '1'
        ) AS break3start
       ,(SELECT timereportbreak.endtime FROM timereportbreak, timereport,
employee
           WHERE
             (timereport.startdate BETWEEN $2 AND $3)
             AND
             (upper(timereport.employeesignature) = upper($4))
             AND
             upper(timereport.employeesignature) = upper(employee.signature)
             AND
             employee.companyid = $1
             AND
             timereport.id = timereportbreak.timereportid
             AND
             timereportbreak.indexnumber = '2'
           LIMIT '1'
        ) AS break3end
       ,(SELECT timereportbreak.starttime FROM timereportbreak, timereport,
employee
           WHERE
             (timereport.startdate BETWEEN $2 AND $3)
             AND
             (upper(timereport.employeesignature) = upper($4))
             AND
             upper(timereport.employeesignature) = upper(employee.signature)
             AND
             employee.companyid = $1
             AND
             timereport.id = timereportbreak.timereportid
             AND
             timereportbreak.indexnumber = '3'
           LIMIT '1'
        ) AS break4start
       ,(SELECT timereportbreak.endtime FROM timereportbreak, timereport,
employee
           WHERE
             (timereport.startdate BETWEEN $2 AND $3)
             AND
             (upper(timereport.employeesignature) = upper($4))
             AND
             upper(timereport.employeesignature) = upper(employee.signature)
             AND
             employee.companyid = $1
             AND
             timereport.id = timereportbreak.timereportid
             AND
             timereportbreak.indexnumber = '3'
           LIMIT '1'
        ) AS break4end
FROM
  timereport, timereportbreak, employee
WHERE
  (timereport.startdate BETWEEN $2 AND $3)
  AND
  (upper(timereport.employeesignature) = upper($4))
  AND
  (upper(timereport.employeesignature) = upper(employee.signature))
  AND
  (employee.companyid = $1)
  AND
  (timereportbreak.timereportid = timereport.id)
ORDER BY
  timereport.startdate,
  timereport.starttime
$BODY$
  LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION timereport_getbydateandemployee("varchar", date, date,
"varchar") OWNER TO postgres;
[/code]



Any help would be tremendously appretiated.

// Kind Regards Robert

On Mon, Dec 05, 2005 at 10:28:04 +0100,
  Robert Blixt <robert.blixt@transpa.se> wrote:
>
> Timereportbreak consists of four breaks for
> each timereport. They are ordered by the
> indexnumber (0-3). They are connected to the
> timereport table using the column timereportid.
>
> What I would like to retrieve is all columns from timereport
> and the four timereportbreaks connected to the timereport table.
> However, since each break is not a separate column I am not
> sure how to solve this. And since this database already has data
> in it. I can't remodel the table. This is the output I want:

Normally you want to do this by joining to Timereportbreak once for each
break (i.e. 4 times) using a different table alias for each join. Then
you can use the a different condition on indexnumber for each alias to
get the appropiate break.

Who can I ask about large scale use of postgresql installations in a
large company? What are the gotchas? Why
would you pay for Oracle if can use postgresql especially if it's just
for data warehousing and not a transactional.

Sean


-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Bruno Wolff III
Sent: Monday, December 05, 2005 11:46 AM
To: Robert Blixt
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Need help building SQL query

On Mon, Dec 05, 2005 at 10:28:04 +0100,
  Robert Blixt <robert.blixt@transpa.se> wrote:
>
> Timereportbreak consists of four breaks for each timereport. They are
> ordered by the indexnumber (0-3). They are connected to the timereport

> table using the column timereportid.
>
> What I would like to retrieve is all columns from timereport and the
> four timereportbreaks connected to the timereport table.
> However, since each break is not a separate column I am not sure how
> to solve this. And since this database already has data in it. I can't

> remodel the table. This is the output I want:

Normally you want to do this by joining to Timereportbreak once for each
break (i.e. 4 times) using a different table alias for each join. Then
you can use the a different condition on indexnumber for each alias to
get the appropiate break.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

> Who can I ask about large scale use of postgresql
> installations in a
> large company? What are the gotchas? Why
> would you pay for Oracle if can use postgresql
> especially if it's just
> for data warehousing and not a transactional.
>
> Sean

Sean, the largest install that i'm aware of is
sesamestreet.org.  i'm not exactly sure how they use
it (together with php and apache), but they get 500k
hits/month - so it is a busy site.  again, i'm not
sure if pgsql is that busy.

i think most folks use oracle b/c

1. that is what they are sold.
2. the next guy is using it.
3. the PHB(pointy haird boss)es don't know enough to
ask their IT departments to evaluate if PGSQL could do
the job.
4. some folks believe the FUD and think that if you
don't pay for it, it must not work well.

there are definitely some situations where oracle
makes more sense than PGSQL, but the evaluation with
PGSQL should take place every time.  i'd venture to
guess PGSQL is never even mentioned as an alternative
in 95% of oracle installs, some of which could surely
be handled very well by PGSQL.

the word is starting to get out, though.  few business
leaders want to risk their status on anything they
perceive as "new," so as other implement it with
success, the herd of management sheep will surely
follow...  but it will take time.

i think Sun (or IBM???) has recently gotten involved
with PGSQL...  so it may get some improved marketing.



__________________________________________
Yahoo! DSL – Something to write home about.
Just $16.99/mo. or less.
dsl.yahoo.com

Sugrue, Sean wrote:
> Who can I ask about large scale use of postgresql installations in a
> large company? What are the gotchas? Why
> would you pay for Oracle if can use postgresql especially if it's just
> for data warehousing and not a transactional.

I'd state the opposite is the case. Oracle/DB2 is more compelling for
data warehousing but has little if no advantage in transactional
situations. You have more data partitioning capabilities with Oracle and
DB2 -- in a large enough install, those extra features may be a
significant performance win. (On the otherhand, you could throw extra
hardware at Postgres using the license costs so what's better/faster
depends on the exact situation.)

For transactional DBs, there's no compelling advantage at all (other
than CYA w/ PHBs). Everybody has triggers/rules/stored
procs/etc/etc/etc. Multi-master replication at the LAN level is the only
  feature I can think of that's missing from Postgres that could be used
for transaction systems.

> -----Ursprungligt meddelande-----
> Från: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-
> owner@postgresql.org] För Bruno Wolff III
> Skickat: den 5 december 2005 17:46
> Till: Robert Blixt
> Kopia: pgsql-novice@postgresql.org
> Ämne: Re: [NOVICE] Need help building SQL query
>
> On Mon, Dec 05, 2005 at 10:28:04 +0100,
>   Robert Blixt <robert.blixt@transpa.se> wrote:
> >
> > Timereportbreak consists of four breaks for
> > each timereport. They are ordered by the
> > indexnumber (0-3). They are connected to the
> > timereport table using the column timereportid.
> >
> > What I would like to retrieve is all columns from timereport
> > and the four timereportbreaks connected to the timereport table.
> > However, since each break is not a separate column I am not
> > sure how to solve this. And since this database already has data
> > in it. I can't remodel the table. This is the output I want:
>
> Normally you want to do this by joining to Timereportbreak once for each
> break (i.e. 4 times) using a different table alias for each join. Then
> you can use the a different condition on indexnumber for each alias to
> get the appropiate break.
>

Thanks for your help, highly appretiated.


Regards,
Robert

Case in point, my uncle is an Oracle database administrator for a Lockheed Martin site.  When I mentioned PL/pgSQL, he was shocked - he had no idea Postgres had a PL/SQL analog.

Unfortunately, he's too low down on the corporate food chain to push for a migration.