SPL Weirdness????

I've seen weird stuff like that before in various versions of I-SQL/Ace, 4GL, etc. Just one of those things... "allenj" <allenj@ndr.com> wrote in message news:38FE18E9.64BB2BD9@ndr.com... > <scratching head> > OK...that worked. However, I can't find anywhere in the RTFM that says > I can't name a variable the same as a column....... > > Thanks for the help. Much appreciated. > > AJ > > Michael Krzepkowski wrote: > > > > Hi! > > > > Try naming your variables differently than columns, like r_sysdate etc. > > > > HTH > > > > Michael > > > > allenj wrote: > > > > > The facts: IDS 7.31.UC2 HP-UX 10.20 HP K Box 260 > > > > > > Why is it that everytime I get brave and try to write a SP in Informix, > > > I wind up beating my head against my office wall????????? Or more to > > > the point: > > > > > > The following SP: > > > > > > CREATE PROCEDURE displaynewcharts() > > > RETURNING CHAR(25),CHAR(135),DATE,CHAR(15) ;> > > > > > DEFINE primary_key CHAR(25); > > > DEFINE general_desc CHAR(135); > > > DEFINE sysdate DATE; > > > DEFINE username CHAR(15); > > > > > > {SET DEBUG FILE TO "/tmp/trace.sp";} > > > {TRACE ON ;} > > > > > > FOREACH > > > select first 5 primary_key,general_desc,sysdate,username > > > INTO primary_key,general_desc,sysdate,username > > > from audit_trail > > > where table = 'charts' and action = 'insert' > > > order by sysdate desc > > > RETURN primary_key,general_desc,sysdate,username > > > WITH RESUME ;> > > END FOREACH ; > > > > > > {TRACE OFF ;} > > > END PROCEDURE > > > > > > Generates the following error when I execute procedure > > > displaynewcharts() > > > > > > 309: ORDER BY column (sysdate) must be in SELECT list.> > > > > > Looks to me line column sysdate IS IN THE %$#@&*^% BLOODY SELECT > > > LIST......... > > > > > > Any help appreciated. > > > > > > Allen

The facts: IDS 7.31.UC2 HP-UX 10.20 HP K Box 260 Why is it that everytime I get brave and try to write a SP in Informix, I wind up beating my head against my office wall????????? Or more to the point: The following SP: CREATE PROCEDURE displaynewcharts() RETURNING CHAR(25),CHAR(135),DATE,CHAR(15) ; DEFINE primary_key CHAR(25); DEFINE general_desc CHAR(135); DEFINE sysdate DATE; DEFINE username CHAR(15); {SET DEBUG FILE TO "/tmp/trace.sp";} {TRACE ON ;} FOREACH select first 5 primary_key,general_desc,sysdate,username INTO primary_key,general_desc,sysdate,username from audit_trail where table = 'charts' and action = 'insert' order by sysdate desc RETURN primary_key,general_desc,sysdate,username WITH RESUME ; END FOREACH ; {TRACE OFF ;} END PROCEDURE Generates the following error when I execute procedure displaynewcharts() 309: ORDER BY column (sysdate) must be in SELECT list. Looks to me line column sysdate IS IN THE %$#@&*^% BLOODY SELECT LIST......... Any help appreciated. Allen

Hi! Try naming your variables differently than columns, like r_sysdate etc. HTH Michael allenj wrote: > The facts: IDS 7.31.UC2 HP-UX 10.20 HP K Box 260 > > Why is it that everytime I get brave and try to write a SP in Informix, > I wind up beating my head against my office wall????????? Or more to > the point: > > The following SP: > > CREATE PROCEDURE displaynewcharts() > RETURNING CHAR(25),CHAR(135),DATE,CHAR(15) ;> > DEFINE primary_key CHAR(25); > DEFINE general_desc CHAR(135); > DEFINE sysdate DATE; > DEFINE username CHAR(15); > > {SET DEBUG FILE TO "/tmp/trace.sp";} > {TRACE ON ;} > > FOREACH > select first 5 primary_key,general_desc,sysdate,username > INTO primary_key,general_desc,sysdate,username > from audit_trail > where table = 'charts' and action = 'insert' > order by sysdate desc > RETURN primary_key,general_desc,sysdate,username > WITH RESUME ;> END FOREACH ; > > {TRACE OFF ;} > END PROCEDURE > > Generates the following error when I execute procedure > displaynewcharts() > > 309: ORDER BY column (sysdate) must be in SELECT list.> > Looks to me line column sysdate IS IN THE %$#@&*^% BLOODY SELECT > LIST......... > > Any help appreciated. > > Allen

