ORA-24374:define not done before fetch or execute and fetch

beamer108 — Tue, 19 Feb 2019 14:03:06 GMT

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

Re: ORA-24374:define not done before fetch or execute and fetch

ChrisNZ — Tue, 19 Feb 2019 21:35:45 GMT

Do you use SQL pass-through?

Re: ORA-24374:define not done before fetch or execute and fetch

ChrisNZ — Tue, 19 Feb 2019 21:37:45 GMT

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.

topic ORA-24374:define not done before fetch or execute and fetch in SAS Programming

ORA-24374:define not done before fetch or execute and fetch

Re: ORA-24374:define not done before fetch or execute and fetch

Re: ORA-24374:define not done before fetch or execute and fetch