Create Table?

Sorry for the noobish question - I'm new to informix and have been having trouble with a seemingly simple operation. I'm trying to create a table. Basically like so: CREATE TABLE tab1 ( column1 VARCHAR(5), column2 VARCHAR(5) ) But i keep getting a syntax error. That was taken straight from an informix book (albeit with a few more columns), but i just cant seem to get it to work. Probably something simple, but i dont know what :) -- While i am here i'll ask another. I have a (badly designed) table with sales figures like so. Year | Month1Value | Month2Value | Month3Value | ... | Month1Cost | Month2Cost | ... etc I was writing a report on this data but due to the layout i was finding it difficult to do it the way i want. I'm trying to compare 2 years against each other for a specific month. so i was gonna put it in a table like Year1Value | Year2Value | Year1Cost | Year2Cost | ... etc I tried sticking it into a view like Create View Yearcompare As Select Month1Value, (SELECT Month1Value FROM tab1 WHERE Year=2007) FROM tab1 WHERE Year=2006.But this didnt work, 'more than 1 result returned in sub-query'. Is there a decent way of doin it? Plan B was to create the table as above, then copy data into that. - Oh and does anyone know if there is a good, free GUI designer for informix?

James said: > Sorry for the noobish question - I'm new to informix and have been > having trouble with a seemingly simple operation. > > I'm trying to create a table. > Basically like so: > > CREATE TABLE tab1 > ( > column1 VARCHAR(5), > column2 VARCHAR(5) > )> > But i keep getting a syntax error. > > That was taken straight from an informix book (albeit with a few more > columns), but i just cant seem to get it to work. > Probably something simple, but i dont know what :) What version of Informix? > -- > While i am here i'll ask another. > I have a (badly designed) table with sales figures like so. > Year | Month1Value | Month2Value | Month3Value | ... | Month1Cost | > Month2Cost | ... etc > > I was writing a report on this data but due to the layout i was > finding it difficult to do it the way i want. > I'm trying to compare 2 years against each other for a specific month. > so i was gonna put it in a table like > Year1Value | Year2Value | Year1Cost | Year2Cost | ... etc > > I tried sticking it into a view like > Create View Yearcompare As > Select Month1Value, (SELECT Month1Value FROM tab1 WHERE Year=2007) > FROM tab1 WHERE Year=2006.> But this didnt work, 'more than 1 result returned in sub-query'. > > Is there a decent way of doin it? select a.month1value, b.month1value from table a, table b where a.year = 2007 and b.year = 2006 ? > Plan B was to create the table as above, then copy data into that. > > > - > Oh and does anyone know if there is a good, free GUI designer for > informix? To do what? -- Bye now, Obnoxio "I'm astonished anyone pays real money for this crap." -- Cosmo "Cluster in my trousers" -- Guy Bowerman -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

> > Sorry for the noobish question - I'm new to informix and have been > > having trouble with a seemingly simple operation. > > > I'm trying to create a table. > > Basically like so: > > > CREATE TABLE tab1 > > ( > > column1 VARCHAR(5), > > column2 VARCHAR(5) > > )> > > But i keep getting a syntax error. > > > That was taken straight from an informix book (albeit with a few more > > columns), but i just cant seem to get it to work. > > Probably something simple, but i dont know what :) > > What version of Informix? > > Not sure what version of informix, i will try and check on monday. Are the methods different depending on version? > > > -- > > While i am here i'll ask another. > > I have a (badly designed) table with sales figures like so. > > Year | Month1Value | Month2Value | Month3Value | ... | Month1Cost | > > Month2Cost | ... etc > > > I was writing a report on this data but due to the layout i was > > finding it difficult to do it the way i want. > > I'm trying to compare 2 years against each other for a specific month. > > so i was gonna put it in a table like > > Year1Value | Year2Value | Year1Cost | Year2Cost | ... etc > > > I tried sticking it into a view like > > Create View Yearcompare As > > Select Month1Value, (SELECT Month1Value FROM tab1 WHERE Year=2007) > > FROM tab1 WHERE Year=2006.> > But this didnt work, 'more than 1 result returned in sub-query'. > > > Is there a decent way of doin it? > > select a.month1value, b.month1value > from table a, table b > where a.year = 2007 > and b.year = 2006> > ? Hmmm i will try that one out. Will it work if table a and b are the same? I didnt know you could do that and extract information from the same table twice. > > > Plan B was to create the table as above, then copy data into that. > > > - > > Oh and does anyone know if there is a good, free GUI designer for > > informix? > > To do what? > > -- To create and modify tables mainly. Working with a gui window (similar to access or sql server management studio) in stead of coding the sql manually. Cheers

