merge into card f using ( select e.card_status,e.issue_date,c.id id,e.card_no card_no,e.maincard_flag maincard_flag,e.new_flag new_flag,e.suspend_times suspend_times from etl_custcard e,customer c where e.new_flag='1' And e.sys_date='20070904' and e.cust_no=c.id_no ) b on (f.card_number=b.card_no) when matched then update set CUSTOMER_ID =b.id, MAINCARD_FLAG =b.maincard_flag, NEW_FLAG =b.new_flag, SUSPEND_TIMES = nvl(b.suspend_times,0),f.state=b.card_status,f.open_date=b.issue_date when not matched then insert ( ID, CARD_LIMIT,MONTHLY_PAY,LIMIT_USE_PECENT_HY,TIMES_HY,AMOUNT_HY,CORRECT_AMOUNT_HY,CORRECT_PERCENT_HY,DAILY_BALANCE_HY,LIMIT_USE_PECENT_Y, TIMES_Y,AMOUNT_Y,CORRECT_AMOUNT_Y,CORRECT_PERCENT_Y,DAILY_BALANCE_Y ,DISTRUSTFUL_AMOUNT,DISTRUSTFUL_TIMES,OWE_TIMES,ACCOUNT_LIMIT, OWE_AMOUNT, CUSTOMER_ID,CARD_NUMBER,MAINCARD_FLAG,NEW_FLAG,SUSPEND_TIMES,record_date,state,open_date ) values( hibernate_sequence.nextval,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0, 0, b.id,b.card_no, b.maincard_flag,b.new_flag,nvl(b.suspend_times,0),sysdate,b.card_status,b.issue_date );
这条语句在pl/sql dev中执行没有问题
但是 用shell 调用sqlplus 却报错说:
SQL> 2 ag,nvl(b.suspend_times,0),sysdate,b.card_status,b.issue_date )
*
ERROR at line 2:
ORA-00917: missing comma
折腾良久 发现:select e.card_status,e.issue_date,c.id id ...
前面两个字段没有加别名。加上别名就好了。
这条语句在pl/sql dev中执行没有问题
但是 用shell 调用sqlplus 却报错说:
SQL> 2 ag,nvl(b.suspend_times,0),sysdate,b.card_status,b.issue_date )
*
ERROR at line 2:
ORA-00917: missing comma
折腾良久 发现:select e.card_status,e.issue_date,c.id id ...
前面两个字段没有加别名。加上别名就好了。