CODEWITHSUNDEEP
sqlpostgresql
CXJ
CXJ

Reputation: 4459

SQL view of union with parameter on each subclause

How can I create a VIEW which produces the same result as the following SQL at bottom. The VIEW needs to be usable by anyone in this manner:

SELECT * FROM my_view WHERE merch_id = 'some_value';

The current SQL uses PHP to substitute the WHERE parameter on both sides of the UNION, but the language does not matter.

Here's the base SQL:

SELECT
    mine.cust_id,
    cust_meta.cust_name,
    mine.bank_id,
    sum(mine.invoices) AS invoices,
    sum(mine.payments) AS payments
FROM (
        SELECT
            cust_id,
            bank_id,
            count(*) AS invoices,
            NULL AS payments
        FROM
            s_bill_meta.t_invoice
        WHERE
            merch_id = '$var'
        GROUP BY
            cust_id,
            bank_id

    UNION

        SELECT
            cust_id,
            bank_id,
            NULL AS invoices,
            count(*) AS payments
        FROM
            s_credit_meta.t_check
        WHERE
            merch_id = '$var'
        GROUP BY
            cust_id,
            bank_id
    ) AS mine
LEFT JOIN
    s_user.t_cust AS cust_meta
        ON mine.cust_id = cust_meta.cust_id
GROUP BY
    mine.cust_id,
    cust_meta.cust_name,
    mine.bank_id
ORDER BY
    mine.cust_id,
    mine.bank_id

Upvotes: 1

Views: 54

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175726

You could use function:

CREATE FUNCTION myview(text) RETURNS TABLE
(
cust_id INT,
...
)
 AS '
    SELECT cust_id, ...
    FROM ...
    WHERE  merch_id = $1
    GROUP BY ...
' LANGUAGE SQL;

And call:

SELECT * FROM myview('some_value') AS t1;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You would include merch_id in the aggregation columns:

SELECT mine.cust_id, cust_meta.cust_name, mine.bank_id, mine.merch_id,
       sum(mine.invoices) AS invoices,
       sum(mine.payments) AS payments
FROM ((SELECT cust_id, bank_id, merch_id, count(*) AS invoices, NULL AS payments
       FROMs_bill_meta.t_invoice
       GROUP BY cust_id, bank_id, merch_id 
      ) UNION ALL
      (SELECT cust_id, bank_id, merch_id, NULL AS invoices, count(*) AS payments
       FROM s_credit_meta.t_check
       GROUP BY cust_id, bank_id, merch_id
      )
    ) mine LEFT JOIN
    s_user.t_cust cust_meta
    ON mine.cust_id = cust_meta.cust_id
GROUP BY mine.cust_id, cust_meta.cust_name, mine.bank_id, mine.merch_id
ORDER BY mine.cust_id, mine.bank_id;

Upvotes: 2

Related Questions