SQL表达式太复杂了 [英] SQL expression too complex

问题描述

大家好吧:)


我刚刚开始使用access / sql。

我马上遇到了这个问题SQL表达式过于复杂 ;


我知道谷歌了很多，而且我见过的唯一解决方法是什么，

只是改写我的代码/查询。


情况：

我正在将表格分类为它们的使用方式（打印，存储，只读等等）。

但是表格可以使用20种不同的方式，

所以我最初的方法是编写一个大的IFF语句，

整理每个案例。


然而，大约11个左右的IFF陈述，我开始得到这个错误，

所以我想知道是否有人对如何制作这个有任何想法排序更容易/可行。


代码：

这就是我的巨大IFF声明：


如果查询<> ""然后


query = query& ，iif（mq.ffileusage ="& Chr（34）&" 0"& Chr（34）&，& Chr（34）&仅查看& ; Chr（34）_

&"，iif（mq.ffileusage ="& Chr（34）&"〜"& Chr（34）&" ，& Chr（34）&"仅查看"& Chr（34）_

&"，iif（mq.ffileusage ="& Chr（34） &"""& Chr（34）&""& Chr（34）&View and Print"& Chr（34）_

& ，iif（mq.ffileusage ="& Chr（34）& - 1"& Chr（34）&，& Chr（34）&View and Print ;& Chr（34）_

&"，iif（mq.ffileusage ="& Chr（34）&" ~1~2"& Chr（34） &"，"& Chr（34）&" View and Print and Fill and Print"& Chr（34）_

&"，iif（mq.ffileusage =& Chr（34）&&quo吨; 2英寸&安培; Chr（34）& "，" &安培; Chr（34）& 填写并打印 &安培; Chr（34）_

& "，iif（mq.ffileusage ="& Chr（34）&" ~2"& Chr（34）&"，& Chr（34）&"" Fill and Print" ;& Chr（34）_

&"，iif（mq.ffileusage ="& Chr（34）&""& Chr（34）&" ;，"& Chr（34）&" Fill and Print&Submit"& Chr（34）_

&"，iif（mq.ffileusage ="& Chr（34）&2~3& Chr（34）&"& Chr（34）&Fill and Print and Submit& Chr（34）_

&"，iif（mq.ffileusage ="& Chr（34）&""& Chr（34）&"，& Chr（34） &Print and Fill and Mail& Chr（34）_

&"，iif（mq.ffileusage =& Chr（34）&" 5" & Chr（34）&"，& Chr（34）&" Fill Print Save& Chr（34）_

&"，iif（mq .ffileusage ="&am磷; Chr（34）& " H" &安培; Chr（34）& "，" &安培; Chr（34）& 纸质复印件 &安培; Chr（34）_

& ，iif（mq.ffileusage ="& Chr（34）&" ~H& Chr（34）&，& Chr（34）&Paper Copy" & Chr（34）_

&"，iif（mq.ffileusage ="& Chr（34）&"& Chr（34）&" ，& Chr（34）&Source Application& Chr（34）_

&"，iif（mq.ffileusage ="& Chr（34） &" ~H~0"& Chr（34）&"，& Chr（34）&" xxxxxxx"& Chr（34）_

& <，iif（mq.ffileusage =&& Chr（34）&" ~H-1"& Chr（34）&"& Chr（34）& xxxxxx"& Chr（34）_

&"，iif（mq.ffileusage ="& Chr（34）&" ~H~2"& Chr（34 ）&"，"& Chr（34）&" xxxxx"& Chr（34）_

&"，iif（mq.ffileusage ="& Chr（34）&H-1& Chr（34）&a熔点; "，" &安培; Chr（34）& " XXXX" &安培; Chr（34）_

& ，iif（mq.ffileusage =&& Chr（34）&H~2~1& Chr（34）&，"& Chr（34）& xxx"& Chr（34）_

&"，iif（mq.ffileusage ="& Chr（34）&" H~1~4"& Chr（ 34）&"，"& Chr（34）&" xx"& Chr（34）_

&"，& Chr（34）& ;"& Chr（34）&））））））））））））））））））））作为[Attatchment Usage]


**基本上，我正在做的就是检查表格中的各种值（表单用途），

，当它在excel中输出表单时，它会相应地标记它。

**即。如果读取的值是0，则读取的值是0。然后它将输出仅查看在excel文件中


我知道代码有效，因为如果我将IFF语句的数量限制为12，它将正常工作。

但是尽快随着我的添加，它会给我过于复杂的感觉。错误。


任何人都知道如何让这更简单？

我想的可能是一个CASE函数或其他东西，

但我不确定，因为我几天前才开始使用sql。



感谢您的帮助：）

k，40

解决方案

大家好:)


我刚刚开始使用access / sql。

我马上遇到了这个问题SQL表达式太复杂了


我google 这意味着什么，以及我见过的唯一解决办法，

只是改写我的代码/查询。


情况：

我正在将表单分类为它们的使用方式（打印，存储，只读等等）。

但是表单可以有20种不同的方式使用，

所以我最初的方法是写一个大的IFF声明，

整理每个案例。


然而，大约11左右的IFF声明，我开始得到这个错误，

所以我想知道是否有人对如何使这种排序更容易有任何想法/
代码：

这就是我的IFF声明：/ b如果查询<> ""然后


query = query& ，iif（mq.ffileusage ="& Chr（34）&" 0"& Chr（34）&，& Chr（34）&仅查看& ; Chr（34）_

&"，iif（mq.ffileusage ="& Chr（34）&"〜"& Chr（34）&" ，& Chr（34）&"仅查看"& Chr（34）_

&"，iif（mq.ffileusage ="& Chr（34） &"""& Chr（34）&""& Chr（34）&View and Print"& Chr（34）_

