SQL Anywhere Feedback - default virtual tables INTEGERS and DATES

Author

default virtual tables INTEGERS and DATES

krisztian pinter

2005-11-21, 7:23 am

proposal:

system tables "INTEGERS" and "DATES"

select * from integers where N between 1 and 10

would give a resultset with one column "N" and ten rows, 1, 2, ... 10

if not filtered, values would range from minimum representable number
to maximum representable number.

select * from dates where "date" between '20040101' and '20040331'

would give all days between the dates given.

reason:

i have a table with all holidays, and all weekends that are special =

workdays.
so it is an exception table to the general rule that days mon to fri are=
=

workdays
and sat and sun are holidays. now i need a result set with all days and =
=

their
kind. i would write

select DA.date, coalesce(OV.day_kind, if (DOW(DA."date") in (1,7) then =

'HOLI' else 'WORK' endif) as day_kind
from dates DA
left join day_overrides OV on OV."date" =3D DA."date"

or if i have a table with some data for monthes, but only where there is=
=

nonzero
value, i could write

select i.N as month, d.somedata
from integers i
left join mydata d on d.month =3D i.N and d.year =3D 2004
where i.N between 1 and 12

i can emulate this behaviour with a real INTEGERS table, populated for a=
=

specific
range, and derive all others from it, but if the server would support =

these, it
could create a much more optimal plan, and without real data storage.

Reg Domaratzki \(iAnywhere Solutions\)

2005-11-21, 11:23 am

The following SQL creates a view called INTEGERS with values that range from
0 to 16,581,374. You'll get about 4.2 billion values if you join the table
to itself 4 times, as seen in the MORE_INTEGERS view.

create view INTEGERS as
select 255*255*(a.row_num-1) + 255*(b.row_num - 1) + c.row_num - 1 as N
from dbo.RowGenerator a,dbo.RowGenerator b, dbo.RowGenerator c

create view MORE_INTEGERS as
select 255*255*255*(a.row_num-1) + 255*255*(b.row_num - 1) +
255*(c.row_num - 1) + d.row_num - 1 as N
from dbo.RowGenerator a,dbo.RowGenerator b, dbo.RowGenerator c,
dbo.RowGenerator d

The following SQL creates a view called DATES who's values range from Jan 1,
1900 to Jan 11, 2078. You can move the start date to suit your needs.
create view DATES as
select dateadd( day, 255*(a.row_num - 1) + b.row_num - 1, '1900-01-01' ) as
D
from dbo.RowGenerator a,dbo.RowGenerator b

Do these views meet your needs?

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

"krisztian pinter" <pinterkr@freemail.hu> wrote in message
news:ops0ldlwg1wwfeh
v@karwst_pint...

proposal:

system tables "INTEGERS" and "DATES"

select * from integers where N between 1 and 10

would give a resultset with one column "N" and ten rows, 1, 2, ... 10

if not filtered, values would range from minimum representable number
to maximum representable number.

select * from dates where "date" between '20040101' and '20040331'

would give all days between the dates given.

reason:

i have a table with all holidays, and all weekends that are special
workdays.
so it is an exception table to the general rule that days mon to fri are
workdays
and sat and sun are holidays. now i need a result set with all days and
their
kind. i would write

select DA.date, coalesce(OV.day_kind, if (DOW(DA."date") in (1,7) then
'HOLI' else 'WORK' endif) as day_kind
from dates DA
left join day_overrides OV on OV."date" = DA."date"

or if i have a table with some data for monthes, but only where there is
nonzero
value, i could write

select i.N as month, d.somedata
from integers i
left join mydata d on d.month = i.N and d.year = 2004
where i.N between 1 and 12

i can emulate this behaviour with a real INTEGERS table, populated for a
specific
range, and derive all others from it, but if the server would support
these, it
could create a much more optimal plan, and without real data storage.

Reg Domaratzki \(iAnywhere Solutions\)

2005-11-21, 1:23 pm

Here's a MORE_DATES view that gives ranges from Jan 1, 0001 to Nov 25, 9970

create view MORE_DATES as
select dateadd( day, 255*255*(a.row_num - 1) + 255*(b.row_num - 1) +
c.row_num - 1, '0001-01-01' ) as D
from dbo.RowGenerator a,dbo.RowGenerator b, dbo.RowGenerator c
where a.row_num<=56

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

"krisztian pinter" <pinterkr@freemail.hu> wrote in message
news:ops0ldlwg1wwfeh
v@karwst_pint...

proposal:

system tables "INTEGERS" and "DATES"

select * from integers where N between 1 and 10

would give a resultset with one column "N" and ten rows, 1, 2, ... 10

if not filtered, values would range from minimum representable number
to maximum representable number.

select * from dates where "date" between '20040101' and '20040331'

would give all days between the dates given.

reason:

i have a table with all holidays, and all weekends that are special
workdays.
so it is an exception table to the general rule that days mon to fri are
workdays
and sat and sun are holidays. now i need a result set with all days and
their
kind. i would write

select DA.date, coalesce(OV.day_kind, if (DOW(DA."date") in (1,7) then
'HOLI' else 'WORK' endif) as day_kind
from dates DA
left join day_overrides OV on OV."date" = DA."date"

or if i have a table with some data for monthes, but only where there is
nonzero
value, i could write

select i.N as month, d.somedata
from integers i
left join mydata d on d.month = i.N and d.year = 2004
where i.N between 1 and 12

i can emulate this behaviour with a real INTEGERS table, populated for a
specific
range, and derive all others from it, but if the server would support
these, it
could create a much more optimal plan, and without real data storage.

Mark Culp

2005-11-21, 8:23 pm

In addition to Reg's responses, ASA 9.0.2 added the sp_rowgenerator()
procedure that you can use to efficiently generate an arbitrary list
of integers. (Its more efficient and faster than joining the rowgenerator
table to itself multiple times).

An excerpt from the 9.0.2 docs:

ASA SQL Reference
System Procedures and Functions
System and catalog stored procedures
sa_rowgenerator system procedure
--------------------------------------------------------------------------------
Function
Returns a result set with rows between a specified start and end value.

Syntax
sa_rowgenerator ( [ rstart [, rend [, rstep ] ] ])

Arguments
rstart This optional integer parameter specifies the starting value. The
default value is 0.
rend This optional integer parameter specifies the ending value. The default
value is 100.
rstep This optional integer parameter specifies the increment by which the
sequence values are increased. The default value is 1.
--
Mark Culp
SQLAnywhere Research and Development
-------------------------------------------------------------------------
** Whitepapers, TechDocs, bug fixes are all available through the **
** iAnywhere Developer Community at http://www.ianywhere.com/developer **
-------------------------------------------------------------------------
krisztian pinter wrote:
>
> proposal:
>
> system tables "INTEGERS" and "DATES"
>
> select * from integers where N between 1 and 10
>
> would give a resultset with one column "N" and ten rows, 1, 2, ... 10
>
> if not filtered, values would range from minimum representable number
> to maximum representable number.
>
> select * from dates where "date" between '20040101' and '20040331'
>
> would give all days between the dates given.
>
> reason:
>
> i have a table with all holidays, and all weekends that are special
> workdays.
> so it is an exception table to the general rule that days mon to fri are
> workdays
> and sat and sun are holidays. now i need a result set with all days and
> their
> kind. i would write
>
> select DA.date, coalesce(OV.day_kind, if (DOW(DA."date") in (1,7) then
> 'HOLI' else 'WORK' endif) as day_kind
> from dates DA
> left join day_overrides OV on OV."date" = DA."date"
>
> or if i have a table with some data for monthes, but only where there is
> nonzero
> value, i could write
>
> select i.N as month, d.somedata
> from integers i
> left join mydata d on d.month = i.N and d.year = 2004
> where i.N between 1 and 12
>
> i can emulate this behaviour with a real INTEGERS table, populated for a
> specific
> range, and derive all others from it, but if the server would support
> these, it
> could create a much more optimal plan, and without real data storage.

krisztian pinter

2005-11-22, 3:23 am

On 21 Nov 2005 09:17:42 -0800, Reg Domaratzki (iAnywhere Solutions) =

<FirstName.LastName@ianywhere.com> wrote:

> create view INTEGERS as
> select 255*255*(a.row_num-1) + 255*(b.row_num - 1) + c.row_num - 1 as =
N
> from dbo.RowGenerator a,dbo.RowGenerator b, dbo.RowGenerator c

very cool. only one thing would be better: if online documentation
would contain this :/

Reg Domaratzki \(iAnywhere Solutions\)

2005-11-22, 11:23 am

I'm glad it wasn't documented, since Mark's suggestion to use
sa_rowgenerator was much more clever (and faster) than my idea of joining
RowGenerator to itself. I would now suggest re-writing the queries to :

create view INTEGERS as
select row_num as N from sa_rowgenerator(0,21
47483647,1);

create view DATES as
select dateadd(day, row_num, '0001-01-01') as D from sa_rowgenerator( 0,
3652058, 1 )

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

"krisztian pinter" <pinterkr@freemail.hu> wrote in message
news:ops0mu8cn7wwfeh
v@karwst_pint...
On 21 Nov 2005 09:17:42 -0800, Reg Domaratzki (iAnywhere Solutions)
<FirstName.LastName@ianywhere.com> wrote:

> create view INTEGERS as
> select 255*255*(a.row_num-1) + 255*(b.row_num - 1) + c.row_num - 1 as N
> from dbo.RowGenerator a,dbo.RowGenerator b, dbo.RowGenerator c

very cool. only one thing would be better: if online documentation
would contain this :/

krisztian pinter

2005-11-23, 7:23 am

On 22 Nov 2005 08:27:32 -0800, Reg Domaratzki (iAnywhere Solutions)
<FirstName.LastName@ianywhere.com> wrote:

> I'm glad it wasn't documented, since Mark's suggestion to use
> sa_rowgenerator was much more clever (and faster)

as soon as we upgrade to v9

Sponsored Links