I am working on a project where I need to use the “WITH” clause to create CTE’s in my queries. This works well in MSSQL, but I found out that GridDB does not support the “WITH” clause. I tried using below mentioned way:
def top_customers(gridstore):
query = """
WITH HighValueCustomers AS (
SELECT
CustomerID
FROM
Orders
GROUP BY
CustomerID
HAVING
COUNT(*) > 15
),
AverageOrderAmount AS (
SELECT
CustomerID,
AVG(OrderAmount) AS AvgOrderAmount
FROM
Orders
GROUP BY
CustomerID
),
MostValuableCustomers AS (
SELECT
hao.CustomerID
FROM
AverageOrderAmount hao
JOIN
HighValueCustomers hv ON hao.CustomerID = hv.CustomerID
WHERE
hao.AvgOrderAmount > 100
),
TopCustomers AS (
SELECT
pc.CustomerID,
COUNT(*) AS TotalOrders
FROM
Orders o
JOIN
MostValuableCustomers pc ON o.CustomerID = pc.CustomerID
GROUP BY
pc.CustomerID
)
SELECT
tc.CustomerID,
tc.TotalOrders,
c.FirstName,
c.LastName,
c.Email
FROM
TopCustomers tc
JOIN
Customers c ON tc.CustomerID = c.CustomerID;
"""
try:
results = gridstore.query(query)
while results.has_next():
print(results.next())
except griddb.GSException as e:
print (f"Error Message: {e.get_message()}")
def main():
try:
gridstore = connect_to_griddb()
top_customers(gridstore)
except griddb.GSException as e:
print(f"General GridDB Exception: {e}")
finally:
gridstore.close()
if __name__ == "__main__":
main()
Error Message: Syntax error: unexpected ‘WITH’
I want to fetch top customer based on orders amount and value etc. This query works well with mssql server but giving error in grid dB:
Kindly guide me on how I can improve logic on query level or python application level so that I can get desired result. (Application-level logic in python will be highly appreciated but query level is also welcomed)