Re: [Firebird-net-provider] Help with SQL
Hello Steve, join InvoiceDetail d ON (d.InvoiceID = :InvoiceID) join InvoicePayment p ON (p.InvoiceID = :InvoiceID) where (i.InvoiceID = :InvoiceID) Are parameters supposed to work that way in ADO.net?? I always use @ for a parameter identifier instead of : regards, André - 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=100url=/ ___ Firebird-net-provider mailing list Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Re: [Firebird-net-provider] Help with SQL
in .net using always @ On Wed, Aug 27, 2008 at 09:59, André Knappstein, Controlling [EMAIL PROTECTED] wrote: Hello Steve, join InvoiceDetail d ON (d.InvoiceID = :InvoiceID) join InvoicePayment p ON (p.InvoiceID = :InvoiceID) where (i.InvoiceID = :InvoiceID) Are parameters supposed to work that way in ADO.net?? I always use @ for a parameter identifier instead of : regards, André - 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=100url=/ ___ Firebird-net-provider mailing list Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider -- Sem mais com os cumprimentos Joaquim Pais Ao reenviares mensagens apaga os endereços de correio electrónico nelas incluídos Usa Bcc ou Cco em vez de Para. - 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=100url=/___ Firebird-net-provider mailing list Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
[Firebird-net-provider] Help with SQL
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=100url=/___ Firebird-net-provider mailing list Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Re: [Firebird-net-provider] Help with SQL
You have to write the correct join ... I don't see it at first time! select i.SiteID, i.InvoiceID, Sum(COALESCE(d.Charge, 0)) AS Itm, Sum(COALESCE(p.Amount, 0)) AS Pmt, (Sum(COALESCE(d.Charge, 0)) + SUM(COALESCE(p.Amount, 0))) AS Total from Invoice i join InvoiceDetail d ON (d.InvoiceID = i.InvoiceID) join InvoicePayment p ON (p.InvoiceID = i.InvoiceID) where (i.InvoiceID = :InvoiceID) group by i.SiteID, i.InvoiceID order by i.SiteID, i.InvoiceID Regards, Paul From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mercea Paul Sent: Wednesday, August 27, 2008 7:53 AM To: 'For users and developers of the Firebird .NET providers' Subject: Re: [Firebird-net-provider] Help with SQL Try this select i.SiteID, i.InvoiceID, Sum(COALESCE(d.Charge, 0)) AS Itm, Sum(COALESCE(p.Amount, 0)) AS Pmt, (Sum(COALESCE(d.Charge, 0)) + SUM(COALESCE(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 Regards, Paul From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Steve Harp Sent: Wednesday, August 27, 2008 12:06 AM To: firebird-net-provider@lists.sourceforge.net Subject: [Firebird-net-provider] Help with SQL 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=100url=/___ Firebird-net-provider mailing list Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Re: [Firebird-net-provider] Help with SQL
--- Steve Harp [EMAIL PROTECTED] wrote: 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. [..] I haven't tried it in NET, but am venturing a guess that this has to do with parameters. Can you try to remove the parameters from the SQL statement and see if it works. -Steve - 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=100url=/ ___ Firebird-net-provider mailing list Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
