Re: 4gl SELECT Statement Woes
This is a multi-part message in MIME format.
--------------3AD61792A4C
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
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?
Because you are supplying a SINGLE value of "500,400,300", when you want
to supply three or more values.
What you can do is to PREPARE the statement, DECLARE a CURSOR and FETCH
values. Also, unless you actually WANT the count, don't bother to count
them. With a cursor you can simply determine there are records by
FETCHing
the first one only. :-)
DEFINE sql_text CHAR(1000)
INPUT BY NAME cStuff
{ User will type in the following: 500,400,300 }
LET sql_text = "SELECT key_field",
" FROM customers",
" WHERE cust_no IN(", cStuff CLIPPED, ")"
PREPARE pre_customers FROM sql_text
DECLARE sel_customers CURSOR FOR pre_customers
OPEN sel_customers
FETCH sel_customers INTO dummy_field
IF SQLCA.SQLCODE = 0
THEN
ERROR "Duplicate Customer Number Found!"
END IF
This of course has no error checking, and assumes the values in cStuff
are numeric only with correct delimiters. :-)
Hope that helps,
--
--------------3AD61792A4C
Content-Type: text/plain; charset=us-ascii; name="ATT_SIG.TXT"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="ATT_SIG.TXT"
Mark.
+------------------------------------------------------------------+
|Mark D. Stock - The West Solutions Group http://www.west.co.za |
| The Informix FAQ is at http://www.iiug.org |
|mailto:marks@west.co.za +-----------------------------------------+
|Tel: +27 11 803 2151 |If it doesn't work, force it! |
|Fax: +27 11 803 2189 |If it breaks, it needed replacing anyway!|
|Cell: +27 83 250 2325 |Well, that's how I code anyway! |
+------------------------+-----------------------------------------+
--------------3AD61792A4C--