public inbox for [email protected]  

help / color / mirror / Atom feed

From: JORGE MALDONADO <[email protected]>
To: [email protected]
Subject: Declaring a constant or variable in a query
Date: Tue, 25 Jul 2023 09:35:45 -0500
Message-ID: <CAAY=A7_x27sHHgWzDRyWmdhMSVoF0CzDH5121f1=VwJ+iv8RXQ@mail.gmail.com> (raw)

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

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Declaring a constant or variable in a query
  In-Reply-To: <CAAY=A7_x27sHHgWzDRyWmdhMSVoF0CzDH5121f1=VwJ+iv8RXQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox