专家坐堂-表达式过于复杂Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.net/bbs/index.asp)
--  专家坐堂  (http://foxtable.net/bbs/list.asp?boardid=2)
----  表达式过于复杂  (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=118780)

--  作者：联友
--  发布时间：2018/5/10 20:59:00
--  表达式过于复杂

Dim s1 As Integer = val(Left("2017年秋季",4))
Dim SQL As String = "IIF(b.学期 Like \'%秋%\',IIF((\'"& s1 &"\' - val(Left(b.学期,4))) *100 + val(班级) < 1000,(\'"& s1 &"\'- val(Left(b.学期,4))) *100 + val(班级)),IIF(b.学期 Like \'%春%\',IIF((\'"& s1 &"\' - val(Left(b.学期,4))) *100 + val(班级) < 1000,(\'"& s1 &"\' - val(Left(b.学期,4))) *100 + val(班级))))"
Dim SQL1 As String = "IIF(Left(" & SQL & ",1) = 1,\'一年级\',IIF(Left(" & SQL & ",1) = 2,\'二年级\',IIF(Left(" & SQL & ",1) = 3,\'三年级\',IIF(Left(" & SQL & ",1) = 4,\'四年级\',IIF(Left(" & SQL & ",1) = 5,\'五年级\',IIF(Left(" & SQL & ",1) = 6,\'六年级\',IIF(Left(" & SQL & ",1) = 7,\'七年级\',IIF(Left(" & SQL & ",1) = 8,\'八年级\',IIF(Left(" & SQL & ",1) = 9,\'九年级\')))))))))"
Dim SQL2 As String = "Select b.学期,b.学生编号,学生姓名,"& SQL1 &" As 年级 Fr om (({学生基本信息} a Inner JOIN {缴退费信息} b ON a.学生编号 = b.学生编号) Inner JOIN {违规信息} c ON b.学生编号 = c.学生编号) Inner JOIN (Select 学生编号,max(缴费日期) As 缴费日期 Fr om {缴退费信息} WHERE 缴费项目 = \'学费\' And 缴费状态 = \'已缴\' GROUP BY 学生编号) n on b.学生编号 = n.学生编号 And b.缴费日期 = n.缴费日期 WHERE 缴费项目 = \'学费\' And 缴费状态 = \'已缴\' And c.审批状态 = \'拒绝报名\'"

错误信息：表达式过于复杂在查询表达式 \'IIF(Left(IIF(b.学期 Like \'%秋%\',IIF((\'2017\' - val(Left(b.学期,4))) *100 + val(班级) < 1000,(\'2017\'- val(Left(b.学期,4))) *100 + val(班级)),IIF(b.学期 Like \'%春%\',IIF((\'2017\' - val(Left(b.学期,4))) *100 + val(班级) < 1000,(\'2017\' - val(Left(b.学期,4))) *100 + val(班级)))),1) = \' 中。
请老师帮我看一下问题在哪？删除任意类似红色字段就可以，请老师执教，谢谢！

[此贴子已经被作者于2018/5/10 21:14:10编辑过]

--  作者：有点甜
--  发布时间：2018/5/10 21:05:00
--

IIF(Left(" & SQL & ",1) = 9, \'九年级\', null)

iif有三个参数的，如 iif(条件, 值1, 值2)

--  作者：联友
--  发布时间：2018/5/10 21:18:00
--
老师不行

--  作者：有点甜
--  发布时间：2018/5/10 21:26:00
--

1、贴出可以的代码；

2、贴出不可以的代码；

--  作者：联友
--  发布时间：2018/5/10 21:42:00
--

可以代码：
Dim s1 As Integer = val(Left("2017年秋季",4))
Dim SQL As String = "IIF(b.学期 Like \'%秋%\',IIF((\'"& s1 &"\' - val(Left(b.学期,4))) *100 + val(班级) < 1000,(\'"& s1 &"\'- val(Left(b.学期,4))) *100 + val(班级)),IIF(b.学期 Like \'%春%\',IIF((\'"& s1 &"\' - val(Left(b.学期,4))) *100 + val(班级) < 1000,(\'"& s1 &"\'- val(Left(b.学期,4))) *100 + val(班级))))"
Dim SQL1 As String = "IIF(Left(" & SQL & ",1) = 1,\'一年级\',IIF(Left(" & SQL & ",1) = 2,\'二年级\',IIF(Left(" & SQL & ",1) = 3,\'三年级\',IIF(Left(" & SQL & ",1) = 4,\'四年级\',IIF(Left(" & SQL & ",1) = 5,\'五年级\',IIF(Left(" & SQL & ",1) = 6,\'六年级\',IIF(Left(" & SQL & ",1) = 8,\'八年级\',IIF(9 = 9,\'九年级\'))))))))"
Dim SQL2 As String = "Select b.学期,b.学生编号,学生姓名,"& SQL1 &" As 年级 Fr om (({学生基本信息} a Inner JOIN {缴退费信息} b ON a.学生编号 = b.学生编号) Inner JOIN {违规信息} c ON b.学生编号 = c.学生编号) Inner JOIN (Select 学生编号,max(缴费日期) As 缴费日期 Fr om {缴退费信息} WHERE 缴费项目 = \'学费\' And 缴费状态 = \'已缴\' GROUP BY 学生编号) n on b.学生编号 = n.学生编号 And b.缴费日期 = n.缴费日期 WHERE 缴费项目 = \'学费\' And 缴费状态 = \'已缴\' And c.审批状态 = \'拒绝报名\'"
不可以代码：
Dim s1 As Integer = val(Left("2017年秋季",4))
Dim SQL As String = "IIF(b.学期 Like \'%秋%\',IIF((\'"& s1 &"\' - val(Left(b.学期,4))) *100 + val(班级) < 1000,(\'"& s1 &"\'- val(Left(b.学期,4))) *100 + val(班级)),IIF(b.学期 Like \'%春%\',IIF((\'"& s1 &"\' - val(Left(b.学期,4))) *100 + val(班级) < 1000,(\'"& s1 &"\'- val(Left(b.学期,4))) *100 + val(班级))))"
Dim SQL1 As String = "IIF(Left(" & SQL & ",1) = 1,\'一年级\',IIF(Left(" & SQL & ",1) = 2,\'二年级\',IIF(Left(" & SQL & ",1) = 3,\'三年级\',IIF(Left(" & SQL & ",1) = 4,\'四年级\',IIF(Left(" & SQL & ",1) = 5,\'五年级\',IIF(Left(" & SQL & ",1) = 6,\'六年级\',IIF(Left(" & SQL & ",1) = 7,\'七年级\',IIF(Left(" & SQL & ",1) = 8,\'八年级\',IIF(9 = 9,\'九年级\'))))))))"
Dim SQL2 As String = "Select b.学期,b.学生编号,学生姓名,"& SQL1 &" As 年级 Fr om (({学生基本信息} a Inner JOIN {缴退费信息} b ON a.学生编号 = b.学生编号) Inner JOIN {违规信息} c ON b.学生编号 = c.学生编号) Inner JOIN (Select 学生编号,max(缴费日期) As 缴费日期 Fr om {缴退费信息} WHERE 缴费项目 = \'学费\' And 缴费状态 = \'已缴\' GROUP BY 学生编号) n on b.学生编号 = n.学生编号 And b.缴费日期 = n.缴费日期 WHERE 缴费项目 = \'学费\' And 缴费状态 = \'已缴\' And c.审批状态 = \'拒绝报名\'"
区别：可以
Dim SQL1 As String = "IIF(Left(" & SQL & ",1) = 1,\'一年级\',IIF(Left(" & SQL & ",1) = 2,\'二年级\',IIF(Left(" & SQL & ",1) = 3,\'三年级\',IIF(Left(" & SQL & ",1) = 4,\'四年级\',IIF(Left(" & SQL & ",1) = 5,\'五年级\',IIF(Left(" & SQL & ",1) = 6,\'六年级\',IIF(Left(" & SQL & ",1) = 8,\'八年级\',IIF(Left(" & SQL & ",1) = 9,\'九年级\'))))))))"
不可以
Dim SQL1 As String = "IIF(Left(" & SQL & ",1) = 1,\'一年级\',IIF(Left(" & SQL & ",1) = 2,\'二年级\',IIF(Left(" & SQL & ",1) = 3,\'三年级\',IIF(Left(" & SQL & ",1) = 4,\'四年级\',IIF(Left(" & SQL & ",1) = 5,\'五年级\',IIF(Left(" & SQL & ",1) = 6,\'六年级\',IIF(Left(" & SQL & ",1) = 7,\'七年级\',IIF(Left(" & SQL & ",1) = 8,\'八年级\',IIF(Left(" & SQL & ",1) = 9,\'九年级\'))))))))"

[此贴子已经被作者于2018/5/10 21:47:13编辑过]

--  作者：有点甜
--  发布时间：2018/5/10 21:45:00
--

Dim SQL1 As String = "IIF(Left(" & SQL & ",1) = 1,\'一年级\',IIF(Left(" & SQL & ",1) = 2,\'二年级\',IIF(Left(" & SQL & ",1) = 3,\'三年级\',IIF(Left(" & SQL & ",1) = 4,\'四年级\',IIF(Left(" & SQL & ",1) = 5,\'五年级\',IIF(Left(" & SQL & ",1) = 6,\'六年级\',IIF(Left(" & SQL & ",1) = 7,\'七年级\',IIF(Left(" & SQL & ",1) = 8,\'八年级\',IIF(9 = 9,\'九年级\',null)))))))))"

--  作者：联友
--  发布时间：2018/5/10 21:51:00
--

这样可以
Dim SQL1 As String = "IIF(Left(" & SQL & ",1) = 1,\'一年级\',IIF(Left(" & SQL & ",1) = 2,\'二年级\',IIF(Left(" & SQL & ",1) = 3,\'三年级\',IIF(Left(" & SQL & ",1) = 4,\'四年级\',IIF(Left(" & SQL & ",1) = 5,\'五年级\',IIF(Left(" & SQL & ",1) = 6,\'六年级\',IIF(Left(" & SQL & ",1) = 7,\'七年级\',IIF(Left(" & SQL & ",1) = 8,\'八年级\',IIF(9 = 9,\'九年级\',null)))))))))"
这样不可以

Dim SQL1 As String = "IIF(Left(" & SQL & ",1) = 1,\'一年级\',IIF(Left(" & SQL & ",1) = 2,\'二年级\',IIF(Left(" & SQL & ",1) = 3,\'三年级\',IIF(Left(" & SQL & ",1) = 4,\'四年级\',IIF(Left(" & SQL & ",1) = 5,\'五年级\',IIF(Left(" & SQL & ",1) = 6,\'六年级\',IIF(Left(" & SQL & ",1) = 7,\'七年级\',IIF(Left(" & SQL & ",1) = 8,\'八年级\',IIF(Left(" & SQL & ",1) = 9,\'九年级\',null)))))))))"

[此贴子已经被作者于2018/5/10 21:53:25编辑过]

--  作者：有点甜
--  发布时间：2018/5/10 21:59:00
--
应该是iif嵌套个数太多导致的，这个没办法，简化你的sql语句吧，只能这样。

--  作者：联友
--  发布时间：2018/5/10 22:20:00
--

我把代码长度减短也不行，
可能是
Dim SQL As String = "IIF(b.学期 Like \'%秋%\',IIF((\'"& s1 &"\' - val(Left(b.学期,4))) *100 + val(班级) < 1000,(\'"& s1 &"\'- val(Left(b.学期,4))) *100 + val(班级)),IIF(b.学期 Like \'%春%\',IIF((\'"& s1 &"\' - val(Left(b.学期,4))) *100 + val(班级) < 1000,(\'"& s1 &"\' - val(Left(b.学期,4))) *100 + val(班级)+100)))"
或下面组多了
Dim SQL1 As String = "IIF(Left(" & SQL & ",1) = 1,\'一年级\',IIF(Left(" & SQL & ",1) = 2,\'二年级\',IIF(Left(" & SQL & ",1) = 3,\'三年级\',IIF(Left(" & SQL & ",1) = 4,\'四年级\',IIF(Left(" & SQL & ",1) = 5,\'五年级\',IIF(Left(" & SQL & ",1) = 6,\'六年级\',IIF(Left(" & SQL & ",1) = 7,\'七年级\',IIF(Left(" & SQL & ",1) = 8,\'八年级\',IIF(Left(" & SQL & ",1) = 9,\'九年级\',null)))))))))"

--  作者：有点甜
--  发布时间：2018/5/10 22:23:00
--
iif嵌套不能过多，如果过多就会报错。