Re: column doesn't get calculated - update # 2 - Mailing list pgsql-jdbc

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