pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: column doesn't get calculated - updated
2+ messages / 2 participants
[nested] [flat]
* Re: column doesn't get calculated - updated
@ 2003-07-16 15:04 floyds@4peakstech.com
2003-07-20 04:42 ` Re: column doesn't get calculated - updated Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: floyds@4peakstech.com @ 2003-07-16 15:04 UTC (permalink / raw)
To: pgsql-sql; +Cc: Josh Wardle <josh@truckmaster.com>; Gregory S. Dodson <greg@truckmaster.com>
when i copy/paste the select stmt into psql, it works. or if i use it
"dynamically". it doesn't work properly when i use it in a prepared
statement -- which is what i am doing.
Regards,
Floyd Shackelford
4 Peaks Technology Group, Inc.
VOICE: 334.735.9428
FAX: 702.995.6462
EMAIL: FloydS@4PeaksTech.com
ICQ #: 161371538
PGP Fone at private.fwshackelford.com on request
Shackelford Motto: ACTA NON VERBA - Actions, not words
Alabama StateMotto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our
Rights
The Philosophy of Liberty: http://www.isil.org/resources/introduction.swf
-----Original Message-----
From: floyds@4peakstech.com [mailto:floyds@4peakstech.com]
Sent: Tuesday, July 15, 2003 11:14 AM
To: Pgsql-Sql
Subject: column doesn't get calculated
this must be a problem with my sql, but this one has me stumped. the column:
Debit."cumm_dbt_blnce" - Credit."cumm_crd_blnce" in the long sql statement
below comes out as the literal: Debit."cumm_dbt_blnce" -
Credit."cumm_crd_blnce" rather than as the calculated value. WHY!?!?!
this works with simple sql:
select a."field1" - b."field2" from (select field1 as "field1" from
someTable) as a, (select field2 as "field2" from someOtherTable) as b;
but with my more complex sql, it doesn't. it comes out as a literal string.
it's almost like postgresql forgot to process this column.
select
cred_vend_acct_table.num as "num" ,
cred_vend_acct_table.name as "name" ,
abs_vend_acct_type_table.name as "name" ,
Debit."cumm_dbt_blnce" - Credit."cumm_crd_blnce" as "undefined" ,
Debit."cumm_dbt_blnce" as "cumm_dbt_blnce" ,
Credit."cumm_crd_blnce" as "cumm_crd_blnce" ,
cred_vend_acct_table.objid as "__OBJID__" ,
cred_vend_acct_table.clsref as "__CLSREF__"
from
only cred_vend_acct_table ,
abs_vend_acct_type_table ,
( select
daily_acct_blnce_table.cumm_dbt_blnce as "cumm_dbt_blnce"
from
only daily_acct_blnce_table
where
( ( ( daily_acct_blnce_table.company_objid *= 2147483647 ) ) and
( ( daily_acct_blnce_table.status = 'e' ) ) and
( daily_acct_blnce_table.abs_acct_objref[2] =
cred_vend_acct_table.objid ) and
( daily_acct_blnce_table.abs_acct_objref[1] =
cred_vend_acct_table.clsref ) and
daily_acct_blnce_table.dte =
( select
max(daily_acct_blnce_table.dte) as "dte"
from
only daily_acct_blnce_table
where
( ( ( daily_acct_blnce_table.company_objid *=
147483647 ) ) and
( ( daily_acct_blnce_table.status = 'e' ) ) and
( daily_acct_blnce_table.abs_acct_objref[2] =
cred_vend_acct_table.objid ) and
( daily_acct_blnce_table.abs_acct_objref[1] =
cred_vend_acct_table.clsref ) ) ) ) )
as Debit ,
( select
daily_acct_blnce_table.cumm_crd_blnce as "cumm_crd_blnce"
from
only daily_acct_blnce_table
where
( ( ( daily_acct_blnce_table.company_objid *= 2147483647 ) )
and
( ( daily_acct_blnce_table.status = 'e' ) ) and
( daily_acct_blnce_table.abs_acct_objref[2] =
cred_vend_acct_table.objid ) and
( daily_acct_blnce_table.abs_acct_objref[1] =
cred_vend_acct_table.clsref ) and
daily_acct_blnce_table.dte =
( select
max(daily_acct_blnce_table.dte) as "dte"
from
only daily_acct_blnce_table
where
( ( ( daily_acct_blnce_table.company_objid *=
2147483647 ) ) and
( ( daily_acct_blnce_table.status = 'e' ) ) and
( daily_acct_blnce_table.abs_acct_objref[2] =
cred_vend_acct_table.objid ) and
( daily_acct_blnce_table.abs_acct_objref[1] =
cred_vend_acct_table.clsref ) ) ) ) )
as Credit
where
( ( ( cred_vend_acct_table.company_objid *= 2147483647 ) and
( abs_vend_acct_type_table.company_objid *= 2147483647 ) ) and
( ( cred_vend_acct_table.status = 'e' ) and
( abs_vend_acct_type_table.status = 'e' ) ) and
cred_vend_acct_table.owner_objref[1] = 100110 and
cred_vend_acct_table.owner_objref[2] = 2147483647 and
( ( ( cred_vend_acct_table.abs_acct_type_objref[1] =
abs_vend_acct_type_table.clsref ) and
( cred_vend_acct_table.abs_acct_type_objref[2] =
abs_vend_acct_type_table.objid ) ) ) )
order by 2 asc
limit 100
Regards,
Floyd Shackelford
4 Peaks Technology Group, Inc.
VOICE: 334.735.9428
FAX: 702.995.6462
EMAIL: FloydS@4PeaksTech.com
ICQ #: 161371538
PGP Fone at private.fwshackelford.com on request
Shackelford Motto: ACTA NON VERBA - Actions, not words
Alabama StateMotto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our
Rights
The Philosophy of Liberty: http://www.isil.org/resources/introduction.swf
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: column doesn't get calculated - updated
2003-07-16 15:04 Re: column doesn't get calculated - updated floyds@4peakstech.com
@ 2003-07-20 04:42 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2003-07-20 04:42 UTC (permalink / raw)
To: floyds@4peakstech.com; +Cc: pgsql-sql; Josh Wardle <josh@truckmaster.com>; Gregory S. Dodson <greg@truckmaster.com>
<floyds@4peakstech.com> writes:
> when i copy/paste the select stmt into psql, it works. or if i use it
> "dynamically". it doesn't work properly when i use it in a prepared
> statement -- which is what i am doing.
Could we see a self-contained example of the problem? It's hard to tell
whether you are dealing with a bug or pilot error ...
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-07-20 04:42 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-16 15:04 Re: column doesn't get calculated - updated floyds@4peakstech.com
2003-07-20 04:42 ` Tom Lane <tgl@sss.pgh.pa.us>
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