执行Oracle CTE

Question

我在Oracle中构建了一个关系数据模型，现在使用Python创建了一个GUI。我需要一个SQL语句在IDE中执行，但是得到一条cx_Oracle.DatabaseError: ORA-00936: missing expression错误消息。这是一个在TOAD中运行良好的CTE，当我删除CTE并放入一个简单的SQl语句时，它执行得很好。在

我可以在我的数据库中构建一个视图并执行select * from，但我不想这样。在

我是Python新手，所以我确信有更好的方法来实现这一点。在

import cx_Oracle

con = cx_Oracle.connect('Example', 'Example', "Example")
cur = con.cursor()


statement = ("with r1 as (" +
             " select " +
             " r.PARENT_ITEM_id, " +
             " D.SC_ID, " +
             " --F.TONS" +
             " SUM(ROUND(F.TONS*Pic_Distro*2000*s.stk_lvl_mult)) as Stocking_Lvl" +
             "    from PIC_DISTRO_TBL D" +
             "    Left Join Part_Velocity_TBL P on (P.item_ID = D.Item_ID and D.SC_ID = P.SC_ID)" +
             "    Left Join Forecast_TBL F on  (D.Bucket_ID = F.Bucket_ID and D.SC_ID =F.SC_ID)" +
             "    left join Stock_lvl_tbl S on (S.Velocity_id = P.VELOCITY_ID)" +
             "    left join item_tbl I on (i.item_ID = D.ITEM_ID)" +
             "    left join parent_item_tbl R on (r.PARENT_ITEM_id = i.PARENT_ITEM_id)" +
             "        Where F.MTH = '4'" +
             "        and F.YEAR = '2017'" +
             "        and P.Velocity_id in ('A','B','C')" +
             "        and D.SC_ID in ('01','02')" +
             "      --  and SUM(ROUND(F.TONS*Pic_Distro*2000*s.stk_lvl_mult)) > 0" +
             "            Group by    " +
             "            r.PARENT_ITEM_id, D.SC_ID " +
             "                Order by " +
             "                 D.SC_ID DESC, Stocking_lvl DESC" +
             ")," +
             "R2 as (" +
             "select r.Parent_Item_ID, o.SC_ID, " +
             "coalesce(sum(avail_wt), 0) as Avail_Wt" +
             "    from" +
             "    open_inv_tbl O" +
             "    left join item_tbl I on (i.item_ID = o.ITEM_ID)" +
             "    left join parent_item_tbl R on (r.PARENT_ITEM_id = i.PARENT_ITEM_id)" +
             "        Where r.Parent_item_ID is not null" +
             "        Group by r.Parent_Item_ID,o.SC_ID)" +
             "select " +
             " r1.PARENT_ITEM_id, " +
             " R1.SC_ID, R1.Stocking_Lvl , " +
             " coalesce(R2.Avail_wt, 0 ) as Avail_Wt,  " +
             " coalesce(R2.Avail_wt/R1.Stocking_Lvl, 0) as Precantage" +
             "    From R1" +
             "    left join R2 on (R1.parent_item_id  = R2.parent_item_id and R1.Sc_ID = R2.Sc_ID) " +
             "        Where R1.Stocking_lvl > '0' " +
             "        Order by SC_id Desc, Stocking_Lvl Desc)" )

cur.arraysize = 2000
cur.execute(statement)

Answer 1

Python提供了多行字符串，如果用三个引号括起来。 尝试执行单个字符串并检查查询是否正确。在

statement = """
    with r1 as (
        select 
            r.PARENT_ITEM_id, 
            D.SC_ID,
              F.TONS
        ...
    """
cur.execute(statement)