I have this query that I am trying run:
Select I.UID, I.Datepaid, I.InvoiceNumber, I.InvoiceDate, I.DueDate, I.Closed, I.BillName,
I.CustName, I.SalesTax, I.Total, I.Balance, I.TaxID, C.TaxGroup, I.AccountNumber,
I.Address1, I.Address2, I.City, I.St, I.Zip, I.Phone, I.Contact, I.BillAddress1,
I.BillAddress2, I.BillCity, I.BillSt, I.BillPhone, I.BillZip, I.BillContact, I.CustUID,
I.BillingUID, I.AddressBlock, I.BillAddressBlock,  I.Materials, I.Labor, I.OtherCharges, I.Travel, I.PrepaidUsed,
Coalesce(sum(PM.MaterialsApplied), 0) as matCredit,
Coalesce(sum(PM.LaborApplied), 0) as LabCredit,
Coalesce(sum(PM.OtherApplied), 0) as OtherCredit,
Coalesce(sum(PM.SalesTaxApplied), 0) as SalesTaxCredit,
i.Materials - matCredit as MaterialTotal  ----------------------Fails - 'expected column name, but instead found matcredit'


from Cust C, invoice I left outer join paydetail P on upper(I.UID) = upper(P.InvoiceUID)
    and (P.DateIn >= :StartDate) and (P.DateIn <= :EndDate)
    join Payment PM on upper(P.PaymentUID) = upper(PM.UID)
Where I.InvoiceDate >= :StartDate and I.InvoiceDate <= :EndDate and
          C.UID = I.CustUID
Group by C.TaxGroup, I.CustUID, I.UID, I.BillingUID
Order by C.TaxGroup,  I.InvoiceDate, I.InvoiceNumber NoCase

Is there a way to do this?

Connie,

<<  Is there a way to do this? >>

Sure, just repeat the same expression for the matCredit column:

i.Materials - Coalesce(sum(PM.MaterialsApplied), 0) as MaterialTotal

You must be using the latest 4.x version to do this, however, since earlier
4.x versions didn't support using aggregates in expressions.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Connie,

<<  Is there a way to do this? >>

Sure, just repeat the same expression for the matCredit column:

i.Materials - Coalesce(sum(PM.MaterialsApplied), 0) as MaterialTotal

You must be using the latest 4.x version to do this, however, since earlier
4.x versions didn't support using aggregates in expressions.


I tried that.
I am using 4.22 B5.
I set my 'select' to :
Select I.UID, I.Datepaid, I.InvoiceNumber, I.InvoiceDate, I.DueDate, I.Closed, I.BillName,
I.CustName, I.SalesTax, I.Total, I.Balance, I.TaxID, C.TaxGroup, I.AccountNumber,
I.Address1, I.Address2, I.City, I.St, I.Zip, I.Phone, I.Contact, I.BillAddress1,
I.BillAddress2, I.BillCity, I.BillSt, I.BillPhone, I.BillZip, I.BillContact, I.CustUID,
I.BillingUID, I.AddressBlock, I.BillAddressBlock,  I.Materials, I.Labor, I.OtherCharges, I.Travel, I.PrepaidUsed,
Coalesce(sum(PM.MaterialsApplied), 0) as matCredit,
Coalesce(sum(PM.LaborApplied), 0) as LabCredit,
Coalesce(sum(PM.OtherApplied), 0) as OtherCredit,
Coalesce(sum(PM.SalesTaxApplied), 0) as SalesTaxCredit,
i.Materials - Coalesce(sum(PM.MaterialsApplied), 0) as MaterialTotal
and I get :
Invalid use of non-aggregate column

Connie,

<< and I get :
Invalid use of non-aggregate column >>

Sorry about that - I forgot that this isn't permitted since the value of
i.Materials is ambiguous in the context of subtracting the SUM() expression
from it).  DBISAM only permits aggregate expressions to be used in
conjunction with one another in the same SELECT expression.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Connie,

<< and I get :
Invalid use of non-aggregate column >>

Sorry about that - I forgot that this isn't permitted since the value of
i.Materials is ambiguous in the context of subtracting the SUM() expression
from it).  DBISAM only permits aggregate expressions to be used in
conjunction with one another in the same SELECT expression.


So, can it be done?

Connie McBride <conniem@axcis.net> wrote in
news:C430E9EC-5D6B-4D00-9A30-0FB4F49ACA11@news.elevatesoft.com:

> DBISAM only permits aggregate expressions to be
> used in conjunction with one another in the same SELECT expression.
>
> So, can it be done?
>

Connie,

 You'll have to put everything into another memory table and then you
can subtract the last two from each other:

...
Coalesce(sum(PM.SalesTaxApplied), 0) as SalesTaxCredit,
i.Materials,
Coalesce(sum(PM.MaterialsApplied), 0) as MaterialCredit
INTO memory\tmpTbl
....
;
select *,materials - MaterialCredit as MaterialTotal
FROM memory\tmpTbl
;



--

Chris Erdal <chris@No-Spam-erdal.net> wrote:

Connie McBride <conniem@axcis.net> wrote in
news:C430E9EC-5D6B-4D00-9A30-0FB4F49ACA11@news.elevatesoft.com:

> DBISAM only permits aggregate expressions to be
> used in conjunction with one another in the same SELECT expression.
>
> So, can it be done?
>

Connie,

 You'll have to put everything into another memory table and then you
can subtract the last two from each other:

...
Coalesce(sum(PM.SalesTaxApplied), 0) as SalesTaxCredit,
i.Materials,
Coalesce(sum(PM.MaterialsApplied), 0) as MaterialCredit
INTO memory\tmpTbl
....
;
select *,materials - MaterialCredit as MaterialTotal
FROM memory\tmpTbl
;


Can this be run in the same SQL, or do I need a second query?
--

Connie McBride <conniem@axcis.net> wrote in news:5EBF1254-1519-4525-8351-
F609BE7D430F@news.elevatesoft.com:

> Can this be run in the same SQL, or do I need a second query?

The same one - DBISAM does all the background stuff first and then gives
the result of the last part, if it is a straightfroward SELECT statement.

The only thing you have to remember is that all memory tables stay around
as long as the Session is active, so you should do a:

DROP TABLE IF EXISTS memory\tmpTbl;

before filling it each time. You can't do it after the SELECT, as it is
then the last part, and it's not a SELECT, so you get nothing back (perhaps
Tim can fix that one for us?)

--
Chris

Chris,

<< before filling it each time. You can't do it after the SELECT, as it is
then the last part, and it's not a SELECT, so you get nothing back (perhaps
Tim can fix that one for us?) >>

I've looked into this, and it's not fixable in 4.x due to the design of the
SQL script processing.  It's basically a matter of DBISAM not knowing for
sure whether the SELECT result set should be kept open or not.  IOW, the SQL
statements after the SELECT may try to do something that requires exclusive
access with the tables involved in the SELECT, etc.

--
Tim Young
Elevate Software
www.elevatesoft.com

Connie,

<< So, can it be done? >>

Yes, but only using multiple SQL statements as Chris indicated.

--
Tim Young
Elevate Software
www.elevatesoft.com