Link to home
Start Free TrialLog in
Avatar of Culwatrnca11
Culwatrnca11Flag for United States of America

asked on

Teradata SQL Assistant - Need assistance with Sub Queries - Get out of spool space error.

I get a spool space error when running report if date range is larger so I have reduced range to return data. I need to run report from 1/1/2017 to current date.  I am wondering if my sub queries are correct ? first time creating sub query and not sure if I understand the concept.  My question is if I have sub queries does my "Where clause"  go at the end of each sub query for the parameters I am focusing on or does it go at end of code?  How do I incorporate my parameters into the "where clause" when using sub queries, etc. and prevent "spool space errors".  Assistance greatly appreciated.


Focus of report is on the following to narrow what is pulled back:

where            
AP.SERV_DATE BETWEEN '03/12/2017' AND CURRENT_DATE

PX.POS_TYPE_C IN ('11','15','17','20','49','50','71','72')

CPT_CODE IN ('62320','62321','62322','62324','62324','62325','62326','62327','99151','99152','99153','99154','99155','99156','99157')
     



select distinct		
'SCAL' as "Region"		
,clm.internal_id AS "Claim ID"		
,ap.acct_num_with_ven as "Invoice"		

,PATIENT.PAT_MRN_ID AS PAT_MRN
,Cast (patient.Birth_Date as date)
,extract(year from current_date) - extract(year from PATIENT.BIRTH_DATE) -
       case when current_date (format 'MMDD') (char(4)) <
               PATIENT.BIRTH_DATE (format 'MMDD')  (char(4)) then 1
        else 0 end                        as AGE


,case	ap.status_c 	
	when	'1' then  'New'   
                               when '2' then 'Pending'    		
                               when  '3' then  'Denied'   		
                                when '4'  then 'Clean'     		
                               when  '5'  then 'Void'   		
else	' ' 	
end	as "Claim Status" 	
		
,case	when ap.ap_sts_c = 1 then 'Released to Accounts Payable '	
           when ap.ap_sts_c = 2 then 'Batched for Payment '		
           when ap.ap_sts_c = 3 then 'EOB Generated' 		
else	'Not Released to Accounts Payable ' 	
end	 as "AP Status"	
		
,cast(ap.adj_time as char(10)) as "Status Date"		
,ap.ap_status_ap_dt as "Sent to AP Date"		
,ckr.ap_run_date as "AP Run Date"		
,chk.name as "Check Status"		
		
,CASE		
when (ven.vendor_name LIKE '%kaiser foundation hospital%'		
OR ven.vendor_name LIKE '%the permanente medical group%'		
OR ven.vendor_name LIKE 'kaiser foundation medical group%'		
OR ven.vendor_name LIKE '%tpmg%'		
OR VEN.VENDOR_EXTERNAL_ID = '1644129787' --HAWAII PERMANENTE MEDICAL		
OR VEN.VENDOR_EXTERNAL_ID  = '1644120262'  --MEDIMPACT		
OR VEN.VENDOR_EXTERNAL_ID  = '1644120363'  --EMPLOYERS MUTUAL INC		
OR VEN.VENDOR_EXTERNAL_ID IN ('1644120366') --SHIELD HEALTHCARE		
OR AP.VENNET_CONTRACT_ID IN ('150902',  '150903') --APRIA HEALTHCARE INC		
  )		
	then 'Internal'  	
	else 'External'	
end as "Int_Ext"		
		
,case		
        when ap.orig_rev_clm_id is null and ap.orig_adjst_clm_id is null then 'Original Claim'		
        when ap.orig_rev_clm_id is null and ap.adjst_clm_id is not null and ap.orig_adjst_clm_id is not null then 'Multiple Adj Interim Claim'		
        when ap.orig_rev_clm_id is not null then 'Reversal Claim'		
        when ap.orig_rev_clm_id is null and ap.adjst_clm_id is null and ap.orig_adjst_clm_id is not null then 'Adjusted Claim'		
end as "Claim State"		
		
--,hip.registry_name as "Work Queue Pool"		
,upper(spec.name) as "Specialty"		
		
,case		
	when ap.claim_format_c = 1 then 'CMS'	
	when ap.claim_format_c = 2 then 'UB'	
	else 'N/A'	
end as "Format"		
		
