Can I join two queries together in a Stored procedure on Redshift?

by Connor Meek   Last Updated July 12, 2019 14:26 PM - source

Originally I was trying this with a function but to no avail. Redshift doesn't allow for table return types or select data from tables. I'm hoping that maybe there is a way to do this with a Stored procedure, but I don't have much confidence any more. I have created a SP for the code listed below is it possible two join the two queries to itself for the SP?

tried doing a function, can't happen I have read the slim documentation that Amazon has for SPs and didn't get an answer for what I'm looking for

YTD current year
select 
   year
  , month
  , revenue
  ,transactiondate
  , Flag
from (
select
  extract(year from transactiondate) as year
  , to_char(transactiondate, 'Mon') as month
  , extract(month from transactiondate) as month_number
  , sum(netamount) as revenue
  ,transactiondate
  ,Flag
from
  vw_costs_of_businesss_copy

--where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(transactiondate, 'YYYY-MM-DD') ->=  concat(to_char(extract(year from '2019-01-01'), 'YYYY'),'01-01') --Convert the date param to year and concatenate with '01/01'
--where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(concat(to_char(extract(year from to_date('2019-01-01', 'YYYY-MM-DD'))),'01-01') , 'YYYY-MM-DD') >= '2019-01-01' --Convert the date param to year and concatenate with '01/01'
--where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(concat(to_char(Cas(extract(year from to_date('2019-01-01', 'YYYY-MM-DD')) as text ),'-01-01') , 'YYYY-MM-DD') >= '2019-01-01' --Convert the date param to year and concatenate with '01/01'
--where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(concat(to_char(Cast(extract(year from to_date('2019-01-01', 'YYYY-MM-DD')) as Text),'0000'),'-01') , 'YYYY-MM-DD') >= '2019-01-01' --Convert the date param to year and concatenate with '01/01'
where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(transactiondate, 'YYYY-MM-DD')>= to_date(concat(to_char(Cast(extract(year from to_date('2019-01-01', 'YYYY-MM-DD')) as Text),'0000'),'-01') , 'YYYY-MM-DD')

group by
  year
  , month
  , month_number
  ,transactiondate
 , Flag
)
order by month_number, year

--YTD PREV YEAR
select 
   year
  , month
  , revenue
  ,transactiondate
  , Flag
from (
select
  extract(year from transactiondate) as year
  , to_char(transactiondate, 'Mon') as month
  , extract(month from transactiondate) as month_number
  , sum(netamount) as revenue
  ,transactiondate
  ,Flag
from
  vw_costs_of_businesss_copy

--where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(transactiondate, 'YYYY-MM-DD') ->=  concat(to_char(extract(year from '2019-01-01'), 'YYYY'),'01-01') --Convert the date param to year and concatenate with '01/01'
--where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(concat(to_char(extract(year from to_date('2019-01-01', 'YYYY-MM-DD'))),'01-01') , 'YYYY-MM-DD') >= '2019-01-01' --Convert the date param to year and concatenate with '01/01'
--where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(concat(to_char(Cas(extract(year from to_date('2019-01-01', 'YYYY-MM-DD')) as text ),'-01-01') , 'YYYY-MM-DD') >= '2019-01-01' --Convert the date param to year and concatenate with '01/01'
--where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(concat(to_char(Cast(extract(year from to_date('2019-01-01', 'YYYY-MM-DD')) as Text),'0000'),'-01') , 'YYYY-MM-DD') >= '2019-01-01' --Convert the date param to year and concatenate with '01/01'
where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(transactiondate, 'YYYY-MM-DD')>= to_date(concat(to_char(Cast(extract(year from to_date('2019-01-01', 'YYYY-MM-DD')-1) as Text),'0000'),'-01') , 'YYYY-MM-DD')

group by
  year
  , month
  , month_number
  ,transactiondate
 , Flag
)

order by month_number, year

It needs to have the YTD and prev_YTD listed so I can pass it as a param, is this possible? I realize I'd have to make a table for it to do so. Where do I place the Prev_year, Prev-month columns



Answers 1


I think you are looking for the concept of UNION ALL to "join the two queries".

Here is an example using a Stored Procedure. See "Overview of Stored Procedures in Amazon Redshift" for more info.

CREATE PROCEDURE ytd_comparison(
      date_param IN    DATE
    , result_set INOUT REFCURSOR) 
AS $$
BEGIN
    OPEN result_set FOR --YTD
    SELECT year , month , revenue , transactiondate , Flag
    FROM (SELECT EXTRACT(year FROM transactiondate) AS year
               , TO_CHAR(transactiondate, 'Mon') AS month
               , EXTRACT(month FROM transactiondate) AS month_number
               , SUM(netamount) AS revenue
               , transactiondate
               , Flag
          FROM vw_costs_of_businesss_copy
          WHERE TO_DATE(transactiondate, 'YYYY-MM-DD') <= date_param
            AND TO_DATE(transactiondate, 'YYYY-MM-DD') >= DATE_TRUNC('year',date_param)
          GROUP BY year , month , month_number , transactiondate , Flag
          )
    UNION ALL --YTD PREV YEAR
    SELECT year ,month ,revenue ,transactiondate ,Flag
    FROM (SELECT EXTRACT(year FROM transactiondate) AS year
               , TO_CHAR(transactiondate, 'Mon') AS month
               , EXTRACT(month FROM transactiondate) AS month_number
               , SUM(netamount) AS revenue
               , transactiondate
               , Flag
          FROM vw_costs_of_businesss_copy
          WHERE TO_DATE(transactiondate, 'YYYY-MM-DD') <= DATEADD(year, 1, date_param)
            AND TO_DATE(transactiondate, 'YYYY-MM-DD') >= DATE_TRUNC('year', DATEADD(year, 1, date_param))
          GROUP BY year , month , month_number , transactiondate , Flag
          )
    ORDER BY month_number , year
    ;
    RETURN;
END;
$$ LANGUAGE plpgsql;

You need to provide a name for the output REFCURSOR when calling the procedure. You must open a transaction with BEGIN to maintain the cursor, then fetch the output from the REFCURSOR, and then discard the results cursor with END transaction.

BEGIN;
CALL ytd_comparison ('2016-07-01'::DATE, 'ytd_results'); 
FETCH ALL FROM ytd_results; 
END;
Joe Harris
Joe Harris
July 12, 2019 14:25 PM

Related Questions