Re: Need Help!! - Mailing list pgsql-php
From | Papp Gyozo |
---|---|
Subject | Re: Need Help!! |
Date | |
Msg-id | 000901c14db6$be503a40$01fdfea9@jaguar Whole thread Raw |
In response to | Need Help!! (Gurudutt <guru@indvalley.com>) |
List | pgsql-php |
Hello, next time post the error message what you got and your pgsql version number. It helps us to find the weak point in the "whole mess". JOIN syntax was introduced in v7.1 and some fixes was made up to the recent version (v7.1.3), AFAIR. SELECT SUM(ACT_DueTab.CableAmount) as NetworkTotal FROM ACT_NetworkTab, ACT_DueTab, ACT_InvoiceTab LEFT JOIN ACT_CustomerTab -- ON (ACT_CustomerTab.CustCode=ACT_InvoiceTab.CustCode) -- instead, and pg won't duplicate this column: USING(CustCode) -- you might give a table alias for this JOIN and use it instead -- I'm just guessing ... WHERE ACT_DueTab.InvCode=ACT_InvoiceTab.InvNumber AND ACT_NetworkTab.NetCode=3 AND ACT_CustomerTab.NetCode=ACT_NetworkTab.NetCode AND ACT_InvoiceTab.InvGenDate <= '2001-08-31' AND ACT_InvoiceTab.InvGenDate >= '2001-08-01') ORDER BY ACT_InvoiceTab.InvGenDate DESC So: SELECT SUM(ACT_DueTab.CableAmount) as NetworkTotal FROM ACT_NetworkTab, ACT_DueTab, (ACT_InvoiceTab LEFT JOIN ACT_CustomerTab USING(CustCode)) AS A WHERE ACT_DueTab.InvCode=A.InvNumber AND ACT_NetworkTab.NetCode=3 AND A.NetCode=ACT_NetworkTab.NetCode AND A.InvGenDate <= '2001-08-31' AND A.InvGenDate >= '2001-08-01' ORDER BY ACT_InvoiceTab.InvGenDate DESC > 3. I was using PEAR for data abstraction layer ( to make code > independent of the database), I find that PEAR which worked fine with > mysql doesn't work so well with pgsql > Can you detail it a bit? What's wrong with PEAR or its abstraction? ----- Original Message ----- From: "Gurudutt" <guru@indvalley.com> To: <pgsql-sql@postgresql.org> Cc: <pgsql-php@postgresql.org> Sent: Monday, May 21, 2001 4:09 PM Subject: [PHP] Need Help!! > Hello pgsql-sql, > > I am the new member for the postgres mailing list. Actually I have > been working with mysql, php and perl for a very long time now, and > offlate shifted to pgsql. I have many technical difficulties > > 1. I need to port mysql data to pgsql. I tried both mysql2pg.pl and > my2pg.sql. Both have some problem. I think it is got something to do > with the auto increment feature that i used in mysql. Can that issue > be addressed while porting. > > 2. Some of the joins that were successfully working in mysql are not > working, most importantly LEFT JOIN. > > eg. > > SELECT SUM(ACT_DueTab.CableAmount) as NetworkTotal FROM > ACT_NetworkTab,ACT_DueTab, ACT_InvoiceTab LEFT JOIN ACT_CustomerTab ON > (ACT_CustomerTab.CustCode=ACT_InvoiceTab.CustCode) WHERE > ACT_DueTab.InvCode=ACT_InvoiceTab.InvNumber and > ACT_NetworkTab.NetCode=3 and > ACT_CustomerTab.NetCode=ACT_NetworkTab.NetCode and > (ACT_InvoiceTab.InvGenDate <= '2001-08-31' and > ACT_InvoiceTab.InvGenDate >= '2001-08-01') > ORDER BY ACT_InvoiceTab.InvGenDate DESC > > This query works fine in mysql, but suffers in pgsql. > > 3. I was using PEAR for data abstraction layer ( to make code > independent of the database), I find that PEAR which worked fine with > mysql doesn't work so well with pgsql > > > Any help on all these issues will be greatly appreciated. I am in the > midst of a porject porting exercise. > > > -- > Best regards, > Gurudutt mailto:guru@indvalley.com > > Life is not fair - get used to it. > Bill Gates > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)