Trying something new and not able to find what the error is.
Variables are in the SQL statment but it errors out when trying to open the cursor
Code:
#define TRM4_SQL \
"WITH \
TMP_CLM_TOTALS as \
(SELECT D.SAK_DRUG, COUNT(x.sak_claim) as clm_count \
FROM \
t_drug d \
,aim01.t_pd_pharm_dtl x \
,aim01.t_pd_pharm_hdr q \
,aim01.T_PHRM_HDR_KEYS w \
WHERE \
NOT EXISTS( select null from tmp_cmstape505 t \
where t.cde_ndc = d.cde_ndc ) \
AND x.sak_drug = d.sak_drug \
AND q.sak_claim = x.sak_claim \
AND q.dte_dispense between :qrtDtebegin AND :qrtDtelast \
AND w.sak_claim = x.sak_claim \
AND w.IND_ENCOUNTER IN(' ','N') \
GROUP BY d.sak_drug \
) \
SELECT \
d.cde_thera_cls_spec, \
d.dsc_50, \
case when exists (select 'x' \
from t_drug_specl_ind \
where ind_spec = 'DME' \
and id_group = '2' \
and sak_drug = a.sak_drug \
and EFF_DATE >= :qrtDtebegin \
and END_DATE <= :qrtDtelast) then 'Y' \
else 'N' \
end ind_dme, \
min(t.clm_count), \
a.cde_ndc, \
MIN(a.dsc_ndc) AS dsc_ndc , \
'0' AS cms_begin , \
TO_CHAR(MAX(a.dte_term_hcfa)) AS cms_end , \
TO_CHAR(MAX(a.dte_drug_obsolete)) AS cms_obsolete , \
TO_CHAR(MIN(b.dte_effective)) AS dte_effective , \
TO_CHAR(MAX(b.dte_end)) AS dte_end \
FROM t_drug a, \
t_covered_benefit b, \
t_pub_hlth_pgm p, \
t_generic_drug c, \
t_therapeutic d, \
tmp_clm_totals t \
WHERE a.sak_drug = b.sak_benefit \
AND b.cde_benefit_type = 'N' \
AND :dteRun BETWEEN b.dte_effective AND b.dte_end \
AND (a.DTE_TERM_HCFA IS NULL OR a.DTE_TERM_HCFA = 0 \
OR :dteRun < a.DTE_TERM_HCFA) \
AND (a.DTE_TERM_CMS IS NULL OR a.DTE_TERM_CMS = 0 \
OR :dteRun < a.DTE_TERM_CMS) \
AND (a.dte_drug_obsolete IS NULL OR a.dte_drug_obsolete = 0 OR \
to_date(:dteRun, 'YYYYMMDD') < add_months(to_date(DECODE( \
a.dte_drug_obsolete,0, 19800101 \
,a.dte_drug_obsolete), 'YYYYMMDD'),12)) \
AND p.SAK_PUB_HLTH = b.SAK_PUB_HLTH \
AND p.CDE_PGM_HEALTH IN ('HCB01','HCB02','HCB03','HCB04','HCB05' \
,'HCB06','HCB07' ,'HCB08','HCB10','HCB11','HCB13','HCB14','HCB15' ) \
AND NOT EXISTS (select 0 \
from tmp_cmstape505 t where t.cde_ndc = a.cde_ndc) \
AND c.sak_drug_gen = a.sak_drug_gen \
AND d.sak_drug_thera_cls = c.sak_drug_thera_cls \
AND t.sak_drug (+) = a.sak_drug \
GROUP BY d.cde_thera_cls_spec, d.dsc_50, a.sak_drug, a.cde_ndc \
Order By d.cde_thera_cls_spec "
Code:
sprintf( sqlStmt, TRM4_SQL);
EXEC SQL PREPARE SQL_STMT from :sqlStmt;
EXEC SQL DECLARE TRM4_CUR CURSOR FOR SQL_STMT;
EXEC SQL OPEN TRM4_CUR USING :qrtDtebegin, :qrtDtelast, :dteRun;
Any Help would be greatly appreciated.
Thanks