Skip to content
Advertisement

Tag: informix

How can i filter a datetime column in informix with a date

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

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

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,