[SOLVED] SQLite Pivot Query screws up Field Types
lazarus / 16-Databases

----------------------------------------------------------------------
wp · 2012-07-24 20:01

Suppose a SQLite database table contains the following fields:
- DateOfValue TIMESTAMP
- Project INTEGER
- Value FLOAT
The table might store, for example, the money earned for several
projects at certain dates.

I want to create a pivot table with dates in the rows and projects in
the columns, and the sum of the values at each crossing point. In the
internet I found the following command for an appropriate SQL query:

Code: [Select]SELECT
  DATE(DateOfValue) AS "Date",
  SUM(CASE WHEN Project=0 THEN Value END) AS "Project 0",
  SUM(CASE WHEN Project=1 THEN Value END) AS "Project 1",
  SUM(CASE WHEN Project=2 THEN Value END) AS "Project 2"
FROM
  TestTable
GROUP BY
  DATE(DateOfValue)
ORDER BY
  DATE(DateOfValue);

This query runs fine and does exactly what I want. With one exception:
It screws up FieldTypes, at least with Lazarus' SQLDB and
SQLite3Dataset.

The attachment of this posting contains demo projects and a sample
table to demonstrate the issue. Project1 uses the SQLDB components,
Project2 uses SQLite3Dataset.

In each project, the upper DBGrid displays the "raw" data by means of
a simple "SELECT *" query. The lower DBGrid displays the results of
the pivot query. At first sight, everything looks fine, but have a
look at the four labels at the bottom: The first column which should
be a ftDate column is reported as ftString, and the numeric columns
are ftString as well, except for the intermediate one which is
ftFloat.

This is very confusing. Both SQLDB and SQLite3Dataset show the same
effect.

The issue is that the results of this query cannot be passed to a
TAChart for graphical display because I am on locale settings which do
not accept the decimal point and which does not recognize the date in
the first column.

Does anybody have an idea how to avoid this issue?

P.S.
Please note that the sqlite3.dll must be available in the search path
or the exe folder in order to compile the projects.

----------------------------------------------------------------------
TheBlackSheep · 2012-07-24 22:57

haven't tried your code but I think your SQL might need adjusting...

Code: [Select]SELECT
  DATE(DateOfValue) AS "Date",
  SUM(CASE WHEN Project=0 THEN Value ELSE 0 END) AS "Project 0",
  SUM(CASE WHEN Project=1 THEN Value ELSE 0 END) AS "Project 1",
  SUM(CASE WHEN Project=2 THEN Value ELSE 0 END) AS "Project 2"
FROM
  TestTable
GROUP BY
  DATE(DateOfValue)
ORDER BY
  DATE(DateOfValue);
Internally, in SQLite everything is a string and if your summing null
values (which you'll get where the CASE statement doesn't handle every
value possible) then it might return strings by default when it can't
work out a correct value for every eventuality.  Adding the else
statment will force a 0 rather than a null which won't affect the sum
but might help the interpreter to work out that the value should be a
number as opposed to a string.

Also consider the use of IFNULL wrappers on values where it might
return a null entry, that way you'll force the value returned to at
least be of a particular type.

TheBlackSheep

----------------------------------------------------------------------
wp · 2012-07-25 07:33

Thank you. This is the perfect solution for the numerical columns! I
had to write "ELSE 0.0", though, otherwise the columns would be
transformed to ftLongInt which cuts off all the decimals.

What is left is the question how to get ftDate for the first column.

----------------------------------------------------------------------
BigChimp · 2012-07-25 09:43

Can you set the field type explicitly, something like
Code: [Select]sqlquery.fields[0].fieldtype=ftdate
Note: no idea if it works or even if it compiles (was off the top of
my head)...

----------------------------------------------------------------------
TheBlackSheep · 2012-07-25 10:12

your field is marked as a timestamp but that's not actually a
supported fieldtype in SQlite so it'll be treating it as a string type
as that's the default!  (note: there's a specific differentiation
between datetime and timestamp in some database engines - notably SQL
Server)

You can call your fieldtype anything you like in SQLite, e.g "BLA",
internally it's still a string and if it doesn't recognise how to
convert it back again to whatever fieldtype it'll just leave it as a
string.

Change your field type to Date in your database and then try again
without the Date() function;

Code: [Select]SELECT
  DateOfValue as "Date",
  SUM(CASE WHEN Project=0 THEN Value ELSE 0.0 END) AS "Project 0",
  SUM(CASE WHEN Project=1 THEN Value ELSE 0.0 END) AS "Project 1",
  SUM(CASE WHEN Project=2 THEN Value ELSE 0.0 END) AS "Project 2"
FROM
  TestTable
GROUP BY
  DateOfValue
ORDER BY
  DateOfValue;

your fieldtype in Lazarus will now be date(time) but the field
displayed will be a real number - not tested but it should be the
case.

I also see from the table structure that this field can be NULL so
this could be a potential issue (what does the Date() function return
on a NULL field - probably an error)  - in which case an IFNULL() may
be necessary at some point or better still, just make it a NOT NULL
field in the field type and force the issue.

finally (and most importanlty)... because a date (and a datetime) is a
number (the integer/mantissa part is the date the decimal part is the
time) - the date() function is effectively turning it into a readable
string and so it loses it's original format (i.e. it's no longer a
date at this point) - you shoudn't expect that it should always be a
date field type just because it looks like a date.

TheBlackSheep

----------------------------------------------------------------------
wp · 2012-07-25 13:05

Thank you for the detailed explanation. I dropped the DATE function
and Lazarus recognizes the values as ftDateTime now.