|
|
|
|
|
Informix v7.3 introduced the CASE statement. This is used in the queries used to populate the Database Explorer and in a number of places in the Admin Component.
If you are using a version of Informix prior 7.3, you will find that a few functions of the Database Explorer and Admin Component do not work correctly.
This topic is important if you are dealing with date or datetime columns.
In Informix, the way in which you specify date and datetime values is determined by your environment variables DBDATE, DBTIME, GLS_DATE and GLS_TIMESTAMP. For instance, if you have DBDATE=Y4DM/ then you would need to code a query such as:
select * from order_details where date_required = '2004/25/06'
However if you had DBDATE=Y4MD- then the query will need to be:
select * from order_details where date_required = '2004-06-25'
In most places, AQT will generate the date in the correct format (for instance, if you are using the Get Values function to get a list of date values). However, in order to do this, AQT needs to know the format in which date literals are to be specified.
Unfortunately, AQT cannot determine the values of the environment variables (DBDATE etc). Instead, you must tell AQT the format to use for date and time literals. This is done in Options > Display Format > Format of Informix date/time literals.
If you are dealing with date or datetime columns, you will need to set these options.
In Informix you have great flexibility in defining date / time columns which a particular granularity. For instance the following defines a column containing a time:
update_time datetime hour to second
There are no literals which can be used such a data type. Instead, you must use the datetime function if you are specifying a particular value for the column. Eg. the following is not valid:
select * from order_details where update_time = '12:23:55'
Instead you must use:
select * from order_details where update_time = datetime(12:23:55) hour to second
Note that there are no quotes around the 12:23:55 value.
At this stage, AQT will not generate the datetime clause for you. Consequently you cannot update such datetime values using the AQT inline-edit. Nor can the Data Loader load datetime values such as this.
If you are running an early version of Informix, you will only be able to use native syntax for outer joins. In the GUI Query Builder, the syntax used for outer joins is specified on the Options tab > Join Syntax. If you have this set to the first option (Tab1 Left Outer Join Tab2) you will get a syntax error when you use an outer join. Change this to the second of the two options (Tab1, OUTER Tab2).
When you use the native join syntax you will only be able to do left outer joins; right and full joins are not possible with native syntax.