James said: >> > Sorry for the noobish question - I'm new to informix and have been >> > having trouble with a seemingly simple operation. >> >> > I'm trying to create a table. >> > Basically like so: >> >> > CREATE TABLE tab1 >> > ( >> > column1 VARCHAR(5), >> > column2 VARCHAR(5) >> > )>> >> > But i keep getting a syntax error. >> >> > That was taken straight from an informix book (albeit with a few more >> > columns), but i just cant seem to get it to work. >> > Probably something simple, but i dont know what :) >> >> What version of Informix? >> >> > > Not sure what version of informix, i will try and check on monday. > Are the methods different depending on version? Some older versions of Informix don't support VARCHARs ... I think. It's always good to know the version and platform, though. >> > -- >> > While i am here i'll ask another. >> > I have a (badly designed) table with sales figures like so. >> > Year | Month1Value | Month2Value | Month3Value | ... | Month1Cost | >> > Month2Cost | ... etc >> >> > I was writing a report on this data but due to the layout i was >> > finding it difficult to do it the way i want. >> > I'm trying to compare 2 years against each other for a specific month. >> > so i was gonna put it in a table like >> > Year1Value | Year2Value | Year1Cost | Year2Cost | ... etc >> >> > I tried sticking it into a view like >> > Create View Yearcompare As >> > Select Month1Value, (SELECT Month1Value FROM tab1 WHERE Year=2007) >> > FROM tab1 WHERE Year=2006.>> > But this didnt work, 'more than 1 result returned in sub-query'. >> >> > Is there a decent way of doin it? >> >> select a.month1value, b.month1value >> from table a, table b >> where a.year = 2007 >> and b.year = 2006>> >> ? > > > Hmmm i will try that one out. Will it work if table a and b are the > same? Yes. You alias table with a and b and it treats them as two separate tables. > I didnt know you could do that and extract information from the same > table twice. > > >> >> > Plan B was to create the table as above, then copy data into that. >> >> > - >> > Oh and does anyone know if there is a good, free GUI designer for >> > informix? >> >> To do what? >> >> -- > > To create and modify tables mainly. > Working with a gui window (similar to access or sql server management > studio) in stead of coding the sql manually. Sounds like you want ServerStudio from www.agsltd.com -- Bye now, Obnoxio "I'm astonished anyone pays real money for this crap." -- Cosmo "Cluster in my trousers" -- Guy Bowerman -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

"James" <jamesb457@gmail.com> wrote in message news:1190399041.940328.65520@r29g2000hsg.googlegroups.com... > Sorry for the noobish question ... Is that newbie-ish? Or (k)nobbish?

