How to concatenate fields via SQL for use in TCRDBGrid?

[Steven]

I've got a contact database and I need to extract the contacts First & Last name as a single entity for use in a DBGrid column.

I've done this with other database formats but can't get it to work in SQLite using the TLiteQuery component.

For example the following code fails:

Code: Select all

sSQL := 'Select Vendors.*, (Contacts.First + '' '' + Contacts.Last) as Contact ' +
        ' from Vendors, Contacts' +
        ' WHERE Contacts.Cont_ID = Vendors.Cont_ID';

I am using the trial version of LiteDAC if that makes any difference.

[AlexP]

Hello,

For string concatenation in SQLite, the "||" symbol is used instead of "+". You can find more detailed information in the SQLite documentation.

[Steven]

Code: Select all

sSQL := 'Select Vendors.*, Contacts.First || '' '' || Contacts.Last as Contact ' +
        ' from Vendors, Contacts' +
        ' WHERE Contacts.Cont_ID = Vendors.Cont_ID'

The fields being concatenated are VarChar fields 65 characters in length.
In TCRDBGrid the contents of the Contact field show as (MEMO)
If you click on the cell a panel opens and displays the concatenated contact name.
Is there a way to have the text e.g. 'Susan Someone' show in the cell?

You can find more detailed information in the SQLite documentation.

I looked and sorry but that information is Not in the LiteDAC help that installs in the Delphi IDE.

[AlexP]

Hello,

If you are using concatenation, aggregating functions (MAX, AVG, ...), etc., SQLite returns the Unknown type for such fields. For correct data display, you should use DataTypeMapping http://www.devart.com/litedac/docs/data ... apping.htm .
The SQLite documentation is available on the developer's website, our product distribution includes only our product documentation.

[Steven]

Hi Alex,

Appreciate the help, thanks for the link to the documentation.
While the documentation was somewhat useful - I found the applicable section of "Rules for a particular field" to be too case specific and too cryptic to be an answer in itself.

Providing an example would have clarified what the documentation was trying to say, would have helped resolve my issue and saved some frustration.

I finally figured it by hunting through the forum for users having similar issues.
Here'e the solution in case anyone else runs into similar issues.

In the code example below Contact is the name of my concatenated field.

Code: Select all

LiteQuery1.SQL.Text := sSQLtextString;
LiteQuery1.DataTypeMap.AddFieldNameRule('Contact', ftString);
LiteQuery1.Open;

[AlexP]

Hello,

If you haven't found description of any class, property, etc., or it seems to you that description is not clear or full enough, please let us know - and we will correct the documentation.