I am running Oracle 12.1 on Windows.
I need help creating a list aggregate by date (entire day is the day condition, trunc(date)) and location and counting the amount of inventory type for that day in the list result. I plan on making this a view of the car_equipment table to show the list aggregate.
I have made a simple view before showing the list of inventory for a max(date).
CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "DB1"."CAR_INVENTORY_VW" ("MAX_INVENTORY_DATE","LOCATION", "INVENTORY_LIST") AS
SELECT
inventory_date max_inventory_date,
location,
LISTAGG(num_cars || ' ' || equipment_type, ', ')
WITHIN GROUP (ORDER BY equipment_type) inventory_list
FROM (
SELECT c.*, RANK() OVER(PARTITION BY location ORDER BY inventory_date DESC) rn
FROM car_equipment c
) x
WHERE rn = 1
GROUP BY inventory_date, location;
but now I want to show all dates for a location and gather the sum of the count the inventory types by date per location. Below is the code to create the table and rows: First create the table and insert the rows for example of the data condition.
CREATE TABLE "CAR_EQUIPMENT"
("NUM_CARS" NUMBER(10,0),
"EQUIPMENT_TYPE" VARCHAR2(100 BYTE),
"LOCATION" VARCHAR2(500 BYTE),
"INVENTORY_DATE" DATE)
SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
INSERT INTO "CAR_EQUIPMENT" (NUM_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('8', 'Rovers', 'coventry', TO_DATE('2019-09-07 09:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CAR_EQUIPMENT" (NUM_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('1', 'Rovers', 'coventry', TO_DATE('2019-09-07 18:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CAR_EQUIPMENT" (NUM_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('9', 'Jaguars', 'coventry', TO_DATE('2019-09-07 06:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CAR_EQUIPMENT" (NUM_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('7', 'Rovers', 'leamington', TO_DATE('2019-08-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CAR_EQUIPMENT" (NUM_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('10','Trans Am', 'leamington', TO_DATE('2019-08-30 09:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CAR_EQUIPMENT" (NUM_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('2','Trans Am', 'leamington', TO_DATE('2019-08-30 18:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CAR_EQUIPMENT" (NUM_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('8', 'Rovers', 'coventry', TO_DATE('2019-09-06 18:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CAR_EQUIPMENT" (NUM_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('4', 'Rovers', 'leamington', TO_DATE('2019-09-06 09:00:00', 'YYYY-MM-DD HH24:MI:SS'));
commit;
I could not find examples where I can get the sum(num_cars) within the listagg.
Also, ( and I hate to ask a follow on question here), but what would happen if the list aggregate exceeded 4000 bytes. I read that xmlagg can be used, but what would be the best way to show the list if the results exceeded 4000 bytes. Have you run into this problem before and how do you address that issue. Thank you again for any advice.
Kindest regards, Gilly
I tried:
create or replace view TEST_LAGG(
WITH
TEMP AS (SELECT LOCATION,
trunc(INVENTORY_DATE) inventory_date,
EQUIPMENT_TYPE,
sum(NUM_CARS) sum_num_cars
from CAR_EQUIPMENT
group by LOCATION,
trunc(INVENTORY_DATE),
EQUIPMENT_TYPE
)
select LOCATION,
INVENTORY_DATE,
listagg(sum_num_cars ||' '|| EQUIPMENT_TYPE, ', ') within group (order by inventory_date) lagg
from TEMP
group by LOCATION,
inventory_date);
But I get ORA-00903: invalid table name 00903. 00000 - "invalid table name"
Thanks to Littlefoot again, I tried:
create or replace view TEST_LAGG
AS WITH
TEMP (SELECT LOCATION,
trunc(INVENTORY_DATE) inventory_date,
EQUIPMENT_TYPE,
sum(NUM_CARS) sum_num_cars
from CAR_EQUIPMENT
group by LOCATION,
trunc(INVENTORY_DATE),
EQUIPMENT_TYPE
)
select LOCATION,
INVENTORY_DATE,
listagg(sum_num_cars ||' '|| EQUIPMENT_TYPE, ', ') within group (order by inventory_date) lagg
from TEMP
group by LOCATION,
inventory_date;
But I get the error: ORA-00903: invalid table name
00903. 00000 - "invalid table name"
*Cause:
*Action:
Error at Line: 135 Column: 3 -- the TEMP table reference line.
You'll first have to compute that sum, and then use it in listagg. Something like this:
SQL> with
2 temp as (select location, inventory_date, equipment_type, sum(num_cars) sum_num_cars
3 from car_equipment
4 group by location, inventory_date, equipment_type
5 )
6 select location,
7 inventory_date,
8 listagg(sum_num_cars ||' '|| equipment_type, ', ') within group (order by inventory_date) lagg
9 from temp
10 group by location,
11 inventory_date;
LOCATION INVENTORY_DATE LAGG
--------------- ------------------- ------------------------------
coventry 2019-09-06 18:00:00 8 Rovers
coventry 2019-09-07 06:00:00 9 Jaguars
coventry 2019-09-07 09:00:00 8 Rovers
coventry 2019-09-07 18:00:00 1 Rovers
leamington 2019-08-30 00:00:00 7 Rovers
leamington 2019-08-30 09:00:00 10 Trans Am
leamington 2019-08-30 18:00:00 2 Trans Am
leamington 2019-09-06 09:00:00 4 Rovers
8 rows selected.
SQL>
Now, it depends on what exactly you want to get as a result.
As for your second question: yes, that's XMLAGG you'll have to use if the result exceeds limit of 4000 characters.
Aha; it seems that it is TRUNC(inventory_date) you need. It'll remove time component from date columns.
SQL> with
2 temp as (select location,
3 trunc(inventory_date) inventory_date,
4 equipment_type,
5 sum(num_cars) sum_num_cars
6 from car_equipment
7 group by location,
8 trunc(inventory_date),
9 equipment_type
10 )
11 select location,
12 inventory_date,
13 listagg(sum_num_cars ||' '|| equipment_type, ', ') within group (order by inventory_date) lagg
14 from temp
15 group by location,
16 inventory_date;
LOCATION INVENTORY_DATE LAGG
--------------- ------------------- ------------------------------
coventry 2019-09-06 00:00:00 8 Rovers
coventry 2019-09-07 00:00:00 9 Jaguars, 9 Rovers
leamington 2019-08-30 00:00:00 12 Trans Am, 7 Rovers
leamington 2019-09-06 00:00:00 4 Rovers
SQL>
SQL> create or replace view TEST_LAGG as
2 WITH
3 TEMP AS (SELECT LOCATION,
4 trunc(INVENTORY_DATE) inventory_date,
5 EQUIPMENT_TYPE,
6 sum(NUM_CARS) sum_num_cars
7 from CAR_EQUIPMENT
8 group by LOCATION,
9 trunc(INVENTORY_DATE),
10 EQUIPMENT_TYPE
11 )
12 select LOCATION,
13 INVENTORY_DATE,
14 listagg(sum_num_cars ||' '|| EQUIPMENT_TYPE, ', ') within group (order by inventory_date) lagg
15 from TEMP
16 group by LOCATION,
17 inventory_date;
View created.
SQL> select * from test_lagg;
LOCATION INVENTORY_DATE LAGG
--------------- ------------------- ------------------------------
coventry 2019-09-06 00:00:00 8 Rovers
coventry 2019-09-07 00:00:00 9 Jaguars, 9 Rovers
leamington 2019-08-30 00:00:00 12 Trans Am, 7 Rovers
leamington 2019-09-06 00:00:00 4 Rovers
SQL>