首页 新闻 搜索 专区 学院

SQL的错误

0
悬赏园豆:5 [已解决问题] 解决于 2019-12-27 16:55

弄不明白这段SQL错在什么地方?请大神指点。

去掉 INSERT INTO cxmq141_temp 是正常的。

 1 WITH a AS (
 2 SELECT xmdkdocno,xmdlseq,xmdl001,xmdl002
 3 FROM xmdk_t
 4 LEFT JOIN xmdl_t ON xmdkent=xmdlent AND xmdkdocno=xmdldocno
 5 WHERE xmdkent=60 AND xmdksite='D' AND xmdkstus='Y' AND xmdk000='4' AND xmdk007='A0114010'
 6 )
 7 ,b AS (
 8 SELECT a.xmdkdocno,a.xmdlseq,xmdg.xmdgdocno,xmdh.xmdhseq
 9 FROM a
10 LEFT JOIN xmdl_t xmdl ON xmdl.xmdldocno=a.xmdl001 AND xmdl.xmdlseq=a.xmdl002
11 LEFT JOIN xmdh_t xmdh ON xmdh.xmdhdocno=xmdl.xmdl001 AND xmdh.xmdhseq=xmdl.xmdl002
12 LEFT JOIN xmdg_t xmdg ON xmdg.xmdgdocno=xmdh.xmdhdocno 
13 WHERE xmdgstus='Y'
14 ),
15 c AS (
16 SELECT 'N' sel,b.xmdkdocno,b.xmdlseq,x1.xmab006,x1.xmab007,x1.xmab009,x1.xmab010,x1.xmab012,x1.xmab013,x1.xmab011,
17        b.xmdgdocno,b.xmdhseq,x2.xmab009 xmab009_2,x2.xmab010 xmab010_2,x2.xmab011 xmab011_2
18 FROM b
19 LEFT JOIN xmab_t x1 ON x1.xmab003=b.xmdkdocno AND x1.xmab004=b.xmdlseq
20 LEFT JOIN xmab_t x2 ON x2.xmab003=b.xmdgdocno AND x2.xmab004=b.xmdhseq
21 WHERE x2.xmab009-x2.xmab010>0
22 ORDER BY b.xmdkdocno DESC,b.xmdlseq
23 )
24 INSERT INTO cxmq141_temp
25 SELECT sel,xmdkdocno,xmdlseq,xmab006,xmab007,xmab009,xmab010,xmab012,xmab013,xmab011,xmdgdocno,xmdhseq,xmab009_2,xmab010_2,xmab011_2 FROM c

临时表结构:

复制代码
CREATE TEMP TABLE cxmq141_temp
   (
      sel       LIKE type_t.chr1, 
   xmdkdocno    LIKE xmdk_t.xmdkdocno, 
   xmdlseq      LIKE xmdl_t.xmdlseq, 
   xmab006      LIKE xmab_t.xmab006, 
   xmab007      LIKE xmab_t.xmab007, 
   xmab009      LIKE xmab_t.xmab009, 
   xmab010      LIKE xmab_t.xmab010, 
   xmab012      LIKE xmab_t.xmab012, 
   xmab013      LIKE xmab_t.xmab013, 
   xmab011      LIKE xmab_t.xmab011, 
   xmdgdocno    LIKE xmdg_t.xmdgdocno, 
   xmdhseq      LIKE xmdh_t.xmdhseq, 
   xmab009_2    LIKE xmab_t.xmab009, 
   xmab010_2    LIKE xmab_t.xmab010, 
   xmab011_2    LIKE xmab_t.xmab011
    );
复制代码

 

错误提示:

SQLCA.sqlcode = -6372

SQL
AaronLi的主页 AaronLi | 初学一级 | 园豆:42
提问于:2018-07-06 11:42
< >
分享
最佳答案
0

你是要把哪个查询结果insert到临时表呢? 我看到你 With As 之后就直接Insert了     

with tb as (xxxxxx)

select * into cxmq141_temp from tb

收获园豆:5
张小三、 | 菜鸟二级 |园豆:238 | 2018-07-06 17:41

是的,我要把查询出来的数据放到临时表去用,我在执行SQL之前先create了临时表,再select into 的话可以的吗?

AaronLi | 园豆:42 (初学一级) | 2018-07-06 17:46

@AaronLi: 你是要把你那个c的结果插入到临时表么?

张小三、 | 园豆:238 (菜鸟二级) | 2018-07-06 17:47

@张小三、: 是的,要把C的结果插入到临时表

AaronLi | 园豆:42 (初学一级) | 2018-07-06 17:48

@AaronLi: 24行25行 合并

SELECT sel,xmdkdocno,xmdlseq,xmab006,xmab007,xmab009,xmab010,xmab012,xmab013,xmab011,xmdgdocno,xmdhseq,xmab009_2,xmab010_2,xmab011_2 INTO cxmq141_temp FROM c

张小三、 | 园豆:238 (菜鸟二级) | 2018-07-06 17:49

@张小三、: 用这个写法还是提示同样的错误

AaronLi | 园豆:42 (初学一级) | 2018-07-09 14:50

@AaronLi: 先把临时表删了,select into 这个语法不需要手动创建,会自动创建的

张小三、 | 园豆:238 (菜鸟二级) | 2018-07-10 09:16
其他回答(5)
0

INSERT INTO cxmq141_temp 之前的一行加上分号;试试

dudu | 园豆:38815 (高人七级) | 2018-07-06 12:53

 大神,在INSERT 前加了个;号,还是不行,同样的错误,还有什么招呢?

支持(0) 反对(0) AaronLi | 园豆:42 (初学一级) | 2018-07-06 14:44
0

insert into  表(field1,field2...) select  ...

jello chen | 园豆:7096 (大侠五级) | 2018-07-06 19:34

 不行啊

支持(0) 反对(0) AaronLi | 园豆:42 (初学一级) | 2018-07-09 14:50
0

操作之前 清空临时表试试

^keepHungry$ | 园豆:532 (小虾三级) | 2018-07-07 16:34

操作前 已经清空了

支持(0) 反对(0) AaronLi | 园豆:42 (初学一级) | 2018-07-09 14:50
0

看看sql的官方参考手册,能不能支持你这种语法????!!!

不要瞎写。

jinzhenshui | 园豆:305 (菜鸟二级) | 2018-07-09 09:29

 那要怎样写??

支持(0) 反对(0) AaronLi | 园豆:42 (初学一级) | 2018-07-09 14:50
0

关闭解决了

AaronLi | 园豆:42 (初学一级) | 2019-12-27 16:55
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册