How can I check field values and then fire DQL

Question

I have some string type attributes on my custom type and a date type attribute r_creation_date. I want to use them all for search, so that as more fields have entered a value, the results will narrow down.

I am using D2-Client 3.1 and using D2-Composer to build search property page and a query form (where I am specifying DQL).

For filtering on string type fields I have formed this DQL:

select * from my_custom_type
    where (strattrib1 like '%$value(strattribinput1)%') and
          ("strattrib2" like '%$value(strattribintput2)%') and
          ("strattrib3" like'$value(strattribintput3)%') and
          ("strattrib4" like '%$value(strattribintput4)%') and
          ("strattrib5" like '%$value(strattribintput5)%') and
          ("strattrib6" like '%$value(strattribintput6)%')

Here LIKE % serves dual purpose: First of all whenever some string has entered it checks the occurrence of that string in that attribute. When nothing entered it considers all values in that attribute. This allows ANDing without explicitly checking for empty input. (as $value(strattrib1)<>'')

Now I want to add the filter on date-type attribute r_creation_date to this query. However I cant get how can I do this, since I have to use DATEDIFF() function for date comparison and have to explicitly check for no input in r_creation_date as follows:

select * from my_custom_type
    where (strattrib1 like '%$value(strattribinput1)%') AND
          ("strattrib2" like '%$value(strattribintput2)%') AND
          ("strattrib3" like'$value(strattribintput3)%') AND
          ("strattrib4" like '%$value(strattribintput4)%') AND
          ("strattrib5" like '%$value(strattribintput5)%') AND
          ("strattrib6" like '%$value(strattribintput6)%') AND
          (DATEDIFF(day, "r_creation_date", DATE('$value(r_creation_date_intput)')) >= 0 AND $value(r_creation_date_input)<>'')

Here if user does not select any date for r_creation_date_input, $value(r_creation_date_input)<>'' results in FALSE evaluating whole query to FALSE. Thus select query does not return anything.

Questions

How can I do this?
Is there any other approach to do this?
What is the standard approach?
I want to add some more filters on date fields to same DQL. So is it possible?
How can I validate input for string attributes? Can I write Java and attach it to D2-Client?

score 0 · Answer 1 · answered Oct 31 '13 at 10:59

One of the approach is like this

AND ((DATEDIFF(day, "r_creation_date", DATE('$value(r_creation_date_intput)') OR $value(r_creation_date_intput) = '')

if user does not entere anything second condition will equal true and all other parts of the query will work fine.

But usually Documentum automatically exclude clauses when user input is empty. For example with taskspace search form Documentum does it automatically. I am not sure about D2, but maybe it will work as well.

How can I check field values and then fire DQL

1 Answers1