<scratching head> OK...that worked. However, I can't find anywhere in the RTFM that says I can't name a variable the same as a column....... Thanks for the help. Much appreciated. AJ Michael Krzepkowski wrote: > > Hi! > > Try naming your variables differently than columns, like r_sysdate etc. > > HTH > > Michael > > allenj wrote: > > > The facts: IDS 7.31.UC2 HP-UX 10.20 HP K Box 260 > > > > Why is it that everytime I get brave and try to write a SP in Informix, > > I wind up beating my head against my office wall????????? Or more to > > the point: > > > > The following SP: > > > > CREATE PROCEDURE displaynewcharts() > > RETURNING CHAR(25),CHAR(135),DATE,CHAR(15) ;> > > > DEFINE primary_key CHAR(25); > > DEFINE general_desc CHAR(135); > > DEFINE sysdate DATE; > > DEFINE username CHAR(15); > > > > {SET DEBUG FILE TO "/tmp/trace.sp";} > > {TRACE ON ;} > > > > FOREACH > > select first 5 primary_key,general_desc,sysdate,username > > INTO primary_key,general_desc,sysdate,username > > from audit_trail > > where table = 'charts' and action = 'insert' > > order by sysdate desc > > RETURN primary_key,general_desc,sysdate,username > > WITH RESUME ;> > END FOREACH ; > > > > {TRACE OFF ;} > > END PROCEDURE > > > > Generates the following error when I execute procedure > > displaynewcharts() > > > > 309: ORDER BY column (sysdate) must be in SELECT list.> > > > Looks to me line column sysdate IS IN THE %$#@&*^% BLOODY SELECT > > LIST......... > > > > Any help appreciated. > > > > Allen

In article <38FE11E3.D1D5CC7C@ndr.com>, allenj <allenj@ndr.com> wrote: > The facts: IDS 7.31.UC2 HP-UX 10.20 HP K Box 260 > > Why is it that everytime I get brave and try to write a SP in Informix, > I wind up beating my head against my office wall????????? Or more to > the point: > > The following SP: > > CREATE PROCEDURE displaynewcharts() > RETURNING CHAR(25),CHAR(135),DATE,CHAR(15) ;> > DEFINE primary_key CHAR(25); > DEFINE general_desc CHAR(135); > DEFINE sysdate DATE; > DEFINE username CHAR(15); > > {SET DEBUG FILE TO "/tmp/trace.sp";} > {TRACE ON ;} > > FOREACH > select first 5 primary_key,general_desc,sysdate,username > INTO primary_key,general_desc,sysdate,username > from audit_trail > where table = 'charts' and action = 'insert' > order by sysdate desc > RETURN primary_key,general_desc,sysdate,username > WITH RESUME ;> END FOREACH ; > > {TRACE OFF ;} > END PROCEDURE > > Generates the following error when I execute procedure > displaynewcharts() > > 309: ORDER BY column (sysdate) must be in SELECT list.> > Looks to me line column sysdate IS IN THE %$#@&*^% BLOODY SELECT > LIST......... > > Any help appreciated. > > Allen > I believe the problem is being caused by the fact that your local variable names are the same as the column names in the table you are selecting from. Therefore, the "sysdate" in the select statement is ambiguous -- do you mean the database column or the local variable? I know what you mean, but the SP parser seems to be assuming you mean the local variable in the select statement, thus the "sysdate" column is NOT in the select list. I would recommend prefixing your local variable names with something like "l_" to differentiate them from the table column names and thus remove any ambiguity which the SP parser may be having trouble with. HTH, -- Irwin Goldstein Objective Software Systems, Inc. http://www.objectsoft.com Sent via Deja.com http://www.deja.com/ Before you buy.

allenj <allenj@ndr.com> writes: > <scratching head> > OK...that worked. However, I can't find anywhere in the RTFM that says > I can't name a variable the same as a column....... Neither could I, but I couldn't;) Maybe we haven't RTFM'd enough? Thomas

All, Just looked in the Informix Guide to SQL: Tutorial pg 14-23 It says if you use the same column name and variable name, qualify the column name with the table name e.g.: define lname char(15); select customer.lname into lname from customer .... Not being a smart-arse - I'm learning SPL and read it last week! Andrew. allenj wrote in message <38FE18E9.64BB2BD9@ndr.com>... ><scratching head> >OK...that worked. However, I can't find anywhere in the RTFM that says >I can't name a variable the same as a column....... > >Thanks for the help. Much appreciated. > >AJ > >Michael Krzepkowski wrote: >> >> Hi! >> >> Try naming your variables differently than columns, like r_sysdate etc. >> >> HTH >> >> Michael