Re: ACE with PROMPTS and NULLS

Scott Black wrote: > > 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). Did you get that syntax to compile? I was cursing myself for a novice when I saw your example until I tried it and got: -293 IS [NOT] NULL predicate may be used only with simple columns. The test for a null value may be applied only to a column-name; not, for example, to a subscripted character column or an expression. Review the uses of these keywords in the WHERE clause. I just finished testing an alternative method. It involves moving the prompts out out the ace program into a shell script and generating part of the where-string there that then replaces a place-holder string in the ace report. Then the new ace report gets compiled and run. -- ____________________________________________________________________ | 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 |___________________________________________________________________