Table dual is not in the database

I may be going mad here: This worked yesterday (unless I dreamed it): select current year to month as this_month, current year to month - interval(1) month to month as last_month from dual; As it happens, I was in a sysmaster database but that's hardly important. Today I get this error: 206: The specified table (dual) is not in the database. 111: ISAM error: no record found.Of course, there is an easy workaround: select unique current year to month as this_month,current year to month - interval(1) month to month as last_month from systables; -- Surely there is a better table to use than systables but that's not my point. I recall getting the test data I was expecting - yesterday - but I can't duplicate it now from "dual". I've long ago lost my mind (or merely misplaced it), a hazard of the IT trade. But where the dual dummy table go? Ideas, please? Reassurances? Confirmation of my delusions? Thanks. ------------------------------ Jacob Salomon --- Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra ------------------------------

The sysdual table only exists by default in the sysmaster database. Assuming that you are using logging, you can change your query to be: select current year to month as this_month, current year to month - interval(1) month to month as last_month from sysmaster:dual ; You could always create a dummy sysdual table in your database. If you do want to use systables then do NOT select unique, but add, "where tabid=1" to avoid reading the whole table and having to sort the results. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

Hi Jacob, you can use this: select current year to month as this_month, current year to month - interval(1) month to month as last_month from table(set{1}); Best Regards, ------------------------------ Jean-Guy Charron ------------------------------

Hi Mike. That's multiple ideas in one reply. I'll break it up and reorder. > The sysdual table only exists by default in the sysmaster database. Indeed, that works. And yesterday I happened be in the sysmaster database with it worked with "dual" as opposed to sysdual. > You could always create a dummy sysdual table in your database. How would I do that? This is just curiosity now, since I'm writing a monitoring utility that will work mainly from sysmaster. > If you do want to use systables then do NOT select unique, but add, "where tabid=1" Of course any table will do. I tried this and it does work so that would have been the way to go if sysdual had failed. Thanks. I good for now (but still scratching my head over why I remember it working yesterday with dual.) ------------------------------ Jacob Salomon --- Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra ------------------------------

Jacob: What you are trying to do is: select current year to month as this_month, current year to month - interval(1) month to month as last_month from sysmaster:sysdual; If you want to use a catalog table in the current database, the old query (before sysdual) was: select current year to month as this_month, current year to month - interval(1) month to month as last_month from systables where tabid = 1 ; However, since v11.70, you can do this instead (with any constant, even built-in pseudo constants): select current year to month as this_month, current year to month - interval(1) month to month as last_month; With no FROM clause at all. Witness: > select current year to month as this_month, current year to month - interval(1) month to month as last_month; this_month last_month 2025-04 2025-03 1 row(s) retrieved. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Jacob: The best way to create a dual table in the current database would be to synonym sysmaster:sysdual locally. (David probably did that in sysmaster sometime in the past): > create synonym dual for sysmaster:sysdual;Synonym created. > select current from dual;(expression) 2025-04-24 18:04:33.611 1 row(s) retrieved. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

For the query you're describing you might not even need sysdual, or dual, or any FROM clause at all - if on 14.10. Try, in any database: select current year to month as this_month, current year to month - interval(1) month to month as last_month; ------------------------------ Andreas Legner Informix Dev HCL Software ------------------------------