OraFAQ Forum: SQL & PL/SQL » Duplicate rows by generating date based on column value

Home » SQL & PL/SQL » SQL & PL/SQL » Duplicate rows by generating date based on column value (12.1.0.1.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Duplicate rows by generating date based on column value [message #686893]

Mon, 30 January 2023 03:54

OraFerro
Messages: 433
Registered: July 2011

Senior Member

Hi All,

I need to generate 2 rows for every existing row in my data. Each row contains 2 month numbers and a year. I need each of the generated rows to hold a data = 01-one of the month numbers- the year.
Following is my example:

create table testc
  (
    id number,
    fist_mon number,
    second_mon number,
    dyear number
  );

insert all 
  into TESTC(id, FIST_MON, SECOND_MON,DYEAR) values (1, 1, 7, 2000)
  into TESTC(id, FIST_MON, SECOND_MON,DYEAR) values (1, 5, 11, 2001)
  into TESTC(id, FIST_MON, SECOND_MON,DYEAR) values (1, 2, 8, 2002)
  into TESTC(id, FIST_MON, SECOND_MON,DYEAR) values (2, 3, 9, 2005)
  into TESTC(id, FIST_MON, SECOND_MON,DYEAR) values (2, 1, 7, 2007)
  into TESTC(id, FIST_MON, SECOND_MON,DYEAR) values (3, 4, 10, 2022)
select * from dual;

select * from testc;

ID	FIST_MON	SECOND_MON	DYEAR
1	1	7	2000
1	5	11	2001
1	2	8	2002
2	3	9	2005
2	1	7	2007
3	4	10	2022

--- what I want to get after adding the generated_date is:

ID	FIST_MON	SECOND_MON	DYEAR generated_date
1	1	7	2000 1-1-2000
1	1	7	2000 1-7-2000
1	5	11	2001 01-05-2001
1	5	11	2001 01-11-2001
1	2	8	2002 01-02-2002
1	2	8	2002 01-08-2002
2	3	9	2005 01-03-2005
2	3	9	2005 01-09-2005
2	1	7	2007 01-01-2007
2	1	7	2007 01-07-2007
3	4	10	2022 01-04-2022
3	4	10	2022 01-10-2022

Thanks,
Ferro

Report message to a moderator

Re: Duplicate rows by generating date based on column value [message #686894 is a reply to message #686893]

Mon, 30 January 2023 04:05

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

I have a solution based on a compound query:

             ID TO_DATE(TO_CHAR(DYE
--------------- -------------------
              1 2000-01-01:00:00:00
              1 2000-07-01:00:00:00
              1 2001-05-01:00:00:00
              1 2001-11-01:00:00:00
              1 2002-02-01:00:00:00
              1 2002-08-01:00:00:00
              2 2005-03-01:00:00:00
              2 2005-09-01:00:00:00
              2 2007-01-01:00:00:00
              2 2007-07-01:00:00:00
              3 2022-04-01:00:00:00
              3 2022-10-01:00:00:00

12 rows selected.

There are probably more elegant solutions. What SQL have you tried so far? Smile

Report message to a moderator

Re: Duplicate rows by generating date based on column value [message #686895 is a reply to message #686893]

Mon, 30 January 2023 04:25

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

I used a compound query as well. Since this looks like homework, I will just show partial code and leave it for you to fill in the missing code where you see ... and post what you try.

SCOTT@orcl_12.1.0.2.0> select ...
  2  	   to_date (...) as generated_date
  3  from  testc
  4  union all
  5  select ...
  6  	   to_date (...) as generated_date
  7  from  testc
  8  order by ...;

        ID   FIST_MON SECOND_MON      DYEAR GENERATED_DATE
---------- ---------- ---------- ---------- ---------------
         1          1          7       2000 Sat 01-Jan-2000
         1          1          7       2000 Sat 01-Jul-2000
         1          5         11       2001 Tue 01-May-2001
         1          5         11       2001 Thu 01-Nov-2001
         1          2          8       2002 Fri 01-Feb-2002
         1          2          8       2002 Thu 01-Aug-2002
         2          3          9       2005 Tue 01-Mar-2005
         2          3          9       2005 Thu 01-Sep-2005
         2          1          7       2007 Mon 01-Jan-2007
         2          1          7       2007 Sun 01-Jul-2007
         3          4         10       2022 Fri 01-Apr-2022
         3          4         10       2022 Sat 01-Oct-2022

12 rows selected.

Report message to a moderator

Re: Duplicate rows by generating date based on column value [message #686896 is a reply to message #686895]

Mon, 30 January 2023 04:44

OraFerro
Messages: 433
Registered: July 2011

Senior Member

@John Watson, I am trying to avoid the obvious compound query as I also believe a better solution exists. I tried reading about multiset and MATCH_RECOGNIZE but the former did not work, and the later is too complex for me.

@Barbara, I assure you its not a homework Smile

Report message to a moderator

Re: Duplicate rows by generating date based on column value [message #686899 is a reply to message #686896]

Mon, 30 January 2023 05:30

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Union all solution is less efficient since it scans table twice. We can use lateral/cross apply:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY'
/
SELECT  T.*,
        L.GENERATED_DATE
  FROM  TESTC T,
        LATERAL(
                SELECT  TO_DATE(
                                CASE LEVEL
                                  WHEN 1 THEN '01-' || LPAD(FIRST_MON,2,'0') || '-' || DYEAR
                                  ELSE '01-' || LPAD(SECOND_MON,2,'0') || '-' || DYEAR
                                END,
                                'DD-MM-YYYY'
                               ) GENERATED_DATE
                  FROM  DUAL
                  CONNECT BY LEVEL <= 2
               ) L
/

        ID  FIRST_MON SECOND_MON      DYEAR GENERATED_
---------- ---------- ---------- ---------- ----------
         1          1          7       2000 01-01-2000
         1          1          7       2000 01-07-2000
         1          5         11       2001 01-05-2001
         1          5         11       2001 01-11-2001
         1          2          8       2002 01-02-2002
         1          2          8       2002 01-08-2002
         2          3          9       2005 01-03-2005
         2          3          9       2005 01-09-2005
         2          1          7       2007 01-01-2007
         2          1          7       2007 01-07-2007
         3          4         10       2022 01-04-2022
         3          4         10       2022 01-10-2022

12 rows selected.

SQL>

SY.

Report message to a moderator

Re: Duplicate rows by generating date based on column value [message #686900 is a reply to message #686896]

Mon, 30 January 2023 05:30

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Union all solution is less efficient since it scans table twice. We can use lateral/cross apply:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY'
/
SELECT  T.*,
        L.GENERATED_DATE
  FROM  TESTC T,
        LATERAL(
                SELECT  TO_DATE(
                                CASE LEVEL
                                  WHEN 1 THEN '01-' || LPAD(FIRST_MON,2,'0') || '-' || DYEAR
                                  ELSE '01-' || LPAD(SECOND_MON,2,'0') || '-' || DYEAR
                                END,
                                'DD-MM-YYYY'
                               ) GENERATED_DATE
                  FROM  DUAL
                  CONNECT BY LEVEL <= 2
               ) L
/

        ID  FIRST_MON SECOND_MON      DYEAR GENERATED_
---------- ---------- ---------- ---------- ----------
         1          1          7       2000 01-01-2000
         1          1          7       2000 01-07-2000
         1          5         11       2001 01-05-2001
         1          5         11       2001 01-11-2001
         1          2          8       2002 01-02-2002
         1          2          8       2002 01-08-2002
         2          3          9       2005 01-03-2005
         2          3          9       2005 01-09-2005
         2          1          7       2007 01-01-2007
         2          1          7       2007 01-07-2007
         3          4         10       2022 01-04-2022
         3          4         10       2022 01-10-2022

12 rows selected.

SQL>

SY.

Report message to a moderator

Re: Duplicate rows by generating date based on column value [message #686902 is a reply to message #686899]

Mon, 30 January 2023 09:21

mathguy
Messages: 106
Registered: January 2023

Senior Member

We can use UNPIVOT, but the code is a bit ugly since you want to repeat the input columns in the output, you don't just want the generated dates. The code is simpler if we use the poor man's way of unpivoting (the way the operation was done before Oracle 11 - that is, before UNPIVOT was introduced), with a Cartesian join. As a side note - "first" is spelled with an "r" in the middle; "fist" means something else.

select id, fist_mon, second_mon, dyear,
       to_date(to_char(dyear, '0000') || to_char(case flag when 1 then fist_mon else second_mon end, '00'), 'yyyymm') as generated_date
from   testc cross join (select 1 as flag from dual union all select 2 from dual)
order  by id, generated_date   --  if needed
;



    ID   FIST_MON SECOND_MON DYEAR GENERATED_DATE 
    -- ---------- ---------- ----- ---------------
     1          1          7  2000 01-01-2000     
     1          1          7  2000 01-07-2000     
     1          5         11  2001 01-05-2001     
     1          5         11  2001 01-11-2001     
     1          2          8  2002 01-02-2002     
     1          2          8  2002 01-08-2002     
     2          3          9  2005 01-09-2005     
     2          1          7  2007 01-01-2007     
     2          1          7  2007 01-07-2007     
     3          4         10  2022 01-04-2022     
     3          4         10  2022 01-10-2022

Report message to a moderator

Re: Duplicate rows by generating date based on column value [message #686903 is a reply to message #686902]

Mon, 30 January 2023 09:29

mathguy
Messages: 106
Registered: January 2023

Senior Member

An UNPIVOT solution might look like this:

with    prep as (select id, fist_mon, second_mon, dyear, fist_mon as f, second_mon as s from testc)
select  id, fist_mon, second_mon, dyear, to_date(to_char(dyear, '0000') || to_char(mon, '00'), 'yyyymm') as generated_date
from    prep
unpivot (mon for name in (f as 'first', s as 'second'))
order   by id, generated_date   -- or whatever is needed
;

The output can include the NAME column as well, to distinguish 'first' from 'second' rows for each input row. (The same can be added easily to the Cartesian join solution as well.)

Report message to a moderator

Re: Duplicate rows by generating date based on column value [message #686906 is a reply to message #686903]

Mon, 30 January 2023 22:22

OraFerro
Messages: 433
Registered: July 2011

Senior Member

@Solomon
@mathguy
Thanks a lot. I tried both and I applied the solution with Lateral as it avoids joins. I also failed to use MATCH_RECOGNIZE although I am sure it can be used!

Report message to a moderator

Re: Duplicate rows by generating date based on column value [message #686907 is a reply to message #686906]

Mon, 30 January 2023 23:19

mathguy
Messages: 106
Registered: January 2023

Senior Member

OraFerro wrote on Mon, 30 January 2023 22:22

I applied the solution with Lateral as it avoids joins.

What do you mean by "avoids joins"? The "lateral" thing is called a lateral join. Solomon demonstrated the Oracle join syntax, in which joins are marked by comma in the FROM clause; the ANSI syntax is CROSS JOIN LATERAL. A lateral join is simply a UNION ALL of cross joins of each row in the main table to the corresponding LATERAL view.

In your problem (and Solomon's solution), the lateral view is the same for all rows in the base table. This can be factored out; there is no need for LATERAL [JOIN] in the solution. The query should use a standard CROSS JOIN - to a trivial helper view with just two rows, one column - as I demonstrated in my first solution. UNPIVOT does essentially the same thing, just with different syntax.

In any case - there may be reasons to prefer the LATERAL approach (even if I can't think of any in this case), but "avoids joins" can't be one of them.

Report message to a moderator

Re: Duplicate rows by generating date based on column value [message #686911 is a reply to message #686907]

Tue, 31 January 2023 01:58

OraFerro
Messages: 433
Registered: July 2011

Senior Member

@mathguy
Quote:

The "lateral" thing is called a lateral join

Of course, what I meant is that it avoids the obvious union all by repeating the base table select as in the above examples.
I am trying to use MATCH_RECOGNIZE after the below failed without connect by or join all:

SELECT T.*,
       TO_DATE(
         CASE rn
           WHEN 1 THEN '01-' || LPAD(fist_mon, 2, '0') || '-' || dyear
           ELSE '01-' || LPAD(second_mon, 2, '0') || '-' || dyear
         END,
         'DD-MM-YYYY'
       ) AS generated_date
FROM (
  SELECT T.*,
         ROW_NUMBER() OVER (PARTITION BY id, fist_mon, second_mon, dyear ORDER BY fist_mon) AS rn
  FROM TESTC T
) T;

In fact I am doing this just for fun and to learn more about MATCH_RECOGNIZE, with MATCH_RECOGNIZE it only worked with the stupid repetition (due to my limited knowledge) which also does not work!:

WITH testc_data AS (
SELECT 1 id, 1 fist_mon, 7 second_mon, 2000 dyear FROM DUAL UNION ALL
SELECT 1 id, 5 fist_mon, 11 second_mon, 2001 dyear FROM DUAL UNION ALL
SELECT 1 id, 2 fist_mon, 8 second_mon, 2002 dyear FROM DUAL UNION ALL
SELECT 2 id, 3 fist_mon, 9 second_mon, 2005 dyear FROM DUAL UNION ALL
SELECT 2 id, 1 fist_mon, 7 second_mon, 2007 dyear FROM DUAL UNION ALL
SELECT 3 id, 4 fist_mon, 10 second_mon, 2022 dyear FROM DUAL
)
SELECT *
FROM testc_data
MATCH_RECOGNIZE (
ORDER BY id, dyear
MEASURES
FIRST(fist_mon) as fist_mon,
FIRST(second_mon) as second_mon,
FIRST(dyear) as dyear,
TO_DATE('01-' || LPAD(FIRST(fist_mon), 2, '0') || '-' || dyear, 'DD-MM-YYYY') as generated_date
ONE ROW PER MATCH
AFTER MATCH SKIP TO NEXT ROW
PATTERN (A B)
DEFINE
A AS fist_mon <= second_mon,
B AS fist_mon > second_mon
)
UNION ALL
SELECT *
FROM testc
MATCH_RECOGNIZE (
ORDER BY id, dyear
MEASURES
FIRST(fist_mon) as fist_mon,
FIRST(second_mon) as second_mon,
FIRST(dyear) as dyear,
TO_DATE('01-' || LPAD(FIRST(second_mon), 2, '0') || '-' || dyear, 'DD-MM-YYYY') as generated_date
ONE ROW PER MATCH
AFTER MATCH SKIP TO NEXT ROW
PATTERN (A B)
DEFINE
A AS fist_mon <= second_mon,
B AS fist_mon > second_mon
);

Report message to a moderator

Re: Duplicate rows by generating date based on column value [message #686912 is a reply to message #686911]

Tue, 31 January 2023 01:59

OraFerro
Messages: 433
Registered: July 2011

Senior Member

Its interesting to mention that I also used the help of Chat GPT who gave me a couple of ideas (that also did not work) Smile

Report message to a moderator

Re: Duplicate rows by generating date based on column value [message #686913 is a reply to message #686911]

Tue, 31 January 2023 03:00

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

The following is just enough modification to your combination of union all and match_recognize to make it return the correct results and remove some things that seemed unnecessary, but probably not an efficient method.

SCOTT@orcl_12.1.0.2.0> WITH testc_data AS (
  2    SELECT 1 id, 1 fist_mon, 7 second_mon, 2000 dyear FROM DUAL UNION ALL
  3    SELECT 1 id, 5 fist_mon, 11 second_mon, 2001 dyear FROM DUAL UNION ALL
  4    SELECT 1 id, 2 fist_mon, 8 second_mon, 2002 dyear FROM DUAL UNION ALL
  5    SELECT 2 id, 3 fist_mon, 9 second_mon, 2005 dyear FROM DUAL UNION ALL
  6    SELECT 2 id, 1 fist_mon, 7 second_mon, 2007 dyear FROM DUAL UNION ALL
  7    SELECT 3 id, 4 fist_mon, 10 second_mon, 2022 dyear FROM DUAL
  8  )
  9  SELECT id, fist_mon, second_mon, dyear, generated_date
 10  FROM   testc_data
 11  MATCH_RECOGNIZE (
 12    ORDER BY id, dyear
 13    MEASURES
 14  	 TO_DATE('01-' || LPAD((fist_mon), 2, '0') || '-' || dyear, 'DD-MM-YYYY') as generated_date
 15    ALL ROWS PER MATCH
 16    PATTERN (A)
 17    DEFINE
 18  	 A AS fist_mon <= second_mon)
 19  UNION ALL
 20  SELECT id, fist_mon, second_mon, dyear, generated_date
 21  FROM   testc_data
 22  MATCH_RECOGNIZE (
 23    ORDER BY id, dyear
 24    MEASURES
 25  	 TO_DATE('01-' || LPAD((second_mon), 2, '0') || '-' || dyear, 'DD-MM-YYYY') as generated_date
 26    ALL ROWS PER MATCH
 27    PATTERN (B)
 28    DEFINE
 29  	 B AS fist_mon <= second_mon)
 30  ORDER BY generated_date
 31  /

        ID   FIST_MON SECOND_MON      DYEAR GENERATED_DATE
---------- ---------- ---------- ---------- ---------------
         1          1          7       2000 Sat 01-Jan-2000
         1          1          7       2000 Sat 01-Jul-2000
         1          5         11       2001 Tue 01-May-2001
         1          5         11       2001 Thu 01-Nov-2001
         1          2          8       2002 Fri 01-Feb-2002
         1          2          8       2002 Thu 01-Aug-2002
         2          3          9       2005 Tue 01-Mar-2005
         2          3          9       2005 Thu 01-Sep-2005
         2          1          7       2007 Mon 01-Jan-2007
         2          1          7       2007 Sun 01-Jul-2007
         3          4         10       2022 Fri 01-Apr-2022
         3          4         10       2022 Sat 01-Oct-2022

12 rows selected.

Report message to a moderator

Re: Duplicate rows by generating date based on column value [message #686914 is a reply to message #686913]

Tue, 31 January 2023 03:08

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

Just to be clear, the following produces the same result as the above, without the match_recognize.

SCOTT@orcl_12.1.0.2.0> WITH testc_data AS (
  2    SELECT 1 id, 1 fist_mon, 7 second_mon, 2000 dyear FROM DUAL UNION ALL
  3    SELECT 1 id, 5 fist_mon, 11 second_mon, 2001 dyear FROM DUAL UNION ALL
  4    SELECT 1 id, 2 fist_mon, 8 second_mon, 2002 dyear FROM DUAL UNION ALL
  5    SELECT 2 id, 3 fist_mon, 9 second_mon, 2005 dyear FROM DUAL UNION ALL
  6    SELECT 2 id, 1 fist_mon, 7 second_mon, 2007 dyear FROM DUAL UNION ALL
  7    SELECT 3 id, 4 fist_mon, 10 second_mon, 2022 dyear FROM DUAL
  8  )
  9  SELECT id, fist_mon, second_mon, dyear,
 10  	    TO_DATE('01-' || LPAD((fist_mon), 2, '0') || '-' || dyear, 'DD-MM-YYYY') as generated_date
 11  FROM   testc_data
 12  UNION ALL
 13  SELECT id, fist_mon, second_mon, dyear,
 14  	    TO_DATE('01-' || LPAD((second_mon), 2, '0') || '-' || dyear, 'DD-MM-YYYY') as generated_date
 15  FROM   testc_data
 16  ORDER BY generated_date
 17  /

        ID   FIST_MON SECOND_MON      DYEAR GENERATED_DATE
---------- ---------- ---------- ---------- ---------------
         1          1          7       2000 Sat 01-Jan-2000
         1          1          7       2000 Sat 01-Jul-2000
         1          5         11       2001 Tue 01-May-2001
         1          5         11       2001 Thu 01-Nov-2001
         1          2          8       2002 Fri 01-Feb-2002
         1          2          8       2002 Thu 01-Aug-2002
         2          3          9       2005 Tue 01-Mar-2005
         2          3          9       2005 Thu 01-Sep-2005
         2          1          7       2007 Mon 01-Jan-2007
         2          1          7       2007 Sun 01-Jul-2007
         3          4         10       2022 Fri 01-Apr-2022
         3          4         10       2022 Sat 01-Oct-2022

12 rows selected.

Report message to a moderator

Re: Duplicate rows by generating date based on column value [message #686915 is a reply to message #686914]

Tue, 31 January 2023 03:48

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

Here is another variation, using match_recognize without union all.

SCOTT@orcl_12.1.0.2.0> WITH testc_data AS (
  2    SELECT 1 id, 1 fist_mon, 7 second_mon, 2000 dyear FROM DUAL UNION ALL
  3    SELECT 1 id, 5 fist_mon, 11 second_mon, 2001 dyear FROM DUAL UNION ALL
  4    SELECT 1 id, 2 fist_mon, 8 second_mon, 2002 dyear FROM DUAL UNION ALL
  5    SELECT 2 id, 3 fist_mon, 9 second_mon, 2005 dyear FROM DUAL UNION ALL
  6    SELECT 2 id, 1 fist_mon, 7 second_mon, 2007 dyear FROM DUAL UNION ALL
  7    SELECT 3 id, 4 fist_mon, 10 second_mon, 2022 dyear FROM DUAL
  8  )
  9  SELECT id, fist_mon, second_mon, dyear, generated_date
 10  FROM   (SELECT id, fist_mon, second_mon, dyear, lvl
 11  	     FROM   testc_data,
 12  		    (SELECT LEVEL lvl FROM DUAL CONNECT BY LEVEL <= 2))
 13  MATCH_RECOGNIZE (
 14    ORDER BY id, dyear
 15    MEASURES
 16  	 DECODE (lvl,
 17  		 1, TO_DATE('01-' || LPAD((fist_mon), 2, '0') || '-' || dyear, 'DD-MM-YYYY'),
 18  		 2, TO_DATE('01-' || LPAD((second_mon), 2, '0') || '-' || dyear, 'DD-MM-YYYY'))  as generated_date
 19    ALL ROWS PER MATCH
 20    PATTERN (A)
 21    DEFINE
 22  	 A AS fist_mon <= second_mon)
 23  ORDER BY generated_date
 24  /

        ID   FIST_MON SECOND_MON      DYEAR GENERATED_DATE
---------- ---------- ---------- ---------- ---------------
         1          1          7       2000 Sat 01-Jan-2000
         1          1          7       2000 Sat 01-Jul-2000
         1          5         11       2001 Tue 01-May-2001
         1          5         11       2001 Thu 01-Nov-2001
         1          2          8       2002 Fri 01-Feb-2002
         1          2          8       2002 Thu 01-Aug-2002
         2          3          9       2005 Tue 01-Mar-2005
         2          3          9       2005 Thu 01-Sep-2005
         2          1          7       2007 Mon 01-Jan-2007
         2          1          7       2007 Sun 01-Jul-2007
         3          4         10       2022 Fri 01-Apr-2022
         3          4         10       2022 Sat 01-Oct-2022

12 rows selected.

Report message to a moderator

Re: Duplicate rows by generating date based on column value [message #686916 is a reply to message #686915]

Tue, 31 January 2023 03:51

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

Similarly, the following produces the same results as above.

SCOTT@orcl_12.1.0.2.0> WITH testc_data AS (
  2    SELECT 1 id, 1 fist_mon, 7 second_mon, 2000 dyear FROM DUAL UNION ALL
  3    SELECT 1 id, 5 fist_mon, 11 second_mon, 2001 dyear FROM DUAL UNION ALL
  4    SELECT 1 id, 2 fist_mon, 8 second_mon, 2002 dyear FROM DUAL UNION ALL
  5    SELECT 2 id, 3 fist_mon, 9 second_mon, 2005 dyear FROM DUAL UNION ALL
  6    SELECT 2 id, 1 fist_mon, 7 second_mon, 2007 dyear FROM DUAL UNION ALL
  7    SELECT 3 id, 4 fist_mon, 10 second_mon, 2022 dyear FROM DUAL
  8  )
  9  SELECT id, fist_mon, second_mon, dyear,
 10  	 DECODE (lvl,
 11  		 1, TO_DATE('01-' || LPAD((fist_mon), 2, '0') || '-' || dyear, 'DD-MM-YYYY'),
 12  		 2, TO_DATE('01-' || LPAD((second_mon), 2, '0') || '-' || dyear, 'DD-MM-YYYY'))  as generated_date
 13  FROM   testc_data,
 14  	    (SELECT LEVEL lvl FROM DUAL CONNECT BY LEVEL <= 2)
 15  ORDER BY generated_date
 16  /

        ID   FIST_MON SECOND_MON      DYEAR GENERATED_DATE
---------- ---------- ---------- ---------- ---------------
         1          1          7       2000 Sat 01-Jan-2000
         1          1          7       2000 Sat 01-Jul-2000
         1          5         11       2001 Tue 01-May-2001
         1          5         11       2001 Thu 01-Nov-2001
         1          2          8       2002 Fri 01-Feb-2002
         1          2          8       2002 Thu 01-Aug-2002
         2          3          9       2005 Tue 01-Mar-2005
         2          3          9       2005 Thu 01-Sep-2005
         2          1          7       2007 Mon 01-Jan-2007
         2          1          7       2007 Sun 01-Jul-2007
         3          4         10       2022 Fri 01-Apr-2022
         3          4         10       2022 Sat 01-Oct-2022

12 rows selected.

[Updated on: Tue, 31 January 2023 03:57]

Report message to a moderator

Re: Duplicate rows by generating date based on column value [message #686918 is a reply to message #686916]

Tue, 31 January 2023 04:59

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

You are on 12.1.0.1.0 (very buggy version). LATERAL/CROSS APPLY joins and MATCH_RECOGNIZE were also introduced in 12.1 so it should work. You need to be specific about "didn't work". Post your SQL along with errors/wrong results.

SY.

Report message to a moderator

Re: Duplicate rows by generating date based on column value [message #686919 is a reply to message #686911]

Tue, 31 January 2023 14:08

mathguy
Messages: 106
Registered: January 2023

Senior Member

OraFerro wrote on Tue, 31 January 2023 01:58

Of course, what I meant is that it avoids the obvious union all by repeating the base table select as in the above examples.

There is no "union all", obvious or otherwise, in unpivoting. The unpivoting solution, whether written with a cross join to a trivial helper table with two rows or using the UNPIVOT operator, does not use UNION ALL, explicitly or implicitly, and does not repeat the base table SELECT. Before you reject an approach, perhaps you could try to understand what it does, instead of just guessing (and guessing wrong) - especially when the approach is pretty basic, used by almost all programmers for this type of problem.

I also don't understand why you insist on writing a solution using MATCH_RECOGNIZE. That feature has many useful applications, but they all have something to do with pattern recognition, one way or another. MATCH_RECOGNIZE is not well suited for unpivoting tasks.

[Updated on: Tue, 31 January 2023 14:13]

Report message to a moderator

Re: Duplicate rows by generating date based on column value [message #686920 is a reply to message #686919]

Tue, 31 January 2023 23:02

OraFerro
Messages: 433
Registered: July 2011

Senior Member

@mathguy
The union all from the above replies that I am referring to:

SCOTT@orcl_12.1.0.2.0> select ...
  2  	   to_date (...) as generated_date
  3  from  testc
  4  union all
  5  select ...
  6  	   to_date (...) as generated_date
  7  from  testc
  8  order by ...;

Quote:

I also don't understand why you insist on writing a solution using MATCH_RECOGNIZE

I am doing this to explore and understand it more as I explained earlier. But I agree with you it might not be meant for this problem in particular.

Report message to a moderator

Previous Topic:	xml auto generate atribute
Next Topic:	Loop and save the index in an incremental number array

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 12:29:42 CDT 2024