How can I use having clause outside the subquery?

Question

List item

I attached my query below and I need to filter the data based on below condition. The output is display only when the parameter is passed from the respective table.

 SELECT 
    (SELECT 'Static Field') AS account_type,
    (SELECT 
            COALESCE(SUM(ln.amount), 0)
        FROM
             ln
        WHERE
                AND ln.brch_id = $P{PARA_ID}
                AND ln.date < $P{PARAM_FROM_DATE}
                AND $P{PARA_Account_Type} = 'Static Field') AS debit_amount,
    (SELECT 
            COALESCE(SUM(pd.paid_amount), 0)
        FROM
         pd,
             ln
        WHERE
            pd.loan_id = ln.id AND ln.brch_id = $P{PARA_ID}
                AND pd.paid_date < $P{PARAM_FROM_DATE}
                AND $P{PARA_Account_Type} = 'Static Field') AS credit_amount
    having 
    account_type = 'Static Field'

Output should be: I expect the sum of the values based on above different condition only when the respective parameter is passed if it does not passed the respective parameter it will not display the row

Because that `$P{...}` syntax it is clear that a programming laugauge is involved aswell not 100% sure which programming laugauge , i assume that syntax will not protect against SQL injections as it seams to be string interpolation syntax.. — Raymond Nijland, Sep 07 '19 at 13:45
... i assume it is PHP syntax as PHP supports curly braces syntax to string interpolate like that if that is the case [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) .. if it is not PHP research the manual/internet for methods to use prepared statements and parameterized queries in your programming laugauge .. — Raymond Nijland, Sep 07 '19 at 13:45
Also see [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) .. We need example data and expected results to give more accurate answers.. — Raymond Nijland, Sep 07 '19 at 13:47
Please in code questions give a [mre]--cut & paste & runnable code; example input (as tabular initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) PS You have a syntax error. Read the grammar & manual. Show that constituent subexpressions are OK. Ask re *that error*. Ask re your overall goal later in a new post. — philipxy, Sep 07 '19 at 17:10
This is extremely unclear. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. To describe a result: Say enough that someone could go away & come back with a solution. When giving a relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. — philipxy, Sep 07 '19 at 17:14

score 0 · Accepted Answer · answered Sep 07 '19 at 21:41

Your query is interesting because it is a SELECT without a FROM and that's why you cannot use a WHERE or a HAVING to filter.

When your query is a SELECT without any FROM it always returns exactly 1 record.

A quick fix is to just add a simple FROM with 1 row as follows to your query which then enables you to filter with a HAVING.

FROM  ( select 1 as justarow ) r
HAVING account_type = 'Static Field'

How can I use having clause outside the subquery?

1 Answers1