我正在尝试运行一个直到现在运行良好的追加查询(我每个月都运行查询)。我现在收到"无效的过程调用"错误,即使没有任何变化。基础select
查询运行正常。
下面是append
查询的代码 -
INSERT INTO
Audit_Vsurvey_All_Data_t
(
Department,
LAST_NAME,
FIRST_NAME,
[Last Name],
[First Name],
[CPM ID],
[HP Author],
Provider,
[Chart ID],
Audit_Date,
Admit_Date,
[Question Group],
HP_Contains_Allergies,
HP_Contains_Assessment,
HP_Contains_Chief_Complaint,
HP_Vitals,
HP_Contains_Family_History,
HP_Contains_HX_Present_Illness,
HP_Contains_Diagnostic_Tests,
HP_Contains_Medications,
HP_Contains_Medical_History,
HP_Contains_Surgical_History,
HP_Contains_Review_Of_Systems,
HP_Contains_Social_History,
HP_Contains_Treatment_Plan,
HP_Documented_In_30_Days,
Charted_Within_24
)
SELECT
[TT2870-LA Active].Department_Name & " " & [TT2870-LA Active].SECTION_NAME AS Department,
[TT2870-LA Active].LAST_NAME,
[TT2870-LA Active].FIRST_NAME,
[Vsurvey data to NameSplit].[Last Name],
[Vsurvey data to NameSplit].[First Name],
[TT2870-LA Active].[CPM ID],
[Vsurvey data to NameSplit].Author,
[Vsurvey data to NameSplit].Provider,
[Vsurvey data to NameSplit].[Chart ID],
[Vsurvey data to NameSplit].Audit_Date,
[Vsurvey data to NameSplit].Admit_Date,
[Vsurvey data to NameSplit].[Question Group],
[Vsurvey data to NameSplit].[H&P contains allergies],
[Vsurvey data to NameSplit].[H&P contains assessment/impression],
[Vsurvey data to NameSplit].[H&P contains chief complaint],
[Vsurvey data to NameSplit].[H&P contains current physical examination],
[Vsurvey data to NameSplit].[H&P contains family history],
[Vsurvey data to NameSplit].[H&P contains history of present illness],
[Vsurvey data to NameSplit]. [H&P contains labs/diagnostic test results],
[Vsurvey data to NameSplit].[H&P contains medications],
[Vsurvey data to NameSplit].[H&P contains past medical history],
[Vsurvey data to NameSplit].[H&P contains past surgical history],
[Vsurvey data to NameSplit].[H&P contains review of systems],
[Vsurvey data to NameSplit].[H&P contains social history],
[Vsurvey data to NameSplit].[H&P contains the treatment plan],
[Vsurvey data to NameSplit].[H&P is was documented less than 30 days from admission date_],
[Vsurvey data to NameSplit].[H&P present within 24 hours of admission]
FROM
[Vsurvey data to NameSplit] LEFT JOIN [TT2870-LA Active] ON
[Vsurvey data to NameSplit].[Last Name] = [TT2870-LA Active].LAST_NAME AND
[Vsurvey data to NameSplit].LastSplit = [TT2870-LA Active].FIRST_NAME
WHERE
[Vsurvey data to NameSplit].Audit_Date Between #10/1/2018# And #12/31/2018#
有没有办法告诉是什么导致了上面的代码,因为我无法发布数据。
非常感谢!
使用适当的缩进格式化SQL代码会发现一些奇怪的问题,我在下面的代码中进行了注释:
INSERT INTO
Audit_Vsurvey_All_Data_t
(
Department,
LAST_NAME,
FIRST_NAME,
[Last Name],
[First Name],
[CPM ID],
[HP Author],
Provider,
[Chart ID],
Audit_Date,
Admit_Date,
[Question Group],
HP_Contains_Allergies,
HP_Contains_Assessment,
HP_Contains_Chief_Complaint,
HP_Vitals,
HP_Contains_Family_History,
HP_Contains_HX_Present_Illness,
HP_Contains_Diagnostic_Tests,
HP_Contains_Medications,
HP_Contains_Medical_History,
HP_Contains_Surgical_History,
HP_Contains_Review_Of_Systems,
HP_Contains_Social_History,
HP_Contains_Treatment_Plan,
HP_Documented_In_30_Days,
Charted_Within_24
)
SELECT
[TT2870-LA Active].Department_Name & " " & [TT2870-LA Active].SECTION_NAME AS Department,
[TT2870-LA Active].LAST_NAME,
[TT2870-LA Active].FIRST_NAME,
[Vsurvey data to NameSplit].[Last Name],
[Vsurvey data to NameSplit].[First Name],
[TT2870-LA Active].[CPM ID],
[Vsurvey data to NameSplit].Author,
[Vsurvey data to NameSplit].Provider,
[Vsurvey data to NameSplit].[Chart ID],
[Vsurvey data to NameSplit].Audit_Date,
[Vsurvey data to NameSplit].Admit_Date,
[Vsurvey data to NameSplit].[Question Group],
[Vsurvey data to NameSplit].[H&P contains allergies],
[Vsurvey data to NameSplit].[H&P contains assessment/impression],
[Vsurvey data to NameSplit].[H&P contains chief complaint],
[Vsurvey data to NameSplit].[H&P contains current physical examination],
[Vsurvey data to NameSplit].[H&P contains family history],
[Vsurvey data to NameSplit].[H&P contains history of present illness],
[Vsurvey data to NameSplit]. [H&P contains labs/diagnostic test results],
^------- Extra space here
[Vsurvey data to NameSplit].[H&P contains medications],
[Vsurvey data to NameSplit].[H&P contains past medical history],
[Vsurvey data to NameSplit].[H&P contains past surgical history],
[Vsurvey data to NameSplit].[H&P contains review of systems],
[Vsurvey data to NameSplit].[H&P contains social history],
[Vsurvey data to NameSplit].[H&P contains the treatment plan],
[Vsurvey data to NameSplit].[H&P is was documented less than 30 days from admission date_],
Should this be here? -------^
[Vsurvey data to NameSplit].[H&P present within 24 hours of admission]
FROM
[Vsurvey data to NameSplit] LEFT JOIN [TT2870-LA Active] ON
[Vsurvey data to NameSplit].[Last Name] = [TT2870-LA Active].LAST_NAME AND
[Vsurvey data to NameSplit].LastSplit = [TT2870-LA Active].FIRST_NAME
WHERE
[Vsurvey data to NameSplit].Audit_Date Between #10/1/2018# And #12/31/2018#
如果上述建议不能解决问题,我只能真正建议,对于要追加的每个字段,您暂时将 select
语句中的表达式替换为该字段的默认值,对所有字段重复,直到查询计算没有错误。
要测试第一个字段是否导致问题,请将附加值临时更改为构成该字段有效数据的默认字符串值,例如:
INSERT INTO
Audit_Vsurvey_All_Data_t
(
Department,
LAST_NAME,
FIRST_NAME,
-- ... < more fields > ...
Charted_Within_24
)
SELECT
"Default Department" AS Department, -- Temporarily change to a default value
[TT2870-LA Active].LAST_NAME,
[TT2870-LA Active].FIRST_NAME,
-- ... < more fields > ...
[Vsurvey data to NameSplit].[H&P present within 24 hours of admission]
FROM
[Vsurvey data to NameSplit] LEFT JOIN [TT2870-LA Active] ON
[Vsurvey data to NameSplit].[Last Name] = [TT2870-LA Active].LAST_NAME AND
[Vsurvey data to NameSplit].LastSplit = [TT2870-LA Active].FIRST_NAME
WHERE
[Vsurvey data to NameSplit].Audit_Date Between #10/1/2018# And #12/31/2018#
这将使您能够确定哪些字段导致错误,以便您可以进一步调查。