Informix - select from a table of another user

user_17831952 0
Viewed 32

I have to do CRUD operations on a table that is not owned by the user I am using to connect to my Informix database. I have been granted the necessary privileges to do the operations, but I do not know how to do the actual query.

I have little experience with Informix, but I remember in OracleDB I had to do reference the shema like so:

SELECT * FROM SCHEMA.TABLE;

In Informix should I reference the user that owns the table ? Like :

SELECT * FROM OWNER:TABLE

Or can I just do :

SELECT * FROM TABLE

Thanks for any help !

informix oracle
1 Answers

In Informix you can generally use the table name without or without the owner prefix unless the database was created with mode ANSI in which case the owner prefix is required. Note that the correct syntax when using the owner is to use a period "." as in:

SELECT * FROM owner.table;

The colon is used to separate the database name as shown in the Informix Guide to SQL: Syntax https://www.ibm.com/docs/en/informix-servers/14.10?topic=segments-database-object-name#ids_sqs_1649

FYI you can determine if the database is mode ANSI with this query:

SELECT is_ansi FROM sysmaster:sysdatabases WHERE name = "<database name>";
user_76306980
Related