Excel style cell references.
lazarus / 16-Databases

----------------------------------------------------------------------
Robz · 2016-08-07 15:04

Hi,

How do I use Excel header references (a1,b1,c1...) and display real
header names? For example the following master detail databases (C and
R), shows correctly:

select [Ar] as [Ingredient],
      
 as [Amount],
       [Fc]as[Unit],
       [Dc]/([Ec]*[Gc]) as [Ingredient Cost],
       [Fc] as [Unit],
      
*[Dc]/([Ec]*[Gc]) as [Cost for Recipe]
from   [main].[R]
       inner join [main].[C] on [main].[R].[Ar] = [main].[C].[Ac]
order  by upper([main].[R].[Ar]);

Note:
“Ingredient” is the column name rather than Ar.
“Ingredient Cost” is a column name rather than [Dc]/([Ec]*[Gc]
Obviously, “Ingredient Cost” header name is easy to understand where
“Dc]/([Ec]*[Gc] “ is cryptic. However using [Dc]/([Ec]*[Gc] format for
arithmetic referencing is far easier than using real header column
names.
While the above SQL works it does not permit writing to the “Amount”
field. Also writing is disabled even if the
 column remains
.
How can I use Excel style cell references; but use meaningful column
header names?
Using sqlite3.dll version 3.13.0  64 bit
Windows 10 64 bit
Any help is greatly appreciated.

----------------------------------------------------------------------
LacaK · 2016-08-09 07:45

I am not user if I understand right, but you want in SQL use aliases
for real column names and later update dataset, so changed values are
written in real columns.
Then you must play with TSQLQuery.UpdateSQL, InsertSQL, DeleteSQL
properties ...
(because local dataset does know nothing about your real column names
in underlaying tables , which was hidden by SQL select)