Access 的SQL语句:
--------------------------------------------------------------------------------- SELECT CheckStatus5.ID, CheckStatus5.Area, CheckStatus5.SubArea, CheckStatus5.Tag_No,
IIf(
(([vbl_code] Like '*VSBL*' Or [reading]>=[leak_def])
And (IsNull([checkstatus5].[status_date])
Or
([checkstatus4].[maxofm_date]>[checkstatus2b].[maxofm_date])
And [checkstatus4].[maxofm_date]>=cdate([checkstatus5].[status_date])
And [checkstatus5].[status]<>'S/D'
)
)
Or
(
([vbl_code] Like '*VSBL*' Or [reading]>=[leak_def])
And
(
([checkstatus4].[maxofm_date]=[checkstatus2b].[maxofm_date])
And [checkstatus5].[status]='O/S'
)
),
'LKR',
IIf
(Not IsNull([CheckStatus5].[status]),[checkstatus5].[status],'')
) AS Status,
IIf(
([vbl_code] Like '*VSBL*' Or [reading]>=[leak_def])
And (
IsNull([checkstatus5].[status_date])
Or ([checkstatus4].[maxofm_date]>[checkstatus2b].[maxofm_date])
And [checkstatus4].[maxofm_date]>cdate([checkstatus5].[status_date])
And [checkstatus5].[status]<>'S/D'
)
Or ([vbl_code] Like '*VSBL*' Or [reading]>=[leak_def])
And (
([checkstatus4].[maxofm_date]=[checkstatus2b].[maxofm_date])
And[checkstatus5].[status]='O/S'
)
,[checkstatus4].[maxofm_date],[checkstatus5].[status_date]
)AS Status_Date
INTO tmpStatTableSingle FROM CheckStatus5
----------------------------------------------------------------------------------------------------------以上代码转成MS SQL 支持的语法。
因: IIf 嵌套的内容太多,不知所错,晓得用 case when then else 语法。请大侠赐教,最好跟贴 转化好的代码。 谢谢~!
--------------------------------------------------------------------------------- SELECT CheckStatus5.ID, CheckStatus5.Area, CheckStatus5.SubArea, CheckStatus5.Tag_No,
IIf(
(([vbl_code] Like '*VSBL*' Or [reading]>=[leak_def])
And (IsNull([checkstatus5].[status_date])
Or
([checkstatus4].[maxofm_date]>[checkstatus2b].[maxofm_date])
And [checkstatus4].[maxofm_date]>=cdate([checkstatus5].[status_date])
And [checkstatus5].[status]<>'S/D'
)
)
Or
(
([vbl_code] Like '*VSBL*' Or [reading]>=[leak_def])
And
(
([checkstatus4].[maxofm_date]=[checkstatus2b].[maxofm_date])
And [checkstatus5].[status]='O/S'
)
),
'LKR',
IIf
(Not IsNull([CheckStatus5].[status]),[checkstatus5].[status],'')
) AS Status,
IIf(
([vbl_code] Like '*VSBL*' Or [reading]>=[leak_def])
And (
IsNull([checkstatus5].[status_date])
Or ([checkstatus4].[maxofm_date]>[checkstatus2b].[maxofm_date])
And [checkstatus4].[maxofm_date]>cdate([checkstatus5].[status_date])
And [checkstatus5].[status]<>'S/D'
)
Or ([vbl_code] Like '*VSBL*' Or [reading]>=[leak_def])
And (
([checkstatus4].[maxofm_date]=[checkstatus2b].[maxofm_date])
And[checkstatus5].[status]='O/S'
)
,[checkstatus4].[maxofm_date],[checkstatus5].[status_date]
)AS Status_Date
INTO tmpStatTableSingle FROM CheckStatus5
----------------------------------------------------------------------------------------------------------以上代码转成MS SQL 支持的语法。
因: IIf 嵌套的内容太多,不知所错,晓得用 case when then else 语法。请大侠赐教,最好跟贴 转化好的代码。 谢谢~!
(case when (
([vbl_code] Like '%VSBL%' Or [reading]>=[leak_def])
And ([checkstatus5].[status_date] is null
Or [checkstatus4].[maxofm_date]>[checkstatus2b].[maxofm_date]
And [checkstatus4].[maxofm_date]>=convert(datetime,[checkstatus5].[status_date])
And [checkstatus5].[status]<>'S/D')
)Or(
([vbl_code] Like '%VSBL%' Or [reading]>=[leak_def])
And [checkstatus4].[maxofm_date]=[checkstatus2b].[maxofm_date]
And [checkstatus5].[status]='O/S'
)
then 'LKR' else
(case when [CheckStatus5].[status] is not null then [checkstatus5].[status] else '' end)
end) AS Status, (case when (
([vbl_code] Like '%VSBL%' Or [reading]>=[leak_def])
And ([checkstatus5].[status_date] is null
Or [checkstatus4].[maxofm_date]>[checkstatus2b].[maxofm_date]
And [checkstatus4].[maxofm_date]>convert(datetime,[checkstatus5].[status_date])
And [checkstatus5].[status]<>'S/D')
)Or (
([vbl_code] Like '%VSBL%' Or [reading]>=[leak_def])
And [checkstatus4].[maxofm_date]=[checkstatus2b].[maxofm_date]
And [checkstatus5].[status]='O/S'
)
then [checkstatus4].[maxofm_date] else [checkstatus5].[status_date] end
)AS Status_Date
INTO tmpStatTableSingle FROM CheckStatus5