[Phpcompta-dev] r266 - trunk/coprop/sql

[phpcompta-dev]

Author: danydb
Date: 2012-01-07 23:42:05 +0100 (Sat, 07 Jan 2012)
New Revision: 266

Modified:
   trunk/coprop/sql/create_view_summary.sql.sql
Log:

update view


Modified: trunk/coprop/sql/create_view_summary.sql.sql
===================================================================
--- trunk/coprop/sql/create_view_summary.sql.sql        2012-01-07 04:59:00 UTC 
(rev 265)
+++ trunk/coprop/sql/create_view_summary.sql.sql        2012-01-07 22:42:05 UTC 
(rev 266)
@@ -1,14 +1,11 @@
-create view coprop.summary as select a.f_id,
-(select ad_value from fiche_detail as fd1 where fd1.f_id=a.f_id and 
fd1.ad_id=1) as lot_name,
-(select ad_value from fiche_detail as fd1 where fd1.f_id=a.f_id and 
fd1.ad_id=23) as lot_qcode,
-m.ad_value as building_id,
-(select ad_value from fiche_detail as fd1 where fd1.f_id=m.ad_value::numeric 
and fd1.ad_id=1) as immeuble,
-c.ad_value as coprop_id,
-(select ad_value from fiche_detail as fd1 where fd1.f_id=c.ad_value::numeric 
and fd1.ad_id=1) as coprop
-
-  from fiche_detail as a
-   join fiche as f1 on (f1.f_id=a.f_id)
-   join (select f_id,ad_value from fiche_detail as fd1 where ad_id=9003) as m 
on (m.f_id=a.f_id)
-join (select f_id,ad_value from fiche_detail as fd1 where ad_id=9002) as c on 
(c.f_id=a.f_id)
-
-  where fd_id=8 and ad_id=1;
+CREATE OR REPLACE VIEW coprop.summary AS
+ SELECT a.f_id AS lot_id, m.ad_value AS building_id, c.ad_value AS coprop_id
+   FROM fiche_detail a
+   JOIN fiche f1 ON f1.f_id = a.f_id
+   JOIN ( SELECT fd1.f_id, fd1.ad_value
+      FROM fiche_detail fd1
+     WHERE fd1.ad_id = 70) m ON m.f_id = a.f_id
+   JOIN ( SELECT fd1.f_id, fd1.ad_value
+   FROM fiche_detail fd1
+  WHERE fd1.ad_id = 71) c ON c.f_id = a.f_id
+  WHERE f1.fd_id = 8 AND a.ad_id = 1;
\ No newline at end of file