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 Go to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #665368 is a reply to message #665364] Fri, 01 September 2017 08:45 Go to previous messageGo to next message
winfire
Messages: 6
Registered: September 2017
Junior Member
max(price) won't work.
the challenge is to find most recent price <= lastdayofmonth in subquery
have a look at my fiddle http://sqlfiddle.com/#!4/8892f/6

[Updated on: Fri, 01 September 2017 08:50]

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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

Re: mysql statement to PL/SQL [message #665386 is a reply to message #665375] Sat, 02 September 2017 03:36 Go to previous messageGo to next message
winfire
Messages: 6
Registered: September 2017
Junior Member
Thank you quirks! It works.
Re: mysql statement to PL/SQL [message #665398 is a reply to message #665386] Mon, 04 September 2017 01:37 Go to previous message
quirks
Messages: 82
Registered: October 2014
Member
You are welcome Wink
Previous Topic: Bulk Inserts with CLOB column Slower in Oracle 12c than Oracle 11g
Next Topic: SQL Query
Goto Forum:
  


Current Time: Thu Mar 28 20:54:54 CDT 2024