mvhub-dev team mailing list archive

[Saroun Sek <saroun_sek@xxxxxxxxxxxxxxx>]

Saroun Sek has proposed merging lp:~saroun-sek/mvhub/simplify_query_596605 into lp:mvhub.

Requested reviews:
  MVHub devs with commit rights (mvhub-commit)
Related bugs:
  #596605 simplify query PROGRAM_X_PROGRAM_AGENCY_ID
  https://bugs.launchpad.net/bugs/596605

-- 
https://code.launchpad.net/~saroun-sek/mvhub/simplify_query_596605/+merge/29602
Your team MVHub Developers is subscribed to branch lp:mvhub.

=== modified file 'app-mvhub/conf/sql_select.lib'
--- app-mvhub/conf/sql_select.lib	2010-07-03 16:28:30 +0000
+++ app-mvhub/conf/sql_select.lib	2010-07-09 19:27:43 +0000
@@ -710,10 +710,11 @@
       AND p.reminders_sent < ?
  
 [PROGRAM_X_HAS_NEWSLETTER]
-SELECT get_agency_name(agency_id) as agency_name, program_name, 
-      email, main_phone, news_letter_description
-FROM program WHERE news_letter = 'Yes' 
-ORDER BY agency_name, program_name;
+SELECT a.agency_name, p.program_name, 
+      p.email, p.main_phone, p.news_letter_description
+FROM program AS p JOIN agency AS a ON a.agency_id = p.agency_id
+WHERE news_letter = 'Yes' 
+ORDER BY agency_name, program_name
 
 [PROGRAM_X_LAST_UPDATED]
 SELECT 
@@ -747,11 +748,12 @@
 ORDER BY categories DESC, subq.program_and_agency_name
  
 [PROGRAM_X_PROGRAM_AGENCY_ID]
-SELECT program_id, 
-       get_agency_name(agency_id) || ': ' || program_name
-AS agency_program_name 
-FROM program ORDER BY agency_program_name
-		 
+SELECT p.program_id, a.agency_name || ': ' || program_name 
+	AS agency_program_name 
+FROM agency a,program p
+WHERE a.agency_id = p.agency_id 
+ORDER BY agency_program_name
+
 [PROGRAM_X_PROGRAM_CONTACT_FIRST_LAST_NAME_EMAIL]
 SELECT program_name, contact_first_name, contact_last_name, contact_email 
 FROM program 

=== added file 'app-mvhub/setup/database/sql/009_drop_get_agency_name.sql'
--- app-mvhub/setup/database/sql/009_drop_get_agency_name.sql	1970-01-01 00:00:00 +0000
+++ app-mvhub/setup/database/sql/009_drop_get_agency_name.sql	2010-07-09 19:27:43 +0000
@@ -0,0 +1,8 @@
+--add version number and note before you add the other sql
+BEGIN;
+INSERT INTO version_log ( version,note ) 
+	VALUES (9,'drop get_agency_name');
+
+DROP FUNCTION IF EXISTS get_agency_name(integer);
+
+COMMIT;