From: Ross J. Reedstrom <reedstrm@rice.edu>
To: pgsql-sql@postgresql.org
Subject: Re: Need Help!!
Date: Thu, 4 Oct 2001 17:33:56 -0500
Message-ID: <20011004173356.C31453@rice.edu> (raw)
In-Reply-To: <1256185600.20010521193906@indvalley.com>
References: <1256185600.20010521193906@indvalley.com>
On Mon, May 21, 2001 at 07:39:06PM +0530, Gurudutt wrote:
> 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
>
> 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.
suffers? What's suffers? It's slower? It doesn't work at all? What?
Looking at it, I'd guess that you get something about lack of GROUPing
when using an aggregate, right? So, you'll need to use correct SQL to
express the summation your trying to achieve. I don't have your schema,
nor the time to reverse engineer it from your example query, but if what
your expecting back from that is 31 rows in order, each one representing
the total invoices due on that day, you need to add:
GROUP BY BY ACT_InvoiceTab.InvGenDate
just before the ORDER BY line
Or, if you want the summation of all of them, and only expect one number
back, why are you ORDERing it?
>
> 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
Again, vague. What "doesn't work so well" ? What is PEAR? Hmm, seems
to be some PHP specific thing. I guess I'll let PHP PostgreSQL people
answer this one.
>
>
> Any help on all these issues will be greatly appreciated. I am in the
> midst of a porject porting exercise.
>
Hope I helped.
Ross
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: reedstrm@rice.edu
Subject: Re: Need Help!!
In-Reply-To: <20011004173356.C31453@rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox