CODEWITHSUNDEEP
oracle-databaseoracle11gsqlplus
Kalana
Kalana

Reputation: 6153

Nested Tables gives unexpected table in Oracle

I am working with this oracle script these days.

create type virus_Statistic_t as object(
    vDate date,
    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,6),
    Longt Number(10,6),
    virus virus_Statistic_tlb
)
/

create table countries of countries_t (
       primary key(Province_or_State, Country_or_Region)
) nested table virus store as virus_ntb;

INSERT INTO countries VALUES (
       countries_t('British Columbia', 'Canada', 49.2827, -123.1207, 
                 virus_Statistic_tlb(
                        virus_Statistic_t('22-JAN-20', 5, 0, 0), 
                        virus_Statistic_t('23-JAN-20', 10, 2, 5)
                 )
        )
);

INSERT INTO countries VALUES (
       countries_t('Queensland', 'Australia', -28.0167, 153.4, 
                 virus_Statistic_tlb(
                        virus_Statistic_t('22-JAN-20', 20, 0, 0), 
                        virus_Statistic_t('23-JAN-20', 10, 8, 10)
                 )
        )
);

select c.Province_or_State, c.Country_or_Region, c.Lat, c.Longt,  v.vDate, v.infection, v.dead, v.recovered 
from countries c, table(c.virus) v

After I ran this it gives me this table

PROVINCE_OR_STATE  COUNTRY_OR_REGION    LAT         LONGT    VDATE     INFECTION    DEAD    RECOVERED
British Columbia    Canada            49.2827   -123.1207   22-JAN-20   5            0      0
British Columbia    Canada            49.2827   -123.1207   23-JAN-20   10           2      5
Queensland          Australia        -28.0167    153.4      22-JAN-20   20           0      0
Queensland          Australia        -28.0167    153.4      23-JAN-20   10           8      10

But my expected table is

PROVINCE_OR_STATE  COUNTRY_OR_REGION    LAT         LONGT    VDATE     INFECTION    DEAD    RECOVERED
British Columbia    Canada            49.2827   -123.1207   22-JAN-20   5            0      0
                                                            23-JAN-20   10           2      5
Queensland          Australia        -28.0167    153.4      22-JAN-20   20           0      0
                                                            23-JAN-20   10           8      10

What changes should I apply to my code?

You can test that script in here

Upvotes: 1

Views: 42

Answers (2)

Popeye
Popeye

Reputation: 35910

You are converting c.virus into the table and it contains two records that are cross joined to the main table. Hence, You are getting two records (1 record cross join 2 records = 2 records)

You can use analytical function as follows:

SELECT CASE WHEN RN = 1 THEN Province_or_State END AS Province_or_State,
       CASE WHEN RN = 1 THEN Country_or_Region END AS Country_or_Region,
       CASE WHEN RN = 1 THEN Lat END AS Lat,
       CASE WHEN RN = 1 THEN Longt END AS Longt,
       vDate, infection, dead, recovered 
  FROM
 (select c.Province_or_State, c.Country_or_Region, c.Lat, c.Longt,  v.vDate, v.infection, v.dead, v.recovered 
 ,ROW_NUMBER() OVER (PARTITION BY c.Province_or_State, c.Country_or_Region, c.Lat, c.Longt ORDER BY V.VDATE) AS RN,
 , DENSE_RANK() OVER (ORDER BY c.Province_or_State, c.Country_or_Region, c.Lat, c.Longt) AS DRN
  from countries c, table(c.virus) v)
 ORDER BY DRN, RN
/

See db<>fiddle demo

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142788

As you tagged it with SQL*Plus tag, then break is what you need.

This is what you have now:

SQL> select c.Province_or_State, c.Country_or_Region, c.Lat, c.Longt,
  2         v.vDate, v.infection, v.dead, v.recovered
  3  from countries c, table(c.virus) v;

PROVINCE_OR_STAT COUNTRY_OR_REGION           LAT      LONGT VDATE     INFECTION       DEAD  RECOVERED
---------------- -------------------- ---------- ---------- -------- ---------- ---------- ----------
British Columbia Canada                  49,2827  -123,1207 22.01.20          5          0          0
British Columbia Canada                  49,2827  -123,1207 23.01.20         10          2          5

Break:

SQL> break on province_or_state on country_or_region on lat on longt
SQL> select c.Province_or_State, c.Country_or_Region, c.Lat, c.Longt,
  2         v.vDate, v.infection, v.dead, v.recovered
  3  from countries c, table(c.virus) v;

PROVINCE_OR_STAT COUNTRY_OR_REGION           LAT      LONGT VDATE     INFECTION       DEAD  RECOVERED
---------------- -------------------- ---------- ---------- -------- ---------- ---------- ----------
British Columbia Canada                  49,2827  -123,1207 22.01.20          5          0          0
                                                            23.01.20         10          2          5

SQL>

Other (reporting) tools, such as Oracle Reports Builder or Apex Classic Report have their own breaking capabilities.

With another row inserted, query (actually, break) still works as expected:

SQL> /

PROVINCE_OR_STAT COUNTRY_OR_REGION           LAT      LONGT VDATE     INFECTION       DEAD  RECOVERED
---------------- -------------------- ---------- ---------- -------- ---------- ---------- ----------
British Columbia Canada                  49,2827  -123,1207 22.01.20          5          0          0
                                                            23.01.20         10          2          5
Queensland       Australia              -28,0167      153,4 22.01.20         20          0          0
                                                            23.01.20         10          8         10

SQL>

Upvotes: 2

Related Questions