mvhub-dev team mailing list archive

[noreply@xxxxxxxxxxxxx]

Merge authors:
  Saroun Sek <ssek@xxxxxxxxxx>
Related merge proposals:
  https://code.launchpad.net/~saroun-sek/mvhub/simplify_query_596605/+merge/29602
  proposed by: Saroun Sek (saroun-sek)
------------------------------------------------------------
revno: 444 [merge]
committer: Dan MacNeil <dan@xxxxxxxxxxxx>
branch nick: trunk
timestamp: Fri 2010-07-09 17:47:53 -0400
message:
  merged lp:~saroun-sek/mvhub/simplify_query_596605
added:
  app-mvhub/setup/database/sql/009_drop_get_agency_name.sql
modified:
  app-mvhub/conf/sql_select.lib


--
lp:mvhub
https://code.launchpad.net/~mvhub-commit/mvhub/trunk

Your team MVHub Developers is subscribed to branch lp:mvhub.
To unsubscribe from this branch go to https://code.launchpad.net/~mvhub-commit/mvhub/trunk/+edit-subscription

=== 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 18:49:34 +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:20:03 +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;