专家坐堂-select语句语法错误Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://www.foxtable.com/bbs/index.asp)
--  专家坐堂  (http://www.foxtable.com/bbs/list.asp?boardid=2)
----  select语句语法错误  (http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&id=119986)

--  作者：yetle
--  发布时间：2018/6/5 11:44:00
--  select语句语法错误

在sql 管理器查询数据正常，放在sql查询表也正常，但进行筛选和取消筛选的时候提示sql语句语法错误，sql语句如下：

select a.WorkNo AS 车间,a.Outdate as 货期 ,E.Goods as 类别 , A.InDate AS 制单日期 , E.MakeGro as 生产班组,E.Gyno as 物供号,e.ClientOrderno AS 批次号,e.OrderNo as 订单号,
E.ClientStyle as 客款号,a.Ftyno as 厂号,a.Goodsxl as 系列名,A.DJNO as 制单号,b.color as 颜色,b.SizeBi as 杯型,b.size2 as S_70,b.size3 as M_75,b.size4 as L_80,b.size5 as XL_85,b.size6 as XXL_90,
(isnull(b.size2,0)+isnull(b.size3,0)+isnull(b.size4,0)+isnull(b.size5,0)+isnull(b.size6,0)) as 订单合计,
ISNULL(g.insize1,0)+ISNULL(g.insize2,0)+ISNULL(g.insize3,0)+ISNULL(g.insize4,0)+ISNULL(g.insize5,0)+ISNULL(g.insize6,0) as 入库,
ISNULL(d.outsize1,0)+ISNULL(d.outsize2,0)+ISNULL(d.outsize3,0)+ISNULL(d.outsize4,0)+ISNULL(d.outsize5,0)+ISNULL(d.outsize6,0) as 发货
f rom PQ_OutID A Left join PQ_Out_List b on A.OUTID=B.UPID left join pq_main E ON B.Orderno=E.OrderNo left join PQ_Size_Color_Num F on E.id=F.upid left join BAI_FTYNO V ON E.Ftyno=V.Ftyno
left join (select Orderno, ftyno,color,SizeBi,
sum(case when inout=2 then isnull(Size1,0) else 0 end) as outsize1,
sum(case when inout=2 then isnull(Size2,0) else 0 end) as outsize2,
sum(case when inout=2 then isnull(Size3,0) else 0 end) as outsize3,
sum(case when inout=2 then isnull(Size4,0) else 0 end) as outsize4,
sum(case when inout=2 then isnull(Size5,0) else 0 end) as outsize5,
sum(case when inout=2 then isnull(Size6,0) else 0 end) as outsize6
f rom Goods_SizeGroup_List group by Orderno,ftyno,color,SizeBi) d on LTRIM(RTRIM(f.orderno))=LTRIM(RTRIM(d.orderno)) and LTRIM(RTRIM(f.ftyno))=LTRIM(RTRIM(d.ftyno)) and LTRIM(RTRIM(f.color))=LTRIM(RTRIM(d.color)) and ISNULL(f.sizebi,\'\')=ISNULL(d.sizebi,\'\')
left join (select Orderno, ftyno,color,SizeBi,
sum(case when inout=1 then isnull(Size1,0) else 0 end) as insize1,
sum(case when inout=1 then isnull(Size2,0) else 0 end) as insize2,
sum(case when inout=1 then isnull(Size3,0) else 0 end) as insize3,
sum(case when inout=1 then isnull(Size4,0) else 0 end) as insize4,
sum(case when inout=1 then isnull(Size5,0) else 0 end) as insize5,
sum(case when inout=1 then isnull(Size6,0) else 0 end) as insize6
f rom Goods_SizeGroup_List group by Orderno,ftyno,color,SizeBi) g on LTRIM(RTRIM(f.orderno))=LTRIM(RTRIM(g.orderno)) and LTRIM(RTRIM(f.ftyno))=LTRIM(RTRIM(g.ftyno)) and LTRIM(RTRIM(f.color))=LTRIM(RTRIM(g.color)) and ISNULL(f.sizebi,\'\')=ISNULL(g.sizebi,\'\')
where A.InDate>\'2017-06-01\'

取消筛选语句：（提示错误：\'Goods_SizeGroup_List\' 附近有语法错误。）

DataTables("dbscmx").LoadFilter=""
DataTables("dbscmx").Load()
Tables("dbscmx").Filter=""

筛选语句：（\')\' 附近有语法错误。）
Dim stade As WinForm.DateTimePicker = e.Form.Controls("DateTimePicker1")
Dim Endde As WinForm.DateTimePicker = e.Form.Controls("DateTimePicker2")
\'Dim pd01 As WinForm.ComboBox = e.Form.Controls("ComboBox2")
Dim cs01 As WinForm.ComboBox = e.Form.Controls("ComboBox1")
Dim cs02 As WinForm.ComboBox = e.Form.Controls("ComboBox2")
Dim cs03 As WinForm.ComboBox = e.Form.Controls("ComboBox3")
Dim cs04 As WinForm.ComboBox = e.Form.Controls("ComboBox4")

Dim dt As DataTable=DataTables("dbscmx")
Dim filter As String = "1=1"
With e.Form.Controls("DateTimePicker1")
If .Value IsNot Nothing Then
If stade.text >"" Then
Filter = Filter & " And "
End If
\' filter= filter & " and a.InDate >=\'" & stade.text & "\'"
filter= filter & "a.InDate >=\'" & stade.text & "\'"
End If
End With

With e.Form.Controls("DateTimePicker2")
If .Value IsNot Nothing Then
If Endde.text>"" Then
Filter = Filter & " And "
End If

filter=filter & "a.InDate <= \'" & Endde.text & "\'"
End If
End With

With e.Form.Controls("ComboBox1")
If .Value IsNot Nothing Then
If cs01.text>"" Then
Filter = Filter & " And "
End If
filter=filter & "E.Gyno = \'" & cs01.text & "\'"
End If
End With

With e.Form.Controls("ComboBox2")
If .Value IsNot Nothing Then
If cs02.text>"" Then
Filter = Filter & " And "
End If
filter=filter & "E.Goods = \'" & cs02.text & "\'"
End If
End With

With e.Form.Controls("ComboBox3")
If .Value IsNot Nothing Then
If cs03.text>"" Then
Filter = Filter & " And "
End If
filter=filter & "a.Ftyno = \'" & cs03.text & "\'"
End If
End With

With e.Form.Controls("ComboBox4")
If .Value IsNot Nothing Then
If cs04.text>"" Then
Filter = Filter & " And "
End If
filter=filter & "E.ClientStyle = \'" & cs04.text & "\'"
End If
End With

If Filter > "" Then
dt.LoadFilter=filter
dt.Load()
End If

--  作者：有点甜
--  发布时间：2018/6/5 12:06:00
--

1、弹出你合成的条件 filter，直接加载你sql语句后面，能否正常查询？必须确保能查询

2、设置筛选，是用途tables("").filter = "" 这样设置的，你load是加载数据，加载对于查询表比较麻烦，条件必须写对。

--  作者：yetle
--  发布时间：2018/6/5 14:02:00
--
Tables("dbscmx").Filter=" select a.WorkNo AS 车间,a.Outdate as 货期 ,E.Goods as 类别 , A.InDate AS 制单日期 , E.MakeGro as 生产班组,E.Gyno as 物供号,e.ClientOrderno AS 批次号,e.OrderNo as 订单号,E.ClientStyle as 客款号,a.Ftyno as 厂号,a.Goodsxl as 系列名,A.DJNO as 制单号,b.color as 颜色,b.SizeBi as 杯型,b.size2 as S_70,b.size3 as M_75,b.size4 as L_80,b.size5 as XL_85,b.size6 as XXL_90,(isnull(b.size2,0)+isnull(b.size3,0)+isnull(b.size4,0)+isnull(b.size5,0)+isnull(b.size6,0)) as 订单合计,ISNULL(g.insize1,0)+ISNULL(g.insize2,0)+ISNULL(g.insize3,0)+ISNULL(g.insize4,0)+ISNULL(g.insize5,0)+ISNULL(g.insize6,0) as 入库,ISNULL(d.outsize1,0)+ISNULL(d.outsize2,0)+ISNULL(d.outsize3,0)+ISNULL(d.outsize4,0)+ISNULL(d.outsize5,0)+ISNULL(d.outsize6,0) as 发货 f rom PQ_OutID A Left join PQ_Out_List b on A.OUTID=B.UPID left join pq_main E ON B.Orderno=E.OrderNo left join PQ_Size_Color_Num F on E.id=F.upid left join BAI_FTYNO V ON E.Ftyno=V.Ftyno left join (select Orderno, ftyno,color,SizeBi, sum(case when inout=2 then isnull(Size1,0) else 0 end) as outsize1,sum(case when inout=2 then isnull(Size2,0) else 0 end) as outsize2, sum(case when inout=2 then isnull(Size3,0) else 0 end) as outsize3,sum(case when inout=2 then isnull(Size4,0) else 0 end) as outsize4,sum(case when inout=2 then isnull(Size5,0) else 0 end) as outsize5,sum(case when inout=2 then isnull(Size6,0) else 0 end) as outsize6 f rom Goods_SizeGroup_List group by Orderno,ftyno,color,SizeBi) d on LTRIM(RTRIM(f.orderno))=LTRIM(RTRIM(d.orderno)) and LTRIM(RTRIM(f.ftyno))=LTRIM(RTRIM(d.ftyno)) and LTRIM(RTRIM(f.color))=LTRIM(RTRIM(d.color)) and ISNULL(f.sizebi,\'\')=ISNULL(d.sizebi,\'\') left join (select Orderno, ftyno,color,SizeBi,sum(case when inout=1 then isnull(Size1,0) else 0 end) as insize1,sum(case when inout=1 then isnull(Size2,0) else 0 end) as insize2,sum(case when inout=1 then isnull(Size3,0) else 0 end) as insize3,sum(case when inout=1 then isnull(Size4,0) else 0 end) as insize4,sum(case when inout=1 then isnull(Size5,0) else 0 end) as insize5,sum(case when inout=1 then isnull(Size6,0) else 0 end) as insize6 f rom Goods_SizeGroup_List group by Orderno,ftyno,color,SizeBi) g on LTRIM(RTRIM(f.orderno))=LTRIM(RTRIM(g.orderno)) and LTRIM(RTRIM(f.ftyno))=LTRIM(RTRIM(g.ftyno)) and LTRIM(RTRIM(f.color))=LTRIM(RTRIM(g.color)) and ISNULL(f.sizebi,\'\')=ISNULL(g.sizebi,\'\')where A.InDate>\'2017-06-01\' "

提示：
.NET Framework 版本：2.0.50727.3655
Foxtable 版本：2018.3.9.1
错误所在事件：
详细错误信息：
语法错误:“a”运算符后缺少操作数。

条件肯定是对的，在数据库管理器查询没有问题，在用窗口表格调用sql查询表也没有问题，就是在筛选的时候有问题

--  作者：有点甜
--  发布时间：2018/6/5 16:00:00
--

如果sql语句是对的，那么就使用fill执行sql语句生成表格，如

http://www.foxtable.com/webhelp/scr/1777.htm