[Solved] CONCAT Error - "Column Not Found"

[Steve R.]

I have a BASE front-end connected to MySQL. I had hoped through a query to concatenate the first and last names into one string "AuthorName" and then filter that string. In searching this forum I ran across the following post by MTP "SQL statements are parsed before running, and if the column doesn't exist the parsing fails." in response to "Why column not found ?". I assume that is the situation here.

Problem area. The column "AuthorName" is not found when using a filter macro to filter the result found in "AuthorName".
The filter macro does work when using either "AuhtorFirst" and "AuthorLast":

Code: Select all

CONCAT( CONCAT( "tblAuthorList"."AuthorFirst", ' ' ), "tblAuthorList"."AuthorLast" ) AS "AuthorName",

Full SQL Statement:

Code: Select all

SELECT "tblAuthorList"."AuthorIDNUM", CONCAT( CONCAT( "tblAuthorList"."AuthorFirst", ' ' ), "tblAuthorList"."AuthorLast" ) AS "AuthorName","tblStoryList"."StoryName", "tblMagazineList"."MagazineName", "tblIssueList"."IssueDate", "tblAuthorList"."AuthorLast", "tblAuthorList"."AuthorFirst" FROM "sfmags"."tblLinkedList" AS "tblLinkedList", "sfmags"."tblAuthorList" AS "tblAuthorList", "sfmags"."tblStoryList" AS "tblStoryList", "sfmags"."tblIssueList" AS "tblIssueList", "sfmags"."tblMagazineList" AS "tblMagazineList" WHERE "tblLinkedList"."LinkAuthorNUM" = "tblAuthorList"."AuthorIDNUM" AND "tblLinkedList"."LinkStoryNUM" = "tblStoryList"."StoryIDNUM" AND "tblStoryList"."MagazineIssueNUM" = "tblIssueList"."IssueIDNUM" AND "tblIssueList"."MagazineNUM" = "tblMagazineList"."MagazineIDNUM" ORDER BY "tblAuthorList"."AuthorLast", "tblAuthorList"."AuthorFirst" ASC

My workaround below, which works:

Code: Select all

strFilter = """AuthorLast"" LIKE '%"+strSearch+"%' OR ""AuthorFirst"" LIKE '%"+strSearch+"%'"

I also experimented some with expanding the SQL "WHERE" clause with the "LIKE" operator using "AuthorLast" which gave positive results.

[rudolfo]

As I am reluctant to read SQL code that is optimized for machines and not for humans (consists of 30 or more percent of quotes and the SQL keywords like SELECT, FROM or WHERE are hard to spot) I can only make a guess. You are using a nested CONCAT, but MySQL supports a CONCAT(string1, string2, ...) with a variable number of parameters it seems like you have turned the "direct SQL" option off. It probably has to be like that if you want to use your query with a filter condition, because somehow the Base parse has to bind and evaluate parameters in the filter condition.

But there might be a different approach if you have "Create View" permissions for the MySQL database. Just create a view that has the columns AuthorFirst and AuthorLast and additionaly a column AuthorName:

Code: Select all

CREATE VIEW v_author_list
AS
SELECT ...., AuthorFirst, AuthorLast, concat(AuthorFirst, ' ', AuthorLast) AS AuthorName, ....
  FROM tblAuthorList

and use this view instead of the plain table tblAuthorList in you select statement.
UPDATES on this view should still work as long as you only include direct columns in your update set.

[Steve R.]

Thanks. I will need to revisit: "MySQL supports a CONCAT(string1, string2, ...)". I thought I had tried that approach with one of my many variations. Thanks for the link.
The SQL statement is long since involves pulling (relational) data from three different tables.

I will also have to look into: " turned the "direct SQL" option off".

Since MySQL resides on my home network, their won't be any issues with the assignment of privileges.

Thanks for the feedback. Provides me some thoughts that I can experiment with.
---------------------------------------------------------------------------------------------------------------------------------------

PS: Of course, since you told me, "CONCAT(string1, string2, ...)" now works. Your advise scared the gremlins away.
Won't have time to test filtering the "AuthorName" column tonight.