I’d like to filter a datetime column in informix 12.10. I got the following “test” table name insert_date create_date … 2022-04-06 11:03:22.000 …. … 2022-03-02 12:03:22.000 …. … 2021-02-04 11:15:22.000 …. … 2021-01-05 11:03:22.000 …. My approach is the following I got the error: SQL-Error [IX000]: Non-numeric character in datetime or interval. How can i filter a datetime column with
Tag: informix
Informix doubly nested MULTISET remains empty
Given this schema: I tried running a query like this: But the output suggests that the inner most nested MULTISET query doesn’t work. The output is: Is this a known limitation or a bug? How can I work around this limitation? I’m using IBM Informix Dynamic Server Version 14.10.FC5DE Answer Just like a similar problem I’ve discovered recently, there seems
Internal error when lateral unnesting collection derived table in Informix
I’m playing around with what’s possible in Informix related to collection derived tables and nested collections. Given this schema: I tried the following query: Running it the first time from the Dbeaver SQL editor produces this error: SQL Error [IX000]: The current transaction has been rolled back due to an internal error. Running it the second time produces this error:
How to nest MULTISET in unnamed ROW in Informix?
Given this schema: The following query looks correct to me: But it produces some unspecified internal error: SQL Error [IX000]: User Defined Routine (collectionsend) execution failed. Is this a documented limitation? How can I work around this problem? I’m using IBM Informix Dynamic Server Version 14.10.FC5DE Answer One workaround might be to wrap the entire thing in a dummy MULTISET
MERGE Statement is not working in Informix v10
I tried to use the merge statement to Insert/Update in Informix v10.0 but it throws a Syntax Error: Could you please help? Answer The MERGE statement was not available in Informix 10.00. It was added in 11.50 — see the MERGE statement in the SQL syntax manual. The new features page suggests it was added in 11.50.xC6, part of the
SQL Informix advanced query
Let me try to explain what I want to do with my data. Structure of my data is as follows: I have 3 columns: date, id, and stage. Stages can be different numbers, but I’m interested in showing specific transition of some id from stage -1 to stage 1. Please see the example below. I have an id that was
informix 14.10 How to join three table for double mapping
I have 3 tables c1,map1,map2 like this: And the output should be such a table: If it was just table map1, the problem was simple Can anybody help? Answer On the face of it, this should do the job: Tested with this SQL to confirm the result: Output:
convert date unix to timestamp
I want to convert type date unix to timestamp in Informix. My column date1 contains values as 1598915961, 1598911249, 1598911255… expected output: 2020-02-13 15:00:00 How should I do it, please?
UPPER and LOWER functions in Informix DB LIKE queries do not work with all entries in the table
Background information: IBM Informix Dynamic Server Version 12.10.FC14WE DB_LOCALE=et_EE.utf8 Table has an ID primary key, no indexes Column datatype lvarchar (-1) The table column has names like “FIRST COMPANY”, “SECOND COMPANY”, “Third”. To find name matches the UPPER function is being used. Example of the problem: SELECT * FROM companies WHERE UPPER(name) LIKE ‘FIRST%’; – gives no results SELECT *
How to count the difference in Informix SQL?
I’m counting number of rated calls and average rating in callcenter: (12 rated calls for sales and 4 rated calls for service). I can also count number of total calls (rated and unrated): (21 total calls for sales and 4 total calls for service). But I want to count only unrated calls. SQL request: runs slowly and produce incorrect result,