Limit query to process fewer records

General
Enthusiast

Limit query to process fewer records

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 (
select *
from (select
a.EDW_PHRM_CLM_TRANS_ID
, a.PHRM_DIM_ID
, a.PRSCBR_DIM_ID
, a.MBR_DIM_ID
, c.RXCLM_CARR_ID
, c.RXCLM_ACCT_ID
, c.RXCLM_GRP_ID
, c.RXCLM_MBR_ID
, a.PAT_AGE_NUM
, a.SUBM_DOB_DT
, b.CALNDR_DT
, a.PHRM_CLM_SRC_1_ID
, a.PHRM_CLM_SRC_2_ID
, a.CLM_STAT_CD
, a.PAID_CLM_CNT
, d.GPI
, d.GPI_GENRC_NME
, d.PROD_NME
, d.PROD_NME_EXT
, d.PROD_DESC_ABBR
, d.MTRC_STR_NUM
, d.STR_UOM_CD
, a.DSPNSD_PRC_QTY
, a.DAY_SPLY_CNT
, a.DAY_SPLY_CNT + b.CALNDR_DT -1 AS SUPPLY_TO
, a.SUBM_PROD_ID_QLFR_CD
, a.SUBM_PROD_ID
, a.SUBM_PRSCBR_ID_QLFR_CD
, a.SUBM_PRSCBR_ID
, f.PRSCBR_NPI
, e.PHRM_NCPDP
, e.PHRM_NPI

/* 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
) t1
WHERE Dw_Final_Paid = 'Y' ) WITH DATA;
3 REPLIES
Teradata Employee

Re: Limit query to process fewer records

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.

Teradata Employee

Re: Limit query to process fewer records

Replace

"From        P_EDW_DIST_VIEW.PHRM_CLM_ALL_TRANS_FACT a "

with

"From (SELECT TOP 100 * FROM P_EDW_DIST_VIEW.PHRM_CLM_ALL_TRANS_FACT) a "

Enthusiast

Re: Limit query to process fewer records

Thank you for your input guys. This helps.