CODEWITHSUNDEEP
databaseoracle-databaseoracle11g
phzao
phzao

Reputation: 81

Oracle INSERT when selecting from multiple tables

What's wrong with this query? My insert needs to get data from other tables, but when I use select, it gives me error.

Here is the query:

INSERT INTO PAYMENT (
  OWNER_HI,
  ACCOUNT_ID,
  DATE_PAYMENT,
  ACCOUNT_VALUE_BEFORE,
  CURRENCY,EXCHANGE_RATE,
  SUM,
  SUM_USD,
  DATE_INPUT,
  OPERATOR_ID,
  DOCUMENT,
  INVOICE_ID)
VALUES (
  OWNER,
  ID,
  TODAY,
  SALDO,
  CURRENCY,
  RATE,
  50,
  (50 * RATE),
  TODAY,
  386,
  'teste sis',
  null)
(SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') "NOW" FROM DUAL) TODAY
(SELECT VALUE FROM ACCOUNT WHERE ACCOUNT_ID = 386) SALDO
(SELECT CURRENCY_IDCURRENCY_ID FROM CURRENCY_EXCHANGE WHERE rownum=1 ORDER BY CURRENCY_ID DESC) CURRENCY
(SELECT EXCHANGE_RATE FROM CURRENCY_EXCHANGE WHERE rownum=1 ORDER BY CURRENCY_ID DESC) RATE;

And this is the erro:

Erro de SQL: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"

Upvotes: 0

Views: 1227

Answers (2)

APC
APC

Reputation: 146329

We cannot mix INSERT ... VALUES and INSERT ... SELECT syntax. Choose one or the other. As you need values from other tables, you need INSERT ... SELECT.

There is no relationship between the tables you are querying so use a CROSS JOIN. This won't create a problem as long as you select only one row from each.

SELECT EXCHANGE_RATE FROM CURRENCY_EXCHANGE WHERE rownum=1 ORDER BY CURRENCY_ID DESC doesn't do what you think it does, because ROWNUM is allocated before the sort not afterwards. To get the toppermost currency, use an analytic function like ROW_NUMBER() in a sub-query and filter on that.

I've had to make a couple of guesses because you aren't clear about all the business rules you are implementing but you need something like this:

INSERT INTO PAYMENT (
  OWNER_HI,
  ACCOUNT_ID,
  DATE_PAYMENT,
  ACCOUNT_VALUE_BEFORE,
  CURRENCY,EXCHANGE_RATE,
  SUM,
  SUM_USD,
  DATE_INPUT,
  OPERATOR_ID,
  DOCUMENT,
  INVOICE_ID)
select  user, -- where does OWNER come from??
  saldo.account_id,
  trunc(sysdate),
  SALDO.value,
  CURRENCY.CURRENCY_ID ,
  CURRENCY.EXCHANGE_RATE ,
  50,
  (50 * CURRENCY.EXCHANGE_RATE ),
  trunc(sysdate),
  386,
  'teste sis',
  null
from ( select CURRENCY_ID, 
              EXCHANGE_RATE, 
              row_number() over (order by CURRENCY_ID DESC ) as rn
       FROM CURRENCY_EXCHANGE   ) currency
cross join 
    (SELECT * FROM ACCOUNT WHERE ACCOUNT_ID = 386) SALDO
where currency.rn = 1

Note: I've ignored your casting of sysdate to a string (as "TODAY") because storing dates as strings is such incredibly bad practice. I'm hoping you're just doing it as a wait of stripping away the time element from sysdate, which we can also achieve with truncation.

Upvotes: 2

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50067

Perhaps what you meant was something more like

INSERT INTO PAYMENT (
  OWNER_HI,
  ACCOUNT_ID,
  DATE_PAYMENT,
  ACCOUNT_VALUE_BEFORE,
  CURRENCY,EXCHANGE_RATE,
  SUM,
  SUM_USD,
  DATE_INPUT,
  OPERATOR_ID,
  DOCUMENT,
  INVOICE_ID)
VALUES (
  OWNER,
  ID,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD'),
  (SELECT VALUE FROM ACCOUNT WHERE ACCOUNT_ID = 386),
  (SELECT CURRENCY_IDCURRENCY_ID FROM CURRENCY_EXCHANGE WHERE rownum=1 ORDER BY CURRENCY_ID DESC),
  (SELECT EXCHANGE_RATE FROM CURRENCY_EXCHANGE WHERE rownum=1 ORDER BY CURRENCY_ID DESC),
  50,
  (50 * RATE),
  TODAY,
  386,
  'teste sis',
  null);

Best of luck.

Upvotes: 2

Related Questions