Re: column doesn't get calculated - update # 2

Floyd,

I would recommend turning sql statement tracing on in the database to
see the exact sql text that the driver is sending to the database (in
case it is somehow munging it). Then take that exact same text (as
found in the server log files) and run it in psql to see how it works there.

--Barry

floyds(at)4peakstech(dot)com wrote:
> i was wrong. it doesn't work as a prepared statement nor as a dynamic string
> using jdbc.
>
> it works fine if i paste it into psql.
>
> is it possible that a problem with a calculated column and a subselect in
> conjunction is a jdbc bug?
>
> Regards,
>
> Floyd Shackelford
> 4 Peaks Technology Group, Inc.
> VOICE: 334.735.9428
> FAX: 702.995.6462
> EMAIL: FloydS(at)4PeaksTech(dot)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(at)4peakstech(dot)com [mailto:floyds(at)4peakstech(dot)com]
> Sent: Wednesday, July 16, 2003 10:05 AM
> To: Pgsql-Sql
> Cc: Josh Wardle; Gregory S. Dodson
> Subject: RE: column doesn't get calculated - updated
>
>
>
> 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(at)4PeaksTech(dot)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(at)4peakstech(dot)com [mailto:floyds(at)4peakstech(dot)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 in psql:
>
> 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(at)4PeaksTech(dot)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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>