CODEWITHSUNDEEP
sqllinqinformix
The New Guy
The New Guy

Reputation: 71

Informix to linq/sql

I have queries in Informix (IBM), i think so at least.
I have some syntaxes that i do not know what they would be like in linq or sql.
My objective is to make the queries in linq but anything that helps is welcome. 

select x 
from db:table, outer (db.table2 , db2.table)
where Etc..

Now i don't know an outer(join) with 2 parameters. I Also have a script of table creation that have something i don't understand 

int prop1 not null constraint n140-123,
varchar prop2 not null constraint n144-139

now i know they are not foreign or primary but is this expressing an exception of some sort ?

Upvotes: 0

Views: 269

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 754420

Outer joins

Informix supported an OUTER JOIN notation long before the SQL standard did. For reasons of backwards compatibility, it still supports the pre-standard notation.

For most practical purposes, you can convert a query like this (using the old-style OUTER JOIN notation):

select x 
  from db:table, outer(db.table2, db2.table)
 where db:table.col1 = db.table2.col2
   and db.table2.col3 = db2.table.col4

to the modern 'ANSI' OUTER JOIN notation:

SELECT x
  FROM db:table AS t1
  LEFT JOIN db.table2 AS t2 ON t1.col1 = t2.col2
  JOIN db2.table AS t3 ON t2.col3 = t3.col4

Note that Informix uses the notation db:table to indicate a table called table in the database db; the notations db2.table means the table owned by a user db2 called table in the current database, and db.table2 similarly means the table owned by user db and called table2 in the current database. Unless the current database is called db, this is a cross-database query.

You can find a whole lot more information about the Informix old-style OUTER JOIN notation at Informix Outer Joins.

Constraint notation

The claimed notation is a syntax error:

int prop1 not null constraint n140-123,
varchar prop2 not null constraint n144-139

The plausible notation uses underscore rather than dash:

int prop1 not null constraint n140_123,
varchar prop2 not null constraint n144_139

This simply means that there is a NOT NULL constraint on the columns, and the constraints were given names by the system. Interestingly, if the two lines come from the schema for a single table, then the constraint on prop1 was created before the table was modified and while its tabid was 140, and the constraint on prop2 was created as or after the table was modified and the tabid changed to 144.

I believe standard SQL puts the constraint name before the constraint condition; Informix puts the constraint name after the constraint condition. (There are ancient historical reasons for the discrepancy.) Unless you have plans to enable or disable the constraints, it is simplest not to give explicit names to NOT NULL constraints and let the system deal with it. Primary key, foreign key and check constraints are generally best named explicitly.

Upvotes: 1

Related Questions