& ，iif（mq.ffileusage ="& Chr（34）& - 1"& Chr（34）&，& Chr（34）&View and Print ;& Chr（34）_

&"，iif（mq.ffileusage ="& Chr（34）&" ~1~2"& Chr（34） &"，"& Chr（34）&" View and Print and Fill and Print"& Chr（34）_

&"，iif（mq.ffileusage =& Chr（34）&&quo吨; 2英寸&安培; Chr（34）& "，" &安培; Chr（34）& 填写并打印 &安培; Chr（34）_

& "，iif（mq.ffileusage ="& Chr（34）&" ~2"& Chr（34）&"，& Chr（34）&"" Fill and Print" ;& Chr（34）_

&"，iif（mq.ffileusage ="& Chr（34）&""& Chr（34）&" ;，"& Chr（34）&" Fill and Print&Submit"& Chr（34）_

&"，iif（mq.ffileusage ="& Chr（34）&2~3& Chr（34）&"& Chr（34）&Fill and Print and Submit& Chr（34）_

&"，iif（mq.ffileusage ="& Chr（34）&""& Chr（34）&"，& Chr（34） &Print and Fill and Mail& Chr（34）_

&"，iif（mq.ffileusage =& Chr（34）&" 5" & Chr（34）&"，& Chr（34）&" Fill Print Save& Chr（34）_

&"，iif（mq .ffileusage ="&am磷; Chr（34）& " H" &安培; Chr（34）& "，" &安培; Chr（34）& 纸质复印件 &安培; Chr（34）_

& ，iif（mq.ffileusage ="& Chr（34）&" ~H& Chr（34）&，& Chr（34）&Paper Copy" & Chr（34）_

&"，iif（mq.ffileusage ="& Chr（34）&"& Chr（34）&" ，& Chr（34）&Source Application& Chr（34）_

&"，iif（mq.ffileusage ="& Chr（34） &" ~H~0"& Chr（34）&"，& Chr（34）&" xxxxxxx"& Chr（34）_

& <，iif（mq.ffileusage =&& Chr（34）&" ~H-1"& Chr（34）&"& Chr（34）& xxxxxx"& Chr（34）_

&"，iif（mq.ffileusage ="& Chr（34）&" ~H~2"& Chr（34 ）&"，"& Chr（34）&" xxxxx"& Chr（34）_

&"，iif（mq.ffileusage ="& Chr（34）&H-1& Chr（34）&a熔点; "，" &安培; Chr（34）& " XXXX" &安培; Chr（34）_

