Insert Into Third Table Based Two Another- oracle

Question

I have these three tables; how can I insert into Table C (c_table) FROM b_table and a_table assuming that the volume of table's (especially A) data is very large:

Table B:

CREATE TABLE b_table 
(
     NAME varchar2(10),
     ID number PRIMARY KEY
)

Table A:

CREATE TABLE a_table 
(
     CODE number,
     RATE number,
     DATEE date, 

     CONSTRAINT fk_a_table 
         FOREIGN KEY (CODE) REFERENCES b_table(ID)
);

Table C:

CREATE TABLE c_table 
(
     DATEE date,
     USD number, 
     EUR number,
     OBP number 
);

"Table A" is called `b_table`, while "Table B" is called `a_table` ?!?!? This goes against all *Principles of Least Surprise* !! — marc_s, Aug 17 '21 at 05:57
Does this answer your question? [Oracle SQL pivot query](https://stackoverflow.com/questions/4841718/oracle-sql-pivot-query) — astentx, Aug 17 '21 at 06:37
@astentx Thins Is So Close But My Problem Is Grouping the Rows By Date — Zakaro, Aug 17 '21 at 07:01
The currencies are data. You can write a query that displays particular currencies in columns, but you cannot write a query that has a variable number of columns. So, if you restrict your query to USD, EUR, and OBP you can use a pivot query (with a `PIVOT` clause or conditional aggregation). If you don't want to restrict this to particular currencies, then select the data in rows and let your app care abot the display in columns. — Thorsten Kettner, Aug 17 '21 at 09:09
In any way, you should not create a table (c_table) for this, as this would introduce redundancy into your database. If you want such a table, create a view instead. If access speed is critical, make this view a materialized one. — Thorsten Kettner, Aug 17 '21 at 09:10
And [Please do not upload images of code/errors when asking a question.](//meta.stackoverflow.com/q/285551). Poste your sample data as text instead. You can use a tool like https://ozh.github.io/ascii-tables/ for formatting. — Thorsten Kettner, Aug 17 '21 at 09:13
Just a remark on the table design: You should stay consistent with your names. If you call the currency ID `id` in the currency table, then don't call it `code` in the rates table. Call it `currency_id` for instance. Then, with your current two tables you could consider using natural keys. USD and EUR are ISO codes that don't ever change, so you could use these as keys in your database. (I have no idea, though, what OBP is. If the table really contains "currencies" unknown to ISO, then using the column as a natural key may not be such a good idea.) — Thorsten Kettner, Aug 17 '21 at 09:23
@masoudzaker If you need to group by date, then do it with a `group by` clause. You should try something by yourself — astentx, Aug 17 '21 at 09:34

score 0 · Answer 1 · answered Aug 17 '21 at 05:48

begin
for r in(
    with m as(
        select 1 as xcode, 1000 as xrate, sysdate as xdate from dual
        union all
        select 2, 2000, sysdate from dual
        union all
        select 3, 3000, sysdate-1 from dual
        union all
        select 1, 2000, sysdate from dual
        union all
        select 2, 4000, sysdate from dual
        union all
        select 3, 5000, sysdate-2 from dual
        )
        select * from m
                pivot(
                    sum(xrate) for xcode in (
                        1     as usd,
                        2     as eur,
                        3     as gbp
                        ))
)loop
    update your_table set foo = r.xrate, foo_currency_usd = x.usd where foo_date = r.date;
    end loop;
end;

Thank You But I Don't Think This is The Answer Or Can Help To Solve This By a way — Zakaro, Aug 17 '21 at 07:04
output should be same if you use your test values instead of mines — Oguen, Aug 17 '21 at 07:28

score 0 · Answer 2 · answered Aug 17 '21 at 09:00

Try the below code.

INSERT INTO c_table(DATEE, USD, EUR, OBP)
     Select DATEE, USD, EUR, OBP  from(
            Select 
              TO_CHAR(a.DATEE,'DD-MON-YYYY') DATEE, a.Rate, b.name from a_table a
            inner join b_table b on b.id=a.code
        )PIVOT(SUM(rate)for name in ('USD' USD, 'EUR' EUR,'OBP' OBP));

score 0 · Accepted Answer · answered Aug 17 '21 at 11:27

0

You can use conditional aggregation:

insert into c_table (DATEE, USD, EUR, OBP) 
    select a.DATEE,
           max(case WHEN a.code = 1 THEN a.rate end),
           max(case WHEN a.code = 2 THEN a.rate end),
           max(case WHEN a.code = 3 THEN a.rate end)
    from a_table a
    group by a.DATEE

answered Aug 17 '21 at 11:27

Gordon Linoff

1,242,037
58
646
786

Exactly The Right Answer. Thanks, A lot – Zakaro Aug 19 '21 at 05:28

Insert Into Third Table Based Two Another- oracle

3 Answers3