[Info-ingres] Ingres Star and group by.

[Adrian Williamson]

Hi,

 

Using

 

II 11.0.0 (su9.us5/100)

15419

 

I recently found the need to use star to do daily checks across two databases.

 

I tried to write an SQL to group some data in one database with the where clause referencing two other tables in the other database.

 

I got an ungrouped data set – kind of

 

I thought this might be really difficult to summarise but while waiting for some queries to finish I found this did the trick:

 

In database number2:

 

create table grouptest1 ( dt date not null);\g

insert into grouptest1 values ('1-jan-2020');\g

insert into grouptest1 values ('2-jan-2020');\g

insert into grouptest1 values ('3-jan-2020');\g

insert into grouptest1 values ('1-jul-2020');\g

insert into grouptest1 values ('2-jul-2020');\g

insert into grouptest1 values ('20-nov-2020');\g

insert into grouptest1 values ('21-nov-2020');\g

 

In the star database:

 

register table  gt

as link from grouptest1

with node = 'nodey', database = 'number2';\g

 

select date_trunc('month',dt) , count(*) from gt group by 1;\g

 

+-------------------------+-------------+

|col1                     |col2         |

+-------------------------+-------------+

|01-nov-2020              |            2|

|01-nov-2020              |            2|

|01-jul-2020              |            2|

|01-jul-2020              |            2|

|01-jan-2020              |            3|

|01-jan-2020              |            3|

|01-jan-2020              |            3|

+-------------------------+-------------+

 

So I would expect three rows back as if I had done it directly on the database number2:

 

* select date_trunc('month',dt) , count(*) from grouptest1  group by 1;\g

Executing . . .

 

 

+-------------------------+-------------+

|col1                     |col2         |

+-------------------------+-------------+

|01-nov-2020              |            2|

|01-jul-2020              |            2|

|01-jan-2020              |            3|

+-------------------------+-------------+

 

Anyone else using star and noticed this?

 

I think I am using legitimate syntax.

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20211020/fa240c49/attachment.html>