HI All,
I'm trying to get invoice totals to show item totals and payments. I have 3
tables; Invoice, InvoiceDetail, and InvoicePayment. I first tried a join
like this.
select i.SiteID, i.InvoiceID,
NULLIF(Sum(d.Charge), 0) AS Itm,
NULLIF(Sum(p.Amount), 0) AS Pmt,
(NULLIF(Sum(d.Charge), 0) + NULLIF(Sum(p.Amount), 0)) AS Total
from Invoice i
join InvoiceDetail d ON (d.InvoiceID = :InvoiceID)
join InvoicePayment p ON (p.InvoiceID = :InvoiceID)
where (i.InvoiceID = :InvoiceID)
group by i.SiteID, i.InvoiceID
order by i.SiteID, i.InvoiceID
This multiplies each sum of charges by the number of payments and each sum
of payments by the number of charges.
Secondly, I tried sub-queries like this.
select (Select NULLIF(Sum(Charge), 0) from invoicedetail where InvoiceID =
:InvoiceID) AS Itm,
(Select NULLIF(Sum(Amount), 0) from invoicepayment where InvoiceID =
:InvoiceID) AS Pmt,
((Select NULLIF(Sum(Charge), 0) from invoicedetail where InvoiceID =
:InvoiceID) +
(Select NULLIF(Sum(Amount), 0) from invoicepayment where InvoiceID =
:InvoiceID)) AS Tot
from Invoice i
where (i.InvoiceID = :InvoiceID)
This gives me the correct results and works fine in IBExpert but craps out
with the .NET provider with the error message:
08d26y2008 16:15:06 - FirebirdSql.Data.FirebirdClient.FbException: Dynamic
SQL Error
SQL error code = -206
Column unknown
INVOICEID
No message for error code 336397208 found. --->
FirebirdSql.Data.Common.IscException: Exception of type
'FirebirdSql.Data.Common.IscException' was thrown.
at FirebirdSql.Data.Client.Gds.GdsConnection.ReadStatusVector()
at FirebirdSql.Data.Client.Gds.GdsConnection.ReadResponse()
at FirebirdSql.Data.Client.Gds.GdsDatabase.ReadResponse()
at FirebirdSql.Data.Client.Gds.GdsStatement.Prepare(String commandText)
at FirebirdSql.Data.FirebirdClient.FbCommand.Prepare(Boolean returnsSet)
at
FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteCommand(CommandBehavior
behavior, Boolean returnsSet)
at
FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader(CommandBehavior
behavior)
--- End of inner exception stack trace ---
at
FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader(CommandBehavior
behavior)
at
FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteDbDataReader(CommandBehavio
r behavior)
at
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBeh
avior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable)
Any ideas?
Steve Harp
-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Firebird-net-provider mailing list
Firebird-net-provider@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
