FOR EACH clause in Teratada SQL

Tools

FOR EACH clause in Teratada SQL

I have the statment below:

INSERT INTO P_WENL.RR_tmpParque2

SELECT

         Prqe.dt_foto_lnha

        ,Pssa.cdCliente

        ,Pssa.cdGrupo

        ,Pssa.nmCliente

        ,Pssa.nmGrupo

        ,Pssa.dsRazaoSocial

        ,Pssa.nmGerenteSenior

        ,Pssa.nmDiretor        

        ,Prqe.id_uf

        ,TpMtrl.ds_tipo_mtrl

        ,Area.nm_area_rgnl

        ,Plno.ds_plno

        ,EstdLnha.ds_estd_lnha

        ,CAST(CAST(( Prqe.dt_prmr_atvc_lnha (FORMAT 'YYYYMM') ) AS VARCHAR(6)) AS INTEGER)  AS prmr_atvc

        ,COUNT(*) AS qtde

   FROM   P_Viedb.vw_fat_prqe_lnha_dspt Prqe

     INNER JOIN  P_WENL.RR_tmpPessoa2 Pssa

        ON CASE WHEN Prqe.Id_Pssa_rspl_cnta <= 0 THEN Prqe.Id_Pssa ELSE Prqe.Id_Pssa_rspl_cnta END = Pssa.Id_Pssa

     LEFT JOIN   P_Viedb.Vw_Dim_Crtr Crtr

        ON Prqe.Id_Crtr = Crtr.Id_Crtr

     LEFT JOIN   P_Viedb.Vw_Dim_Tipo_Mtrl TpMtrl

        ON Prqe.Id_Tipo_Mtrl_Srvc = TpMtrl.Id_Tipo_Mtrl

     LEFT JOIN   P_Viedb.Vw_Dim_Area_Rgnl Area

        ON Prqe.Id_Area_Rgnl = Area.Id_Area_Rgnl

     LEFT JOIN   P_Viedb.Vw_Dim_Tipo_Crtr     TpCrtr

        ON Prqe.Id_Tipo_Crtr = TpCrtr.Id_Tipo_Crtr

     LEFT JOIN   P_Viedb.Vw_Dim_Sist_Pgto SisPgto

        ON Prqe.Id_Sist_Pgto = SisPgto.Id_Sist_Pgto

     LEFT JOIN   P_Viedb.Vw_Dim_Plno Plno

        ON Prqe.Id_Plno = Plno.Id_Plno

     LEFT JOIN   P_Viedb.Vw_Dim_Estd_Lnha EstdLnha

        ON Prqe.Id_Estd_Lnha = EstdLnha.Id_Estd_Lnha

WHERE Prqe.dt_foto_lnha = '2016-01-31'

  AND Prqe.Fl_Prqe_Ofcl = 1

GROUP BY Prqe.dt_foto_lnha  

        ,Pssa.cdCliente

        ,Pssa.cdGrupo

        ,Pssa.nmCliente

        ,Pssa.nmGrupo

        ,Pssa.dsRazaoSocial

        ,Pssa.nmGerenteSenior

        ,Pssa.nmDiretor        

        ,Prqe.id_uf

        ,TpMtrl.ds_tipo_mtrl

        ,Area.nm_area_rgnl

        ,Plno.ds_plno

        ,EstdLnha.ds_estd_lnha     

        ,CAST(CAST(( Prqe.dt_prmr_atvc_lnha (FORMAT 'YYYYMM') ) AS VARCHAR(6)) AS INTEGER)

As you can see in the WHERE, I have a date as condition. I need to do the same statment for the year 2015 monthly.

I thinking use a FOR EACH statment, but I didn't find anything about it.

How can I do this without manually change the date?

Thanks

1 REPLY

Re: FOR EACH clause in Teratada SQL

are you looking for the first day of each month for the year ?

if so , join to the sys_calendar.calendar view on the date, with the additional condition of where day_of_month = 1