R 中的 SQLite - result_create(conn@ptr, statement) 中的错误:靠近“)"；

问题描述

对于这个基本问题很抱歉，但我试图在 R 中使用 sqldf 函数运行以下代码，但每次它都显示result_create(conn@ptr, statement) 中的错误: 附近")': 语法错误"，我找不到错误的确切位置.

Sorry for the basic question, but I am trying to run the following code with sqldf function in R, but every time it shows "Error in result_create(conn@ptr, statement) : near ')': syntax error" and I can't find exactly where the error is.

我尝试以不同的方式缩进/格式化，但接近 ')'"的错误指示一直存在.

I tried to indent/format in different ways but the error indication to "near ')'" persists all the time.

似乎所有括号都可以.

代码:

sqldf("

SELECT  Cia_2, Nombre_cia_2, N_orden_2, Tipo_orden_2, N_linea_2, N_direc_2, Nombre_alfa_2, Fecha_orden_2,
           Surtido_programado_2, Original_prometida_2,

           N_orden, Tipo_orden, N_linea, Cia,

           Cant_recibida_2, Cant_pendiente_2, Cantidad_2, estado_sig_2, estado_ult_2,
           (case when [N_orden_2] is null then 'SinOC' else 'ConOC' end) as TieneOC,

           (case when [Cant_recibida_2]=0 and [Cant_pendiente_2]=0 and [estado_sig_2]=999 then 'Anulada'
           else (case when [Cant_recibida_2] = [Cantidad_2] and [estado_sig_2] = 999 and [Tiene_OC_2] = 'SinOC' then 'Anulada' else 'NoAnulada' )) as Anulada,

           (case when [N_direc_2] = 35999999 then 'Normal'
            else (case when [N_direc_2] = 35999998 then 'Urgente'
                  else (case when [N_direc_2] = 35999997 then 'Emergente'
                        else (case when [N_direc_2] = 35999995 then 'Proyecto'
                              else (case when [N_direc_2] = 35999994 then 'Importaciones' else 'Expost' ))))) as TipoReq,

          Unidad_negocios_2,
          aprob_or_2.LastOfFecha_aprobac,
          [SumOfOrden de Cambio_2]

          FROM bases_or_con_oc
          LEFT JOIN aprob_or_2 ON (N_orden = aprob_or_2.N_orden_OR)
                               AND (Tipo_orden = aprob_or_2.Tipo_ordenOR)
                               AND (N_linea = aprob_or_2.N_lineaOR)
          WHERE  estado_sig_2 > 110
                               AND (case when [Cant_recibida_2] = 0 and [Cant_pendiente_2] = 0 and [estado_sig_2] = 999 then 'Anulada'
                                    else (case when [Cant_recibida_2] = [Cantidad_2] and [estado_sig_2] = 999 and [Tiene_OC_2] = 'SinOC'
                                    then 'Anulada' else 'NoAnulada')) = 'noAnulada'
     ")

错误图片:

我也尝试排除一些行，以便找出错误点...

I also tried to exclude some lines in order to and find out the error point...

你知道发生了什么吗?

推荐答案

考虑使用 SQLite 支持的 CTE 子句 WITH() 简化您的 SQL，以避免为 重新键入相同的计算列SELECT 和 WHERE 子句中的 Anulada.并确保用 END 关闭所有 CASE 语句:

Consider simplifying your SQL using the CTE clause, WITH(), supported by SQLite to avoid retyping same calculated column for Anulada in SELECT and WHERE clauses. And be sure to close all CASE statements with END:

WITH cte AS
     (SELECT Cia_2, Nombre_cia_2, N_orden_2, Tipo_orden_2, N_linea_2, N_direc_2,
             Nombre_alfa_2, Fecha_orden_2, Surtido_programado_2, Original_prometida_2,
             N_orden, Tipo_orden, N_linea, Cia,
             Cant_recibida_2, Cant_pENDiente_2, Cantidad_2, estado_sig_2, estado_ult_2,
             CASE WHEN [N_orden_2] IS NULL THEN 'SinOC' ELSE 'ConOC' END AS TieneOC,
             CASE
                 WHEN [Cant_recibida_2]=0 and [Cant_pENDiente_2]=0 AND [estado_sig_2] = 999
                 THEN 'Anulada'
                 ELSE
                     CASE
                          WHEN [Cant_recibida_2] = [Cantidad_2] AND [estado_sig_2] = 999 and [Tiene_OC_2] = 'SinOC'
                          THEN 'Anulada'
                          ELSE 'NoAnulada'
                     END
             END AS Anulada,
             CASE [N_direc_2]
                  WHEN 35999999 THEN 'Normal'
                  WHEN 35999998 THEN 'Urgente'
                  WHEN 35999997 THEN 'Emergente'
                  WHEN 35999995 THEN 'Proyecto'
                  WHEN 35999994 THEN 'Importaciones'
                  ELSE 'Expost'
             END AS TipoReq,
             Unidad_negocios_2, aprob_or_2.LastOfFecha_aprobac, [SumOfOrden de Cambio_2]

      FROM bases_or_con_oc
      LEFT JOIN aprob_or_2 ON (N_orden = aprob_or_2.N_orden_OR)
            AND (Tipo_orden = aprob_or_2.Tipo_ordenOR)
            AND (N_linea = aprob_or_2.N_lineaOR)
      WHERE  estado_sig_2 > 110
     )

SELECT * FROM cte WHERE [Anulada] = 'NoAnulada';

这篇关于R 中的 SQLite - result_create(conn@ptr, statement) 中的错误:靠近“)"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！