dynamic - How to use a parameter column name value in postgresql? -
i have issue need calculate variable on basis of different start , end date can change per client.
currently, have implemented solution below:-
for example:
created static table : ref_table
columns : training_start_date, training_end_date
values : isb_start_dt , isb_end_dt
i.e. 1 row. table created if client wants use dates, values can changed to
values : cs_start_dt , cs_end_dt
or anything.
note: table contain 1 row @ time.
query used:
create table ref_table(training_start_date varchar(30), training_end_date varchar(30); insert ref_table values('isb_start_dt','isb_end_dt')
now, there sql query use table , calculate features using that, there lot of columns , features being used in query giving sample code.
sample code: create or replace view prm_vw select s2.*, case when newvalue < 0 null else newvalue end newvalue1 ( select s1.*, case when training_start_date = 'isb_start_dt' , training_start_date = 'isb_end_dt' isb_start_dt - isb_end_dt when training_start_date = 'cs_start_dt' , training_start_date = 'cs_end_dt' cs_start_dt - cs_end_dt end newvalue ( select avg(date_to_calculate) on (partition country) isb_start_dt, avg(date_to_calculate) on (partition region) cs_start_dt, isb_end_dt, cs_end_dt ref.* prm_cmop cross join ref_table ref )s1 )s2
i have implemented in above way feel not optimized way still hard coding , checking if training_start_date = 'isb_start_dt' can't directly right code as
isb_start_dt - isb_end_dt newvalue training_start_date - training_end_date because training_start_date contains column name isb_start_dt , interested in column value of isb_start_dt. also, if in future client wants use column, have change view script again include in case when lines.
please can me in explaining best way achieve this.
Comments
Post a Comment