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

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 12089 views
  • 0 likes
  • 2 in conversation