user6808217 user6808217 - 4 years ago 99
SQL Question

Concatenate function result with sql select

I've created table rep where I store strings which are repeated in a few of my queries, example: ('birthday_month', 'extract(month from my_table)') but there are a few much longer with CASE, and function which returns value for input key. Now I would like to concatenate sql select using records from this table.

select name, (select get_rep('birthday_month')) as timestep
from my_table
group by timestep

(I've just made simple example up, in real it would be more complex). But unfortunatelly it returns

timestep count
extract(month from my_table) 12345

I have really a lot of common parts in my selects, so I would like to gather it in some variables..

Answer Source

Just create a view with all the columns you want:

create view v_my_table
     select t.*,
            extract(month from date_of_birth) as birthday_month
     from my_table t;

You can add as many more columns as you like.

Then use the columns that you want in a query:

select birthday_month, count(*)
from v_my_table
group by birthda_month;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download