x 1

[JohnGa1]

I have a SQL expression something like this:

(select max("act_reg"."act_rmk" + "act_reg"."act_rmk" + "act_reg"."act_rmk" + "act_reg"."act_rmk") from "act_reg"
where "incident"."incident_id"="act_reg"."incident_id")

which is extracting text from the four "rmk" fields and placing them in a field in the report. Unused "rmk" fields have a value of null, and any null values cause the expression to return a null value. I believe that the problem is that a setting CONCAT_NULL_YIELDS_NULL is set to yes because ODBC is being used to connect to the database, so I am asking if there is a way to set it to no in Crystal. In the expression I have tried using isnull and concat ("not a defined built in function" error message displayed) and coalesce (expression still returned null). Any advice appreciated.

 

[JohnGa1]

Apologies for the thread title, I had some difficulties posting. if possible, could it be changed to "SQL expression incorrectly returning null value".

 

[IanWaterman]

I assume you are usin SQL server, its always a good idea to state Crystal Version and database.

YOu can use isnull() inside the brackets

(select max(isnull("act_reg"."act_rmk", '') + isnull("act_reg"."act_rmk", '') + isnull("act_reg"."act_rmk",'') + isnull("act_reg"."act_rmk", '')) from "act_reg" where "incident"."incident_id"="act_reg"."incident_id")

Ian

 

[JohnGa1]

Yes, its Crystal XI and SQL 2008.

Thankyou, that seems to have worked perfectly. I'm sure I tried isnull and couldn't get it to work. Obviously doing something wrong, can't remember now. Thanks again.