I have a query and when I run it, I run out of spool space. So, I was wondering if there is some way (other than subsetting based on table data) to limit the number of records that are processed (to the first 10, 100, 1000...). I don't want to simply limit the number of records shown in the output. I want the query to only execute on the first few records.
Here is my query:
create table dl_pn_pricing.MAC_2 as (
, a.DAY_SPLY_CNT + b.CALNDR_DT -1 AS SUPPLY_TO
/* DW FINAL CLAIM LOGIC */
, Case a.CLM_STAT_CD When 'R' Then 2 Else 1 End As ForceDownReject
, Case a.CLM_STAT_CD When 'P' Then 2 When 'C' Then 3 When 'X' Then 1 Else 4 End As TransactionOrder
, ROW_NUMBER() OVER (PARTITION BY a.PHRM_CLM_SRC_1_ID ORDER BY ForceDownReject, a.PHRM_CLM_SRC_2_ID, TransactionOrder) As RowNumber
, Case When RowNumber = 1 And a.CLM_STAT_CD = 'P' Then 'Y' Else 'N' End As Dw_Final_Paid
From P_EDW_DIST_VIEW.PHRM_CLM_ALL_TRANS_FACT a
Inner Join P_EDW_DIST_VIEW.PHRMTCL_PROD_DIM d On a.PHRMTCL_PROD_DIM_ID = d.PHRMTCL_PROD_DIM_ID
Inner Join P_EDW_DIST_VIEW.CALNDR_DT_DIM b On a.CLM_DOS_FILL_DT_DIM_ID = b.CALNDR_DT_DIM_ID
Inner Join P_EDW_DIST_VIEW.MBR_DIM c On a.MBR_DIM_ID = c.MBR_DIM_ID
Inner Join P_EDW_DIST_VIEW.PHRM_DIM e On a.PHRM_DIM_ID = e.PHRM_DIM_ID
Inner Join P_EDW_DIST_VIEW.PRSCBR_DIM f On a.PRSCBR_DIM_ID = f.PRSCBR_DIM_ID
WHERE Dw_Final_Paid = 'Y' ) WITH DATA;
I think you should create a previous table with a "sample 100" then you must join with that table.
Furthermore you can add this filter to your query "
a.CLM_STAT_CD = 'P'". I think que answerset would be the same and your query would filter something in advance.
P_EDW_DIST_VIEW.PHRM_CLM_ALL_TRANS_FACT a "
"From (SELECT TOP 100 * FROM
P_EDW_DIST_VIEW.PHRM_CLM_ALL_TRANS_FACT) a "