All Forums General
sulliv16 6 posts Joined 04/16
27 Apr 2016
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;

 

AtardecerR0j0 71 posts Joined 09/12
29 Apr 2016

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.
 

Be More!!

ToddAWalter 316 posts Joined 10/11
29 Apr 2016

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 "

sulliv16 6 posts Joined 04/16
03 May 2016

Thank you for your input guys. This helps.

You must sign in to leave a comment.