1

I need to use a date variable in a case statement for the select section of a sql statement in sqr and keep getting an error. It works fine when i use the getdate() function to compare but doesn't work with my variable. help please. code is included.

BEGIN-SELECT

DBO.GET_XLAT_DESC(A.TPD_PARISH,'TPD_PARISH') "DESCR" &LABEL_DESCR (+2,4)
COUNT(1) "CNT" &ENT_CNT (,23) ! total entities matching base criteria

COUNT(CASE WHEN B.ATTACH_TYPE_CD = 'PHC' AND B.EXPIRATION_DATE >= getdate() 
THEN 1 END) "HEALTH_PST" &H_PAST (,34)

COUNT(CASE WHEN B.ATTACH_TYPE_CD = 'PHC' AND B.EXPIRATION_DATE >= $curr_dte 
THEN 1 END) "HEALTH_CUR" &H_CURR (,44)

COUNT(CASE WHEN B.ATTACH_TYPE_CD = 'PHC' AND B.EXPIRATION_DATE >= 
DATEADD($START_DATE,'MONTH',-1) THEN 1 END) "HEALTH_FTR" &H_FUTR

    let #region_total = #region_total + #ent_total
    PRINT #ent_total (, 235) EDIT 999,999 BOLD

FROM PS_TPD_ENTITY A LEFT JOIN PS_TPD_ENT_ATTACH B ON A.TPD_ENTITY = 
B.TPD_ENTITY
WHERE $TPD_REPORT_TYPE = 'P' 
AND (RTRIM(LTRIM(UPPER(A.TPD_ENT_STATUS))) = $TPD_ENT_STATUS OR 
$TPD_ENT_STATUS ='')
AND (RTRIM(LTRIM(UPPER(A.TPD_ENTITY_TYPE))) = $TPD_ENTITY_TYPE OR 
$TPD_ENTITY_TYPE ='') 
AND (RTRIM(LTRIM(UPPER(A.REGION))) = $REGION OR $REGION ='')
AND (RTRIM(LTRIM(UPPER(A.TPD_PARISH))) = $TPD_PARISH OR $TPD_PARISH ='')
GROUP BY A.TPD_PARISH
order BY DESCR
Kenatu Bruce
  • 21
  • 2
  • What is the value of $curr_dte? Also what error are you getting? Finally, you may have to pass it as a dynamic variable with [ ] square brackets around it. Try that – cardmagik Feb 26 '18 at 19:59
  • 1
    Thank you. This worked for me. the code that worked is below I substituted the entire select columns one by one. let $curr_dte = edit($START_DATE,'yyyymmdd') let #ent_cnt = 0 let $h_past = 'COUNT(CASE WHEN B.ATTACH_TYPE_CD = ''PHC'' AND' let $h_past = $h_past || ' B.EXPIRATION_DATE >= DATEADD(MONTH,-1,'''|| $CURR_DTE let $h_past = $h_past || ''') THEN 1 END)' let $h_curr = 'COUNT(CASE WHEN B.ATTACH_TYPE_CD = ''PHC'' AND' let $h_curr = $h_curr || ' B.EXPIRATION_DATE >= '''|| $CURR_DTE let $h_curr = $h_curr || ''' THEN 1 END)' – Kenatu Bruce Feb 27 '18 at 20:25
  • Please stop TYPING IN UPPERCASE. IT READS LIKE YOU'RE SHOUTING. Thanks. – j08691 Feb 27 '18 at 20:58

1 Answers1

1

Thank you Cardmagik. This worked for me. The code that worked is below. I substituted the entire select column section one by one. 

let $curr_dte = edit($START_DATE,'yyyymmdd') 
let #ent_cnt = 0 
let $h_past = 
'COUNT(CASE WHEN B.ATTACH_TYPE_CD = ''PHC'' AND'

let $h_past = $h_past || ' B.EXPIRATION_DATE >= DATEADD(MONTH,-1,'''|| 
$CURR_DTE 

let $h_past = $h_past || ''') THEN 1 END)' 

let $h_curr = 'COUNT(CASE WHEN B.ATTACH_TYPE_CD = ''PHC'' AND' let $h_curr = 
$h_curr || ' B.EXPIRATION_DATE >= '''|| $CURR_DTE 

let $h_curr = $h_curr || 
''' THEN 1 END)'
let $get_parish_count = 'DBO.GET_ENTITY_COUNT(''P'','''||$TPD_ENT_STATUS
let $get_parish_count = $get_parish_count || ''' , '''||$TPD_ENTITY_TYPE
let $get_parish_count = $get_parish_count || ''' , A.TPD_PARISH, NULL)'

begin-procedure parish_rpt

BEGIN-SELECT

DBO.GET_XLAT_DESC(A.TPD_PARISH,'TPD_PARISH') "DESCR" &LABEL_DESCR (+2,4)

A.TPD_PARISH &location_code

[$get_parish_count] &ENT_CNT=NUMBER (,24)

[$h_past] &H_PAST=NUMBER (,34)

[$h_curr] &H_CURR=NUMBER (,44)

FROM PS_TPD_ENTITY A LEFT JOIN PS_TPD_ENT_ATTACH B ON A.TPD_ENTITY = 
B.TPD_ENTITY
WHERE $TPD_REPORT_TYPE = 'C' 
AND (RTRIM(LTRIM(UPPER(A.TPD_ENT_STATUS))) = $TPD_ENT_STATUS OR 
$TPD_ENT_STATUS ='')
AND (RTRIM(LTRIM(UPPER(A.TPD_ENTITY_TYPE))) = $TPD_ENTITY_TYPE OR 
$TPD_ENTITY_TYPE ='') 
AND (RTRIM(LTRIM(UPPER(A.REGION))) = $REGION OR $REGION ='')
AND (RTRIM(LTRIM(UPPER(A.TPD_PARISH))) = $TPD_PARISH OR $TPD_PARISH ='')

GROUP BY A.TPD_PARISH

ORDER BY DESCR

END-SELECT

end-procedure
Kenatu Bruce
  • 21
  • 2