I jump again on this mail to ask for SQL help. Since I've not found a list on
pgsql-users or something like that, I apologize to post it to hackers..
So.. I'm porting from Oracle to PG. I have many CONNECT BY queries, luckyly,
only 2 tables are hierarchical. I've adopted OpenACS solution, since I am sure
it is the best way to do with that problem.
But, I found a problem wich I have no brain left today to resolve.
I port following Oracle CONNECT BY statment:
--ORACLE QUERY
--
--select
-- sum(t01_caf) SCAF,
-- sum(t01_itm_cnt) SART
--from T01_&DateData
--start with T01_upr_lvl_typ = &TypNiv and T01_upr_lvl_nbr = &Niv
--connect by prior T01_lvl_typ = T01_upr_lvl_typ and prior T01_lvl_nbr =
T01_upr_lvl_nbr
--
-- The execution in the Oracle DB returns:
--
--
-- SCAF SART
------------ ----------
--40164802,4 1404296
--
-- with variables &TypNiv = 0 et &Niv = 0
--
-- PG port:
--
\set TypNiv 0
\set Niv 0
--
select sum(t01_caf) as SCAF, sum(t01_itm_cnt) as SCAF
from t01_20011231
where strpos(t01_tree_sortkey,(select t01_tree_sortkey from t01_20011231
where t01_lvl_typ = :TypNiv and t01_lvl_nbr = :Niv))=1
group by ???;
The problem is that I am no longuer able to find the RIGHT group by statment :-/
Can someone help me ? I'm sure it is surely kind simplistic? dunno..
Ah! The purpose of the query is to sum values on all nodes children of one node.
Inthis crappy customer database, a node is identifyied uniquely with couple
(t01_lvl_typ,t01_lvl_nbr), ((couple t01_upr_lvl_typ,t01_upr_lvl_nbr identifies
uniquely the Father of the node)) because there can be nodes at different level
(lvl_typ) with the same identifyier (lvl_nbr). I dont want to user concat || to
create a pseudo-unique-identifyer, because I think there may be perfs problems
...
Thanks. Best regards & wishes.
--
Jean-Paul ARGUDO IDEALX S.A.S
Consultant bases de données 15-17, av. de Ségur
http://IDEALX.com/ F-75007 PARIS