,epp.benefit_plan_name as "Benefit Plan"		
,epm.payor_name as "Payor"		
,pln.plan_grp_name as "Plan Group"		
,ven.vendor_name as "Vendor"		
,ven.vendor_external_id as "Vendor ID"		
,lob.lob_name as "LOB Name"		
,pos.pos_name as "Place of Service"		
,ser.prov_name as "Provider"		
,ap.service_start_date as "SVC Date"		
,ap.service_end_date as "SVC End Date"		
,ap.date_received as "Date Received"		
,(current_date - ap.date_received - 1) as "Claim Age"		
,emp.name as "Entry User"		
,ap.entry_date as "Entry Date"		
	
,px.pos_type_c || ' - ' || PT.NAME AS POS_TYPE
,ap.admission_type_c as "Admission type"		
,ap.type_of_bill as "Type of Bill"		
,coalesce(zc_mtpc.name, 'undefined') as "Payment Method"		
,ap.tot_billed_amt as "Total Billed"		
,ap.tot_net_payable as "Total Payable"		
,ap.tot_allowed_amt as "Total Allowed"		
,ap.tot_copay as "Total Copay"		
,ap.tot_deductible as "Total Deductible"		
,ap.tot_coins as "Total Coinsurance"		
,ap.tot_pat_total as "Patient Total"		
,ap.tot_prim_pat_port AS "Primary Portion"		
,ap.tot_prim_ins_amt as "Primary Insurance"		


, CPT_CODE_1
, CPT_CODE_2
, CPT_CODE_3
, CPT_CODE_4
, CPT_CODE_5	
, CLM_DX_1
, CLM_DX_2
, CLM_DX_3
, CLM_DX_4
, CLM_DX_5

/*,case
	when dx.line = 1 then 'DX_1'	
	when dx.line = 2 then 'DX_2'	
		when dx.line = 3 then 'DX_3'	
		when dx.line = 4 then 'DX_4'	
		when dx.line = 5 then 'DX_5'	
		else 'N/A'	
END AS "DX_Line"	*/

--,edg.ref_bill_code 		
--,eap.proc_code	

--,oproc.cpt_code
,cml.adj_table_name


from ap_claim ap		
inner join clm_map clm on ap.claim_id = clm.cid		
left outer join ap_claim_dofr_chks dofr on ap.claim_id = dofr.claim_id and dofr.line = 1		
left outer join zc_check_sts chk on dofr.pmt_info_chk_sts_c = chk.check_status_c		
left outer join ap_check ckr on dofr.pmt_info_chk_id = ckr.check_id		
left outer join clarity_vendor ven on ap.vendor_id = ven.vendor_id		
left outer join zc_specialty spec on ap.assoc_spec_c = spec.specialty_c		
left outer join ib_receiver ib on ap.inbasket_message_id = ib.msg_id		
left outer join clarity_hip hip on ib.registry_id = hip.registry_id		
left outer join coverage cov on ap.coverage_id = cov.coverage_id		
left outer join clarity_epp epp on ap.plan_id = epp.benefit_plan_id		
left outer join clarity_epm epm on ap.payor_id = epm.payor_id		
left outer join plan_grp pln on ap.plan_group_id = pln.plan_grp_id		
left outer join clarity_lob lob on ap.clm_lob_id = lob.lob_id		
left outer join clarity_pos pos on ap.loc_id = pos.pos_id		
left outer join clarity_ser ser on ap.prov_id = ser.prov_id		
left outer join clarity_emp emp on ap.entry_user_id = emp.user_id		
left outer join ap_claim_2 ap2 on ap.claim_id = ap2.claim_id		
left outer join zc_meth_to_pay_clm zc_mtpc on ap.meth_to_pay_clm_c = zc_mtpc.meth_to_pay_clm_c		
left outer join patient on ap.pat_id = patient.pat_id		

left outer join order_proc oproc on patient.pat_id = oproc.pat_id
	
LEFT OUTER JOIN ap_claim_px px ON ap.claim_id=px.claim_id		
--left outer join ap_claim_dx dx on ap.claim_id = dx.claim_id		
--left outer join clarity_edg edg on dx.dx_id = edg.dx_id		
left outer join ap_claim_px_2 px2 on ap.claim_id = px2.claim_id		
--LEFT OUTER JOIN clarity_eap eap ON px.proc_id = eap.proc_id		
left outer join pat_enc enc on oproc.pat_enc_csn_id = enc.pat_enc_csn_id
LEFT OUTER JOIN ZC_POS_TYPE PT	ON PX.POS_TYPE_C=PT.POS_TYPE_C	
LEFT OUTER JOIN clarity_cml cml ON enc.ben_adj_table_id = cml.adj_table_cml_id

