sql - 找不到sas的日志中指出的错误在哪里
问题描述
我有一个将两个表链接到另一个表并消除重复项的查询,但是当我运行此查询时,日志中会出现一些错误消息,我找不到问题出在哪里。我是 Sas 和 SQL 编程的初学者,所以这个问题看起来很傻。
我该如何解决这个问题?
日志错误消息是:
25 GOPTIONS ACCESSIBLE;
26
27 proc sql ;
28
29 create table Guik.tabela3 (compress=yes reuse=yes) as
30
31 select distinct
32 coalesce(pa11.DHCT_NUM_CTC, pa12.DHCT_NUM_CTC) DHCT_NUM_CTC,
____________
22
33 coalesce(pa11.DTON_COD_ATF, pa12.DTON_COD_ATF) DTON_COD_ATF,
_
79
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, AS,
CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.
ERROR 79-322: Expecting a ).
ERROR 76-322: Syntax error, statement will be ignored.
完整代码是(第二个查询是出现错误的查询):
%include "/sasusers/&sysuserid/gedspla.sas";
libname Guik "/lidata_br/atuaria/dmatuarial/vida/GKK";
proc sql ;
connect to oracle(user=&d_usuario pass=&pass_dtm path='@dtm' preserve_comments);
create table Guik.DMD_HIS_CTC (compress=yes reuse=yes) as
select * from connection to oracle
( select DHCT_TSPR_COD TSPR_COD, DHCT_TSPR_DES TSPR_DES
from DMD_HIS_CTC);
disconnect from oracle;
quit;
proc sql ;
create table Guik.tabela3 (compress=yes reuse=yes) as
select distinct
coalesce(pa11.DHCT_NUM_CTC, pa12.DHCT_NUM_CTC) DHCT_NUM_CTC,
coalesce(pa11.DTON_COD_ATF, pa12.DTON_COD_ATF) DTON_COD_ATF,
a121.DTON_DES DTON_DES,
coalesce(pa11.DDTC_COD_ATF, pa12.DDTC_COD_ATF) DDTC_COD_ATF,
a120.DDTC_DES DDTC_DES,
coalesce(pa11.DDSC_COD_ATF, pa12.DDSC_COD_ATF) DDSC_COD_ATF,
a117.DDSC_DES DDSC_DES,
coalesce(pa11.DSDC_COD_ATF, pa12.DSDC_COD_ATF) DSDC_COD_ATF,
a118.DSDC_DES DSDC_DES,
coalesce(pa11.DHCT_NUM_PRA, pa12.DHCT_NUM_PRA) DHCT_NUM_PRA,
coalesce(pa11.DHCT_TSPR_COD, pa12.DHCT_TSPR_COD)DHCT_TSPR_COD,
a119.TSPR_DES DHCT_TSPR_DES,
coalesce(pa11.DCSN_COD_ATF, pa12.DCSN_COD_ATF) DCSN_COD_ATF,
a115.DCSN_COD_SOG DCSN_COD_SOG,
coalesce(pa11.DHCT_IND_ROB, pa12.DHCT_IND_ROB) DHCT_IND_ROB,
coalesce(pa11.DHCT_IND_ULT_CTC, pa12.DHCT_IND_ULT_CTC) DHCT_IND_ULT_CTC,
coalesce(pa11.DMID_COD_ATF, pa12.DMID_COD_ATF) DMID_COD_ATF,
a114.DMID_DES DMID_DES,
coalesce(pa11.DSIT_COD_ATF, pa12.DSIT_COD_ATF) DSIT_COD_ATF,
a17.DSIT_DES DSIT_DES,
coalesce(pa11.DMBE_APO_NUM, pa12.DMBE_APO_NUM) DMBE_APO_NUM,
coalesce(pa11.DHCT_CTRS_COD, pa12.DHCT_CTRS_COD) DHCT_CTRS_COD,
coalesce(pa11.DCEP_COD_ATF, pa12.DCEP_COD_ATF) DCEP_COD_ATF,
a16.DCEP_COD_CEP_REZ DCEP_COD_CEP_REZ,
coalesce(pa11.DCLD_COD_ATF, pa12.DCLD_COD_ATF) DCLD_COD_ATF,
a113.DCLD_DAT DCLD_DAT,
coalesce(pa11.DCLD_COD_ATF0, pa12.DCLD_COD_ATF0) DCLD_COD_ATF0,
a112.DCLD_DAT DCLD_DAT0,
coalesce(pa11.DCLD_COD_ATF1, pa12.DCLD_COD_ATF1) DCLD_COD_ATF1,
a111.DCLD_DAT DCLD_DAT1,
coalesce(pa11.DCLD_COD_ATF2, pa12.DCLD_COD_ATF2) DCLD_COD_ATF2,
a110.DCLD_DAT DCLD_DAT2,
coalesce(pa11.DCLD_COD_ATF3, pa12.DCLD_COD_ATF3) DCLD_COD_ATF3,
a19.DCLD_DAT DCLD_DAT3,
coalesce(pa11.DHCT_DCLD_DAT_CTC, pa12.DHCT_DCLD_DAT_CTC) DHCT_DCLD_DAT_CTC,
a14.DCLD_DAT DCLD_DAT4,
coalesce(pa11.DCLD_COD_ATF4, pa12.DCLD_COD_ATF4) DCLD_COD_ATF4,
a18.DCLD_DAT DCLD_DAT5,
coalesce(pa11.DPRD_COD_PRD, pa12.DPRD_COD_PRD) DPRD_COD_PRD,
a116.DPRD_NOM DPRD_NOM,
a15.DFFL_DFRG_COD_ATF DFRG_COD_ATF,
a123.DFRG_DES DFRG_DES,
a14.DCLD_DMAN_COD_ATF DMAN_COD_ATF,
a122.DMAN_DES DMAN_DES,
coalesce(pa11.DFFL_COD_ATF, pa12.DFFL_COD_ATF) DFFL_COD_ATF,
a15.DFFL_DES DFFL_DES,
pa11.WJXBFS1 WJXBFS1,
pa12.WJXBFS1 WJXBFS2,
pa11.WJXBFS2 WJXBFS3
from Guik.tabela1 pa11
full outer join Guik.tabela2 pa12 on (pa11.DCEP_COD_ATF = pa12.DCEP_COD_ATF and
pa11.DCLD_COD_ATF = pa12.DCLD_COD_ATF and
pa11.DCLD_COD_ATF0 = pa12.DCLD_COD_ATF0 and
pa11.DCLD_COD_ATF1 = pa12.DCLD_COD_ATF1 and
pa11.DCLD_COD_ATF2 = pa12.DCLD_COD_ATF2 and
pa11.DCLD_COD_ATF3 = pa12.DCLD_COD_ATF3 and
pa11.DCLD_COD_ATF4 = pa12.DCLD_COD_ATF4 and
pa11.DCSN_COD_ATF = pa12.DCSN_COD_ATF and
pa11.DDSC_COD_ATF = pa12.DDSC_COD_ATF and
pa11.DDTC_COD_ATF = pa12.DDTC_COD_ATF and
pa11.DFFL_COD_ATF = pa12.DFFL_COD_ATF and
pa11.DHCT_CTRS_COD = pa12.DHCT_CTRS_COD and
pa11.DHCT_DCLD_DAT_CTC = pa12.DHCT_DCLD_DAT_CTC and
pa11.DHCT_IND_ROB = pa12.DHCT_IND_ROB and
pa11.DHCT_IND_ULT_CTC = pa12.DHCT_IND_ULT_CTC and
pa11.DHCT_NUM_CTC = pa12.DHCT_NUM_CTC and
pa11.DHCT_NUM_PRA = pa12.DHCT_NUM_PRA and
pa11.DHCT_TSPR_COD = pa12.DHCT_TSPR_COD and
pa11.DMBE_APO_NUM = pa12.DMBE_APO_NUM and
pa11.DMID_COD_ATF = pa12.DMID_COD_ATF and
pa11.DPRD_COD_PRD = pa12.DPRD_COD_PRD and
pa11.DSDC_COD_ATF = pa12.DSDC_COD_ATF and
pa11.DSIT_COD_ATF = pa12.DSIT_COD_ATF and
pa11.DTON_COD_ATF = pa12.DTON_COD_ATF)
join DMC_CLD a14 on (coalesce(pa11.DHCT_DCLD_DAT_CTC, pa12.DHCT_DCLD_DAT_CTC) = a14.DCLD_COD_ATF)
join DMF_FIL a15 on (coalesce(pa11.DFFL_COD_ATF, pa12.DFFL_COD_ATF) = a15.DFFL_COD_ATF)
join DMM_CEP a16 on (coalesce(pa11.DCEP_COD_ATF, pa12.DCEP_COD_ATF) = a16.DCEP_COD_ATF)
join DMD_STM_ORI a17 on (coalesce(pa11.DSIT_COD_ATF, pa12.DSIT_COD_ATF) = a17.DSIT_COD_ATF)
join DMC_CLD a18 on (coalesce(pa11.DCLD_COD_ATF4, pa12.DCLD_COD_ATF4) = a18.DCLD_COD_ATF)
join DMC_CLD a19 on (coalesce(pa11.DCLD_COD_ATF3, pa12.DCLD_COD_ATF3) = a19.DCLD_COD_ATF)
join DMC_CLD a110 on (coalesce(pa11.DCLD_COD_ATF2, pa12.DCLD_COD_ATF2) = a110.DCLD_COD_ATF)
join DMC_CLD a111 on (coalesce(pa11.DCLD_COD_ATF1, pa12.DCLD_COD_ATF1) = a111.DCLD_COD_ATF)
join DMC_CLD a112 on (coalesce(pa11.DCLD_COD_ATF0, pa12.DCLD_COD_ATF0) = a112.DCLD_COD_ATF)
join DMC_CLD a113 on (coalesce(pa11.DCLD_COD_ATF, pa12.DCLD_COD_ATF) = a113.DCLD_COD_ATF)
join DMM_IDD a114 on (coalesce(pa11.DMID_COD_ATF, pa12.DMID_COD_ATF) = a114.DMID_COD_ATF)
join DMC_SIM_NAO a115 on (coalesce(pa11.DCSN_COD_ATF, pa12.DCSN_COD_ATF) = a115.DCSN_COD_ATF)
join DMC_PRD a116 on (coalesce(pa11.DPRD_COD_PRD, pa12.DPRD_COD_PRD) = a116.DPRD_COD_PRD)
join DMD_STS_CTC a117 on (coalesce(pa11.DDSC_COD_ATF, pa12.DDSC_COD_ATF) = a117.DDSC_COD_ATF)
join DMD_STS_DTL_CTC a118 on (coalesce(pa11.DSDC_COD_ATF, pa12.DSDC_COD_ATF) = a118.DSDC_COD_ATF)
join (SELECT distinct DHCT_TSPR_COD TSPR_COD,
DHCT_TSPR_DES TSPR_DES
FROM Guik.DMD_HIS_CTC) a119 on (coalesce(pa11.DHCT_TSPR_COD, pa12.DHCT_TSPR_COD) = a119.TSPR_COD)
join DMD_TIP_CTC a120 on (coalesce(pa11.DDTC_COD_ATF, pa12.DDTC_COD_ATF) = a120.DDTC_COD_ATF)
join DMC_TIP_ORI_NGC a121 on (coalesce(pa11.DTON_COD_ATF, pa12.DTON_COD_ATF) = a121.DTON_COD_ATF)
join DMC_MES_ANO a122 on (a14.DCLD_DMAN_COD_ATF = a122.DMAN_COD_ATF)
join DMF_RGI a123 on (a15.DFFL_DFRG_COD_ATF = a123.DFRG_COD_ATF)
;
quit
解决方案
我相信您正在尝试将两个表中的两列合并到具有相同列名的新表中?您应该as
在代码中包含 ,例如:
coalesce(pa11.DHCT_NUM_CTC, pa12.DHCT_NUM_CTC) as DHCT_NUM_CTC,
我希望它有帮助!
推荐阅读
- python - 如何获取列表嵌套字典中键的最大值
- web-crawler - 在 Storm Crawler 中抓取特定基本 URL 的所有子 URL 的完成事件
- java - 自定义行映射器的 Junit 代码覆盖率
- ios - iOS 14 菜单选择器样式的标签在更改时有时会变暗
- testing - 两个设备上的相同功能,但 BDD 测试的不同行为/场景
- python - matplotlib - 带计数的极性密度图
- django - 发送消息后的 Django 频道,当前选项卡显示 2 条消息(发送者 + 接收者)但其他选项卡不显示任何内容?
- javascript - 在 render() 方法中没有使用 React.cloneElement 传递道具
- javascript - 你如何解释 d3 event.x 和 event.y 坐标?
- mongodb - 如何重命名集合的所有文档中的字段?