Re: 4gl SELECT Statement Woes

John Cokos wrote: > > Help.... > > Why doesnt this work: > > INPUT BY NAME cStuff > { User will type in the following: 500,400,300 } > > SELECT count(*) INTO nCount FROM customers > WHERE cust_no IN(cStuff)> > IF nCount > 0 THEN > ERROR "Duplicate Customer Number Found!" > END IF > > If I (in the 4gl code) hard code 500,400,300 into the SELECT, > the program works fine. Why won't it accept the variable, and > is there a work-around to this? > > Thanks, > John Cokos John, Try "set explain on" before the select statement and you will create a file (sqexplain.out ?) that holds something like this: QUERY: ------ select count ( *) from customers where cust_no in (?) Estimated Cost: 1 Estimated # of Rows Returned: 1 1) informix.customers: INDEX PATH (1) Index Keys: cust_no owner (Key-Only) Lower Index Filter: informix.customers.cust_no = '500,400,300' The contents of cStuff is considered a single value and not a list as you expected (notice only one "?"). Try something like this: let tmpStr = "select count(*) from customers where cust_no in (", cStuff clipped, ")" prepare s1 from tmpStr declare c1 cursor for s1 open c1 fetch c1 into n close c1 Good luck! Edwin Babadagian edwinb@panix.com