I'm trying to get the Age distribution. and with this query i'm getting the following erro-CodePudding

    drop table if exists sand_scipher_ds_db.public.Age_Dist;
CREATE TEMPORARY TABLE sand_scipher_ds_db.public.Age_Dist as 
    (select
       sum(case when Age_AT_INDEX >= 18 and Age_AT_INDEX <= 34 then 1 else 0 end) as Adults,
       sum(case when Age_AT_INDEX >= 35 and Age_AT_INDEX <= 44 then 1 else 0 end) as Early_Middle_Age,
       sum(case when Age_AT_INDEX >= 45 and Age_AT_INDEX <= 54 then 1 else 0 end) as Middle_Age,
       sum(case when Age_AT_INDEX >= 55 and Age_AT_INDEX <= 64 then 1 else 0 end) as Old,
       sum(case when Age_AT_INDEX >= 65 then 1 else 0 end) as Above_65
     
    from sand_scipher_ds_db.public.Rfour);

SQL compilation error: Can not convert parameter '18' of type [NUMBER(2,0)] into expected type [DATE]

CodePudding user response：

The error message shows that the Age_AT_INDEX column is of the DATE type, i.e. it rather holds the date of birth. You have to calculate the age first and then check it. For this, I propose to create a simple AGE function:

CREATE OR REPLACE FUNCTION PUBLIC.AGE(birth_date DATE)
RETURNS NUMBER
RETURNS NULL ON NULL INPUT
AS
$$
SELECT FLOOR((TO_NUMBER(TO_CHAR(CURRENT_DATE, 'YYYYMMDD')) - TO_NUMBER(TO_CHAR(birth_date, 'YYYYMMDD')))/10000)
$$;

and then substitute it for your query:

 drop table if exists sand_scipher_ds_db.public.Age_Dist;
CREATE TEMPORARY TABLE sand_scipher_ds_db.public.Age_Dist as 
    (select
       sum(case when PUBLIC.AGE(Age_AT_INDEX) >= 18 and PUBLIC.AGE(Age_AT_INDEX) <= 34 then 1 else 0 end) as Adults,
       sum(case when PUBLIC.AGE(Age_AT_INDEX) >= 35 and PUBLIC.AGE(Age_AT_INDEX) <= 44 then 1 else 0 end) as Early_Middle_Age,
       sum(case when PUBLIC.AGE(Age_AT_INDEX) >= 45 and PUBLIC.AGE(Age_AT_INDEX) <= 54 then 1 else 0 end) as Middle_Age,
       sum(case when PUBLIC.AGE(Age_AT_INDEX) >= 55 and PUBLIC.AGE(Age_AT_INDEX) <= 64 then 1 else 0 end) as Old,
       sum(case when PUBLIC.AGE(Age_AT_INDEX) >= 65 then 1 else 0 end) as Above_65
     
    from sand_scipher_ds_db.public.Rfour);