LEFT OUTER  JOIN 
                 (


								SELECT 
								
								PXX.CLAIM_ID
								,MAX(CASE WHEN PXX.SEQ_NUM = 1 THEN PROC_CODE 	 ELSE NULL END) AS CPT_CODE_1
								,MAX(CASE WHEN PXX.SEQ_NUM = 2 THEN PROC_CODE 	 ELSE NULL END) AS CPT_CODE_2
								,MAX(CASE WHEN PXX.SEQ_NUM = 3 THEN PROC_CODE 	 ELSE NULL END) AS CPT_CODE_3
						    	,MAX(CASE WHEN PXX.SEQ_NUM = 4 THEN PROC_CODE 	 ELSE NULL END) AS CPT_CODE_4
								,MAX(CASE WHEN PXX.SEQ_NUM = 5 THEN PROC_CODE 	 ELSE NULL END) AS CPT_CODE_5
								,MAX(CASE WHEN PXX.SEQ_NUM = 6 THEN PROC_CODE 	 ELSE NULL END) AS CPT_CODE_6
								,MAX(CASE WHEN PXX.SEQ_NUM = 7 THEN PROC_CODE 	 ELSE NULL END) AS CPT_CODE_7
								,MAX(CASE WHEN PXX.SEQ_NUM = 8 THEN PROC_CODE 	 ELSE NULL END) AS CPT_CODE_8
								,MAX(CASE WHEN PXX.SEQ_NUM = 9 THEN PROC_CODE 	 ELSE NULL END) AS CPT_CODE_9
								,MAX(CASE WHEN PXX.SEQ_NUM = 10 THEN PROC_CODE 	 ELSE NULL END) AS CPT_CODE_10
								
								FROM
								(
										SELECT 
										PX.CLAIM_ID											
										,PX.PROC_CODE								
										,ROW_NUMBER() OVER (PARTITION BY PX.CLAIM_ID ORDER BY PX.PROC_CODE	 ASC) AS SEQ_NUM											
										FROM (SELECT  DISTINCT A.CLAIM_ID, C.PROC_CODE ||  ' - ' || C.PROC_NAME AS PROC_CODE 
															FROM AP_CLAIM_PX A 
															INNER JOIN AP_CLAIM B  ON A.CLAIM_ID= B.CLAIM_ID --AND B.STATUS_C = 2
															INNER JOIN CLARITY_EAP C ON A.PROC_ID = C.PROC_ID
														
																
																	
																
																) PX
								
								) PXX
								
															
								GROUP BY PXX.CLAIM_ID
								
								

  
                ) EAP ON AP.CLAIM_ID = EAP.CLAIM_ID	
                
                
              LEFT JOIN 
             (
              
              SELECT 
										
										PXX.CLAIM_ID
										,MAX(CASE WHEN PXX.SEQ_NUM = 1 THEN REF_BILL_CODE 	 ELSE NULL END) AS CLM_DX_1
										,MAX(CASE WHEN PXX.SEQ_NUM = 2 THEN REF_BILL_CODE	 ELSE NULL END) AS CLM_DX_2
										,MAX(CASE WHEN PXX.SEQ_NUM = 3 THEN REF_BILL_CODE	 ELSE NULL END) AS CLM_DX_3
										,MAX(CASE WHEN PXX.SEQ_NUM = 4 THEN REF_BILL_CODE	 ELSE NULL END) AS CLM_DX_4
										,MAX(CASE WHEN PXX.SEQ_NUM = 5 THEN REF_BILL_CODE	 ELSE NULL END) AS CLM_DX_5
										,MAX(CASE WHEN PXX.SEQ_NUM = 6 THEN REF_BILL_CODE	 ELSE NULL END) AS CLM_DX_6
										,MAX(CASE WHEN PXX.SEQ_NUM = 7 THEN REF_BILL_CODE	 ELSE NULL END) AS CLM_DX_7
										,MAX(CASE WHEN PXX.SEQ_NUM = 8 THEN REF_BILL_CODE	 ELSE NULL END) AS CLM_DX_8
										,MAX(CASE WHEN PXX.SEQ_NUM = 9 THEN REF_BILL_CODE	 ELSE NULL END) AS CLM_DX_9
										,MAX(CASE WHEN PXX.SEQ_NUM = 10 THEN REF_BILL_CODE	 ELSE NULL END) AS CLM_DX_10
										
										FROM
										(
												SELECT 
												PX.CLAIM_ID											
												,PX.REF_BILL_CODE								
												,ROW_NUMBER() OVER (PARTITION BY PX.CLAIM_ID ORDER BY PX.REF_BILL_CODE	 ASC) AS SEQ_NUM											
												FROM (SELECT DISTINCT A.CLAIM_ID , F.REF_BILL_CODE	|| ' - '|| F.DX_NAME	AS REF_BILL_CODE					
												                        FROM AP_CLAIM_DX A	
												                        INNER JOIN AP_CLAIM B ON A.CLAIM_ID = B.CLAIM_ID	--AND B.STATUS_C = 2
												                        INNER JOIN CLARITY_EDG F ON A.DX_ID = F.DX_ID
												                       
																		
																		) PX
										
										) PXX
										
										GROUP BY PXX.CLAIM_ID
             ) DX ON AP.CLAIM_ID = DX.CLAIM_ID	  
                
      
