bobb@netcom.UUCP (Bob Beaulieu) (08/28/90)
I am having problems adding a col to an already existing table in INFORMIX. The error message is : #201 A Syntax Error has occured. This happens whether or not I am in the query mode or in the menuing system; although, I am able to modify existing cols. Informix doesn't have an answer and suspects it to be a bug with the version we have: INFORMIX-SQL 2.10.03K (MIPS M/120) RISC/OS UMIPS 4.01 Has any other users of informix sql with mips boxes experienced this problem? bobb
aland@infmx.UUCP (Colonel Panic) (08/29/90)
In article <12702@netcom.UUCP> bobb@netcom.UUCP (Bob Beaulieu) writes: > >I am having problems adding a col to an already existing table in INFORMIX. >The error message is : #201 A Syntax Error has occured. > >This happens whether or not I am in the query mode or in the menuing >system; although, I am able to modify existing cols. It's hard to tell what's happening without an example. By "query mode", do you mean that it fails when you try to use an ALTER TABLE statement in the SQL processor? If so, on what character position does it see a problem? (Enter your statement, run it, let it fail, then immediately enter Modify or Use-editor -- it will point to the start of the bad syntax). >Informix doesn't have an answer and suspects it to be a bug with the >version we have: >INFORMIX-SQL 2.10.03K (MIPS M/120) RISC/OS UMIPS 4.01 >Has any other users of informix sql with mips boxes experienced this >problem? >bobb It's not likely that this is machine-specific. My best guess is that you are trying to add a column with a name = a reserved word, or you are already exceeding the maximum row length (not likely). -- Alan Denney aland@informix.com {pyramid|uunet}!infmx!aland "Girl, we couldn't get much higher." - Joe Isuzu
ahl@technix.oz.au (Tony Landells) (02/27/91)
I've tried the following with the Informix RDS 1.10.03K and Informix SQL 2.10.03K, and both seem to be somewhat confused. I have two tables: # valid departments # code is unique, but reference is through the departmentKey # so that code changes need not be propogated create table department ( code char(3), description char(20), departmentKey serial ) # staff members # code is only unique within a department # staffKey is always unique, and avoids having to propogate changes create table staff ( code char(3), description char(20), departmentKey integer, staffKey serial ) For one of my screens, I want to select the first person in the first department (for which people have been entered). To select the department, therefore, I use: select * into p_department.* from department where @code = (select min(code) from department where @departmentKey in (select departmentKey from staff) ) Then, to get the first person I use: select * into p_staff from staff where @code = (select min(code) from staff where @departmentKey = p_department.departmentKey ) and @departmentKey = p_department.departmentKey For some reason, this won't work! Even though the department has been chosen to be one with staff in it, the second select returns status 100. Apparently, the criterion at fault is the part after the "and"; changing it to something as banal as ... and @departmentKey = (select departmentKey from department where @code = p_department.code) is sufficient to make it work okay. It doesn't matter which order I have the two conditions; it is the departmentKey restriction which causes the problem. I even tried using constants (having determined that the correct departmentKey was 1), still no luck. Obviously, I can't drop the restriction, or I might get back multiple rows (since the code can be repeated in different departments). Can anyone tell me what's going on here? It's annoying that it happens, but if I can predict when it will happen, I can at least avoid it without spending vast amounts of time testing every single query as I write it to ensure I won't get bitten during system debugging, when it would be much harder to find. Thanks, Tony Landells <ahl@technix.oz.au>