时间戳

首页 > 解决方案 > 为什么 SQL Server 在 Aginity/Redshift 环境中运行良好时会提供语法错误？

sql - 为什么 SQL Server 在 Aginity/Redshift 环境中运行良好时会提供语法错误？

问题描述

SELECT
    customer_id,
    SUM(extended_amount) AS total_spent,
    COUNT(DISTINCT(client_web_order_number)) AS #_of_orders,
    MAX(oms_order_date) AS last_purchase_date,
    (CURRENT_DATE - last_purchase_date) AS days_since_last_purchase,
    MIN(oms_order_date) AS registration_date,
    (CURRENT_DATE - registration_date) AS days_active,
    CASE
        WHEN (days_since_last_purchase = days_active) THEN 'One-time purchase'
        ELSE ''
    END AS notes

FROM 
    mdwh.us_raw.l_dmw_order_report

WHERE 
    quantity_ordered > 0
    AND customer_id IS NOT NULL
    AND customer_id != ('')
    AND customer_id LIKE 'US%'
    AND oms_order_date IS NOT NULL
    AND UPPER(line_status) NOT IN ('','RETURN', 'CANCELLED')
    AND UPPER(item_description_1) NOT IN ('','FREIGHT', 'RETURN LABEL FEE', 'VISIBLE STITCH')
    AND (quantity_ordered * unit_price_amount) > 0
    AND extended_amount < 1000 --NO BULK ORDERS
    AND oms_order_date BETWEEN '2018-01-01' AND CURRENT_DATE
    AND SUBSTRING(upc,1,6) IN (SELECT item_code FROM item_master_zs WHERE new_division BETWEEN '11' AND '39')

GROUP BY
    customer_id
--HAVING
--  SUM(extended_amount) BETWEEN 1 AND 50

这是错误消息： 在此处输入图像描述

我尝试重命名查询中的源，将 SQL Server 设置更新为主要源。

这是一个糟糕的问题，我现在意识到了这一点。不会删除，但尽力改进。谢谢大家的帮助。

标签： sqlsql-servertsqldate

解决方案

您只需更改日期算术的语法以使查询在 SQL Server 中有效。此外，您不能访问SELECT在其他列的子句中声明的表达式，您需要重复该表达式。

因此，您要将SELECT子句更改为：

SELECT
    customer_id,
    SUM(extended_amount) AS total_spent,
    COUNT(DISTINCT(client_web_order_number)) AS #_of_orders,
    MAX(oms_order_date) AS last_purchase_date,
    DATEDIFF(day, last_purchase_date, CAST(GETDATE() AS DATE))  AS days_since_last_purchase,
    MIN(oms_order_date) AS registration_date,
    DATEDIFF(day, registration_date, CAST(GETDATE() AS DATE)) AS days_active,
    CASE
        WHEN DATEDIFF(day, last_purchase_date, CAST(GETDATE() AS DATE)) 
            = DATEDIFF(day, registration_date, CAST(GETDATE() AS DATE)) 
        THEN 'One-time purchase'
        ELSE ''
    END AS notes
FROM ...

推荐阅读