我想计算子记录，并在计算字段中使用count ("MVL")函数。我的搜索规范如下:
[Hearing Date] = ParentFieldValue("Schedule Date") AND [GHQ Session Status] = LookupValue("GHQ_SESSION_STATUS", "Scheduled")
我的计算域总是给出0(零)。我查看了日志，发现了一个直接的查询:
SELECT
T10.CONFLICT_ID,
T10.LAST_UPD,
T10.CREATED,
T10.LAST_UPD_BY,
T10.CREATED_BY,
T10.MODIFICATION_NUM,
T10.ROW_ID,
T9.X_SRL_NUMBER,
T10.OCCUR_DT,
T10.X_PAR_JUD_ID,
T10.X_AUTO_CREATED_FLG,
T7.NAME,
T1.FST_NAME,
T1.JOB_TITLE,
T1.LAST_NAME,
T5.POSTN_ID,
T6.PR_POSTN_ID,
T2.ATTRIB_03,
T10.X_REVIEW_FLG,
T10.X_MP_POSTN_ID,
T4.X_PANEL_ID,
T10.X_CASE_ID,
T10.X_PANEL_ID,
T10.X_PANEL_SCHED_ID,
T9.X_REF_NUMBER,
T9.SERIAL_NUM,
T10.X_STATUS_CD,
T10.X_STATUS_CD,
:1
FROM
SIEBEL.S_CONTACT T1,
SIEBEL.S_PROJ_XM T2,
SIEBEL.S_OFFENSE T3,
SIEBEL.S_INCIDENT T4,
SIEBEL.S_PROJ_POSTN T5,
SIEBEL.S_PROJ T6,
SIEBEL.S_PROJ T7,
SIEBEL.S_POSTN T8,
SIEBEL.S_CASE T9,
SIEBEL.S_INCIDENT T10
WHERE
T10.X_PANEL_SCHED_ID = T2.ROW_ID (+) AND
T10.X_PANEL_ID = T6.ROW_ID (+) AND
T10.X_PANEL_ID = T5.PROJ_ID (+) AND T5.X_PANEL_ROLE (+) = 'Head of Bench' AND
T4.X_PANEL_ID = T7.ROW_ID (+) AND
T3.INCIDENT_ID = T4.ROW_ID (+) AND
T10.X_PAR_JUD_ID = T3.ROW_ID (+) AND
T8.PR_EMP_ID = T1.PAR_ROW_ID (+) AND
T10.X_MP_POSTN_ID = T8.PAR_ROW_ID (+) AND
T10.X_CASE_ID = T9.ROW_ID (+) AND
((T10.X_STATUS_CD IN ( :2 )) AND
(T10.X_TYPE = 'GHQ Hearing Session')) AND
(T10.X_PANEL_ID = :3)
ORDER BY
T10.X_PANEL_ID DESC, T10.CREATED DESC
ObjMgrSqlLog Detail 4 0000000257bd2d90:0 2016-08-24 12:02:44 Bind variable 1: ,,,SADMIN,0000000257bd2d90:0,,GHQ Hearing BC,LHC Roster Roster Detail View
ObjMgrSqlLog Detail 4 0000000257bd2d90:0 2016-08-24 12:02:44 Bind variable 2: Scheduled
ObjMgrSqlLog Detail 4 0000000257bd2d90:0 2016-08-24 12:02:44 Bind variable 3: 1-12IWRL
ObjMgrSqlLog Debug 5 0000000257bd2d90:0 2016-08-24 12:02:44 User search spec:
ObjMgrSqlLog Debug 5 0000000257bd2d90:0 2016-08-24 12:02:44 Named search [Link Search Spec Named Search]: [Hearing Date] = ParentFieldValue("Schedule Date") AND [GHQ Session Status] = LookupValue("GHQ_SESSION_STATUS", "Scheduled")
ObjMgrSqlLog Debug 5 0000000257bd2d90:0 2016-08-24 12:02:44 Named search [System Search]: [Type]='GHQ Hearing Session'
ObjMgrSqlLog Debug 5 0000000257bd2d90:0 2016-08-24 12:02:44 User sort spec: Created (DESCENDING)
ObjMgrSqlLog Debug 5 0000000257bd2d90:0 2016-08-24 12:02:44 System sort spec: Panel Id
但是当我删除[听证日期]= ParentFieldValue("日程日期")部分在搜索规范。然后我得到计数，下面是生成的查询:
SELECT
COUNT (*),
:1
FROM
SIEBEL.S_INCIDENT T1
WHERE
((T1.X_STATUS_CD IN ( :2 )) AND
(T1.X_TYPE = 'GHQ Hearing Session')) AND
(T1.X_PANEL_ID = :3)
ObjMgrSqlLog Detail 4 0000000257bd1758:0 2016-08-24 12:52:25 Bind variable 1: ,,,SADMIN,0000000257bd1758:0,,GHQ Hearing BC,LHC Roster Roster Detail View
ObjMgrSqlLog Detail 4 0000000257bd1758:0 2016-08-24 12:52:25 Bind variable 2: Scheduled
ObjMgrSqlLog Detail 4 0000000257bd1758:0 2016-08-24 12:52:25 Bind variable 3: 1-12IWRL
ObjMgrSqlLog Debug 5 0000000257bd1758:0 2016-08-24 12:52:25 User search spec:
ObjMgrSqlLog Debug 5 0000000257bd1758:0 2016-08-24 12:52:25 Named search [Link Search Spec Named Search]: [GHQ Session Status] = LookupValue("GHQ_SESSION_STATUS", "Scheduled")
ObjMgrSqlLog Debug 5 0000000257bd1758:0 2016-08-24 12:52:25 Named search [System Search]: [Type] = "GHQ Hearing Session"
ObjMgrSqlLog Debug 5 0000000257bd1758:0 2016-08-24 12:52:25 User sort spec:
ObjMgrSqlLog Debug 5 0000000257bd1758:0 2016-08-24 12:52:25 System sort spec:
ObjMgrSqlLog Debug 5 0000000257bd1758:0 2016-08-24 12:52:25
链接规范属性为搜索规范中使用的所有字段以及计算字段设置。我不确定我在这里做错了什么，或者这是预期的行为。
BC字段的Link Specification属性起作用了
我怀疑问题出在
ObjMgrSqlLog Debug 5 0000000257bd2d90:0 2016-08-24 12:02:44 System sort spec: Panel Id
我假设这是链接上的关联排序规范。这将导致系统激活字段Panel Id，这将在SQL中添加更多的表和条件，从而限制数据。
建议:尝试删除该排序规范并再次尝试原始计算字段。
我怀疑元凶是:
AND T5.X_PANEL_ROLE (+) = 'Head of Bench'
可以在注释后检查查询的结果吗?