MS-Access Query to PostgreSQL View

Question

I am converting a microsoft access query into a postgresql view. The query has obvious components that I have found reasonable answers to. However, I am still stuck on getting the final result:

SELECT All_Claim_Data.Sec_ID,
    Sum(IIf([Type]="LODE",IIf([Status]="Active",1,0),0)) AS LD_Actv, 
    Sum(IIf([Type]="LODE",IIf([Loc_Date]>#8/31/2017#,IIf([Loc_Date]<#9/1/2018#,1,0),0),0)) AS LD_stkd_17_18, 
    Sum(IIf([Type]="LODE",IIf([Loc_Date]>#8/31/2016#,IIf([Loc_Date]<#9/1/2017#,1,0),0),0)) AS LD_stkd_16_17,
    Sum(IIf([Type]="LODE",IIf([Loc_Date]<#1/1/1910#,IIf(IsNull([Clsd_Date]),1,(IIf([Clsd_Date]>#1/1/1900#,1,0))),0),0)) AS Actv_1900s, 
    Sum(IIf([Type]="LODE",IIf([Loc_Date]<#1/1/1920#,IIf(IsNull([Clsd_Date]),1,(IIf([Clsd_Date]>#1/1/1910#,1,0))),0),0)) AS Actv_1910s,
  FROM All_Claim_Data.Sec_ID,
  GROUP BY All_Claim_Data.Sec_ID,
  HAVING (((Sum(IIf([casetype_txt]="LODE",1,0)))>0));

Realizing I need to use CASE SUM WHEN, here is what I have worked out so far:

    CREATE OR REPLACE VIEW hgeditor.vw_test AS
     SELECT All_Claim_Data.Sec_ID,
     SUM (CASE WHEN(Type='LODE' AND WHEN(Status='Active',1,0),0)) AS LD_Actv, 
     SUM (CASE WHEN(Type='LODE' AND WHEN(Loc_Date>'8/31/2017' AND Loc_Date<'9/1/2018',1,0),0),0)) AS LD_stkd_17_18,
     SUM (CASE WHEN(Type='LODE' AND WHEN(Loc_Date<'1/1/1910' AND (IsNull(Clsd_Date),1,(WHEN([Clsd_Date]>'1/1/1900',1,0))),0),0)) AS Actv_1900s
    FROM All_Claim_Data.Sec_ID,
    GROUP BY All_Claim_Data.Sec_ID,
    HAVING (((SUM(IIf(Type='LODE',1,0)))>0));

The goal is to count the number of instances in which the Sec_ID has the following:

has (Type = LODE and Status = Active) = SUM integer
has (Type = LODE and Loc_Date between 8/31/2017 and 9/1/2018) = SUM Integer

My primary issue is getting a SUM integer to populate in the new columns

`when` is not a function, a case expression is formed like this: `case when ... then when ... then ... else ... end` You seem to be using when like an `IF()` — Paul Maxwell, Dec 06 '18 at 02:17
@Used_By_Already I was using this example as a reference: [link](https://stackoverflow.com/questions/18111280/sumcase-when-resulting-in-multiple-rows-of-the-selection) — dbChic_noob, Dec 06 '18 at 02:26
yes, that reference is ok, but this is not `CASE WHEN(Type='LODE' AND WHEN(Status='Active',1,0),0))` — Paul Maxwell, Dec 06 '18 at 02:33
So, essentially I don't know what my 'THEN' statement would look like..... — dbChic_noob, Dec 06 '18 at 02:34
Read my answer, below. You are actually just returning 1 or 0s in the original, so `THEN 1 ELSE 0` — Paul Maxwell, Dec 06 '18 at 02:35
@Used_By_Already 'SUM (CASE WHEN Type ='LODE' AND Status = 'Active' THEN.....) AS ld_actv,' .......but THEN what? — dbChic_noob, Dec 06 '18 at 02:36
sorry...I am working on it, looking at your comments responding, then messing with it more....sometimes your comment hasn't shown up by then. — dbChic_noob, Dec 06 '18 at 02:38

Paul Maxwell · Accepted Answer · 2018-12-06T02:47:53.793

Case expressions are the equivalent to the Access IIF() functions, but WHEN isn't a function so it isn't used by passing a set of parameters. Think of it as being a tiny where clause instead, it evaluates one or more predicates to determine what to do, and the action taken is established by what you specify after THEN

CREATE OR REPLACE VIEW hgeditor.vw_test AS
SELECT
    All_Claim_Data.Sec_ID
  , SUM( CASE
        WHEN TYPE = 'LODE' AND
            STATUS = 'Active' THEN 1
        ELSE 0
    END ) AS LD_Actv
  , SUM( CASE
        WHEN TYPE = 'LODE' AND
            Loc_Date > to_date('08/31/2017','mm/dd/yyyy') AND
            Loc_Date < to_date('09/1/2018','mm/dd/yyyy')  THEN 1
        ELSE 0
    END ) AS LD_stkd_17_18
  , SUM( CASE
        WHEN TYPE = 'LODE' AND
            Loc_Date < to_date('1/1/1910','mm/dd/yyyy')  AND
            [Clsd_Date] > to_date('1/1/1900','mm/dd/yyyy')  THEN 1
        ELSE 0
    END ) AS Actv_1900s
FROM All_Claim_Data.Sec_ID
GROUP BY
    All_Claim_Data.Sec_ID
HAVING COUNT( CASE
    WHEN Type = 'LODE' THEN 1
END ) > 0
;

By the way, you should NOT be relying on MM/DD/YYYY as dates in Postgres

nb: Aggregate functions ignore NULL, take this example:

+----------+
| id value |
+----------+
| 1  x     |
| 2  NULL  |
| 3  x     |
| 4  NULL  |
| 5  x     |
+----------+

select
       count(*)      c_all
     , count(value)  c_value
from t

+-------+----------+
| c_all |  c_value |
+-------+----------+
|     5 |        3 |
+-------+----------+


select
       sum(case when value IS NOT NULL then 1 else 0 end) sum_case
     , count(case when value IS NOT NULL then 1 end)      count_case
from t

+----------+-------------+
| sum_case |  count_case |
+----------+-------------+
|        3 |           3 |
+----------+-------------+

Note that in Postgres `sum(case when value IS NOT NULL then 1 else 0 end)` can also be written as `count(*) filter (where value is not null)` - I find that more obvious to understand — , Dec 06 '18 at 07:06
It is not proven to be "more obvious" as that is a subjective assessment. To me, using sum() to achieve a count is even less obvious, no matter how you reword the case expression. — Paul Maxwell, Dec 06 '18 at 11:07
Yes, and for that it is "more obvious" in my view. I suspect we agree on rhat. The filter syntax is less obvious I think. But thank you for reminding us of the alternative. — Paul Maxwell, Dec 06 '18 at 12:17

MS-Access Query to PostgreSQL View

1 Answers1