[SQL] Common table expression - parsing questions

2009-10-03 Thread the6campbells 
Couple of questions:

1. Why does Postgres not throw a parsing error during sqlPrepare for this
statement vs at sqlExecute

with t_cte ( c1, ctr ) as (
select 1,0 from tversion union
select 2,0 from tversion union all
select c1, ctr + 1 from t_cte where c1=1 and ctr < 5 union all
select c1, ctr + 1 from t_cte where c1=2 and ctr < 5)
select c1, ctr from t_cte

2. Do you intend to remove the requirement to include the recursive keyword
- as other vendors allow

3. Is it a documented restriction that you can only have one reference to
the CTE .. see above example which fails while this modified version works.
The former
works in other vendors.

with recursive t_cte ( c1, ctr ) as (
select 1,0 from tversion union
select 2,0 from tversion union all
select c1, ctr + 1 from t_cte where c1=1 and ctr < 5 )
select c1, ctr from t_cte

[SQL] Need even more magic. Now for tricky counts.

2009-10-03 Thread Andreas 

Hi,

there is a vast log-table that collects several state data for objects.
(log_id, project_fk, object_fk, state_fk, log_type_fk, 
created_on::timestamp, ...)

log_id   is a sequence,
project_fk  foreign key on a project-table
object_fk  foreign key on a object-table
state_fk   can have 10 values0, 10, 20, 30, ...
log_type_fk   describes the event that caused the entry

I need counts of states of objects for a project starting at a given 
date t0 in 14 days distances.


Because I need a row for every reporting day, I started out by creating 
a view that selects the relevant project_fk and only those log_type_fk 
that MIGHT be relevant.   state_fk = 0 is irrelevant, too.
The same view does a case when ... for every state_fk so that I can add 
them up later to get a cross-table.

e.g.
case when state_fk = 10 then 1 else 0 end as sate_10,
case when state_fk = 20 then 1 else 0 end as sate_20,
...
Then the view adds a integer-column  period_nr  that represents the nr 
of 14 day periods since t0.
In the first 14 days have period_nr = 0, in the second 14 days it is 1 
and so on.



Now I need a query that calculates the sum for every column state_10, 
state_20, ..., state_90 from t0 to the current period_nr.
t0  until  t0 + 1 * 14 days   ===>   count(state_10),  count(state_20),  
count(state_30) ...

t0  until  t0 + 2 * 14 days
...

This would be nice.
I'd be glad if you could hint me up to here.


Even nicer would be a solution that adds just the last occurance for 
every object_fk within the current t0 - period.

e.g.
object_fk = 42   might appear
in period 1 with state 50
in period 3 twice with state 40 and 20

The report should count it in period  1+2  as 50
and in period 3 and further just 1 time as 20 until the object gets 
logged again.


This might prove to be a wee bit tricky.


--
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] Common table expression - parsing questions

2009-10-03 Thread Thomas Kellerer 

the6campbells wrote on 29.09.2009 04:54:
2. Do you intend to remove the requirement to include the recursive 
keyword - as other vendors allow


The standard *requires* the keyword. 


As far as I can tell there are two DBMS that require it (Postgres, Firebird) 
and two that don't (SQL Server and Oracle with the newest release)

Thomas


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