You can translate the question and the replies:

How to multiply rows based on some numbers

Hello! I'm new to SQL world and Denodo community as well. What I'm trying to do is to multiply rows by month gap as below. I've been looking for this for hours.. Result 1 | User | Account | Product | Column D | Start Month | End Month | | XXX | Paul | A12 | 3 | 1st Apr, 2022 | 30 Jun, 2022 | Result 2 (copy two additional rows based on the gap between start month and end month) | User | Account | Product | Column D | Start Month | End Month | Month | | XXX | Paul | A12 | 3 | 1st Apr, 2022 | 30 Jun, 2022 | 4 | | XXX | Paul | A12 | 3 | 1st Apr, 2022 | 30 Jun, 2022 | 5 | | XXX | Paul | A12 | 3 | 1st Apr, 2022 | 30 Jun, 2022 | 6 | So Result 2 has 3 rows because # month from start month to end month is 3. The only difference between the copied rows is Month column. I found I have no previlege to use Create or Insert in the existing table. So is there any way for me to do this using Join? Thank you so much for your help in advance.
user
05-05-2022 22:00:31 -0400

3 Answers

Hi In this scenario, I would make use of the [WITH clause](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/queries_select_statement/with_clause/with_clause#with-clause) which specifies a **common table expression (CTE)**. A CTE is like a temporary result set that is defined and used for the duration of a SQL statement. In order to achieve the expected result, I will create a CTE to hold an integer for every month and apply cross join as below: > WITH vdatemaster AS > ( > SELECT '2022-01-01' as startmonth,1 as intmonth UNION ALL > SELECT '2022-02-01' as startmonth,2 as intmonth UNION ALL > SELECT '2022-03-01' as startmonth,3 as intmonth UNION ALL > SELECT '2022-04-01' as startmonth,4 as intmonth UNION ALL > SELECT '2022-05-01' as startmonth,5 as intmonth UNION ALL > SELECT '2022-06-01' as startmonth,6 as intmonth from dual() > ) > > select a.*,b.intmonth from view1 a CROSS JOIN vdatemaster b WHERE b.startmonth between a.startmonth and a.endmonth order by b.intmonth; Here View1 is the view that returns Result 1. You can have a look at the section [Creating Join Views](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/creating_derived_views/creating_join_views/creating_join_views#creating-join-views) of the for more information on join. Hope this helps!
Denodo Team
06-05-2022 08:08:35 -0400
Thank you so much! This is far simpler way than I thought. In the With clause, can I make some general syntax for the month? Since the data will be expanded, the start month could be 2024-06-01 for example. Or should I address all the start months? Thank you!
user
10-05-2022 19:51:55 -0400
Hi The WITH clause is considered “temporary” because the result is not permanently stored anywhere in the database schema. It acts as a temporary view that only exists for the duration of the query. Hence, in this scenario, I would provide all the start months including '2024-06-01' in the WITH clause. Hope this helps!
Denodo Team
12-05-2022 06:45:28 -0400
You must sign in to add an answer. If you do not have an account, you can register here