& ，iif（mq.ffileusage =&& Chr（34）&H~2~1& Chr（34）&，"& Chr（34）& xxx"& Chr（34）_

&"，iif（mq.ffileusage ="& Chr（34）&" H~1~4"& Chr（ 34）&"，"& Chr（34）&" xx"& Chr（34）_

&"，& Chr（34）& ;"& Chr（34）&））））））））））））））））））））作为[Attatchment Usage]


**基本上，我正在做的就是检查表格中的各种值（表单用途），

，当它在excel中输出表单时，它会相应地标记它。

**即。如果读取的值是0，则读取的值是0。然后它将输出仅查看在excel文件中


我知道代码有效，因为如果我将IFF语句的数量限制为12，它将正常工作。

但是尽快随着我的添加，它会给我过于复杂的感觉。错误。


任何人都知道如何让这更简单？

我想的可能是一个CASE函数或其他东西，

但我不确定，因为我几天前才开始使用sql。



感谢您的帮助：）< br $>
k，40

我不知道Access，但我知道在Excel中，嵌套的if语句只能达到7层。我尝试进行快速谷歌搜索，但没有找到任何关于Access的最大层数，但这可能是你的问题？

你好mlcampeau，

在Access中我可以设法编写13个IFF语句（如上所示），

但是当我超过13时，我开始得到太复杂的信息。错误。


所以我想知道如何更有效地编写这20个IFF语句，

所以它不是太复杂



感谢您的帮助，:)

k，40

我遇到同样的问题访问，表达是wayyyyyyyyyyyyyyyy太复杂！ 选择案例几乎总是最好的方式，如果你正在谈论超过3或4个选项，并且当然是这类情况的唯一选择，有十多种可能性！它将使您的代码现在更容易理解，并且将来更容易维护。


祝你好运！


Linq < b> 0）>

Hi everyone :)

I just recently started using access/sql.
and right away I ran into this problem "SQL expression too complex"

I google''d a lot on what it means, and the only workaround I''ve seen sofar,
is to just rewrite my code/query.

Situation:
I am sorting forms into the way they are used (print,storage,readonly,etc...).
However there are 20 different ways a form can be used,
so my initial approach was to write one big IFF statement,
sorting out each case.

however, around 11 or so IFF statements, I started to get this error,
so i was wondering if anyone had any ideas on how to make this sorting easier/workable.


Code:
This is what my huge IFF statement looks like:

If query <> "" Then

query = query & ",iif(mq.ffileusage=" & Chr(34) & "0" & Chr(34) & "," & Chr(34) & "View Only" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~0" & Chr(34) & "," & Chr(34) & "View Only" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "1" & Chr(34) & "," & Chr(34) & "View and Print" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~1" & Chr(34) & "," & Chr(34) & "View and Print" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~1~2" & Chr(34) & "," & Chr(34) & "View and Print and Fill and Print" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "2" & Chr(34) & "," & Chr(34) & "Fill and Print" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~2" & Chr(34) & "," & Chr(34) & "Fill and Print" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "3" & Chr(34) & "," & Chr(34) & "Fill and Print and Submit" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "2~3" & Chr(34) & "," & Chr(34) & "Fill and Print and Submit" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "4" & Chr(34) & "," & Chr(34) & "Print and Fill and Mail" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "5" & Chr(34) & "," & Chr(34) & "Fill Print Save" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "H" & Chr(34) & "," & Chr(34) & "Paper Copy" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~H" & Chr(34) & "," & Chr(34) & "Paper Copy" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "S" & Chr(34) & "," & Chr(34) & "Source Application" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~H~0" & Chr(34) & "," & Chr(34) & "xxxxxxx" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~H~1" & Chr(34) & "," & Chr(34) & "xxxxxx" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~H~2" & Chr(34) & "," & Chr(34) & "xxxxx" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "H~1" & Chr(34) & "," & Chr(34) & "xxxx" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "H~2~1" & Chr(34) & "," & Chr(34) & "xxx" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "H~1~4" & Chr(34) & "," & Chr(34) & "xx" & Chr(34) _
& "," & Chr(34) & "" & Chr(34) & ")))))))))))))))))))) as [Attatchment Usage]"

