SOLVED - Problem getting SQL VIEW created in SQLite
-
I am trying to create a SQLite SQL VIEW within a Qt application, the SQLite works well within the application, but when I execute the command to create the the VIEW below, I get the error: near "||": syntax error unable to execute statement.
I am using Qt 5.2.1 (GCC 4.6.1, 32 bit) on Linux.
The SQL (SQLite) Command:
@CREATE VIEW Support_Roles AS
SELECT Support_Role_List.Support_Role_List_Key AS Support_Role_List_Key,
Support_Role_Relationship.User_Key AS user,
(SELECT Support_Role_Type_Name FROM Support_Role_Type_List
WHERE Support_Role_Type_List.Support_Role_Type_Key = Support_Role_List.Support_Role_Type_Key) AS Role,
Support_Role_Name AS Role,
Practice_Hospital,
Telephone,
Email_Address AS Email,
(SELECT House_NumName || " " || Address_1 || CHAR(13) || Address_2 || CHAR(13) || City || CHAR(13) || County || CHAR(13) || Postcode || CHAR(13) || Country
FROM Address
WHERE (Address.Address_Key = Support_Role_List.Address_key)) AS Address,
Ref AS Reference
FROM Support_Role_List
JOIN Support_Role_Relationship
ON Support_Role_List.Support_Role_List_Key = Support_Role_Relationship.Support_Role_List_Key;@If I run this command in SQLite not using Qt it works (in the Firefox SQLite Addin) and the VIEW is created and works as expected with the application. The VIEW does what I would expect it to and works in a Qt Table View, the only problem is the creation.
I listed the SQL and the Qt code separately to make it more readable, so the SQL is executed in the following command. The applcation is quite large and stores data in a multi table database and also uses another view.
The Qt to run the SQL:
@QSqlQuery *sql_statement = new QSqlQuery(m_DB);
if(!sql_statement->exec("SQL goes here")) {
QMessageBox::warning(NULL, tr("Testing Error:"), sql_statement->lastError().text(), QMessageBox::Ok);
}
delete sql_statement;@The QMessageBox also displayed database error messages, which didn't occour so I removed that code for the sake of keeping it as readable as possible.
I have also tried replacing the CHAR (13) with x'0A' and '\n', but that doesn't change the error message.
Any suggestions as to how I can create this VIEW?
SOLUTION The problem was that I had used " " rather than ' ' to insert a space between the first two elements of the address.