column doesn't get calculated - Mailing list pgsql-sql

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