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.
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.