CODEWITHSUNDEEP
sqlinformatica
Mukhtar .
Mukhtar .

Reputation: 11

Add values to Audit table

I have to maintain an AUDIT table having columns year,month and status. How do I load data into it dynamically. Like year is 2016, month is 1 and status as Y. How do I do it for all years and months from 2016-2019 and more coming ahead.

it should look like this in 3 columns:

Year    Month    Status
2016    1        Y
2016    2        Y
2016    3        N
.
.
.
2019     10      Y

It should dynamically update for upcoming years as well like in future if 2021 comes then it should update accordingly.

I have to achieve this using Informatica.

Upvotes: 0

Views: 37

Answers (1)

CompEng
CompEng

Reputation: 7376

You can find like this:

    SELECT TO_CHAR (ADD_MONTHS (TO_DATE ('01.12.2015', 'dd.mm.yyyy'), LEVEL),
                    'YYYY') YOUR_YEAR,
           TO_CHAR (ADD_MONTHS (TO_DATE ('01.12.2015', 'dd.mm.yyyy'), LEVEL),
                    'MM')YOUR_MONTH,
           'YOUR_STATUS' YOUR_STATUS
      FROM DUAL
CONNECT BY LEVEL <=
              MONTHS_BETWEEN (TO_DATE ('01.10.2019', 'dd.mm.yyyy'),
                              TO_DATE ('01.12.2015', 'dd.mm.yyyy'))

Upvotes: 1

Related Questions