SQL concatenation for more than 2 columns
Hi,
I am implementing free search text box, which can search data in all columns of tables.
I have created DisplayName column by concatinating two column data with space.
SELECT TOP ALL FirstName||' '||LastName AS DisplayName, UserType, IsActive, RoleName, DOB FROM LISDB.ExternalUsers
Now, in where condition I want to do like search in DisplayName column but it not working.
I have tried both fn CONCAT and || in like query but both are not working for me.
If ((RefineSearchText'="") && ($Length($ZStrip(RefineSearchText,"<>W",""))>0)) {
Set args($Increment(args)) = RefineSearchText //DisplayName
Set args($Increment(args)) = RefineSearchText //UserType
Set args($Increment(args)) = RefineSearchText //RoleName
Set query = query _ "AND ({fn CONCAT(FirstName,LastName)} like ? OR UserType like ? OR RoleName like ?)"
#; Set query = query _ "AND (FirstName like ? OR UserType like ? OR RoleName like ?)"
}