iT邦幫忙

0

Oracle SQL Developer 問題

  • 分享至 

  • xImage

當我輸入:

INSERT INTO reserve VALUES (
(SELECT branch_code FROM branch WHERE branch_contact_no = '8888888883'),
(SELECT bc_id FROM book_copy WHERE book_call_no = '005.74 C822D 2018' AND
SELECT branch_code FROM branch WHERE branch_contact_no = '8888888881'
TO_DATE('20/09/2018 16:00:00', 'DD/MM/YYYY HH24:MI:SS'),
seq_borrower.currVal);

一直出現
Error report -

SQL Error: ORA-00936: missing expression
00936. 00000 - "missing expression"
請問如何修正.

看更多先前的討論...收起先前的討論...
沒看過有這樣的新增資料的方式..
你是否確定你的新增SQL有寫對?
yesongow iT邦大師 1 級 ‧ 2018-10-14 23:07:01 檢舉
可以問問哪間學校教的嗎?
yi741963 iT邦新手 5 級 ‧ 2018-10-15 10:09:25 檢舉
SQL語法 MS SQL 和 Oracle SQL 都差不多
建議可以去上網查Insert 語法
MS的也可以畢竟中文的文章比較多
不過要走IT這條路 還是要會看英文
Oracle 的語法都在官網有教學
windaura iT邦新手 5 級 ‧ 2018-10-15 17:05:14 檢舉
Thank you for your responses!
Sorry, I can't type much in Chinese, I am studying IT in Monash University @ Melbourne, Australia ^^'

I will provide the tables:

1- BRANCH
https://i.gyazo.com/d451e1a76ddb4665e2ca4abb387d3647.png

2- BOOK COPY
https://i.gyazo.com/9b2ee98adff84fdb3ba1c180377f5fc6.png

3- RESERVE
https://i.gyazo.com/6249711e8fe3cf58108358af2babd5b4.png


I needed to get values from some tables (branch, book copy) and put it into the reserve table.

/* Insert the following columns
> BRANCH CODE: obtain by selecting from branch via contact number
> BC_ID: get from book_copy table for a specific book_copy_id
> DATE_TIME PLACED
> BOR_NO */

The question was:

'Immediately after becoming a member, at 4PM, Ada places a reservation on a book at the Mulgrave branch (Ph: 8888888883). Some
of the details of the book that Ada has placed a reservation on are:

Call Number: 005.74 C822D 2018
Title: Database Systems: Design, Implementation, and Management
Publication Year: 2018
Edition: 13

You may assume:
MonLib has not purchased any further copies of this book, beyond those which you inserted in Task 2.1
that nobody has become a member of the library between Ada becoming a member and this reservation.'


Could anyone tell me how to properly format an 'INSERT INTO' statement with multiple SELECT subqueries?

請教各位帮忙 , 谢谢!
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

2 個回答

0
尼克
iT邦大師 1 級 ‧ 2018-10-15 10:22:07

google 關鍵字 oracle insert into

windaura iT邦新手 5 級 ‧ 2018-10-15 23:35:34 檢舉

I am year one IT student at Monash Uni/Melbourne.

0
純真的人
iT邦大師 1 級 ‧ 2018-10-16 10:25:52

我觀落陰了一下...先放棄BOR_NO 是甚麼..

你看看insert into 吧...

create table BRANCH(
  BRANCH_CODE number(10)
  ,BRANCH_NAME varchar2(50)
  ,BRANCH_ADDERSS varchar2(50)
  ,BRANCH_CONTACT_NO varchar2(50)
  ,BRANCH_COUNT_BOOKS number(10)
  ,MAN_ID number(10)
);

insert into BRANCH(BRANCH_CODE,BRANCH_NAME,BRANCH_ADDERSS,BRANCH_CONTACT_NO,BRANCH_COUNT_BOOKS,MAN_ID) 
values(10,'aaa','bbb','8888888881',5000,1);
insert into BRANCH(BRANCH_CODE,BRANCH_NAME,BRANCH_ADDERSS,BRANCH_CONTACT_NO,BRANCH_COUNT_BOOKS,MAN_ID) 
values(12,'ccc','ggg','8888888883',45000,1);

create table BOOK_COPY(
 BRANCH_CODE number(10)
 ,BC_ID number(10)
 ,BC_PURCHASE_PRICE number(10)
 ,BC_RESERVE_FLAG varchar2(5)
 ,BOOK_CALL_NO varchar2(50)
);

insert into BOOK_COPY(BRANCH_CODE,BC_ID,BC_PURCHASE_PRICE,BC_RESERVE_FLAG,BOOK_CALL_NO)
values(10,6,120,'Y','005.74 C822D 2018');
insert into BOOK_COPY(BRANCH_CODE,BC_ID,BC_PURCHASE_PRICE,BC_RESERVE_FLAG,BOOK_CALL_NO)
values(11,6,120,'Y','005.74 C822D 2018');
insert into BOOK_COPY(BRANCH_CODE,BC_ID,BC_PURCHASE_PRICE,BC_RESERVE_FLAG,BOOK_CALL_NO)
values(12,6,120,'Y','005.74 C822D 2018');

create table RESERVE(
 BRANCH_CODE number(10)
 ,BC_ID number(10)
 ,RESERVE_Date TIMESTAMP
 ,BOR_NO varchar2(5)
);

insert into RESERVE(BRANCH_CODE
,BC_ID
,RESERVE_Date
) 
select (
  SELECT branch_code FROM branch WHERE branch_contact_no = '8888888883'
) branch_code
,bc_id
,TO_DATE('2018/09/20 16:00:00', 'yyyy/mm/dd hh24:mi:ss') test_date
FROM book_copy a
LEFT JOIN branch b on a.branch_code = b.branch_code
WHERE book_call_no = '005.74 C822D 2018'
AND branch_contact_no = '8888888881';

test url:
http://sqlfiddle.com/#!4/2f38b4/2

我要發表回答

立即登入回答