Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 2 of 2 total |
UNION causing problems |
Fri, Jun 21 2019 9:05 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Fri, Jun 21 2019 9:21 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |