Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  Table dual is not in the database

    Posted 04/24/25 03:38 PM

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


  • 2.  RE: Table dual is not in the database

    Posted 04/24/25 03:53 PM

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



  • 3.  RE: Table dual is not in the database

    Posted 04/24/25 04:52 PM

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

    Original Message


  • 4.  RE: Table dual is not in the database

    Posted 04/24/25 06:05 PM

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

    Original Message


  • 5.  RE: Table dual is not in the database

    Posted 04/24/25 03:55 PM

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



  • 6.  RE: Table dual is not in the database

    Posted 04/24/25 05:58 PM

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



  • 7.  RE: Table dual is not in the database

    Posted 04/25/25 06:56 AM

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



Related Content