1

I am running the following query using DBISAM 

SELECT((SUM(sales.sale_amount)
+SUM(sales.vat_amount))
-SUM(payments.fee_amount)) 
AS Balance,account_details.id FROM account_details 
JOIN sales ON account_details.id = sales.account_id 
JOIN payments ON account_details.id = payments.account_id GROUP BY account_details.id

However, there are 3 different types of sale - sales.sale_type equating to 0, 1 or 2. If it is 0 a positive sale_amount and vat_amount should be added, ELSE negative values should be used.

I've tried various 

IF(sales.sale_type=0)

BEGIN
...
END
ELSE
BEGIN
...
END

To no avail, encountering only errors with the script not even runnning. I don't know if these errors are confined to DBISAM or it is my SQL in general.

Any help appreciated.

Thanks

raym0nd
  • 3,172
  • 7
  • 36
  • 73
gld22
  • 11
  • 2

1 Answers1

0

Does DBISAM have CASE statements?

SELECT
  CASE WHEN sales.sale_type=0
       THEN SUM(sales.sale_amount) + SUM (sales.vat_amount) - SUM(payments.fee_amount)
       ELSE SUM(sales.sale_amount) - SUM (sales.vat_amount) + SUM(payments.fee_amount)
       END
FROM
  <blah>
MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • Thanks for getting back, yes it turns out DBISAM does have CASE statements. However, when I try and introduce any aggregation into these it fails. It also doesn't seem to like queries containing CASE in the Delphi code I am trying to use them in. I have ended up running multiple queries into 'memory tables' and doing the calculations etc. in code. – gld22 Aug 10 '11 at 14:39
  • Can you give an example of the failure when you introduce aggregation? Propperly formed it should not fail. Such as `SELECT CASE THEN SUM( ELSE 1-SUM() END FROM table GROUP BY ` should work fine. – MatBailie Aug 10 '11 at 15:26