CODEWITHSUNDEEP
oracle-databaseinformix
ROBLCSnail
ROBLCSnail

Reputation: 115

Informix - select from a table of another user

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 !

Upvotes: 1

Views: 267

Answers (1)

Simon Riddle
Simon Riddle

Reputation: 1116

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>";

Upvotes: 1

Related Questions