组长让偶调查一个问题,问题如下:
在VB中,执行下列代码(oracle数据库)
sSql = sSql & "SELECT "
sSql = sSql & "unbincd, "
sSql = sSql & "chokbn,"
sSql = sSql & "NVL(NULLIF(teisiymd,0),99999999) AS teisiymd "
sSql = sSql & "FROM MSOKO "
Set objRs = G_OraDatabase.DbCreateDynaset(sSql, 0)If G_nDate >= objRs!teisiymd Then
Set objRs = Nothing
Exit Function
END IF现在问题是当上面的teisiymd字段是0的时候,在SQLPLUS里面是99999999,但在vb里面objRs!teisiymd竟然是0。为什么呀?当把上面SQL文中的别名由teisiymd改为其他任意的名字时,发现vb里面执行的结果是99999999.好奇怪呀,是不是数据库的设置问题?请各位大虾救救婷婷吧!分不够可以再给~~~
在VB中,执行下列代码(oracle数据库)
sSql = sSql & "SELECT "
sSql = sSql & "unbincd, "
sSql = sSql & "chokbn,"
sSql = sSql & "NVL(NULLIF(teisiymd,0),99999999) AS teisiymd "
sSql = sSql & "FROM MSOKO "
Set objRs = G_OraDatabase.DbCreateDynaset(sSql, 0)If G_nDate >= objRs!teisiymd Then
Set objRs = Nothing
Exit Function
END IF现在问题是当上面的teisiymd字段是0的时候,在SQLPLUS里面是99999999,但在vb里面objRs!teisiymd竟然是0。为什么呀?当把上面SQL文中的别名由teisiymd改为其他任意的名字时,发现vb里面执行的结果是99999999.好奇怪呀,是不是数据库的设置问题?请各位大虾救救婷婷吧!分不够可以再给~~~
替换为
sSql = sSql & "decode(aa,null,99999999,0,99999999) teisiymd "
刚才试了,还是一样啊~~~原来的语法并没有错吧,好像只要换了别名就可以了。但这样的SQL文太多了,一个个换的话会累死偶滴。嘻嘻
select 1, null from dual
union all
select 2, 0 from dual
union all
select 3, 2 from dual;
--执行sql
select decode(aa,null,99999999,0,99999999,aa) from test123
--Result
1 99999999
2 99999999
3 2
--如果有问题只能说明你的vb程序写的有问题
Private Sub Command1_Click()
Dim Rst As ADODB.Recordset
Dim cnDb As ADODB.Connection
Dim cmd As ADODB.Command
Set cnDb = New ADODB.Connection
Set cmd = New ADODB.Command
Set Rst = New ADODB.Recordset
cnDb.CursorLocation = adUseClient
cnDb.ConnectionString = "Provider=MSDAORA.1;Password=mypassword;User ID=redlight;Data Source=orcl;Persist Security Info=True"
cnDb.Open
cmd.ActiveConnection = cnDb
cmd.CommandType = adCmdText
cmd.CommandText = "select decode(aa,null,99999999,0,99999999,aa) aa from test123"
Set Rst = cmd.Execute
Do While Not Rst.EOF
Debug.Print Rst!aa
Rst.MoveNext
Loop
End Sub--debug 输出结果
99999999
99999999
2
NVL(NULLIF(teisiymd,0),99999999) AS teisiymd NULLIF function的实现原理是:
case when expr1 = expr2 then NULL ELSE expr1 end
那么当 teisiymd 为 0 时, NULLIF返回NULL值而NVL又将NULL值转为 99999999 这就是为什么在SQL*PLUS中显示为99999999在原因至于用 objRs!teisiymd 取出来是 0, 我猜可能是 ADO 对SQL进行解析时出现的问题
建议LZ改成 objRs.Fields("teisiymd").Value 或 objRs.Fields(2).Value试试
敏婷-_-
面壁思过ing...