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

Popular posts from this blog

php - Permission denied. Laravel linux server -

google bigquery - Delta between query execution time and Java query call to finish -

python - Pandas two dataframes multiplication? -