如何将Oracle SQL转换为雪花SQL [英] How to convert the oracle sql to Snowflake sql

查看：8
本文介绍了如何将Oracle SQL转换为雪花SQL的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

您好，我在Snowflake中的SELECT中调用SELECT查询。当我注释Rownum时，我面临的错误是无效的标识符As ROWNUM，我面临的错误是不支持的子查询类型无法求值。

我的要求是将现有的Oracle查询转换为Snowflake。

SELECT DISTINCT cust.Id AS Customer_Id
            ,nvl((
                    SELECT *
                    FROM (
                        SELECT to_char(cliterm.Quantity)
                        FROM ContractLineItemTerm cliterm
                        WHERE cliterm.ContractLineItem_id = cli.Id
                            AND (
                                cliterm.EndDate IS NULL
                                OR cliterm.EndDate > add_months(sysdate, - 3)
                                )
                            AND cliterm.PriceRuleItem_id IS NULL
                        ORDER BY cli.id DESC
                        )
                    WHERE rownum = 1
                    ), cli.Quantity) AS Quantity_Purchased
            ,nvl((
                    SELECT *
                    FROM (
                        SELECT cliterm.UsedQuantity
                        FROM ContractLineItemTerm cliterm
                        WHERE cliterm.ContractLineItem_id = cli.Id
                            AND (
                                cliterm.EndDate IS NULL
                                OR cliterm.EndDate > add_months(sysdate, - 3)
                                )
                            AND cliterm.PriceRuleItem_id IS NULL
                        ORDER BY cli.id DESC
                        )
                    WHERE rownum = 1
                    ), cli.UsedQuantity) AS Quantity_Used_To_Date
            ,nvl((
                    SELECT *
                    FROM (
                        SELECT cliterm.StartDate
                        FROM ContractLineItemTerm cliterm
                        WHERE cliterm.ContractLineItem_id = cli.Id
                            AND (
                                cliterm.EndDate IS NULL
                                OR cliterm.EndDate > add_months(sysdate, - 3)
                                )
                            AND cliterm.PriceRuleItem_id IS NULL
                        ORDER BY cli.id DESC
                        )
                    WHERE rownum = 1
                    ), cli.StartDate) AS sbscription_Term_Start_Date
            ,nvl((
                    SELECT *
                    FROM (
                        SELECT cliterm.EndDate
                        FROM ContractLineItemTerm cliterm
                        WHERE cliterm.ContractLineItem_id = cli.Id
                            AND (
                                cliterm.EndDate IS NULL
                                OR cliterm.EndDate > add_months(sysdate, - 3)
                                )
                            AND cliterm.PriceRuleItem_id IS NULL
                        ORDER BY id DESC
                        )
                    WHERE rownum = 1
                    ), cli.EndDate) AS sbscription_Term_End_Date
            ,nvl(to_char(cli.EndDate), (
                    CASE 
                        WHEN (
                                cli.StartDate IS NOT NULL
                                AND con.InitialTerm > 0
                                )
                            THEN 'Auto Renewal'
                        ELSE ''
                        END
                    )) AS Contr_End_Date
        FROM ContractLineItem cli
        INNER JOIN Contract con ON cli.Contract_id = con.Id
        INNER JOIN Customer cust ON con.Customer_id = cust.Id
        INNER JOIN Organization org ON org.Customer_id = cust.Id
        INNER JOIN Product prod ON cli.Product_id = prod.Id
        INNER JOIN Producttype pt ON prod.ProductBrand_id = pt.Id
        LEFT JOIN account acc ON cust.SAN = acc.acc__c
        LEFT JOIN account acc1 ON acc.parentid = acc1.id
        LEFT JOIN sbSCRIPTION sb ON sb.id = cli.sforceid
        LEFT JOIN sbSCRIPTION pasb ON pasb.id = sb.srequired
        LEFT JOIN scontract1 cntr ON cntr.contractnumber = con.ContNumber
        LEFT JOIN user accowner ON acc.ownerid = accowner.id
        LEFT JOIN user accsalesmanager ON accowner.managerid = accsalesmanager.id
        LEFT JOIN (
            SELECT f.accountid
                ,g.managerid
                ,max(g.NAME) CSM
                ,max(g.id) CSMID
            FROM accountteammember f
            JOIN user g ON f.userid = g.id
            WHERE f.teammemberrole = 'AGM'
            GROUP BY f.accountid
                ,g.managerid
            ) acccsm ON acccsm.accountid = acc.id
        LEFT JOIN product2 prd ON prod.Code = prd.productcode
        LEFT JOIN prdfam prod_fam ON prod_fam.product_family_desc = prd.Product_Family__c
        )
    WHERE Quantity_Purchased <> 0
    );