--left outer join arpb_transactions arpb on patient.pat_id = arpb.patient_id
--left outer Join clarity_tdl_tran tdl ON arpb.patient_id = tdl.int_pat_id-- Origional Inner Join
where		
AP.SERV_DATE BETWEEN '03/12/2017' AND CURRENT_DATE
and
PX.POS_TYPE_C IN ('11','15','17','20','49','50','71','72') 
--AND
--CPT_CODE_1 IN ('62320','62321','62322','62324','62324','62325','62326','62327','99151','99152','99153','99154','99155','99156','99157') 	

--and		
--oproc.cpt_code IN ('62320','62321','62322','62324','62324','62325','62326','62327','99151','99152','99153','99154','99155','99156','99157')
	
ORDER BY clm.internal_id ASC;

Open in new window

Avatar of Nitin Sontakke
Nitin Sontakke
Flag of India image

While I am NOT  an authority on the subject, I strongly discourage you to use sub queries. I avoid them at any cost myself.

It is not clear if this is part of the stored procedure or you are just running this script in Management Studio. In either case I would suggest (depending on volume of data) to create either physical temp tables (#) or the table variables (@).

For example, for the following two things you are trying to achieve, I would create a table variable, put these values in it and INNER JOIN on that table.

PX.POS_TYPE_C IN ('11','15','17','20','49','50','71','72')

CPT_CODE IN ('62320','62321','62322','62324','62324','62325','62326','62327','99151','99152','99153','99154','99155','99156','99157')      

From the look at it I suggest you implement the temp table / table variable approach and then keep on improving on it. When you create temp table / table variable, you can also index these tables on join columns thereby significantly improving the performance.
The bottom line is let SQL Server perform tasks in smaller chunks instead of everything in one go. Divide and conquer!
Oh dear. NEVER use "select distinct" on a huge query - very bad for performance. Initial thought only, intened to look for more soon.

suggested reading: https://www.experts-exchange.com/articles/12282/Select-Distinct-is-returning-duplicates.html
in particular the links at the end
How much repetition do you get from this query? i.e. are the rows distinct?
SELECT  
      ap.acct_num_with_ven AS "Invoice"
    , CASE ap.status_c
            WHEN '1' THEN 'New'
            WHEN '2' THEN 'Pending'
            WHEN '3' THEN 'Denied'
            WHEN '4' THEN 'Clean'
            WHEN '5' THEN 'Void'
            ELSE ' ' END AS "Claim Status"
    , CASE
            WHEN ap.ap_sts_c = 1 THEN 'Released to Accounts Payable '
            WHEN ap.ap_sts_c = 2 THEN 'Batched for Payment '
            WHEN ap.ap_sts_c = 3 THEN 'EOB Generated'
            ELSE 'Not Released to Accounts Payable ' END AS "AP Status"
    , CAST(ap.adj_time AS char(10)) AS "Status Date"
    , ap.ap_status_ap_dt AS "Sent to AP Date"
    , CASE
            WHEN ap.orig_rev_clm_id IS NULL AND
                  ap.orig_adjst_clm_id IS NULL THEN 'Original Claim'
            WHEN ap.orig_rev_clm_id IS NULL AND
                  ap.adjst_clm_id IS NOT NULL AND
                  ap.orig_adjst_clm_id IS NOT NULL THEN 'Multiple Adj Interim Claim'
            WHEN ap.orig_rev_clm_id IS NOT NULL THEN 'Reversal Claim'
            WHEN ap.orig_rev_clm_id IS NULL AND
                  ap.adjst_clm_id IS NULL AND
                  ap.orig_adjst_clm_id IS NOT NULL THEN 'Adjusted Claim' END AS "Claim State"
    , CASE
            WHEN ap.claim_format_c = 1 THEN 'CMS'
            WHEN ap.claim_format_c = 2 THEN 'UB'
            ELSE 'N/A' END AS "Format"
    , ap.service_start_date AS "SVC Date"
    , ap.service_end_date AS "SVC End Date"
    , ap.date_received AS "Date Received"
    , (current_date - ap.date_received - 1) AS "Claim Age"
    , ap.entry_date AS "Entry Date"
    , ap.admission_type_c AS "Admission type"
    , ap.type_of_bill AS "Type of Bill"
    , ap.tot_billed_amt AS "Total Billed"
    , ap.tot_net_payable AS "Total Payable"
    , ap.tot_allowed_amt AS "Total Allowed"
    , ap.tot_copay AS "Total Copay"
    , ap.tot_deductible AS "Total Deductible"
    , ap.tot_coins AS "Total Coinsurance"
    , ap.tot_pat_total AS "Patient Total"
    , ap.tot_prim_pat_port AS "Primary Portion"
    , ap.tot_prim_ins_amt AS "Primary Insurance"
FROM ap_claim ap
WHERE ap.SERV_DATE BETWEEN '03/12/2017' AND CURRENT_DATE

Open in new window

Is it fast? Does it run out of spool space?

Qry-B This query allows the LEFT JOIN to work correctly
SELECT  
      ap.acct_num_with_ven AS "Invoice"
    , px.pos_type_c
FROM ap_claim ap
LEFT OUTER JOIN ap_claim_px px ON ap.claim_id = px.claim_id
        AND PX.POS_TYPE_C IN ('11', '15', '17', '20', '49', '50', '71', '72')
WHERE ap.SERV_DATE BETWEEN '03/12/2017' AND CURRENT_DATE

Open in new window


Qry-C This query ignores the left join because the where clause overrides any unmatched rows between ap and px
SELECT  
      ap.acct_num_with_ven AS "Invoice"
    , px.pos_type_c
FROM ap_claim ap
LEFT OUTER JOIN ap_claim_px px ON ap.claim_id = px.claim_id
WHERE ap.SERV_DATE BETWEEN '03/12/2017' AND CURRENT_DATE
AND PX.POS_TYPE_C IN ('11', '15', '17', '20', '49', '50', '71', '72')

Open in new window

Is there a difference OF RESULTS between Qry-B and Qry-C? if not do you really need that left join? (use an inner join instead)

{+edit} please note change to previous line "of results" added
{+edit 2} sorry changed qry-b & qry-c may need to refresh (pt alias removed)
REMOVE everything you do not actually use.

There are MANY joined tables - is EVERY one of those actually used?
   (in the select clause or the where clause or required for joining of tables)

There are many instances of double-dashes in your code (e.g. all dx.* columns) but you still join to the subquery dx
Why?

Do you REALLY need DISTINCT here?
                  SELECT /* DISTINCT ???????????? is this needed really */
                        A.CLAIM_ID
                      , F.REF_BILL_CODE + ' - ' + F.DX_NAME AS REF_BILL_CODE
                  FROM AP_CLAIM_DX A
                  INNER JOIN AP_CLAIM B ON A.CLAIM_ID = B.CLAIM_ID	--AND B.STATUS_C = 2
                  INNER JOIN CLARITY_EDG F ON A.DX_ID = F.DX_ID

Open in new window

looks like the join to alias B is redundant now - so remove it
@Nitin.
I disagree completely about the use of subqueries. Not only are they useful, in many cases they are essential to a query and they can benefit performance. Also note this is Teradata not SQL Server. The syntax is similar but there are differences: e.g. "current_date" instead of getdate()
Avatar of Culwatrnca11

ASKER

Hi PortletPaul,

I focused on your first post first "How much repetition do you get from this query? i.e. are the rows distinct?" - returned data extremely fast after execution 802322 rows (00:00:09) then I pulled in 2000 lines. I re ran again and pulled in all 802322 rows at 00:12:01 never ran out of spool space. Not much repetition from what I can see but if there was any it is because there is different information in some of the other fields (ie AP Status, Claim State, Entry Date) .

look at your other suggestions
I went noticed the date range was within last week so I changed it to 1/1/2017 to current and re ran just to make sure no spooling error. Original query was able to still handle it.
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Regarding your existing query: it lives on the knife edge of failure, but it is your query (and reputation) that that you risk.

Your choice to make.
Query B vs C..     Both Quieries return same data, B seems to take longer to run vs C
B actually generated a Ran out of memory error returning row 3406805
PortletPaul,

Thank you for walking me through each step, breaking them down so I can see the results etc.. much apprecated. My goal is to do it right..

In your final query trying to run...  its telling me clm does not exist. I removed the "etc" that you had put in showing me how each section is put together.   Not sure why it cant find slm, the from clause looks right.
another question...  

FROM (  
        SELECT  
              ap.acct_num_with_ven
            , ap.adj_time
            , ap.ap_status_ap_dt
            , ap.service_start_date
            , ap.service_end_date
            , ap.date_received
            , ap.entry_date
            , ap.admission_type_c
            , ap.type_of_bill
            , ap.tot_billed_amt
            , ap.tot_net_payable
            , ap.tot_allowed_amt
            , ap.tot_copay
            , ap.tot_deductible
            , ap.tot_coins
            , ap.tot_pat_total
            , ap.tot_prim_pat_port
            , ap.tot_prim_ins_amt
           
            , clm.internal_id

        FROM ap_claim ap
        INNER JOIN clm_map clm ON ap.claim_id = clm.cid

        WHERE ap.SERV_DATE BETWEEN '03/12/2017' AND CURRENT_DATE
    ) D

this Subquery..        the )D     on the last line..     this is the name of the query, correct?
D     on the last line..     this is the name of the query, correct?

