is NULL and is blank string

rbuser

I am using the latest version of Report Builder and our application hooks
into the Search expression for field filtering. The problem is that if I
want to find empty string or NULL values, Report Builder treats them as
different.

Does anyone know if there are there any override-able procedure to allow me
to do this?

Many thanks,

J

nicocizik

Hi J,

Which database are you currently using?

The difference between an empty value and a NULL value is often debated
however we leave it to your database to decide.

If your database sees these as two separate values, you will need to add two
search criterias separated by an OR command.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Since it's SQL Server, they are different (rather than Oracle which would be
the same)...

Do you think there would be a way of forcing it in daADO.pas /
daMagicSQL.pas / daSQL.pas ?

maybe look through the SQL before it is generated and adjust it to include
NULLs?

rbuser

Nico,

Looking at it again, and there is a TppAutoSearchField type which uses
"SearchOperator" and "SearchExpression".


However, I tried to change the
SearchOperator := EqualType
and the SearchExpression := IS NULL or = ''

But this just looks for the literal string and is obviously wrong.
Would this be the best area to create Auto Search fields and let the
MagicSQL handle the rest?

J

nicocizik

Hi J,

If you are creating autosearch criteria in code and using DADE, I would
recommend using the TdaSQLBuilder object. This gives you easy access to the
SQL object and allows you to create and alter critera with simple
understandable routines.

See the help topic for TdaSQLBuilder and the following articles for more
information.

http://www.digital-metaphors.com/rbWiki/DADE/SQLBuilder

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Nico,

Many thanks - the SQLBuilder is exactly what I was looking for.

Just to let you know, it would be helpful to see some complex demos/examples
of how to create search criteria with parentheses.

Cheers - have a good weekend!

J