NVL 函数错误，是否有类似 NVL 的等效函数 [英] Error in NVL function,is there any equivalent function like NVL

问题描述

下面的代码是用 VBScript 编写的.但是 Set rs = cmd.Execute 行抛出一个错误，说 NVL 没有定义.所以你能建议一个相当于这样的NVL"

The below code is written VBScript.but the line Set rs = cmd.Execute throwing an error saying that NVL is not defined. So can you suggest an equivalent of such "NVL"

        Option Explicit

        Dim conn, cmd, rs
         Dim clauses(34), i
    Dim xlApp, xlBook
    Dim tempDate,LenDate


    Set conn = CreateObject("ADODB.Connection")
    With conn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=""D:AravoVBGE_Wing_To_Wing_Report - Copy.xlsx"";" & _
        "Extended Properties=""Excel 12.0;HDR=Yes"""
    .Open
    End With
    tempDate="[Business Process ID],"
    For i = 0 To 34
    clauses(i) = "[Task" & i + 1 & " Start Date] > [Task" & i + 2 & " Start Date]"
    tempDate=tempDate & "NVL([Task" & i + 1 & " Start Date],sysdate+"& i &"),"
    Next
    LenDate=Len(tempDate)-1
    tempDate=Mid(tempDate,1,LenDate)
    MsgBox(tempDate)

    Set cmd = CreateObject("ADODB.Command")
    cmd.CommandText = "SELECT " & tempDate &" FROM [GEWingToWingMay25$] WHERE [Business Process ID] NOT IN (" & "SELECT [Business Process ID] FROM [GEWingToWingMay25$] WHERE " & Join(clauses, " AND ") & ")"
    MsgBox(cmd.CommandText)
    cmd.ActiveConnection = conn 
    Set rs = cmd.Execute

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlBook = xlApp.Workbooks.Add
    xlBook.Sheets(1).Range("A1").CopyFromRecordset cmd.Execute

      For i = 0 To 1
      clauses(i) = "IIf(IsNull([Task" & i + 1 & " Start Date]),sysdate+"& i &",[Task" & i + 1 & " Start Date]) > IIf(IsNull([Task" & i + 2 & " Start Date]),sysdate+"& i &",[Task" & i + 2 & " Start Date])"
'tempDate=tempDate & "NVL([Task" & i + 1 & " Start Date],sysdate+"& i &"),"
      Next

错误:获取没有为一个或多个必需参数提供值

MsgBox(cmd.CommandText):

