IDB-1003: Circular joins are not supported.

[Shining]

OS: CentOS 5.7 x86_64
Infinidb: 2.2.4

select n_name,sum(l_extendedprice * (1 - l_discount)) as revenue from customer,orders,lineitem,supplier,nation,region where c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'AMERICA' and o_orderdate >= date '1997-01-01'and o_orderdate < date '1997-01-01' + interval '1' year group by n_name order by revenue desc;

 IDB-1003: Circular joins are not supported.

Is there a way to rewrite above SQL?

[Robert Adams (radams-calpont)]

mysql> SELECT N_NAME, SUM(L_EXTENDEDPRICE * (1-L_DISCOUNT)) AS REVENUE FROM CUSTOMER, ORDERS, LINEITEM, SUPPLIER, NATION, REGION WHERE C_CUSTKEY = O_CUSTKEY AND L_ORDERKEY = O_ORDERKEY AND L_SUPPKEY = S_SUPPKEY AND C_NATIONKEY = S_NATIONKEY + 0 AND S_NATIONKEY = N_NATIONKEY AND N_REGIONKEY = R_REGIONKEY AND R_NAME = 'AMERICA' AND O_ORDERDATE >= date '1997-01-01' AND O_O
RDERDATE < date '1997-01-01' + interval '1' year GROUP BY N_NAME ORDER BY REVENUE DESC;
+---------------+---------------+
| N_NAME | REVENUE |
+---------------+---------------+
| CANADA | 57165000.3824 |
| ARGENTINA | 55213294.4745 |
| UNITED STATES | 53893948.9531 |
| BRAZIL | 53012529.2573 |
| PERU | 51767803.7854 |
+---------------+---------------+
5 rows in set (0.68 sec)