Run time parameter in MACRO/COMPLEX VIEW

Database
Ckp
Enthusiast

Run time parameter in MACRO/COMPLEX VIEW

Hi Gurus,

Is there is a way to pass user defined parameter to macro within macro ?

Or Is there is any alternative which could be useful in case of complex view, I dont want to create stored procedure.

Thanks in advance :)

REPLACE MACRO macTrans(store_num INTEGER)AS
(
SELECT
RTS.Loyalty_Num,
RTS.store_num,
RTS.Store_Id,
RTS.Trans_Id,
RTS.Redemption_Dt,
RTS.Total_Net_Sales_Amt,
disc.Discount_Ref_Num,
disc.Discount_Amt_of_Coupon,
SUM(disc.Discount_Amt_of_Coupon)AS Total_Discount_Amt,
SUM(item.Item_Net_Sales_Amt) AS Total_Item_Net_Sales_Amt,
SUM(item.Scan_Qty) AS Total_Units_Sold,
SUM(CASE WHEN item.Scan_Wgt_Amt <>0 THEN item.Scan_Qty ELSE 0 END) AS Total_Scan_Wgt_Amt
FROM
(EXECUTE macDisc(:store_num)) disc
LEFT OUTER JOIN (EXECUTE macItem(:store_num)) item ------- Here
ON disc.Trans_Id=item.Trans_Id
AND disc.trans_dt=item.trans_dt
LEFT OUTER JOIN (EXECUTE macRts(:store_num)) Rts ------- And Here
ON disc.Trans_Id=Rts.Trans_Id
AND disc.trans_dt=Rts.Redemption_Dt
GROUP BY 1,2,3,4,5,6,7,8;);