sqloracle-databasepentaho-report-designer

Case Expressions in Oracle SQL (Pentaho Report Designer)


I need to pull ages of clients and have results broken down into categories (18-21, 22-35, 36-50, etc).

Using Pentaho Report Designer which is Oracle based.

I can pull a count of specific ages, but want the count to reflect the age ranges, not a single age. Attempting to complete this through a case statement but continually get errors.

Minimal SQL exposure, new to Pentaho/Oracle, new to this website.

--SQL for Age and Count.

SELECT COUNT(*), "CLIENT_TABLE"."AGE"
FROM "CLIENT_TABLE"
GROUP BY "CLIENT_TABLE"."AGE"
ORDER BY "CLIENT_TABLE"."AGE"

--This is my CASE expression.

CASE
    WHEN "CLIENT_TABLE"."AGE" < 18 THEN 'Under 18'
    WHEN "CLIENT_TABLE"."AGE" > 18 AND <= 21 THEN '19 - 21'
    WHEN "CLIENT_TABLE"."AGE" > 21 AND <= 35 THEN '22 - 35'
END AS Age

I have placed the CASE expression in both the SELECT and WHERE clauses, but continually get these two error messages... "FROM keyword not found where expects" & "Missing expression".

Updated code produces error, "not a Group By Expression."

SELECT COUNT(*),
      (CASE
        WHEN "CLIENT_TABLE"."AGE" < 18 THEN 'Under 18'
        WHEN "CLIENT_TABLE"."AGE" <= 21 THEN '18 - 21'
        WHEN "CLIENT_TABLE"."AGE" <= 35 THEN '22 - 35'
        WHEN "CLIENT_TABLE"."AGE" <= 50 THEN '36 - 50'
        WHEN "CLIENT_TABLE"."AGE" <= 64 THEN '51 - 64'
        WHEN "CLIENT_TABLE"."AGE" >= 65 THEN '65+'
       END) AS AgeRange
FROM "CLIENT_TABLE"
GROUP BY (CASE
        WHEN "CLIENT_TABLE"."AGE" < 18 THEN 'Under 18'
        WHEN "CLIENT_TABLE"."AGE" <= 21 THEN '18 - 21'
        WHEN "CLIENT_TABLE"."AGE" <= 35 THEN '22 - 35'
        WHEN "CLIENT_TABLE"."AGE" <= 50 THEN '36 - 50'
        WHEN "CLIENT_TABLE"."AGE" <= 64 THEN '51 - 64'
        WHEN "CLIENT_TABLE"."AGE" >= 65 THEN '65+'
       END)
ORDER BY "CLIENT_TABLE"."AGE"

Solution

  • Let's simplify this:

    SELECT AGERANGE, COUNT(1) AS CNT FROM
    SELECT (CASE
            WHEN "CLIENT_TABLE"."AGE" < 18 THEN 'Under 18'
            WHEN "CLIENT_TABLE"."AGE" <= 21 THEN '18 - 21'
            WHEN "CLIENT_TABLE"."AGE" <= 35 THEN '22 - 35'
            WHEN "CLIENT_TABLE"."AGE" <= 50 THEN '36 - 50'
            WHEN "CLIENT_TABLE"."AGE" <= 64 THEN '51 - 64'
            WHEN "CLIENT_TABLE"."AGE" >= 65 THEN '65+'
           END) AS AgeRange
    FROM "CLIENT_TABLE")
    GROUP BY AGERANGE
    ORDER BY CASE AGERANGE
    WHEN 'Under 18' THEN 1 
    WHEN '18 - 21' THEN 2
    WHEN '22 - 35' THEN 3
    WHEN '36 - 50' THEN 4
    WHEN '51 - 64' THEN 5
    WHEN '65+' THEN 6 
    ELSE 7 END;
    

    Cheers!!