How to pass values to object columns from one table to another?

user_11383441 0
Viewed 56

I have my main table like this

create table final(
    Province varchar2(50),
    Country varchar2(100),
    Latitude Number(10,0),
    Longitude Number(10,0),
    Cdate varchar2(20),
    Confirmed int,
    killed int,
    Recover int
)

Then I have created Table with nested table like this

create type virus_Statistic_t as object(
vDate varchar2(20),
infection int,
dead int,
recovered int
)
/

create type virus_Statistic_tlb as table of virus_Statistic_t
/

create type countries_t as object(
    Province_or_State varchar2(50),
    Country_or_Region varchar2(100),
    Lat Number(10,0),
    Longt Number(10,0),
    virus virus_Statistic_tlb
)
/

create table countries of countries_t (
       Lat not null,
       Longt not null
) nested table virus store as virus_ntb;

Now I am trying to pass all columns values from final to countries table.

This is I have tried

INSERT INTO countries(Province_or_State, Country_or_Region, Lat, Longt, vDate, infection, dead, recovered)
SELECT  Province, Country, Latitude, Longitude, Cdate, Confirmed, killed, Recover
FROM final
/

It gives this error

ERROR at line 1:
ORA-00904: "RECOVERED": invalid identifier

How can I pass all values from final to countries table?

sqlplus oracle11g oracle
1 Answers

You need to use type constructors. The correct syntax is this:

INSERT INTO countries(Province_or_State, Country_or_Region, Lat, Longt, virus)
SELECT  Province, Country, Latitude, Longitude,
        virus_Statistic_tlb (virus_Statistic_t(Cdate, Confirmed, killed, Recover))
FROM final
/

Though note that this is only inserting one virus row per country, is that what you meant? To insert multiple virus rows per country do this:

INSERT INTO countries(Province_or_State, Country_or_Region, Lat, Longt, virus)
SELECT  Province, Country, Latitude, Longitude,
        CAST(MULTISET(SELECT virus_Statistic_t(Cdate, Confirmed, killed, Recover)
                      FROM   final f2
                      WHERE  f2.Province = f1.Province
                      AND    ...etc.
                      ) AS virus_Statistic_tlb
             ) 
FROM final f1
GROUP BY Province, Country, Latitude, Longitude;

Opinion

I can never respond to a question about using nested tables without saying that the correct way to use them is not at all! In a real database you should have a separate database table for virus_statistics with a foreign key to the countries table. I realise you are probably doing this for educational purposes, but you should also be aware that no one should ever use nested tables in real life. No doubt you'll soon realise why, when you try to use this data :-)

user_187470
Related