Need to query Pg database directly

Roderick A. Anderson raanders at acm.org
Mon Nov 29 17:18:44 PST 2004


Is there a collection of SQL scripts that people have developed to query 
the database for "interesting" information?

I have access to a snap-shot (backup copy) of a database and need to query 
it directly to get some odd reports.  I'm on the uphill portion of getting 
used to and learning how to use JOINs (Oracle user 10+ years ago) so I'm 
getting some odd results.  Fiddling with the queries just gets me further 
off the mark.

Or if someone that knows the Freeside ropes and has a moment: I'm trying
to query the database to get all "active" users with their dialup
usernames.

I think my problem is in getting the correct svcnum and not doing a 
Cartesian cross against cust_pkg or cust_svc.

Just to shoe how wrong one can be here is what gets me too much data (ie. 
duplicate users).

	SELECT first, last, username
	  FROM cust_main cm
	  LEFT OUTER JOIN cust_pkg    cp ON ( cm.custnum = cp.custnum )
	  LEFT OUTER JOIN cust_svc    cs ON ( cp.pkgnum  = cs.pkgnum )
	  LEFT OUTER JOIN svc_dialup  sd ON ( cs.svcnum  = sd.svcnum )
	 ORDER BY username;

Thanks for any pointers or help.


Rod
-- 
    "Open Source Software - You usually get more than you pay for..."
     "Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"





More information about the freeside-users mailing list