SQL

         `SELECT * FROM [GEWingToWingMay25$] WHERE [Business Process ID] NOT IN (SELECT [Business Process ID] FROM [GEWingToWingMay25$] WHERE IIf(IsNull([Task1 Start Date]),Date()+0,[Task1 Start Date]) < IIf(IsNull([Task2 Start Date]),Date()+0,[Task2 Start Date]) OR IIf(IsNull([Task2 Start Date]),Date()+1,[Task2 Start Date]) < IIf(IsNull([Task3 Start Date]),Date()+1,[Task3 Start Date]) OR IIf(IsNull([Task3 Start Date]),Date()+2,[Task3 Start Date]) < IIf(IsNull([Task4 Start Date]),Date()+2,[Task4 Start Date]) OR IIf(IsNull([Task4 Start Date]),Date()+3,[Task4 Start Date]) < IIf(IsNull([Task5 Start Date]),Date()+3,[Task5 Start Date]) OR IIf(IsNull([Task5 Start Date]),Date()+4,[Task5 Start Date]) < IIf(IsNull([Task6 Start Date]),Date()+4,[Task6 Start Date]) OR IIf(IsNull([Task6 Start Date]),Date()+5,[Task6 Start Date]) < IIf(IsNull([Task7 Start Date]),Date()+5,[Task7 Start Date]) OR IIf(IsNull([Task7 Start Date]),Date()+6,[Task7 Start Date]) < IIf(IsNull([Task8 Start Date]),Date()+6,[Task8 Start Date]) OR IIf(IsNull([Task8 Start Date]),Date()+7,[Task8 Start Date]) < IIf(IsNull([Task9 Start Date]),Date()+7,[Task9 Start Date]) OR IIf(IsNull([Task9 Start Date]),Date()+8,[Task9 Start Date]) < IIf(IsNull([Task10 Start Date]),Date()+8,[Task10 Start Date]) OR IIf(IsNull([Task10 Start Date]),Date()+9,[Task10 Start Date]) < IIf(IsNull([Task11 Start Date]),Date()+9,[Task11 Start Date]) OR IIf(IsNull([Task11 Start Date]),Date()+10,[Task11 Start Date]) < IIf(IsNull([Task12 Start Date]),Date()+10,[Task12 Start Date]) OR IIf(IsNull([Task12 Start Date]),Date()+11,[Task12 Start Date]) < IIf(IsNull([Task13 Start Date]),Date()+11,[Task13 Start Date]) OR IIf(IsNull([Task13 Start Date]),Date()+12,[Task13 Start Date]) < IIf(IsNull([Task14 Start Date]),Date()+12,[Task14 Start Date]) OR IIf(IsNull([Task14 Start Date]),Date()+13,[Task14 Start Date]) < IIf(IsNull([Task15 Start Date]),Date()+13,[Task15 Start Date]) OR IIf(IsNull([Task15 Start Date]),Date()+14,[Task15 Start Date]) < IIf(IsNull([Task16 Start Date]),Date()+14,[Task16 Start Date]) OR IIf(IsNull([Task16 Start Date]),Date()+15,[Task16 Start Date]) < IIf(IsNull([Task17 Start Date]),Date()+15,[Task17 Start Date]) OR IIf(IsNull([Task17 Start Date]),Date()+16,[Task17 Start Date]) < IIf(IsNull([Task18 Start Date]),Date()+16,[Task18 Start Date]) OR IIf(IsNull([Task18 Start Date]),Date()+17,[Task18 Start Date]) < IIf(IsNull([Task19 Start Date]),Date()+17,[Task19 Start Date]) OR IIf(IsNull([Task19 Start Date]),Date()+18,[Task19 Start Date]) < IIf(IsNull([Task20 Start Date]),Date()+18,[Task20 Start Date]) OR IIf(IsNull([Task20 Start Date]),Date()+19,[Task20 Start Date]) < IIf(IsNull([Task21 Start Date]),Date()+19,[Task21 Start Date]) OR IIf(IsNull([Task21 Start Date]),Date()+20,[Task21 Start Date]) < IIf(IsNull([Task22 Start Date]),Date()+20,[Task22 Start Date]) OR IIf(IsNull([Task22 Start Date]),Date()+21,[Task22 Start Date]) < IIf(IsNull([Task23 Start Date]),Date()+21,[Task23 Start Date]) OR IIf(IsNull([Task23 Start Date]),Date()+22,[Task23 Start Date]) < IIf(IsNull([Task24 Start Date]),Date()+22,[Task24 Start Date]) OR IIf(IsNull([Task24 Start Date]),Date()+23,[Task24 Start Date]) < IIf(IsNull([Task25 Start Date]),Date()+23,[Task25 Start Date]) OR IIf(IsNull([Task25 Start Date]),Date()+24,[Task25 Start Date]) < IIf(IsNull([Task26 Start Date]),Date()+24,[Task26 Start Date]) OR IIf(IsNull([Task26 Start Date]),Date()+25,[Task26 Start Date]) < IIf(IsNull([Task27 Start Date]),Date()+25,[Task27 Start Date]) OR IIf(IsNull([Task27 Start Date]),Date()+26,[Task27 Start Date]) < IIf(IsNull([Task28 Start Date]),Date()+26,[Task28 Start Date]) OR IIf(IsNull([Task28 Start Date]),Date()+27,[Task28 Start Date]) < IIf(IsNull([Task29 Start Date]),Date()+27,[Task29 Start Date]) OR IIf(IsNull([Task29 Start Date]),Date()+28,[Task29 Start Date]) < IIf(IsNull([Task30 Start Date]),Date()+28,[Task30 Start Date]) OR IIf(IsNull([Task30 Start Date]),Date()+29,[Task30 Start Date]) < IIf(IsNull([Task31 Start Date]),Date()+29,[Task31 Start Date]) OR IIf(IsNull([Task31 Start Date]),Date()+30,[Task31 Start Date]) < IIf(IsNull([Task32 Start Date]),Date()+30,[Task32 Start Date]) OR IIf(IsNull([Task32 Start Date]),Date()+31,[Task32 Start Date]) < IIf(IsNull([Task33 Start Date]),Date()+31,[Task33 Start Date]) OR IIf(IsNull([Task33 Start Date]),Date()+32,[Task33 Start Date]) < IIf(IsNull([Task34 Start Date]),Date()+32,[Task34 Start Date]) OR IIf(IsNull([Task34 Start Date]),Date()+33,[Task34 Start Date]) < IIf(IsNull([Task35 Start Date]),Date()+33,[Task35 Start Date]) OR IIf(IsNull([Task35 Start Date]),Date()+34,[Task35 Start Date]) < IIf(IsNull([Task36 Start Date]),Date()+34,[Task36 Start Date]))`

编辑

选项显式

Dim conn, cmd, rs
     Dim clauses(34), i
Dim xlApp, xlBook
Dim tempDate,LenDate


Set conn = CreateObject("ADODB.Connection")
With conn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=""D:AravoVBFinal ScriptsGE_Wing_To_Wing_Report - Copy.xlsx"";" & _
    "Extended Properties=""Excel 12.0;HDR=Yes"""
