Its probably me going code blind but I have a weirdo

I have this bit of sql - essentially monthly analysis of income & expenditure according to analysis codes
------------------------------------------------------------------------------------------------------------------------------------------------
SELECT
COALESCE(_Owner,_Code) AS _ACode,
IF (_DatePaid BETWEEN DATE '2018-10-05' AND DATE '2018-10-31', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_1",
IF (_DatePaid BETWEEN DATE '2018-11-01' AND DATE '2018-11-30', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_2",
IF (_DatePaid BETWEEN DATE '2018-12-01' AND DATE '2018-12-31', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_3",
IF (_DatePaid BETWEEN DATE '2019-01-01' AND DATE '2019-01-31', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_4",
IF (_DatePaid BETWEEN DATE '2019-02-01' AND DATE '2019-02-28', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_5",
IF (_DatePaid BETWEEN DATE '2019-03-01' AND DATE '2019-03-31', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_6",
IF (_DatePaid BETWEEN DATE '2019-04-01' AND DATE '2019-04-30', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_7",
IF (_DatePaid BETWEEN DATE '2019-05-01' AND DATE '2019-05-31', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_8",
IF (_DatePaid BETWEEN DATE '2019-06-01' AND DATE '2019-06-30', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_9",
IF (_DatePaid BETWEEN DATE '2019-07-01' AND DATE '2019-07-31', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_10",
IF (_DatePaid BETWEEN DATE '2019-08-01' AND DATE '2019-08-31', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_11",
IF (_DatePaid BETWEEN DATE '2019-09-01' AND DATE '2019-10-04', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_12",
IF (_DatePaid BETWEEN DATE '2018-10-05' AND DATE '2019-10-04', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_13"
FROM Analysis
JOIN Coding ON Analysis._fkCoding = Coding._Code
JOIN Transactions ON Transactions._ID = Analysis._fkTransaction
WHERE
_DatePaid BETWEEN
DATE '2018-10-05'
AND
DATE '2019-10-04'
GROUP BY _ACode
------------------------------------------------------------------------------------------------------------------------------------------------

It works fine. Next stage is to add on totals for income and expenditure so

------------------------------------------------------------------------------------------------------------------------------------------------
SELECT
'TOTAL'+_InOut AS _ACode,
IF (_DatePaid BETWEEN DATE '2018-10-05' AND DATE '2018-10-31', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_1",
IF (_DatePaid BETWEEN DATE '2018-11-01' AND DATE '2018-11-30', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_2",
IF (_DatePaid BETWEEN DATE '2018-12-01' AND DATE '2018-12-31', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_3",
IF (_DatePaid BETWEEN DATE '2019-01-01' AND DATE '2019-01-31', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_4",
IF (_DatePaid BETWEEN DATE '2019-02-01' AND DATE '2019-02-28', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_5",
IF (_DatePaid BETWEEN DATE '2019-03-01' AND DATE '2019-03-31', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_6",
IF (_DatePaid BETWEEN DATE '2019-04-01' AND DATE '2019-04-30', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_7",
IF (_DatePaid BETWEEN DATE '2019-05-01' AND DATE '2019-05-31', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_8",
IF (_DatePaid BETWEEN DATE '2019-06-01' AND DATE '2019-06-30', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_9",
IF (_DatePaid BETWEEN DATE '2019-07-01' AND DATE '2019-07-31', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_10",
IF (_DatePaid BETWEEN DATE '2019-08-01' AND DATE '2019-08-31', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_11",
IF (_DatePaid BETWEEN DATE '2019-09-01' AND DATE '2019-10-04', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_12",
IF (_DatePaid BETWEEN DATE '2018-10-05' AND DATE '2019-10-04', IF (_InOut = '+', SUM(COALESCE(Analysis._Income, 0)), SUM(COALESCE(Analysis._Expenditure, 0))), 0) AS "Col_13"
FROM Analysis
JOIN Transactions ON Transactions._ID = Analysis._fkTransaction
WHERE
_DatePaid BETWEEN
DATE '2018-10-05'
AND
DATE '2019-10-04'
GROUP BY _InOut
------------------------------------------------------------------------------------------------------------------------------------------------

Note - its almost identical to the first statement, and it also works perfectly

HOWEVER, try and join the two with a UNION and I get

ElevateDB Error #1011 An error occurred with the column _DatePaid (A conversion error occurred with the value +)


Anyone have any idea what's going on?

Roy Lambert

Found a "solution" but reporting to Tim as a bug


If I replace the line

'TOTAL'+_InOut AS _ACode,

with

_InOut AS ACode,

it works


Roy Lambert