BookmarkSubscribeRSS Feed
beamer108
Quartz | Level 8

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

2 REPLIES 2
ChrisNZ
Tourmaline | Level 20

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.

 

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 12029 views
  • 0 likes
  • 2 in conversation