Thread: Access to Postgres query problem

I need to run a query in an Access client to my Postgres backend.

This is the query:

SELECT DISTINCT projectId, sum(billableHours), sum(billableHours *
billingRate)
FROM time_card_hours
GROUP BY projectId

When I run this in psql it says I need to cast to make the multiplication
work.

So I do this:

SELECT DISTINCT projectId, sum(billableHours), sum(billableHours *
cast(billingRate as double precision))
FROM time_card_hours
GROUP BY projectId

or:

SELECT DISTINCT projectId, sum(billableHours), sum(cast(billableHours as
numeric) * billingRate)
FROM time_card_hours
GROUP BY projectId

Both of which work fine, the first rounding and the second showing the
decimal places in the second sum.

Now to my question.  When I do this from within Access it wants to use it's
own SUM and so it complains about the cast().  How can I either force Access
into sending the entire query to Postgres and just waiting for the
resultset, or force Access into performing the multiplication also?

Or my other thought is that I need to build my own operator/function to
handle the multiplying of double precision and numeric data types.  If this
is what I should do how should I go about it?

Thanks,
Rob

On Tue, Mar 19, 2002 at 02:59:23PM -0800, Booth, Robert wrote:
> I need to run a query in an Access client to my Postgres backend.
[snip]

> Now to my question.  When I do this from within Access it wants to use it's
> own SUM and so it complains about the cast().  How can I either force Access
> into sending the entire query to Postgres and just waiting for the
> resultset, or force Access into performing the multiplication also?

Basically you have to solutions. Change the schema so they are they same
type so the cast is not required. Or use a pass-through query. That's listed
right next to the union query. You can't use the query builder then but
access will send exactly what you type.

What I generally do is use the query builder to build the structure and then
convert to passthrough for final touching up.

> Or my other thought is that I need to build my own operator/function to
> handle the multiplying of double precision and numeric data types.  If this
> is what I should do how should I go about it?

You'd have to write you're own function for this, which would slow it down.
Mind you, not really more than what it does with the cast.
--
Martijn van Oosterhout <kleptog@svana.org>   http://svana.org/kleptog/
> Ignorance continues to thrive when intelligent people choose to do
> nothing.  Speaking out against censorship and ignorance is the imperative
> of all intelligent people.

In general, you should see much better performance if you change the query type
to a Pass-Through query, as Martin suggested. If you do that, then not only with
Postgres do the SUM() for you, but it will also do all the joins, where
conditions, and everything else. If you run a query in Access through the normal
query builder without passing it through, then every table mentioned in the
query has to be downloaded in its entirety to the client machine so that Access
can apply the where clauses and so forth. Unless all your tables are very small
to begin with, this will eat up a lot of your network bandwidth, especially if
multiplied by lots of Access clients using the same database this way. You
really want to avoid this by using passthrough queries whenever possible.

I think you can overload the multiply operator in postgresql to handle the
multiplication the way you want; check the docs and mailing list archives for
operator overloading.

HTH,

Wes Sheldahl



"Booth, Robert" <Robert_Booth%intuit.com@interlock.lexmark.com> on 03/19/2002
05:59:23 PM

To:   pgsql-general%postgresql.org@interlock.lexmark.com
cc:    (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject:  [GENERAL] Access to Postgres query problem


I need to run a query in an Access client to my Postgres backend.

This is the query:

<snip>

Now to my question.  When I do this from within Access it wants to use it's
own SUM and so it complains about the cast().  How can I either force Access
into sending the entire query to Postgres and just waiting for the
resultset, or force Access into performing the multiplication also?

Or my other thought is that I need to build my own operator/function to
handle the multiplying of double precision and numeric data types.  If this
is what I should do how should I go about it?

Thanks,
Rob


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org