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
[EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