**basically, all im doing is checking the table for various values (form uses),
and when it outputs the form use in excel it will label it accordingly.
** ie. if the value read is "0" then it will output "View Only" in the excel document

I know the code works, because if I limit the amount of IFF statements to 12, it will work fine.
But as soon as I add anymore, it will give me the "too complex" error.


Anyone have an idea of how I could make this simplier?
I was thinking maybe a CASE function or something,
but I''m not sure, because I''ve only started sql a few days ago.


Thanks for your help :)
k,40

解决方案

Hi everyone :)

I just recently started using access/sql.
and right away I ran into this problem "SQL expression too complex"

I google''d a lot on what it means, and the only workaround I''ve seen sofar,
is to just rewrite my code/query.

Situation:
I am sorting forms into the way they are used (print,storage,readonly,etc...).
However there are 20 different ways a form can be used,
so my initial approach was to write one big IFF statement,
sorting out each case.

however, around 11 or so IFF statements, I started to get this error,
so i was wondering if anyone had any ideas on how to make this sorting easier/workable.


Code:
This is what my huge IFF statement looks like:

If query <> "" Then

query = query & ",iif(mq.ffileusage=" & Chr(34) & "0" & Chr(34) & "," & Chr(34) & "View Only" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~0" & Chr(34) & "," & Chr(34) & "View Only" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "1" & Chr(34) & "," & Chr(34) & "View and Print" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~1" & Chr(34) & "," & Chr(34) & "View and Print" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~1~2" & Chr(34) & "," & Chr(34) & "View and Print and Fill and Print" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "2" & Chr(34) & "," & Chr(34) & "Fill and Print" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~2" & Chr(34) & "," & Chr(34) & "Fill and Print" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "3" & Chr(34) & "," & Chr(34) & "Fill and Print and Submit" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "2~3" & Chr(34) & "," & Chr(34) & "Fill and Print and Submit" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "4" & Chr(34) & "," & Chr(34) & "Print and Fill and Mail" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "5" & Chr(34) & "," & Chr(34) & "Fill Print Save" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "H" & Chr(34) & "," & Chr(34) & "Paper Copy" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~H" & Chr(34) & "," & Chr(34) & "Paper Copy" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "S" & Chr(34) & "," & Chr(34) & "Source Application" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~H~0" & Chr(34) & "," & Chr(34) & "xxxxxxx" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~H~1" & Chr(34) & "," & Chr(34) & "xxxxxx" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~H~2" & Chr(34) & "," & Chr(34) & "xxxxx" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "H~1" & Chr(34) & "," & Chr(34) & "xxxx" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "H~2~1" & Chr(34) & "," & Chr(34) & "xxx" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "H~1~4" & Chr(34) & "," & Chr(34) & "xx" & Chr(34) _
& "," & Chr(34) & "" & Chr(34) & ")))))))))))))))))))) as [Attatchment Usage]"

**basically, all im doing is checking the table for various values (form uses),
and when it outputs the form use in excel it will label it accordingly.
** ie. if the value read is "0" then it will output "View Only" in the excel document

I know the code works, because if I limit the amount of IFF statements to 12, it will work fine.
But as soon as I add anymore, it will give me the "too complex" error.


Anyone have an idea of how I could make this simplier?
I was thinking maybe a CASE function or something,
but I''m not sure, because I''ve only started sql a few days ago.


Thanks for your help :)
k,40

I don''t know about Access, but I know that in Excel, a nested if statement can only be up to 7 layers. I tried doing a quick google search and didn''t find anything about a maximum number of layers for Access, but that may be your problem?

Hi mlcampeau,
In Access I can manage to write 13 IFF statements (like shown above),
but as soon as I go over 13 I start getting the "too complex" error.

So I''m wondering how I could write these 20 IFF statements more efficiently,
so it is not "too complex"



thanks for helping, :)
k,40

I have the same problem Access has, the expression is wayyyyyyyyyyyyyyy too complex! Select Case is almost always the best way to go if you''re talking about more than 3 or 4 options, and certainly is the only choice for this type of situation with over a dozen possibilities! It''ll make your code easier to understand now, and much easier to maintain in the future.

Good Luck!

Linq ;0)>

这篇关于SQL表达式太复杂了的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！