Hi Not really sure if this is the correct location to post this - but I have a sql code that works successfully in Advanced Query Tool (AQT) but when putting this same code into Dataflux into a SQL node - I get the above error.
I am using ; with cte code to create multiple select statements. Below is an example of one of the cte expressions that I am using that I get the error on:
; with cte_d_claim_data_lines (clcl_id, pdpd_id, max_of_cdml_seq_no, clcl_cur_sts, clcl_pay_pr_ind, grgr_ck, clcl_recd_dt, total_patient_paid, total_clm_ded, total_clm_copay)
as
(select *
from
( SELECT DISTINCT
t1.CLCL_ID,
t2.PDPD_ID,
(MAX(t1.CDML_SEQ_NO)) AS MAX_of_CDML_SEQ_NO,
t2.CLCL_CUR_STS,
t2.CLCL_PAY_PR_IND,
t2.GRGR_CK,
t2.CLCL_RECD_DT,
((SUM(t1.CDML_DED_AMT)) + (SUM(t1.CDML_COINS_AMT)) + (SUM(t1.CDML_COPAY_AMT))) AS Total_Patient_Paid,
(SUM(t1.CDML_DED_AMT))as Total_Clm_Ded,
(SUM(t1.CDML_COPAY_AMT))as Total_Clm_Copay
FROM FAcetCOR.CMC_CDML_CL_LINE t1
INNER JOIN FACETCOR.CMC_CLCL_CLAIM t2 ON (t1.CLCL_ID = t2.CLCL_ID)
WHERE t2.CLCL_CUR_STS = '01' AND t2.CLCL_CL_SUB_TYPE = 'H'
GROUP BY t1.CLCL_ID,
t2.PDPD_ID,
t2.CLCL_CUR_STS,
t2.CLCL_PAY_PR_IND,
t2.grgr_ck,
t2.clcl_recd_dt) d_QUERY_FOR_CMC_CDML_CL_LINE_a
WHERE d_QUERY_FOR_CMC_CDML_CL_LINE_a.MAX_of_CDML_SEQ_NO >= 2)
select clcl_id from cte_d_claim_data_lines
Do you use SQL pass-through?
Did you see this?
Cause: The application did not define output variables for data being fetched before issuing a fetch call or invoking a fetch by specifying a non-zero row count in an execute call. Action: Issue OCI define calls for the columns to be fetched.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.