[SQL] dynamic events categorization
Hello,
I'm looking for a more efficient way of dynamically categorizing some
events. The following view definition looks into each event's latest
event_date object (a theater play can have several, a book only one) to
tell whether the event is current, past or future:
SELECT s.id_event_subtype, s.subtype, t.id_event_type, t.type,
e.id_event, e.created_by, e.created_on, e.modified_by, e.modified_on,
e.id_image, e.show_name, e.length, d.id_date,
d.start_date, d.end_date, d.low_price, d.high_price, d.id_location,
d.showtime,
CASE
WHEN d.start_date <= 'now'::text::date AND
CASE
WHEN t.type = 'movie'::text THEN
d.start_date >= ('now'::text::date - 21)
WHEN t.type = 'book'::text THEN
e.created_on >= ('now'::text::date - 28)
ELSE d.end_date >= 'now'::text::date OR
d.end_date IS NULL
END THEN '0_current'::text
WHEN d.start_date > 'now'::text::date THEN
'1_future'::text
WHEN d.start_date IS NOT NULL THEN
'2_past'::text
ELSE ''::text
END AS timing
FROM event e
NATURAL JOIN event_type2 t
LEFT JOIN event_subtype2 s USING (id_event_subtype)
LEFT JOIN show_date d USING (id_event);
This view is widely used in my application, including as a basis for
further views, as I almost always need to know the 'timing' category of
an event (past, current, future). But I have nagging doubts about its
efficiency. It also seems pretty slow in its current form.
Any suggestion on how to improve it (including schema modifications) are
more than welcome.
Thanks,
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] ANSI Standard
Hi, How do I know if a function (or a certain sql syntax) in Postgres is a SQL ANSI Standard, hence it works on all databases such as MySQL, SQL Server, Oracle. I noticed that concat, decode, nvl, instr are functions that work for some databases and don't for others. I only want to use ANSI Standard functions that work everywhere. How do I identify them? How do I know if a function is specific to Postgres and don't work for other databases. Sorry, I may have asked the same question more than a time But I just wanted it to be clear I'd appreciate any reply to my question Thanks Pascal
[SQL] Verify Execute
Hello
Is there a way to find out if the EXECUTE of a UPDATE, INSERT has been
completed successfully?
Here is an example of what I do:
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Form_Store_PSQL"
Set oRs = Server.CreateObject("ADODB.Recordset" )
strSQL = "INSERT INTO TABLE_TEMP (ID) VALUES ('1')"
conn.Execute strSQL
Thank you
Shavonne Wijesinghe
Re: [SQL] Verify Execute
On Tuesday 24 June 2008 17:18:22 Shavonne Marietta Wijesinghe wrote:
> Hello
>
> Is there a way to find out if the EXECUTE of a UPDATE, INSERT has been
> completed successfully?
>
> Here is an example of what I do:
>
> Set conn = Server.CreateObject("ADODB.Connection")
> conn.Open "Form_Store_PSQL"
>
> Set oRs = Server.CreateObject("ADODB.Recordset" )
> strSQL = "INSERT INTO TABLE_TEMP (ID) VALUES ('1')"
> conn.Execute strSQL
If no Exception is thrown, the execution of the query was successfull.
Note that usually INSERT, UPDATE and DELETE return an integer > 0, indicating
affected rows, but not always. There might be 0 affected rows, which is not
nessesarily an error.
--
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11 | know how to do a thing and to watch |
PO. Box 529 Skøyen | somebody else doing it wrong, without |
0214 Oslo | comment.|
NORWAY | |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
+-+
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Sequential event query
I have a table that includes the following columns: event_time timestamptz device_id integer event_type integer ... There are hundreds of unique device_ids, about ten event_types and millions of records in the table. Devices can run the gamut from idle to fully utilized so for any given time-period a device might have anywhere from zero to thousands of events. I am trying to concoct two queries. 1: Analysis query to determine the distribution of sequential event_types. For example, if the event_types, in chronological order, were: 1 3 1 4 4 5 4 2 2 2 4 4 7 4 4 I would want to get: event_type, sequential_events, occurrences 1,1,2 2,3,1 3,1,1 4,1,1 4,2,3 5,1,1 7,1,1 2: Listing of all devices where the most recent N events are all identical. As noted above, the varying load on the devices means that for device 1, the last N might be the last 2 minutes but for device 3 it might be a day or two. I am looking for a query that will list any device having no variation in the recent events. Cheers, Steve -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ANSI Standard
On Tue, Jun 24, 2008 at 7:33 AM, Pascal Tufenkji <[EMAIL PROTECTED]> wrote: > How do I know if a function (or a certain sql syntax) in Postgres is a SQL > ANSI Standard, hence it works on all databases such as MySQL, SQL Server, > Oracle… > I noticed that concat, decode, nvl, instr are functions that work for some > databases and don't for others. > > I only want to use ANSI Standard functions that work everywhere. Well the problem is that most database do not support that same full set of ANSI features. So if you design your schema with a wide range of ansi features, certain ddl will probably not commit or at least will be ignored on most databases. We database brand has incorporated a different set of features. > How do I identify them? This should get you started : http://www.postgresql.org/docs/8.3/interactive/features.html -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