Correct

(d for "derived". Using a subquery in this fashion is also known as a "derived table')

Good luck.
Please remember to do the "fancy stuff" like case expressions/calculations and user friendly column names LAST

Personally I never use column aliases containing spaces, it keeps the code easier to read in my view - but that is entirely up to you.
Here they go Alias crazy which makes it extremely hard to follow someone elses code.

I tried running your last code but get an error message on "clm" says it does not exist.

SELECT
      'SCAL' AS "Region"
    , clm.internal_id AS "Claim ID"
    , ap.acct_num_with_ven AS "Invoice"
    , PATIENT.PAT_MRN_ID AS PAT_MRN
    , CAST(patient.Birth_Date AS DATE)
    --,extract(year from current_date) - extract(year from PATIENT.BIRTH_DATE) -
    --         case when current_date (format 'MMDD') (char(4)) <
    --                 PATIENT.BIRTH_DATE (format 'MMDD')  (char(4)) then 1
    --          else 0 end                        as AGE
    , CASE ap.status_c
            WHEN '1' THEN 'New'
            WHEN '2' THEN 'Pending'
            WHEN '3' THEN 'Denied'
            WHEN '4' THEN 'Clean'
            WHEN '5' THEN 'Void'
            ELSE ' ' END AS "Claim Status"
    , CASE
            WHEN ap.ap_sts_c = 1 THEN 'Released to Accounts Payable '
            WHEN ap.ap_sts_c = 2 THEN 'Batched for Payment '
            WHEN ap.ap_sts_c = 3 THEN 'EOB Generated'
            ELSE 'Not Released to Accounts Payable ' END AS "AP Status"
    , CAST(ap.adj_time AS CHAR(10)) AS "Status Date"
    , ap.ap_status_ap_dt AS "Sent to AP Date"
    , ckr.ap_run_date AS "AP Run Date"
    , chk.name AS "Check Status"
   
 FROM (  
        SELECT  
              ap.acct_num_with_ven
            , ap.adj_time
            , ap.ap_status_ap_dt
            , ap.service_start_date
            , ap.service_end_date
            , ap.date_received
            , ap.entry_date
            , ap.admission_type_c
            , ap.type_of_bill
            , ap.tot_billed_amt
            , ap.tot_net_payable
            , ap.tot_allowed_amt
            , ap.tot_copay
            , ap.tot_deductible
            , ap.tot_coins
            , ap.tot_pat_total
            , ap.tot_prim_pat_port
            , ap.tot_prim_ins_amt
           
            , clm.internal_id

        FROM ap_claim ap
        INNER JOIN clm_map clm ON ap.claim_id = clm.cid

        WHERE ap.SERV_DATE BETWEEN '03/12/2017' AND CURRENT_DATE
    ) D

if I select and run from the select to the end of of current_date it runs in that instance but not if I include FROM (         to the )D
Did I select wrong thing, dont want to close ticket I want to award point as solved..
Thank you for the assistance..   Worked all evening on my query and now able to run for the time frame without getting spool space errors. Your feedback was very helpful.
Really pleased this helped you.

I'm quite sure your queries will be better from now on - because I'm sure you will not want to go through that pain again :=)

Cheers,
Paul