.Open
End With
'tempDate=""
For i = 0 To 34
clauses(i) = "IIf(IsNull([Task" & i + 1 & " Start Date]),Date()+"& i &",[Task" & i + 1 & " Start Date]) < IIf(IsNull([Task" & i + 2 & " Start Date]),Date()+"& i &",[Task" & i + 2 & " Start Date])"
tempDate=tempDate & "NVL([Task" & i + 1 & " Start Date],Date()+"& i &"),"
Next
'LenDate=Len(tempDate)-1
'tempDate=Mid(tempDate,1,LenDate)
MsgBox(tempDate)

Set cmd = CreateObject("ADODB.Command")
cmd.CommandText = "SELECT * FROM [GEWingToWingMay25$] WHERE [Business Process ID] NOT IN (" & "SELECT [Business Process ID] FROM [GEWingToWingMay25$] WHERE " & Join(clauses, " OR ") & ")"
MsgBox(cmd.CommandText)
cmd.ActiveConnection = conn 
Set rs = cmd.Execute

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Add
xlBook.Sheets(1).Range("A1").CopyFromRecordset cmd.Execute
'xlBook.Sheets(1).Cells(1,25).Value=cmd.CommandText

推荐答案

这回答了您关于 NVL，Nz 等效的问题.

只需使用 IIF(IsNull())..

因为 Nz 仅在 Access 中受支持.

Because Nz is only supported within Access.

这里是参考文章:

在 Access 之外的 JET 数据库查询中唯一可用的非内在 SQL 函数是在 VBA 库中找到的那些，它们的对象通过底层 JET 数据库引擎公开使用(考虑到某些沙盒限制).您可以使用 VBA 的 IPmt() 和 PPmt() 等函数在查询中执行一些非常复杂的计算，并且您可以使用 VBA 的 IIf() 评估条件逻辑，但您不能使用 Nz() 列来克服空值，因为 Nz() 是 Access.Application 对象的一个​​方法.

The only non-intrinsic SQL functions available in JET database queries outside of Access are those found in the VBA library, whose objects are exposed for use via the underlying JET database engine (with consideration for certainsandbox restrictions). You can perform some crazy-complex calculations in your queries using functions like VBA'sIPmt() and PPmt(), and you can evaluate conditional logic using VBA's IIf(), but you cannot Nz() a column to overcome a Null value, because Nz() is a method of the Access.Application object.

那么 Nz() 的替代方案是什么?好吧，您可以结合 VBA 的 IIf() 和 IsNull() 来做同样的事情:

So what are the alternatives to Nz()? Well, you could combine VBA's IIf() and IsNull() to do the same thing:

select IIf(IsNull(SomePossiblyNullField), '', SomePossiblyNullField) 
from SomeTable 

或者，如果您正在处理潜在的 Null 字符串值，您可以利用不符合 JET SQL 语法的显式连接运算符 (&)，它与 ​​VBA 共享，并在您的值上添加一个空字符串.以下查询将产生与前一个相同的结果:

Or, if you're dealing with potentially Null string values, you can take advantage of the non-conforming JET SQL syntax's explicit concatenation operator (&), which it shares with VBA, and pound an empty string onto your value. The following query will produce the same result as the previous one:

select SomePossiblyNullField & '' 
from SomeTable 

我说不符合是因为在大多数情况下，通过运算符将 Null 与任何内容结合会导致 Null；但是，连接运算符的工作方式不同，将 Null 视为空字符串.数学运算符，如 +、-、* 等，确实符合标准的三值 Null 逻辑，因为它们对任何 Null 值的应用都会导致 Null 表达式，包括将 + 应用到字符串的不幸情况，JET 的 SQL由于历史原因，语法和 VBA 仍然允许.

I say non-conforming because in most cases combining Null with anything via an operator results in a Null; however, the concatenation operator works differently, treating Nulls like empty strings. Mathematical operators, like +, -, *, etc., do conform with standard tri-value Null logic in that their application to any Null value results in a Null expression, including the unfortunate case where + is applied to strings, which JET's SQL syntax and VBA still allow for historical reasons.

参考文章:

• 原因 1:工作表的第一行是否包含列名.如果是，则连接字符串应包含扩展属性 HDR=YES，否则为 HDR=NO.您已经设置的.

原因 2:Where 子句中使用的 SQL 表达式和列名.这似乎是导致您出错的最可能原因.

Reason 2: SQL Expressions and column names used in Where clause. This seems to be the most possible cause of your error.

您能否将查询字符串编辑如下:SYSDATE 需要被视为 Excel Date() 并使用 DateSerial() 添加天数，然后使用 Format() 维护日期格式.

Can you edit your query string as the following: SYSDATE needs to be treated as an Excel Date() and add days using DateSerial() and then maintain the date format using Format().

Format(DateSerial(Year(Date),Month(Date),Day(Date) + i), "yyyy-mm-dd")

AS 对于 where 子句，最好使用 Switch 语句并确保添加默认条件，例如 Date <;日期 + 1 或它认为的.

AS for the where clause, may be it's best to use a Switch statment and make sure add a default condition such as Date < Date + 1 or as it deems.

这篇关于NVL 函数错误，是否有类似 NVL 的等效函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！