将两个字段显示在一个列中

[gtiroy]

需要插入表SQL语句如下

insert into jc_m_sms_send_main(s_id, s_message, s_sendstuffcode)
 values
(:SeqID, :MessageText +(select s_stuffname from STUFF_DICT where s_stuffcode = :SuffCode),(:SuffCode))

运行时报错：ORA-01722: 无效数字


开始Sql语句为

insert into jc_m_sms_send_main(s_id, s_message, s_sendstuffcode)
 values
(:SeqID, :MessageText,(:SuffCode))

执行正常。

不知道如何在:MessageText后加入(select s_stuffname from STUFF_DICT where s_stuffcode = :SuffCode)查出的值。谢谢

[sleepzzzzz]

-- 你是拼串吗，改为：
insert into jc_m_sms_send_main(s_id, s_message, s_sendstuffcode)
values
(:SeqID, :MessageText ||(select s_stuffname from STUFF_DICT where s_stuffcode = :SuffCode),(:SuffCode))

[mantisXF]

ORACLE 连接两字段用||,或用CONCAT函数。[Quote=引用楼主 gtiroy 的帖子:]
需要插入表SQL语句如下

SQL code
insert into jc_m_sms_send_main(s_id, s_message, s_sendstuffcode)
values
(:SeqID, :MessageText +(select s_stuffname from STUFF_DICT where s_stuffcode = :SuffCode),(:SuffCode))




运行时报错：ORA-01722: 无效数字


开始Sql语句为

SQL code
insert into jc_m_sms_send_main(s_id, s_message, s_sendstuffcode)
values
(:SeqID, :MessageText,(:SuffCode))…
[/Quote]

[bw555]

insert into jc_m_sms_send_main(s_id, s_message, s_sendstuffcode)
select :SeqID, :MessageText||s_stuffname,:SuffCode from STUFF_DICT where s_stuffcode = :SuffCode

[范佩西_11]

这个文件简单分有多。连接字符串的时候
oracle的||相当于sqlserver的+

[jdsnhan]

好高的分啊。
六指琴魔中的迟来大师

[sleepzzzzz]

呵呵，乱军之中，抢分速走．

[wangsong145]

[Quote=引用楼主 gtiroy 的帖子:]
需要插入表SQL语句如下

SQL code
insert into jc_m_sms_send_main(s_id, s_message, s_sendstuffcode)
values
(:SeqID, :MessageText +(select s_stuffname from STUFF_DICT where s_stuffcode = :SuffCode),(:SuffCode))




运行时报错：ORA-01722: 无效数字


开始Sql语句为

SQL code
insert into jc_m_sms_send_main(s_id, s_message, s_sendstuffcode)
values
(:SeqID, :MessageText,(:SuffCode))…
[/Quote]

SQL code
insert into jc_m_sms_send_main(s_id, s_message, s_sendstuffcode)
values
(:SeqID, :MessageText ||(select s_stuffname from STUFF_DICT where s_stuffcode = :SuffCode),(:SuffCode))

Oracle中连接字符串用“||”

[denghuozhi]

10楼的对

[gtiroy]

祝大家新年快乐～

[gtiroy]

concat最好用～

[D13ay]

都是正解

[watson110]

貌似大家的写法都是有问题的:

create table t
(
id int
)

create table s
(
id int
)
insert into s values(1);

insert into t values (select id from s);

执行时会发生缺少表达式的错误吧

[watson110]

楼主可以这样试一下

declare
v_sql varchar(200);
v_stuffname varchar(100);
v_sqlinsert varchar(500);
betin
v_sql:='select s_stuffname   from STUFF_DICT where s_stuffcode = :SuffCode';
EXECUTE IMMEDIATE(v_sql) into v_stuffname;
EXECUTE IMMEDIATE(commit);
v_sqlinsert :='insert into jc_m_sms_send_main(s_id, s_message, s_sendstuffcode) 
values (:SeqID, :MessageText||'||v_stuffname||' ,(:SuffCode)) ';
DBMS_output.put_line('v_sqlinsert:'||v_sqlinsert);
EXECUTE IMMEDIATE(v_sqlinsert);
EXECUTE IMMEDIATE(commit);
end;