Re: Create Table?

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@xxxxxxxxxxxxx, jleffler@xxxxxxxxxx
Guardian of DBD::Informix v2007.0914 -- http://dbi.perl.org/

publictimestamp.org/ptb/PTB-1357 whirlpool0 2007-09-22 03:00:04
1A77E9152A9D106ACD1C22A5F6B9227BFFFEC88FA2B1C965BC6BDBB981910F0C3D07EF
6812C542551D7ECF6A6339F4F8C56C400BFA29F002F464A444D40BAE2
.

Relevant Pages

  • Interesting problem in 4GL - records missing from select
    ... We are facing an issue in our informix 4gl application. ... record for this key from the main table, tab1. ... we used logs at different points of the ... The cursor declared is a plain cursor and not scroll cursor and ...
    (comp.databases.informix)
  • Re: Create Table?
    ... What version of Informix? ... FROM tab1 WHERE Year=2006. ... But this didnt work, 'more than 1 result returned in sub-query'. ... Oh and does anyone know if there is a good, free GUI designer for ...
    (comp.databases.informix)
  • RE: Create Table?
    ... select key1-n, sumsum_yearx, some_functionfrom table where ... That was taken straight from an informix book (albeit with a few more ... FROM tab1 WHERE Year=2006. ... Oh and does anyone know if there is a good, free GUI designer for ...
    (comp.databases.informix)
  • Create Table?
    ... That was taken straight from an informix book (albeit with a few more ... FROM tab1 WHERE Year=2006. ... But this didnt work, 'more than 1 result returned in sub-query'. ... Oh and does anyone know if there is a good, free GUI designer for ...
    (comp.databases.informix)