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.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.