Interesting responses so far - varchar would have caught me, but of course it is so. I would be more interested to hear what front end tool you are using to attempt to create this table and the resultant error code (only bcause nobody else has asked). I you are using dbaccess, I shall retire to my corner, otherwise, look to your tool. As to the view, it is just going take your rotten schema and codify it. If you want to compare two time periods against each other I might suggest putting them side by side in their own table. Sumarize them to the lowest granularity of time of interest and then work from that: select key1-n, sum(whatever) sum_yearx, some_function(date) from table where year(date) = x into temp t1 with no log; select key1-n, sum(whatever) sum_yeary, some_function(date) from table where year(date) = x+1 into temp t2 with no log; select key1-n, sum_yearx, sum_yeary, whatever from t1, t2 where key1-n=key1-n into temp report_tab with no log; select whatever from report_tab; It may seem more expensive, but I suggest that it may be more performant. j. -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]On Behalf Of James Sent: Friday, September 21, 2007 2:24 PM To: informix-list@iiug.org Subject: Create Table? Sorry for the noobish question - I'm new to informix and have been having trouble with a seemingly simple operation. I'm trying to create a table. Basically like so: CREATE TABLE tab1 ( column1 VARCHAR(5), column2 VARCHAR(5) ) But i keep getting a syntax error. That was taken straight from an informix book (albeit with a few more columns), but i just cant seem to get it to work. Probably something simple, but i dont know what :) -- While i am here i'll ask another. I have a (badly designed) table with sales figures like so. Year | Month1Value | Month2Value | Month3Value | ... | Month1Cost | Month2Cost | ... etc I was writing a report on this data but due to the layout i was finding it difficult to do it the way i want. I'm trying to compare 2 years against each other for a specific month. so i was gonna put it in a table like Year1Value | Year2Value | Year1Cost | Year2Cost | ... etc I tried sticking it into a view like Create View Yearcompare As Select Month1Value, (SELECT Month1Value FROM tab1 WHERE Year=2007) FROM tab1 WHERE Year=2006.But this didnt work, 'more than 1 result returned in sub-query'. Is there a decent way of doin it? Plan B was to create the table as above, then copy data into that. - Oh and does anyone know if there is a good, free GUI designer for informix? _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

James wrote: > Sorry for the noobish question - I'm new to informix and have been > having trouble with a seemingly simple operation. > > I'm trying to create a table. > Basically like so: > > CREATE TABLE tab1 > ( > column1 VARCHAR(5), > column2 VARCHAR(5) > )> > But i keep getting a syntax error. > > That was taken straight from an informix book (albeit with a few more > columns), but i just cant seem to get it to work. > Probably something simple, but i dont know what :) I think I am going to diagnose the use of SE (Informix Standard Engine). It does not support VARCHAR. Since your SQL statement is otherwise apparently well formed, it is a plausible and simple hypothesis. Does your database live in a directory database.dbs? Does $INFORMIXDIR contain a file $INFORMIXDIR/lib/sqlexec. If the answer to either is yes, we have a probable diagnosis. > -- > While i am here i'll ask another. > I have a (badly designed) table with sales figures like so. > Year | Month1Value | Month2Value | Month3Value | ... | Month1Cost | > Month2Cost | ... etc > > I was writing a report on this data but due to the layout i was > finding it difficult to do it the way i want. > I'm trying to compare 2 years against each other for a specific month. > so i was gonna put it in a table like > Year1Value | Year2Value | Year1Cost | Year2Cost | ... etc > > I tried sticking it into a view like > Create View Yearcompare As > Select Month1Value, (SELECT Month1Value FROM tab1 WHERE Year=2007) > FROM tab1 WHERE Year=2006.> But this didnt work, 'more than 1 result returned in sub-query'. Are you omitting some key information from your description, such as the product code? Or do you really have one line of data for 2007 and another for 2006? As Jack Parker suggested, you'd probably be best off creating a properly normalized table with columns year, month, {product information}, value and cost. You can then do a self-join to get the two years worth of data: SELECT last_year.*, this_year.* FROM good_table last_year, good_table this_year WHERE this_year.year = 2007 AND last_year.year = 2006 AND this_year.month = 1 AND last_year.month = 1 {AND this_year.product = last_year.product} ; -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2007.0914 -- http://dbi.perl.org/ publictimestamp.org/ptb/PTB-1357 whirlpool0 2007-09-22 03:00:04 1A77E9152A9D106ACD1C22A5F6B9227BFFFEC88FA2B1C965BC6BDBB981910F0C3D07EF 6812C542551D7ECF6A6339F4F8C56C400BFA29F002F464A444D40BAE2