Hi,
I am using DataGrip (a tool very much like pgAdmin) to run a query with several UNION clauses as follows:
SELECT fld1 as alias1, fld2 as alias2, fld3 as alias3, fld4 as alias4, fld5 as alias5 FROM table1 WHERE condition1
UNION
SELECT fld1 as alias1, fld2 as alias2, fld3 as alias3, fld4 as alias4, fld5 as alias5 FROM table2 WHERE condition2
UNION
SELECT fld1 as alias1, fld2 as alias2, fld3 as alias3, fld4 as alias4, fld5 as alias5 FROM table3 WHERE condition3
UNION
SELECT fld1 as alias1, fld2 as alias2, fld3 as alias3, fld4 as alias4, fld5 as alias5 FROM table4 WHERE condition4
I need fld5 to always be a constant in every SELECT that I would like to read interactively or to set somewhere in the query (as a variable or constant for example). I tried to use a parameter for such field like this:
SELECT fld1 as alias1, fld2 as alias2, fld3 as alias3, fld4 as alias4, :fld5 as alias5 FROM table1 WHERE condition1
UNION
SELECT fld1 as alias1, fld2 as alias2, fld3 as alias3, fld4 as alias4, :fld5 as alias5 FROM table2 WHERE condition2
UNION
SELECT fld1 as alias1, fld2 as alias2, fld3 as alias3, fld4 as alias4, :fld5 as alias5 FROM table3 WHERE condition3
UNION
SELECT fld1 as alias1, fld2 as alias2, fld3 as alias3, fld4 as alias4, :fld5 as alias5 FROM table4 WHERE condition4
My issue using this approach is that DataGrip interactively asks for the input value 4 times, which means that the parameter value is asked the number of times it is specified no matter if the parameter name is exactly the same. In this example, the query has 3 UNION clauses that involve 4 SELECT statements, however, there can be more.
I have read that it is possible to use a DECLARE statement but it seems to me that this solution forces me to use a FUNCTION and I would like to avoid it. Another solution that I saw is to declare a session level variable using SET and then use such variable in the query.
I will very much appreciate your feedback on what would be my best choice. I am open to hearing new approaches.
Respectfully,
Jorge Maldonado