CASE WHEN statement in Access query 1

[gwoman]

Hi ... I have a query that I have run in SQL Nav ... and I put it into an Access DB and I'm getting a syntax(missing opertaor) error with my case when statement ... does Access not allow case when statements in queries?
Here is my query ...

SELECT
a.RSVR_SGMT_ID
,cd.PRIM_PURP_TYPE_CDE
,cd.PRIM_MATL_DESC
,A.EFTV_DTTM
, case when cd.PRIM_PURP_TYPE_CDE ='INJ' then count(distinct A.well_FAC_ID)/
(nvl(min(fpp.STM_INJ_ACTL_SGMT_PTCP_PCT/100),0)+nvl(min(fpp.WTR_INJ_ACTL_SGMT_PTCP_PCT/100),0)) end aloc_inj_cnt

,case when cd.PRIM_PURP_TYPE_CDE ='PROD' then round(count(distinct A.well_FAC_ID)/ sum(fpp.OIL_PROD_ACTL_SGMT_PTCP_PCT/100),2) end alco_prod_cnt

FROM
STGRPTG.STG_CMPL_GNTL_SGMT_MNLY_FACT A
,CMPL_DMN cd
,WLBR_CMPL_GNTL_SGMT_FPP_FACT fpp
WHERE cd.CMPL_DMN_KEY = a.CMPL_DMN_KEY
and fpp.WLBR_CMPL_GNTL_SGMT_FPP_F_KEY=a.WLBR_CMPL_GNTL_SGMT_FPP_F_KEY
and a.CMPL_DMN_KEY =cd.CMPL_DMN_KEY
AND cd.ENGR_STRG_NME In('DELS','SGRA','SGRA-DSD','NGRA','NGRA-DSD','NGRA-DTR','KE','MBEL','64ZN','TC','LHL1','LHLX','LHLW')
and a.PRMTV_LVL_INDC='Y'
and
NVL(A.ALOC_WTR_INJ_VOL_QTY,0) +
NVL(A.ALOC_STM_INJ_VOL_QTY,0) +
NVL(A.ALOC_OIL_PROD_VOL_QTY,0) +
NVL(A.ALOC_WTR_PROD_VOL_QTY,0) +
NVL(A.ALOC_GAS_PROD_VOL_QTY,0) >0
-- and a.RSVR_SGMT_ID=27333
and a.EFTV_DTTM='1-jan-2007'

group by a.RSVR_SGMT_ID
,cd.PRIM_PURP_TYPE_CDE
,cd.PRIM_MATL_DESC
,A.EFTV_DTTM

order by 1,2,3;

Thanks
gwoman

 

[dhookom]

Generally you can use IIf() in place of the CASE WHEN ELSE END sequence.

IIf([condition to evaluate], TrueReturn, FalseReturn)

You are also going to have issues with count(distinct A.well_FAC_ID) as Access/JET doesn't support the syntax.

Also NVL is not native to Access/JET. You can probably use Nz().

Duane
Hook'D on Access
MS Access MVP

 

[gwoman]

Thanks .... would it work better if I put it in a module and used Docmd.RunSQL?

 

[dhookom]

DoCmd.RunSQL won't allow you to use syntax not allowed in the interface.

I generally avoid huge, hardcoded value expressions like "'DELS','SGRA','SGRA-DSD','NGRA','NGRA-DSD','NGRA-DTR','KE','MBEL','64ZN','TC','LHL1','LHLX','LHLW'".

Duane
Hook'D on Access
MS Access MVP

 

[gwoman]

Cool ... thanks!!