Andy Hibbs

ExecuteSQL Query Result With Null Values

Discussion created by Andy Hibbs on Oct 31, 2013
Latest reply on Nov 1, 2013 by Andy Hibbs

Further to my ExecuteSQL Speed Improvements posting, I've another example that seems to me to be an obvious requirement, but I've now spent too much time on Google, and failed to spot a solution, and welcome Technet advice.

 

Example data only below to keep it as simple as possible, in our actual query we are using || concantentation within our results to add information to the values.

 

Example values from MyTable:

 

FirstName MiddleName Surname

Tom Campbell Smith

Dick Jones

Freda MacDonald Baker

 

By running the following:

Let (

sql =

"SELECT m.FirstName,

CASE WHEN m.MiddleName IS NULL THEN '' ELSE m.MiddleName END,

m.Surname

FROM MyTable m"

;

ExecuteSQL ( sql ; "¶" ; "" )

)

 

Results in:

Tom

Campbell

Smith

 

Dick

 

Jones

 

Freda

MacDonald

Baker

 

Very simply, we need to suppress the blank row listed between 'Dick' and 'Jones' where the MiddleName value is null

 

The same results could have been achieved by using:

CASE WHEN m.MiddleName IS NOT NULL THEN m.MiddleName END

 

I don't believe the WHERE FROM statements are relevant here, as in our actual query we are pulling data from many locations and the problem is dependent on the values returned, not the selection criteria.

 

We can achieve our desired result using FileMaker's substitute function:

 

Let (

sql =

"SELECT m.FirstName,

CASE WHEN m.MiddleName IS NULL THEN '|' ELSE m.MiddleName END,

m.LastName

FROM MyTable m"

;

Substitute ( ExecuteSQL ( sql ; "¶" ; "" ) ; "|¶" ; "" )

)

(the pipe probably not the best character to use)

 

Which will return the desired result:

Tom

Campbell

Smith

 

Dick

Jones

 

Freda

MacDonald

Baker

 

 

However, it would be nice to do this directly within SQL if possible.

 

Andy

Outcomes