RE: ACE with PROMPTS and NULLS

If I can make some assumptions; You would like to search on entered criteria, or return all rows if the user simply hits the enter key. Try this; Select ... from ... where... (a.loca_2 = $lch_loca_2 or $lch_loca_2 is null) In the above expression, if the variable is null, then the select will return all rows (the second side of the 'or' clause will always be true). You can use something similar if you need to 'match' user input i.e.; Select ... from ... where (a.loca_2 matches $lch_loca_2 or $lch_loca_2 = "*") HTH -----Original Message----- From: Colin M McGrath [SMTP:cmm@trac3000.ueci.com] Posted At: Monday, May 17, 1999 3:51 PM Posted To: Informix Conversation: ACE with PROMPTS and NULLS Subject: ACE with PROMPTS and NULLS 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) ------ OLD format-on-every-row section format on every row PRINT COLUMN 12, ... NEW CONTROL BLOCK AT START OF format-on-every-row section format on every row if (lch_loca_2 = "*" or (lch_loca_2 != "*" and loca_2 IS NOT NULL)) and (lch_loca_3 = "*" or (lch_loca_3 != "*" and loca_3 IS NOT NULL)) and (lch_t_cde = "*" or (lch_t_cde != "*" and t_cde IS NOT NULL)) and (lch_dept = "*" or (lch_dept != "*" and dept IS NOT NULL)) then PRINT COLUMN 12, ... -- Colin McGrath cmm@trac3000.ueci.com Raytheon Engineers & Constructors, Inc. (215) 422-4144 Philadelphia, PA, USA Any opinions I state are my own and not necessarily of my employer