Re: Including 4GL program variable in selection list of SELECT statement?
Richard,
I see Nils has answered your question as to how to get it to work but
not explained why your method failed.
When you use variables in a 4GL statement, it is *ONLY* for use where
you woyld have used a literal value in the same statement using
dbaccess. For example, I can issue the following in dbaccess:
select customer_num, lname, phone
from customer
where fname = "Irving"
See the literal value "Irving"? If I want to issue that statement in 4GL
but not hard-code "Irving" into the statement, I would use:
let p_variable = "Irving" # or get it set by user in a form
.... (other code)
select customer_num, lname, phone
from customer
where fname = p_variable
In 4GL you cannot use a variable as any other part of the SQL statement
expressed this way. If you are uncertain about the names of the columns
while writing the code - i.e. these will be determined runtime - then
you MUST use the PREPARE command as Nils has directed.
As for why the compiler failed to notice this flub, maybe the 6.05
compiler picks up on it now. In general, the code generated by a naked
SQL statement in 4GL is a set of hard-coded strings and a PREPARE. It
might not have been capable of catching this at compile time but the
runtime code runs into the ssyntax error when it executes the generated
PREPARE statement.
Richard Spitz wrote:
>
> Hi Informixers,
>
> is it possible to include a 4GL program variable in the selection list
> of a SELECT statement?
>
> In a 4GL program (4.10, ancient, I know...) I'm trying to do the
> following:
>
> DEFINE p_variable CHAR(4)
>
> LET p_variable = ...
>
> INSERT INTO some_table
> SELECT a.field1, b.field2, p_variable, c.field3, c.field4
> FROM table1 a, table2 b, table3 c
> WHERE ...>
> This does compile, but gives me a runtime "-201 Syntax Error".
>
> Any suggestions on how I might achieve this?
--
-- Jake (In pursuit of undomesticated aquatic avians)
+----------------------------------------------------------+
|Aside from that, how did you enjoy the play, Mrs. Lincoln?|
+----------------------------------------------------------+