Home » SQL & PL/SQL » SQL & PL/SQL » mysql statement to PL/SQL
mysql statement to PL/SQL [message #665355] |
Fri, 01 September 2017 04:48 |
|
winfire
Messages: 6 Registered: September 2017
|
Junior Member |
|
|
Hi,
can you help me to get this statement (mysql) running under PL/SQL Oracle 11g?
My problem is "LIMIT" / "ORDER BY" in subquery which is different from mysql in PL/SQL.
Must be something with "RANK() OVER (PARTITION articleID ORDER BY pdate DESC)"? Sorry I'm not familiar with PL/SQL.
What does it do:
show price trend of articles by month (see desired result)
if there are more entries per month take the latest entry in table pricelist
if there is no entry take the last one (can be null)
working mysql statement:
SELECT
A.name,
C.year,
C.month,
(
SELECT
P.price
FROM
Pricelist AS P
WHERE
P.articleID = A.articleID
AND
P.pdate <= C.lastdayofmonth ### always select last price before current end of month
ORDER BY
P.pdate DESC
LIMIT 1
) AS lastprice
FROM
Articles AS A,
Calendar AS C
ORDER BY a.articleID,c.year,c.month
desired result
name |year|month|lastprice
screw|2017| 1| 10
screw|2017| 2| 10
screw|2017| 3| 10
screw|2017| 4| 12
screw|2017| 5| 12
screw|2017| 6| 11
bolt |2017| 1| null
bolt |2017| 2| 100
bolt |2017| 3| 110
bolt |2017| 4| 110
bolt |2017| 5| 110
bolt |2017| 6| 110
Table Articles
articleID|name
1 |screw
2 |bolt
Table Calendar
year|month|lastdayofmonth
2017|1 |2017/01/31
2017|2 |2017/02/28
2017|3 |2017/03/31
2017|4 |2017/04/30
2017|5 |2017/05/31
2017|6 |2017/06/30
Table Pricelist
articleID |pdate |price
1 |2017/01/08|10
1 |2017/01/05|15
1 |2017/04/02|12
1 |2017/06/29|11
2 |2017/02/13|100
2 |2017/03/24|110
|
|
|
Re: mysql statement to PL/SQL [message #665364 is a reply to message #665355] |
Fri, 01 September 2017 08:11 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Welcome to the forum.
You need to provide the CREATE TABLE statements and the INSERT satements to populate thetables, otherwise no-one can set up the problem.
I think you can do what you want with an aggregation, something like
[deleted]
--update: sorry, that was wrong, I misread your question.
[Updated on: Fri, 01 September 2017 08:13] Report message to a moderator
|
|
|
|
Re: mysql statement to PL/SQL [message #665370 is a reply to message #665368] |
Fri, 01 September 2017 08:59 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
I generated the scripts for you. Please do it next time
CREATE TABLE PRICELIST(
ARTICLEID NUMBER,
PDATE DATE,
PRICE NUMBER);
Insert into PRICELIST
(ARTICLEID, PDATE, PRICE)
Values
(1, TO_DATE('1/8/2017', 'MM/DD/YYYY'), 10);
Insert into PRICELIST
(ARTICLEID, PDATE, PRICE)
Values
(1, TO_DATE('1/5/2017', 'MM/DD/YYYY'), 15);
Insert into PRICELIST
(ARTICLEID, PDATE, PRICE)
Values
(1, TO_DATE('4/2/2017', 'MM/DD/YYYY'), 12);
Insert into PRICELIST
(ARTICLEID, PDATE, PRICE)
Values
(1, TO_DATE('6/29/2017', 'MM/DD/YYYY'), 11);
Insert into PRICELIST
(ARTICLEID, PDATE, PRICE)
Values
(2, TO_DATE('2/13/2017', 'MM/DD/YYYY'), 100);
Insert into PRICELIST
(ARTICLEID, PDATE, PRICE)
Values
(2, TO_DATE('3/24/2017', 'MM/DD/YYYY'), 110);
COMMIT;
CREATE TABLE ARTICLES
(
ARTICLEID NUMBER,
NAME VARCHAR2(100 BYTE)
);
Insert into ARTICLES
(ARTICLEID, NAME)
Values
(1, 'screw');
Insert into ARTICLES
(ARTICLEID, NAME)
Values
(2, 'bolt');
COMMIT;
CREATE TABLE CALENDAR
(
YEAR NUMBER(4),
MONTH NUMBER(2),
LASTDAYOFMONTH DATE
);
Insert into CALENDAR
(YEAR, MONTH, LASTDAYOFMONTH)
Values
(2017, 1, TO_DATE('1/31/2017', 'MM/DD/YYYY'));
Insert into CALENDAR
(YEAR, MONTH, LASTDAYOFMONTH)
Values
(2017, 2, TO_DATE('2/28/2017', 'MM/DD/YYYY'));
Insert into CALENDAR
(YEAR, MONTH, LASTDAYOFMONTH)
Values
(2017, 3, TO_DATE('3/31/2017', 'MM/DD/YYYY'));
Insert into CALENDAR
(YEAR, MONTH, LASTDAYOFMONTH)
Values
(2017, 4, TO_DATE('4/30/2017', 'MM/DD/YYYY'));
Insert into CALENDAR
(YEAR, MONTH, LASTDAYOFMONTH)
Values
(2017, 5, TO_DATE('5/31/2017', 'MM/DD/YYYY'));
Insert into CALENDAR
(YEAR, MONTH, LASTDAYOFMONTH)
Values
(2017, 1, TO_DATE('6/30/2017', 'MM/DD/YYYY'));
COMMIT;
|
|
|
Re: mysql statement to PL/SQL [message #665375 is a reply to message #665370] |
Fri, 01 September 2017 10:16 |
|
quirks
Messages: 82 Registered: October 2014
|
Member |
|
|
WITH
PRICELIST
AS
(SELECT 1 AS ARTICLEID, TO_DATE('1/8/2017', 'MM/DD/YYYY') AS PDATE, 10 AS PRICE FROM DUAL
UNION ALL
SELECT 1, TO_DATE('1/5/2017', 'MM/DD/YYYY'), 15 FROM DUAL
UNION ALL
SELECT 1, TO_DATE('4/2/2017', 'MM/DD/YYYY'), 12 FROM DUAL
UNION ALL
SELECT 1, TO_DATE('6/29/2017', 'MM/DD/YYYY'), 11 FROM DUAL
UNION ALL
SELECT 2, TO_DATE('2/13/2017', 'MM/DD/YYYY'), 100 FROM DUAL
UNION ALL
SELECT 2, TO_DATE('3/24/2017', 'MM/DD/YYYY'), 110 FROM DUAL),
ARTICLES
AS
(SELECT 1 AS ARTICLEID, 'screw' AS "NAME" FROM DUAL
UNION ALL
SELECT 2, 'bolt' FROM DUAL),
CALENDAR
AS
(SELECT 2017 AS "YEAR", 1 AS "MONTH", TO_DATE('1/31/2017', 'MM/DD/YYYY') AS LASTDAYOFMONTH FROM DUAL
UNION ALL
SELECT 2017, 2, TO_DATE('2/28/2017', 'MM/DD/YYYY') FROM DUAL
UNION ALL
SELECT 2017, 3, TO_DATE('3/31/2017', 'MM/DD/YYYY') FROM DUAL
UNION ALL
SELECT 2017, 4, TO_DATE('4/30/2017', 'MM/DD/YYYY') FROM DUAL
UNION ALL
SELECT 2017, 5, TO_DATE('5/31/2017', 'MM/DD/YYYY') FROM DUAL
UNION ALL
SELECT 2017, 6, TO_DATE('6/30/2017', 'MM/DD/YYYY') FROM DUAL)
SELECT "NAME"
,"YEAR"
,"MONTH"
,(SELECT DISTINCT FIRST_VALUE(PRICE) OVER(PARTITION BY ARTICLEID ORDER BY PDATE DESC)
FROM PRICELIST
WHERE PRICELIST.ARTICLEID = ARTICLES.ARTICLEID AND CALENDAR.LASTDAYOFMONTH >= PRICELIST.PDATE)
LASTPRICE
FROM CALENDAR, ARTICLES
[Updated on: Fri, 01 September 2017 10:17] Report message to a moderator
|
|
|
|
|
Goto Forum:
Current Time: Fri Sep 27 16:46:01 CDT 2024
|