Re: ACE with PROMPTS and NULLS

Colin M McGrath <cmm@trac3000.ueci.com> asked: >Is there any way to prompt for variables in an Ace report but then grab >fields containing anything (including NULLS) if "*" (or something) is >entered at the prompt? > >We have a client who's having a challenge with Ace (remember Ace?) when >using ACE variables in the SELECT section when the field being prompted >for in the INPUT section allows NULLS. > > They are prompting for four variables, and here's what they want: > > (1) grab those records whose fields match the variables entered in the > input section > (2) grab ALL records if the value entered at the prompt is "*" > >How can we write the report so that a "*" (or something) entered at the >prompt can grab all records, even NULL field records? > >The only way I can see to make it so that "*" grabs ALL records is to change >both the select AND the print statements as follows. >(Does anyone know of a better way?) (It's so much easier to do this in 4GL!) > >Replace the where-clause in the select-stmt that uses the variables like so: >OLD SELECT: > select .. from ... where > a.loca_2 matches $lch_loca_2 and > b.loca_3 matches $lch_loca_3 and > b.t_cde matches $lch_t_cde and > b.dept matches $lch_dept> >NEW SELECT: > (a.loca_2 matches $lch_loca_2 or a.loca_2 IS NULL) and > (b.loca_3 matches $lch_loca_3 or b.loca_3 IS NULL) and > (b.t_cde matches $lch_t_cde or b.t_cde IS NULL) and > (b.dept matches $lch_dept or b.dept IS NULL) Err, why not test the $lch_loca_2 values for null-ness? (a.loca_2 MATCHES $lch_loca_2 OR $lch_loca_2 IS NULL) AND (b.loca_3 MATCHES $lch_loca_3 OR $lch_loca_3 IS NULL) AND (b.t_cde MATCHES $lch_t_cde OR $lch_t_cde IS NULL) AND (b.dept MATCHES $lch_dept OR $lch_dept IS NULL) If the entered data is null, then the second half of the corresponding OR is true, so the result is true, so the row is selected. No messing around with the FORMAT section, either... Yours, Jonathan Leffler (jleffler@informix.com) #include <quotes/shakespeare.h> Guardian of DBD::Informix v0.60 (v0.61_02) -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn