Home » SQL & PL/SQL » SQL & PL/SQL » Last N TRANSACTIONS for each customer (syntax error) (19c)
| Last N TRANSACTIONS for each customer (syntax error) [message #688006] |
Tue, 08 August 2023 12:13  |
 |
Unclefool
Messages: 85
Registered: August 2021
|
Member |
|
|
I'm trying to display the last N ( in this test CASE hardcoded to 3) TRANSACTIONS per customer. The code in my CTE ( last_purchase] runs successfully. When I try to run the entire query I get the error ORA-00933 "SQL NOT PROPERLY ENDED". I believe it's a syntax error, which I can't seem to figure out. Any help would be greatly appreciated. Apologies for the elementary question.
Below is my entire test CASE and the SQL, which is causing the problem.
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
CREATE TABLE customers
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Aaron' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Bonnie', 'Winterbottom' FROM DUAL UNION ALL
SELECT 4, 'Beth', 'Zanzone' FROM DUAL UNION ALL
SELECT 5, 'Sandy', 'Herring' FROM DUAL;
ALTER TABLE customers
ADD CONSTRAINT customers_pk PRIMARY KEY (customer_id);
CREATE TABLE items
(PRODUCT_ID, PRODUCT_NAME, PRICE) AS
SELECT 100, 'Presto 6-quart Pressure Cooker', 79.99 FROM DUAL UNION ALL
SELECT 101, 'Cuisinart 8-quart Pressure Cooker', 111.99 FROM DUAL UNION ALL
SELECT 102, 'Farberware 3-quart Pressure Cooker', 49.99 FROM DUAL UNION ALL
SELECT 103, 'Farberware 6-quart Pressure Cooker', 89.29 FROM DUAL UNION ALL
SELECT 104, 'Farberware 8-quart Pressure Cooker', 105.99 FROM DUAL;
ALTER TABLE items
ADD CONSTRAINT items_pk PRIMARY KEY (product_id);
create table purchases(
ORDER_ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
customer_id number,
PRODUCT_ID NUMBER,
QUANTITY NUMBER,
purchase_date timestamp
);
ALTER TABLE purchases
ADD CONSTRAINT order_pk PRIMARY KEY (order_id);
ALTER TABLE purchases ADD CONSTRAINT customers_fk FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
ALTER TABLE purchases ADD CONSTRAINT items_fk FOREIGN KEY (PRODUCT_ID) REFERENCES items(product_id);
insert into purchases (customer_id, product_id, quantity, purchase_date)
SELECT 3, 102, 4,TIMESTAMP '2022-12-22 21:44:35' + NUMTODSINTERVAL ( LEVEL * 3, 'DAY') FROM dual
CONNECT BY LEVEL <= 13 UNION ALL
select 1, 104, (2 * LEVEL), date '2023-04-02' + level * interval '1 15:13' day to minute from dual
connect by level <= 7
union all
select 1, 101,3, date '2023-03-29' + level * interval '2' day from dual
connect by level <= 12
union all
select 2, 101,2, date '2023-01-15' + level * interval '8' hour from dual
connect by level <= 15
union all
select 2, 102,2,date '2023-04-13' + level * interval '1 1' day to hour from dual
connect by level <= 11
union all
select 3, 101,2, date '2023-02-01' + level * interval '1 05:03' day to minute from dual
connect by level <= 10
union all
select 3, 101,1, date '2023-04-22' + level * interval '23' hour from dual
connect by level <= 23
union all
select 3, 100,1, date '2022-03-01' + level * interval '1 00:23:05' day to second from dual
connect by level <= 15
union all
select 3, 101,1, date '2022-03-01' + level * interval '1 00:23:05' day to second from dual
connect by level <= 15
union all
select 4, 102,1, date '2023-01-01' + level * interval '5' hour from dual
connect by level <= 60;
with last_purchases as
(select
customer_id,
purchase_date,
product_id,
quantity,
rank() over (partition by customer_id order by purchase_date desc) rnk
from purchases
GROUP BY customer_id, purchase_date,product_id, quantity
)
select
lp.customer_id,
c.first_name,
c.last_name,
lp.purchase_date,
extract(day from (systimestamp - lp.purchase_date)) as number_of_days,
lp.quantity,
sum(lp.quantity * i.PRICE)
"TOTAL_AMT"
from last_purchases lp
left join customers c on lp.customer_id = c.customer_id
JOIN items i ON lp.product_id = i.product_id
GROUP BY customer_id, purchase_date,product_id,quantity
where lp.rnk <= 3
or lp.rnk is null
order by lp.customer_id, lp.purchase_date desc;
|
|
|
|
| Re: Last N TRANSACTIONS for each customer (syntax error) [message #688007 is a reply to message #688006] |
Tue, 08 August 2023 12:47  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You did a great job of providing a working script for testing, but it would have helped if you had also provided what results you want, so I am going to take a guess at what you want. It appears that the problem is that your group by clause for your sum does not include all of the columns. So, you could include all of the columns, which might not produce the result you are looking for and would still leave the group by clause out of order with the where clause, unless you nest the queries one more level. Instead, I am going to guess that maybe what you are looking for is an analytic sum much like your analytic rank partitioned by the columns that you had in the where clause, with aliases added to avoid ambiguous columns. Please see the revised query below. If I have guessed wrong then please provide the results that you want.
C##SCOTT@XE_21.3.0.0.0> column purchase_date format a30
C##SCOTT@XE_21.3.0.0.0> with last_purchases as
2 (select
3 customer_id,
4 purchase_date,
5 product_id,
6 quantity,
7 rank() over (partition by customer_id order by purchase_date desc) rnk
8 from purchases
9 GROUP BY customer_id, purchase_date,product_id, quantity
10 )
11 select
12 lp.customer_id,
13 c.first_name,
14 c.last_name,
15 lp.purchase_date,
16 extract(day from (systimestamp - lp.purchase_date)) as number_of_days,
17 lp.quantity,
18 sum(lp.quantity * i.PRICE) over
19 (partition by lp.customer_id, lp.purchase_date, lp.product_id, lp.quantity)
20 as "TOTAL_AMT"
21 from last_purchases lp
22 left join customers c on lp.customer_id = c.customer_id
23 JOIN items i ON lp.product_id = i.product_id
24 where lp.rnk <= 3
25 or lp.rnk is null
26 order by lp.customer_id, lp.purchase_date desc
27 /
CUSTOMER_ID FIRST_ LAST_NAME PURCHASE_DATE NUMBER_OF_DAYS QUANTITY TOTAL_AMT
----------- ------ ------------ ------------------------------ -------------- ---------- ----------
1 Faith Aaron 22-APR-2023 00:00:00.000000 108 3 335.97
1 Faith Aaron 20-APR-2023 00:00:00.000000 110 3 335.97
1 Faith Aaron 18-APR-2023 00:00:00.000000 112 3 335.97
2 Lisa Saladino 24-APR-2023 11:00:00.000000 105 2 99.98
2 Lisa Saladino 23-APR-2023 10:00:00.000000 107 2 99.98
2 Lisa Saladino 22-APR-2023 09:00:00.000000 108 2 99.98
3 Bonnie Winterbottom 14-MAY-2023 01:00:00.000000 86 1 111.99
3 Bonnie Winterbottom 13-MAY-2023 02:00:00.000000 87 1 111.99
3 Bonnie Winterbottom 12-MAY-2023 03:00:00.000000 88 1 111.99
4 Beth Zanzone 13-JAN-2023 12:00:00.000000 206 1 49.99
4 Beth Zanzone 13-JAN-2023 07:00:00.000000 207 1 49.99
4 Beth Zanzone 13-JAN-2023 02:00:00.000000 207 1 49.99
12 rows selected.
|
|
|
|
| Re: Last N TRANSACTIONS for each customer (syntax error) [message #688008 is a reply to message #688006] |
Tue, 08 August 2023 12:57 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Here is one of the other guesses that I mentioned. It turns out they return the same resultss.
##SCOTT@XE_21.3.0.0.0> column purchase_date format a30
C##SCOTT@XE_21.3.0.0.0> with last_purchases as
2 (select
3 customer_id,
4 purchase_date,
5 product_id,
6 quantity,
7 rank() over (partition by customer_id order by purchase_date desc) rnk
8 from purchases
9 GROUP BY customer_id, purchase_date,product_id, quantity
10 )
11 select
12 lp.customer_id,
13 c.first_name,
14 c.last_name,
15 lp.purchase_date,
16 extract(day from (systimestamp - lp.purchase_date)) as number_of_days,
17 lp.quantity,
18 sum(lp.quantity * i.PRICE)
19 "TOTAL_AMT"
20 from last_purchases lp
21 left join customers c on lp.customer_id = c.customer_id
22 JOIN items i ON lp.product_id = i.product_id
23 where lp.rnk <= 3 or lp.rnk is null
24 GROUP BY lp.customer_id, c.first_name, c.last_name, lp.purchase_date, lp.product_id, lp.quantity
25 order by lp.customer_id, lp.purchase_date desc;
CUSTOMER_ID FIRST_ LAST_NAME PURCHASE_DATE NUMBER_OF_DAYS QUANTITY TOTAL_AMT
----------- ------ ------------ ------------------------------ -------------- ---------- ----------
1 Faith Aaron 22-APR-2023 00:00:00.000000 108 3 335.97
1 Faith Aaron 20-APR-2023 00:00:00.000000 110 3 335.97
1 Faith Aaron 18-APR-2023 00:00:00.000000 112 3 335.97
2 Lisa Saladino 24-APR-2023 11:00:00.000000 105 2 99.98
2 Lisa Saladino 23-APR-2023 10:00:00.000000 107 2 99.98
2 Lisa Saladino 22-APR-2023 09:00:00.000000 108 2 99.98
3 Bonnie Winterbottom 14-MAY-2023 01:00:00.000000 86 1 111.99
3 Bonnie Winterbottom 13-MAY-2023 02:00:00.000000 87 1 111.99
3 Bonnie Winterbottom 12-MAY-2023 03:00:00.000000 88 1 111.99
4 Beth Zanzone 13-JAN-2023 12:00:00.000000 206 1 49.99
4 Beth Zanzone 13-JAN-2023 07:00:00.000000 207 1 49.99
4 Beth Zanzone 13-JAN-2023 02:00:00.000000 207 1 49.99
12 rows selected.
|
|
|
|
|
|
| Re: Last N TRANSACTIONS for each customer (syntax error) [message #688010 is a reply to message #688009] |
Tue, 08 August 2023 13:53 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You had your join conditions a little backwards.
C##SCOTT@XE_21.3.0.0.0> column purchase_date format a30
C##SCOTT@XE_21.3.0.0.0> with ranked_data as
2 (select c.customer_id, c.first_name, c.last_name,
3 p.purchase_date,
4 extract (day from (systimestamp - p.purchase_date)) as number_of_days,
5 p.quantity,
6 sum (p.quantity * i.PRICE) over
7 (partition by c.customer_id, p.purchase_date, p.product_id, p.quantity)
8 as "TOTAL_AMT",
9 rank() over (partition by c.customer_id order by p.purchase_date desc) rnk
10 from customers c left join purchases p
11 on c.customer_id = p.customer_id
12 left join items i
13 on p.product_id = i.product_id)
14 select customer_id, first_name, last_name,
15 purchase_date, number_of_days, quantity,
16 "TOTAL_AMT"
17 from ranked_data
18 where rnk <= 3
19 order by customer_id, purchase_date desc
20 /
CUSTOMER_ID FIRST_ LAST_NAME PURCHASE_DATE NUMBER_OF_DAYS QUANTITY TOTAL_AMT
----------- ------ ------------ ------------------------------ -------------- ---------- ----------
1 Faith Aaron 22-APR-2023 00:00:00.000000 108 3 335.97
1 Faith Aaron 20-APR-2023 00:00:00.000000 110 3 335.97
1 Faith Aaron 18-APR-2023 00:00:00.000000 112 3 335.97
2 Lisa Saladino 24-APR-2023 11:00:00.000000 106 2 99.98
2 Lisa Saladino 23-APR-2023 10:00:00.000000 107 2 99.98
2 Lisa Saladino 22-APR-2023 09:00:00.000000 108 2 99.98
3 Bonnie Winterbottom 14-MAY-2023 01:00:00.000000 86 1 111.99
3 Bonnie Winterbottom 13-MAY-2023 02:00:00.000000 87 1 111.99
3 Bonnie Winterbottom 12-MAY-2023 03:00:00.000000 88 1 111.99
4 Beth Zanzone 13-JAN-2023 12:00:00.000000 207 1 49.99
4 Beth Zanzone 13-JAN-2023 07:00:00.000000 207 1 49.99
4 Beth Zanzone 13-JAN-2023 02:00:00.000000 207 1 49.99
5 Sandy Herring
13 rows selected.
[Updated on: Tue, 08 August 2023 14:03] Report message to a moderator |
|
|
|
|
|
| Re: Last N TRANSACTIONS for each customer (syntax error) [message #688012 is a reply to message #688011] |
Tue, 08 August 2023 14:26 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I don't know how large your data is, but, if speed is an issue, then you can create some composite indexes beginning with the columns used in join conditions, then partitioning, then other columns selected in the query, then the optimizer can use those indexes for access in the execution plan, as shown below.
C##SCOTT@XE_21.3.0.0.0> create index customers_idx on customers (customer_id, first_name, last_name)
2 /
Index created.
C##SCOTT@XE_21.3.0.0.0> create index purchases_idx on purchases (customer_id, product_id, purchase_date, quantity)
2 /
Index created.
C##SCOTT@XE_21.3.0.0.0> create index items_idx on items (product_id, price)
2 /
Index created.
C##SCOTT@XE_21.3.0.0.0> exec dbms_stats.gather_table_stats (USER, 'CUSTOMERS')
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> exec dbms_stats.gather_table_stats (USER, 'PURCHASES')
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> exec dbms_stats.gather_table_stats (USER, 'ITEMS')
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> column purchase_date format a30
C##SCOTT@XE_21.3.0.0.0> set autotrace on explain
C##SCOTT@XE_21.3.0.0.0> with ranked_data as
2 (select c.customer_id, c.first_name, c.last_name,
3 p.purchase_date,
4 extract (day from (systimestamp - p.purchase_date)) as number_of_days,
5 p.quantity,
6 sum (p.quantity * i.PRICE) over
7 (partition by c.customer_id, p.purchase_date, p.product_id, p.quantity)
8 as "TOTAL_AMT",
9 rank() over (partition by c.customer_id order by p.purchase_date desc) rnk
10 from customers c left join purchases p
11 on c.customer_id = p.customer_id
12 left join items i
13 on p.product_id = i.product_id)
14 select customer_id, first_name, last_name,
15 purchase_date, number_of_days, quantity,
16 "TOTAL_AMT"
17 from ranked_data
18 where rnk <= 3
19 order by customer_id, purchase_date desc
20 /
CUSTOMER_ID FIRST_ LAST_NAME PURCHASE_DATE NUMBER_OF_DAYS QUANTITY TOTAL_AMT
----------- ------ ------------ ------------------------------ -------------- ---------- ----------
1 Faith Aaron 22-APR-2023 00:00:00.000000 108 3 335.97
1 Faith Aaron 20-APR-2023 00:00:00.000000 110 3 335.97
1 Faith Aaron 18-APR-2023 00:00:00.000000 112 3 335.97
2 Lisa Saladino 24-APR-2023 11:00:00.000000 106 2 99.98
2 Lisa Saladino 23-APR-2023 10:00:00.000000 107 2 99.98
2 Lisa Saladino 22-APR-2023 09:00:00.000000 108 2 99.98
3 Bonnie Winterbottom 14-MAY-2023 01:00:00.000000 86 1 111.99
3 Bonnie Winterbottom 13-MAY-2023 02:00:00.000000 87 1 111.99
3 Bonnie Winterbottom 12-MAY-2023 03:00:00.000000 88 1 111.99
4 Beth Zanzone 13-JAN-2023 12:00:00.000000 207 1 49.99
4 Beth Zanzone 13-JAN-2023 07:00:00.000000 207 1 49.99
4 Beth Zanzone 13-JAN-2023 02:00:00.000000 207 1 49.99
5 Sandy Herring
13 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 994770111
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 182 | 16562 | 6 (17)| 00:00:01 |
|* 1 | VIEW | | 182 | 16562 | 6 (17)| 00:00:01 |
| 2 | WINDOW SORT | | 182 | 8736 | 6 (17)| 00:00:01 |
|* 3 | HASH JOIN RIGHT OUTER| | 182 | 8736 | 5 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | ITEMS_IDX | 5 | 45 | 1 (0)| 00:00:01 |
|* 5 | HASH JOIN OUTER | | 182 | 7098 | 4 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | CUSTOMERS_IDX | 5 | 90 | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | PURCHASES | 181 | 3801 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNK"<=3)
3 - access("P"."PRODUCT_ID"="I"."PRODUCT_ID"(+))
5 - access("C"."CUSTOMER_ID"="P"."CUSTOMER_ID"(+))
|
|
|
|
Goto Forum:
Current Time: Sat Apr 27 11:08:51 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
