CODEWITHSUNDEEP
db2db2-luw
HHH
HHH

Reputation: 6475

How to store some SQL code in DB2

I have some SQL code, they are basically apply some transformation and do not take any parameter or ...here is a example of this queries

CREATE TABLE RESULT.R AS(
SELECT 
DISTINCT INVOICE, VOLUME, SUBCODE
FROM
(SELECT
    *
FROM
    Table 1
WHERE
    code= '1') AS TB
JOIN
(SELECT
    *
FROM
    Table2
WHERE 
    IND_STATUS_TYPE IS NULL
    AND
    type != 'Error' 
    AND
    STATUSCODE = '153'
)
ON
...

--UPDATES--

This transformation code depends on a couple of other transformations that need to be executed before it and create Table 1 and Table 2. What is the best way to store these codes in the db2 so everyone accessing the database can access it and be able to run that

Upvotes: 0

Views: 90

Answers (2)

mao
mao

Reputation: 12267

Why not use an SQL PL stored procedure to store such logic inside the database? The stored procedure can return a result-set , or not, as you choose (or multiple result sets).

To invoke the logic, just CALL the stored-procedure, and you can grant EXECUTE on that procedure to specific roles or users/groups.

You can also use table-functions to store code.

There are plenty of examples of SQL PL procedures in both the Db2 Knowledge Center and also in the DB2 LUW installation folder in the samples directory tree.

Upvotes: 0

bhamby
bhamby

Reputation: 15450

You want a view. You can read more about a view from the DB2 documentation here.

A view provides a different way of looking at the data in one or more tables; it is a named specification of a result table. The specification is a SELECT statement that is run whenever the view is referenced in an SQL statement. A view has columns and rows just like a table. All views can be used just like tables for data retrieval.

Upvotes: 1

Related Questions