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.

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.

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.

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

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.

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.

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.

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.

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.