如何将其转换为支持雪花，因为应该根据rownum=1选择Quantity列，如果为空，则应该替换为某个x。

推荐答案

示例SQL缺少两层SELECT * FROM (，因为您必须在末尾匹配PARN。但假设您选择的是*，因此WHERE Quantity_Purchased <> 0可以转换为限定，并且可以跳过这些层。

主要有两件事，一是如何转换SQL，二是如何避免协作子查询

首先JOIN中的三个子选择有相同的模式，所以我们将讨论第一个，WHERE rownum = 1可以通过limit 1顺序QUALIFY来完成，我更喜欢后者。

SELECT to_char(cliterm.Quantity)
FROM ContractLineItemTerm cliterm
WHERE cliterm.ContractLineItem_id = cli.Id
    AND (
        cliterm.EndDate IS NULL
        OR cliterm.EndDate > add_months(sysdate, - 3)
        )
    AND cliterm.PriceRuleItem_id IS NULL
QUALIFY row_numumber() OVER (ORDER BY cli.id DESC) = 1
然后，当您在协作子查询中使用它时，这实际上也是随机的，因为您按cli.id排序，但也连接到同一子句上，因此您实际上并没有对数据进行排序。因此，首先您需要有一个更好的子句来从ContractLineItemTerm中进行选择，我将组成一个名为is_best的列，这样SQL就有意义了，然后您就可以在其中放入一些有意义的东西。

接下来要注意的是，您的四个SELECT都是相同的SQL块，因此我们可以将其更改为一个联接并使用它。

所以我使用了CTE，这样您就可以看到四个块是如何写成一个块的，并将JOINON重新格式化为新的行，至于更复杂的SQL，它更好，我将CASE重写为IFF，因为它是一个简单的两分支情况。

WITH best_per_id (
    SELECT cliterm.ContractLineItem_id
        ,to_char(cliterm.Quantity) as Quantity
        ,cliterm.UsedQuantity
        ,cliterm.StartDate
    FROM ContractLineItemTerm cliterm
    WHERE cliterm.PriceRuleItem_id IS NULL
        AND (
            cliterm.EndDate IS NULL
            OR cliterm.EndDate > add_months(sysdate, - 3)
            )
    QUALIFY row_numumber() OVER (PARTITION BY cliterm.ContractLineItem_id 
        ORDER BY cliterm.is_best ) = 1
)
SELECT DISTINCT cust.Id AS Customer_Id
    ,nvl(bpi.Quantity, cli.Quantity) AS Quantity_Purchased
    ,nvl(bpi.UsedQuantity, cli.UsedQuantity) AS Quantity_Used_To_Date
    ,nvl(bpi.StartDate, cli.StartDate) AS sbscription_Term_Start_Date
    ,nvl(bpi.EndDate, cli.EndDate) AS sbscription_Term_End_Date
    ,nvl(to_char(cli.EndDate), 
         IFF(cli.StartDate IS NOT NULL AND con.InitialTerm > 0, 'Auto Renewal', '')
        ) AS Contr_End_Date
FROM ContractLineItem cli
INNER JOIN Contract con 
    ON cli.Contract_id = con.Id
INNER JOIN Customer cust 
    ON con.Customer_id = cust.Id
INNER JOIN Organization org 
    ON org.Customer_id = cust.Id
INNER JOIN Product prod 
    ON cli.Product_id = prod.Id
INNER JOIN Producttype pt 
    ON prod.ProductBrand_id = pt.Id
LEFT JOIN best_per_id as bpi
    ON cli.id = bpi.ContractLineItem_id
LEFT JOIN account acc 
    ON cust.SAN = acc.acc__c
LEFT JOIN account acc1 
    ON acc.parentid = acc1.id
LEFT JOIN sbSCRIPTION sb 
    ON sb.id = cli.sforceid
LEFT JOIN sbSCRIPTION pasb 
    ON pasb.id = sb.srequired
LEFT JOIN scontract1 cntr 
    ON cntr.contractnumber = con.ContNumber
LEFT JOIN user accowner 
    ON acc.ownerid = accowner.id
LEFT JOIN user accsalesmanager 
    ON accowner.managerid = accsalesmanager.id
LEFT JOIN (
    SELECT f.accountid
        ,g.managerid
        ,max(g.NAME) CSM
        ,max(g.id) CSMID
    FROM accountteammember f
    JOIN user g ON f.userid = g.id
    WHERE f.teammemberrole = 'AGM'
    GROUP BY f.accountid
        ,g.managerid
    ) acccsm ON acccsm.accountid = acc.id
LEFT JOIN product2 prd 
    ON prod.Code = prd.productcode
LEFT JOIN prdfam prod_fam 
    ON prod_fam.product_family_desc = prd.Product_Family__c
QUALIFY Quantity_Purchased <> 0;

但这些都可以编写为使用acccsmLIKE完成的SUBSELECT LIKE：

WITH best_per_id (
    SELECT cliterm.ContractLineItem_id
        ,to_char(cliterm.Quantity) as Quantity
        ,cliterm.UsedQuantity
        ,cliterm.StartDate
    FROM ContractLineItemTerm cliterm
    WHERE cliterm.PriceRuleItem_id IS NULL
        AND (
            cliterm.EndDate IS NULL
            OR cliterm.EndDate > add_months(sysdate, - 3)
            )
    QUALIFY row_numumber() OVER (PARTITION BY cliterm.ContractLineItem_id 
       ORDER BY cliterm.is_best ) = 1
)
SELECT DISTINCT cust.Id AS Customer_Id
    ,nvl(bpi.Quantity, cli.Quantity) AS Quantity_Purchased
    ,nvl(bpi.UsedQuantity, cli.UsedQuantity) AS Quantity_Used_To_Date
    ,nvl(bpi.StartDate, cli.StartDate) AS sbscription_Term_Start_Date
    ,nvl(bpi.EndDate, cli.EndDate) AS sbscription_Term_End_Date
    ,nvl(to_char(cli.EndDate), 
         IFF(cli.StartDate IS NOT NULL AND con.InitialTerm > 0, 'Auto Renewal', '')
        ) AS Contr_End_Date
FROM ContractLineItem cli
INNER JOIN Contract con 
    ON cli.Contract_id = con.Id
INNER JOIN Customer cust 
    ON con.Customer_id = cust.Id
INNER JOIN Organization org 
    ON org.Customer_id = cust.Id
INNER JOIN Product prod 
    ON cli.Product_id = prod.Id
INNER JOIN Producttype pt 
    ON prod.ProductBrand_id = pt.Id
LEFT JOIN (
    SELECT cliterm.ContractLineItem_id
        ,to_char(cliterm.Quantity) as Quantity
        ,cliterm.UsedQuantity
        ,cliterm.StartDate
    FROM ContractLineItemTerm cliterm
    WHERE cliterm.PriceRuleItem_id IS NULL
        AND (
            cliterm.EndDate IS NULL
            OR cliterm.EndDate > add_months(sysdate, - 3)
            )
    QUALIFY row_numumber() OVER (PARTITION BY cliterm.ContractLineItem_id ORDER BY cliterm.is_best ) = 1
) as bpi
    ON cli.id = bpi.ContractLineItem_id
LEFT JOIN account acc 
    ON cust.SAN = acc.acc__c
LEFT JOIN account acc1 
    ON acc.parentid = acc1.id
LEFT JOIN sbSCRIPTION sb 
    ON sb.id = cli.sforceid
LEFT JOIN sbSCRIPTION pasb 
    ON pasb.id = sb.srequired
LEFT JOIN scontract1 cntr 
    ON cntr.contractnumber = con.ContNumber
LEFT JOIN user accowner 
    ON acc.ownerid = accowner.id
LEFT JOIN user accsalesmanager 
    ON accowner.managerid = accsalesmanager.id
LEFT JOIN (
    SELECT f.accountid
        ,g.managerid
        ,max(g.NAME) CSM
        ,max(g.id) CSMID
    FROM accountteammember f
    JOIN user g ON f.userid = g.id
    WHERE f.teammemberrole = 'AGM'
    GROUP BY f.accountid
        ,g.managerid
    ) acccsm ON acccsm.accountid = acc.id
LEFT JOIN product2 prd 
    ON prod.Code = prd.productcode
LEFT JOIN prdfam prod_fam 
    ON prod_fam.product_family_desc = prd.Product_Family__c
QUALIFY Quantity_Purchased <> 0;

这篇关于如何将Oracle SQL转换为雪花SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