Let's take an everyday query with a CASE clause so I can show you the problem, see how we'd have to change the query when a new Certification comes out:
select count(*) as "certCount", CASE when c.id = 1 then 'AWS Cloud Practitioner' when c.id = 2 then 'AWS Alexa Skill Builder' when c.id = 3 then 'AWS Solution Architect Associate' when c.id = 4 then 'AWS Developer Associate' when c.id = 5 then 'AWS SysOps Associate' when c.id = 6 then 'AWS Solution Architect Professional' when c.id = 7 then 'AWS DevOps Professional' when c.id = 8 then 'AWS Security' when c.id = 9 then 'AWS Networking' when c.id = 10 then 'AWS Big Data' when c.id = 11 then 'AWS Machine Learning' ELSE 'N/A' END AS name from certification c inner join qualification q on c.id = q.certificationid group by q.certificationid, c.id
Sure we could generate the SQL and execute it in an Ad-Hoc fashion. This is what I've done in the past, though I'm wondering is there another way, like some way of joining that avoids the CASE WHENs?
Schema and sample data below, its in PostGres but I'd also be interested in Dynamic Case statements for Oracle, SQLServer, etc:
CREATE TABLE certification ( id serial NOT NULL, officialcertname text NOT NULL, "name" text NOT NULL, vendorid int4 NOT NULL DEFAULT 1, isdeleted bool NOT NULL DEFAULT false, CONSTRAINT certification_pkey PRIMARY KEY (id) ); CREATE TABLE qualification ( id serial NOT NULL, employeeid int4 NOT NULL, certificationid int4 NOT NULL, date_attained timestamptz NULL, date_expiry timestamptz NULL, certurl text NULL, verified bool NOT NULL DEFAULT false, created_by text NOT NULL, created_date timestamptz NOT NULL, modified_by text NULL, modified_date timestamptz NULL, CONSTRAINT qualification_pkey PRIMARY KEY (id) ); INSERT INTO certification (officialcertname,"name",vendorid,isdeleted) VALUES ('AWS Certified Cloud Practitioner (CLF)','AWS Cloud Practitioner',1,false) ,('AWS Certified Alexa Skill Builder','AWS Alexa Skill Builder',1,false) ,('AWS Certified Solutions Architect - Associate (SAA)','AWS Solution Architect Associate',1,false) ,('AWS Certified Developer - Associate (DVA)','AWS Developer Associate',1,false) ,('AWS Certified SysOps Administrator - Associate (SOA)','AWS SysOps Associate',1,false) ,('AWS Certified Solutions Architect - Professional (SAP)','AWS Solution Architect Professional',1,false) ,('AWS Certified DevOps Engineer - Professional (DOP)','AWS DevOps Professional',1,false) ,('AWS Certified Security - Specialty (SCS)','AWS Security',1,false) ,('AWS Certified Networking - Specialty (SNS)','AWS Networking',1,false) ,('AWS Certified Big Data - Specialty','AWS Big Data',1,false) ;