X-Original-To: pgsql-jdbc-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id B8038CC6F2; Wed, 16 Jul 2003 16:49:20 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 99111-10; Wed, 16 Jul 2003 16:49:46 +0000 (GMT) Received: from mail.troycable.com (mail.troycable.net [66.21.97.10]) by svr1.postgresql.org (Postfix) with ESMTP id 03784CC6F1; Wed, 16 Jul 2003 13:49:19 -0300 (ADT) Received: from fig ([65.82.122.144]) by mail.troycable.com (8.12.8p1/8.12.3) with SMTP id h6GGpDEN027010; Wed, 16 Jul 2003 11:51:13 -0500 (CDT) (envelope-from floyds@4peakstech.com) Reply-To: From: To: "Pgsql-Jdbc" , "Pgsql-Sql" Cc: "Josh Wardle" , "Gregory S. Dodson" Subject: Re: column doesn't get calculated - update # 2 Date: Wed, 16 Jul 2003 11:54:41 -0500 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) In-Reply-To: Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-milter (http://amavis.org/) X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=IN_REP_TO, MSGID_GOOD_EXCHANGE, NO_REAL_NAME, ORIGINAL_MESSAGE X-Spam-Level: X-Archive-Number: 200307/61 X-Sequence-Number: 7445 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@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: 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@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 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@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