Oracle SQL - Creating Function for Where Clause

Question

Oracle 12c. Somewhat contrived example, but easier to explain... Assume I have a series of tables, each based on a separate product...I have a table for CARS, a different table for BOATS, a different table for PLANES, etc. There are approx 15 of these tables. (Remember, somewhat contrived example...;) Each table has a set of identical columns about the rep who sold this item, and the reps manager, and his manager, etc for 4 levels.

A normal query will be to see all activity for the logged on person, or any activity in that person's hierarchy. What I am doing now is

I would like to be able to use a database function so that I could say something like

select * 
from boats 
where in_hierarchy(PERSON_LOGGED_IN) = true;

It appears that each function wants to query the database for the values it uses, not get passed those values from whatever row is the current row in the query.

Is it possible to write a COMMON function to check all the hierarchy based columns, that I can use on the BOATS table, as well as the CARS, PLANES and other tables?

Sigh... another poorly designed table structure. Any chance of changing it? Theoretically there should just be a single REP column. The employee/manager hierarchy should be stored elsewhere. What happens when a rep gets a new manager? Or there's a staff change in general -- do you have to update these tables with the new employees? — Cᴏʀʏ
– Cᴏʀʏ, Commented May 4, 2016 at 16:48
Remember this is a somewhat contrived example, but as relates to your comment on poor design, part of the reason is that this contains historical data. This application needs to know the hierarchy of the rep as of the time of the sale. If it is externally maintained (aka in a another table) then you need to keep all reps and all hierarchy for multiple years. — user1009073
– user1009073, Commented May 4, 2016 at 16:51
That's better, but it's still unfortunately that there is a table for each product instead of one table with a product type. I think you should be able to write a function that takes 6 parameters (Person_logged_in and five more for the employee levels), and so your query might become select * from boats where in_hiearchy(person_logged_in, rep, mgr, mgr2, mgr3, mgr4) = 1 — Cᴏʀʏ
– Cᴏʀʏ, Commented May 4, 2016 at 16:56
If you only want to simplify your queries, just use ANY operator: SELECT * FROM boat WHERE Person_logged_in = Any( Mgr, mgr2, mgr3, mg4 ) — krokodilko
– krokodilko, Commented May 4, 2016 at 17:53

Joel · Accepted Answer · 2016-05-04 20:34:15Z

Assuming that each of your tables has a similar structure (i.e. every table has columns similar to rep, mgr, mgr2, mgr3, mgr4, etc). You could create a function that checks all of those columns dynamically if they exist and return either a true or false condition.

The following function takes in the value you want to search for - the PERSON_LOGGED_IN (assumed to be an integer key value for the purpose of the answer) and a table schema and name to look into (schema name is included to ensure duplicate tables names in different schemas don't conflict).

We then get a list of all columns in the specified table that match your set of criteria (rep, mgr, mgr2, etc). The benefit of this is that if you have different columns in different tables, you can specify all of them that could match and only those that actually exist in the table will be checked against.

After the matching columns are determined, then we just check the number of rows that match and return either a 1 or 0. I'm returning a numeric value because plain SQL doesn't recognize boolean values like PL/SQL does.

Your final query would become:

SELECT *
FROM BOATS
WHERE IN_HIERARCHY(PERSON_LOGGED_IN, 'SCHEMA.BOATS') = 1

And here's the function:

CREATE OR REPLACE FUNCTION IN_HIERARCHY (
    PERSON_LOGGED_IN IN PLS_INTEGER,
    TABLE_SCHEMA_AND_NAME IN VARCHAR2
) RETURN PLS_INTEGER
IS
    MATCHING_COLUMNS VARCHAR2(200);
    NUMBER_OF_ROWS_FOUND PLS_INTEGER := 0;
BEGIN
    select LISTAGG(COLUMN_NAME, ',') WITHIN GROUP (ORDER BY COLUMN_NAME)
    INTO MATCHING_COLUMNS
    from all_tab_columns
    where OWNER = regexp_substr(TABLE_SCHEMA_AND_NAME,'[^.]+', 1, 1)
    AND TABLE_NAME = regexp_substr(TABLE_SCHEMA_AND_NAME,'[^.]+', 1, 2)
    and COLUMN_NAME in ('REP', 'MGR', 'MGR2', 'MGR3', 'MGR4');

    EXECUTE IMMEDIATE
        'SELECT COUNT(*)
         FROM ' || TABLE_SCHEMA_AND_NAME || '
         WHERE :PERSON_LOGGED_IN IN (' || MATCHING_COLUMNS || ')'
    INTO NUMBER_OF_ROWS_FOUND
    USING PERSON_LOGGED_IN;

    IF NUMBER_OF_ROWS_FOUND > 0 THEN
        RETURN 1;
    ELSE
        RETURN 0;
    END IF;
END;
/

Side note: I don't personally know of a way to get the list of tables in the from clause from within this function based on the statement that's executing it, but if you can somehow find a way to determine that, you could theoretically also eliminate the table name argument from the function as well. However, it's probably not worth it and the actual where clause would likely be less clear to the reader.

Then again, as @kordirko pointed out, if you're really just interested in simplifying the query and making it more readable, then something like this would be a lot simpler:

SELECT *
FROM BOATS
WHERE PERSON_LOGGED_IN IN (REP, MGR, MGR2, MGR3, MGR4)

Collectives™ on Stack Overflow

Oracle SQL - Creating Function for Where Clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related