Re: [SQL] dynamic events categorization

[Marc Mamin]

Hello,

I guess that the time offsets (now-21 and now-28) are evaluated each
time the corresponding condition is met.
It may be faster to put them into a separate sub query. I'm not sure
about putting "now" itself within the sub query...

It may also be better to put your query in a procedure where you can put
these constants into variables instead of using a sub query.

Depending of the distribution of a) 2_past,1_future,0_current and '' and
b) t.type,  it may be worth to have different queries, bound with UNION
ALL. This would simplify the "CASE" construct and at least part of the
tests should happen on indexes only.


If the query is run very often, you may want to add a boolean column
is_past on show_date, and have a separate job that put the concerned
records to true every x minutes ...


HTH,

Marc Mamin





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 >= c.a
WHEN t.type = 'book'::text THEN
e.created_on >= c.b
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 
-- added sub query:
(select 'now'::text::date - 21 as a,  'now'::text::date - 28 as
b) c,
event e
NATURAL JOIN event_type2 t
LEFT JOIN event_subtype2 s USING (id_event_subtype)
LEFT JOIN show_date d USING (id_event);




-- 
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

[Steve Midgley]

At 02:20 AM 6/25/2008, [EMAIL PROTECTED] wrote:

Date: Tue, 24 Jun 2008 17:33:11 +0300
From: "Pascal Tufenkji" <[EMAIL PROTECTED]>
To: 
Subject: ANSI Standard
Message-ID: <[EMAIL PROTECTED]>

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.


In general, I find that the Pg docs pretty clear state what is ANSI 
standard and what isn't within Pg. You can also view the ANSI-92 
standard here:


http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

In terms of making sure you're cross platform compatible, I'd say you 
have to designate a series of platforms (e.g. MySQL 5, Pg 8.3, Oracle 
X, MS SQL X, ext) which you will test against and explicitly support. 
You will find that no matter how tightly you attempt to build your 
platform against ANSI-92 (or any other std) if you do not regularly 
test against a set of platforms, your solution will converge on 
supporting only the platforms you do regular test against.


I hope that helps,

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

[Montaseri]

- Try SQL 2003 standards92 is way old
- You'll find that even the big boys like Oracle, DB2 etc will diverge from
SQL standards if they make more $$ thier way...let alone toys like MySQL and
MS-SQL

Cheers
Medi

On Thu, Jun 26, 2008 at 12:19 PM, Steve Midgley <[EMAIL PROTECTED]> wrote:

> At 02:20 AM 6/25/2008, [EMAIL PROTECTED] wrote:
>
>> Date: Tue, 24 Jun 2008 17:33:11 +0300
>> From: "Pascal Tufenkji" <[EMAIL PROTECTED]>
>> To: 
>> Subject: ANSI Standard
>> Message-ID: <[EMAIL PROTECTED]>
>>
>> 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.
>>
>
> In general, I find that the Pg docs pretty clear state what is ANSI
> standard and what isn't within Pg. You can also view the ANSI-92 standard
> here:
>
> http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
>
> In terms of making sure you're cross platform compatible, I'd say you have
> to designate a series of platforms (e.g. MySQL 5, Pg 8.3, Oracle X, MS SQL
> X, ext) which you will test against and explicitly support. You will find
> that no matter how tightly you attempt to build your platform against
> ANSI-92 (or any other std) if you do not regularly test against a set of
> platforms, your solution will converge on supporting only the platforms you
> do regular test against.
>
> I hope that helps,
>
> Steve
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

[SQL] exclude part of result

[Tarlika Elisabeth Schmitz]

SELECT DISTINCT a, b, c, now(), count(item_pk) 
FROM product
LEFT JOIN item ON item.product_fk = product_pk
WHERE ...
GROUP BY a, b, c


I have another table 'navigation' which also has the columns a,b,c

If the combination of (a,b,c) exists in 'navigation', then exclude it
from above result. How can I achieve this?

--


Best Regards,

Tarlika Elisabeth Schmitz


A: Because it breaks the logical sequence of discussion
Q: Why is top posting bad? 

-- 
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] exclude part of result

[A. Kretschmer]

am  Fri, dem 27.06.2008, um  0:35:38 +0100 mailte Tarlika Elisabeth Schmitz 
folgendes:
> 
> SELECT DISTINCT a, b, c, now(), count(item_pk) 
> FROM product
> LEFT JOIN item ON item.product_fk = product_pk
> WHERE ...
> GROUP BY a, b, c
> 
> 
> I have another table 'navigation' which also has the columns a,b,c
> 
> If the combination of (a,b,c) exists in 'navigation', then exclude it
> from above result. How can I achieve this?

For instance with EXCEPT.


Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

-- 
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] exclude part of result

[Tarlika Elisabeth Schmitz]

On Thu, 26 Jun 2008 19:49:01 -0400 (EDT)
"Patricia Mitchell"<[EMAIL PROTECTED]> wrote:

> The columns (a,b,c) of the navigation table should not appear in the
> result because you are only pulling records from the product and item
> table.  You are pulling the records out of the product and item table
> with this statement:  'select a,b,c, count()from product LEFT
> JOIN item on'
> 
> 
> 
> P.M.
> 
>  --- On Thu 06/26, Tarlika Elisabeth Schmitz <
> [EMAIL PROTECTED] > wrote:
> 
> From: Tarlika Elisabeth Schmitz [mailto:
> [EMAIL PROTECTED]
> 
> To: pgsql-sql@postgresql.org
> 
> Date: Fri, 27 Jun 2008 00:35:38 +0100
> 
> Subject: [SQL] exclude part of result
> 

> 
> SELECT DISTINCT a, b, c, now(), count(item_pk) 
> FROM product
> LEFT JOIN item ON item.product_fk = product_pk
> WHERE ...
> GROUP BY a, b, c
> 
> 
> I have another table 'navigation' which also has the columns a,b,c
> 
> If the combination of (a,b,c) exists in 'navigation', then exclude it
> from above result. How can I achieve this?

Thank you for your response, Patricia.

Maybe I did not express myself clearly enough:
Example:

PRODUCT table :

A B C
100 200 300
100 200 301
100 205 300
100 205 301

NAVIGATION table
A B C #ITEMS
100 200 300 5
100 200 301 6



My query needs to return 
100 205 300 #items
100 205 301 #items
so I can insert them in NAVIGATION. NAVIGATION must not contain any
duplicate combinations of [a,b,c].




--


Best Regards,

Tarlika Elisabeth Schmitz


A: Because it breaks the logical sequence of discussion
Q: Why is top posting bad? 

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql