SQR CASE语句在使用局部变量时出现错误

Question

对于sqr中的sql语句的select部分，我需要在case语句中使用日期变量，但一直收到错误。当我使用getdate()函数进行比较时，它工作得很好，但不适用于我的变量。请帮帮忙。代码也包含在内。

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

Answer 1

谢谢Cardmagik。这对我很有效。工作的代码如下所示。我逐个替换了整个select列部分。

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