A lot of code in query, how reduce code length in Oracle SQL-CodePudding

Question for sql expert what use in this situation?

I need call some query 31 times this is a lot of code, is it possibile use some good option to reduce code length.

I have 31 days column in table my_days other column

eg.

with cte as

(

Select  day_1 as DAY_TOTAL, 1 as DAY, t1.column_1, t2.column_2, t3.column_3 from my_days my
left outer join table_1 t1 ....
left outer join table_2 t2 ....
left outer join table_3 t3 .... 
where user = :PARAMETER_USER and CODE =  :PARAMETER_CODE

union all

Select  day_2 as DAY_TOTAL, 2 as DAY, t1.column_1, t2.column_2, t3.column_3 from my_days my
left outer join table_1 t1 ....
left outer join table_2 t2 ....
left outer join table_3 t3 .... 
where user = :PARAMETER_USER and CODE =  :PARAMETER_CODE
---------

Select  day_31 as DAY_TOTAL, 3 as DAY, t1.column_1, t2.column_2, t3.column_3 from my_days my
left outer join table_1 t1 ....
left outer join table_2 t2 ....
left outer join table_3 t3 ....
where user = :PARAMETER_USER and CODE =  :PARAMETER_CODE
)

Select * from cte    other join.....

Maybe create function and use function insted query in union all?

CodePudding user response：

You can UNPIVOT:

SELECT day_total, day, column1, column2, column3
FROM   (
         SELECT /*other_columns,*/ day_total, day
         FROM   my_days
         UNPIVOT (
           day_total FOR day IN (
             day_1 AS 1,
             day_2 AS 2,
             day_3 AS 3,
             day_4 AS 4,
             -- ...
             day_29 AS 29,
             day_30 AS 30,
             day_31 AS 31
           )
         )
       ) my
       left outer join table_1 t1 ....
       left outer join table_2 t2 ....
       left outer join table_3 t3 .... 
         other join ...
WHERE  "USER" = :PARAMETER_USER
AND    CODE =  :PARAMETER_CODE

CodePudding user response：

.. or use MODEL clause

WITH    my_days AS (Select 1 as day_1, 'xx' as col_x, 'yy' as col_y From DUAL),
        cte AS(SELECT INDX, day_x, col_x, col_y FROM my_days
               MODEL 
               DIMENSION BY(0 as INDX)
               MEASURES (0 as day_x, col_x, col_y)
               RULES ITERATE(31)
                      ( day_x[ITERATION_NUMBER 1] = ITERATION_NUMBER 1,
                        col_x[ANY] = col_x[0],
                        col_y[ANY] = col_y[0]
                      ) 
              )
SELECT * FROM ct
left outer join table_1 t1 
       ....