Simplifying a SQL Select Statement in Oracle SQL Developer

Question

The problem I'm facing right now is I'm working with a SQL query that has over 200 lines of code and at the moment in multiple cases I'm just repeating the same sub-query multiple times in this select statement. In the code below I'm using two of the select statements a lot "avail_qty" and "pct_avail" which both having equations in them. Inside the LOW_CNT_&% SELECT statement I use both of the previous two SELECT statements over and over (this is just one example in my code). I would like to be able to make the equation once and assign it to a variable. Is there any way of doing this? I have tried using the WITH clause but for that you need to use a FROM clause, my FROM clause is massive and would look just as ugly if I were to use a WITH clause (plus instead of repeating the SELECT statement now I would be just repeating the FROM statement).

The reason I don't want to type out the whole equation multiple times is for a two reasons the first is it makes the code easier to read. My other reason is because multiple people edit this query and if someone else were to edit the equation in one spot but forgets to edit it in another spot, that could be bad. Also it doesn't feel like good code etiquette to repeat code over and over.

SELECT 
    all_nbr.total_qty,
    NVL (avail_nbr.avail_qty, 0) AS avail_qty,
    100 * TRUNC ( (NVL (avail_nbr.avail_qty, 0) / all_nbr.total_qty), 2) AS pct_avail,

    CASE
        WHEN ((NVL (avail_nbr.avail_qty, 0)) < 35) 
        THEN CASE
                WHEN ((100 * TRUNC ( (NVL (avail_nbr.avail_qty, 0) / all_nbr.total_qty), 2)) < 35)
                THEN (35 - (NVL (avail_nbr.avail_qty, 0)))
                ELSE 0
             END
        ELSE 0
    END AS "LOW_CNT_&%"
FROM
...

Any help would be awesome!!

The equation doesn't make much sense on its own without a from clause. It is possible to use substitution variables for bits of later code but it's not terribly intuitive. But I'm not sure I quite understand exactly which bits you are repeating, or where, or why. From just what you've shown then a CTE or inline view looks like the obvious solution, and it's not clear why that doesn't work for you. Why would you need to repeat the from - maybe you weren't using the CTE properly? — Alex Poole
– Alex Poole, Commented Oct 26, 2018 at 17:08
Can't you use a CTE? You can have multiple CTEs, each one referring to the previous ones. Can you elaborate on what's the problem with a CTE? — The Impaler
– The Impaler, Commented Oct 26, 2018 at 17:11
Perhaps what you want is a reusable function? You can declare these as part of the query using with function f as... from Oracle 12.1 onwards. — William Robertson
– William Robertson, Commented Oct 28, 2018 at 11:38

The Impaler · Accepted Answer · 2018-10-26 17:13:29Z

1

If the subquery is exactly the same one, you can pre-compute it as a Common Table Expression (CTE). For example:

with
cte1 as (
  select ... -- long, tedious, repetitive SELECT here
),
cte2 as (
  select ... -- you can reference/use cte1 here
)
select ...
  from cte1 -- you can use cte1 here, multiple times if you want
  join cte2 -- you can also reference/use cte2 here, also multiple times
  join ... -- all other joins

cte1 (you can use any name) is a precomputed table expression that can be used multiple times. You can also have multiple CTEs, each one with different names; also each CTE can reference previous ones.

edited Oct 26, 2018 at 17:13

answered Oct 26, 2018 at 17:08

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex Poole · Accepted Answer · 2018-10-26 17:20:22Z

I have tried using the WITH clause but for that you need to use a FROM clause, my FROM clause is massive and would look just as ugly if I were to use a WITH clause (plus instead of repeating the SELECT statement now I would be just repeating the FROM statement).

You shouldn't need to repeat the from clause. You move all of the query, including that clause, into the CTE; you just pull out the bits that rely on earlier calculations into the main query, which avoids the code repetition.

The structure would be something like:

WITH cte AS (
    SELECT 
        all_nbr.total_qty,
        NVL (avail_nbr.avail_qty, 0) AS avail_qty,
        100 * TRUNC ( (NVL (avail_nbr.avail_qty, 0) / all_nbr.total_qty), 2) AS pct_avail,
    FROM
    ...
)
SELECT
    cte.total_qty,
    cte.avail_qty,
    cte.pct_avail,
    CASE
        WHEN cte.avail_qty, 0 < 35 
        THEN CASE
                WHEN cte.total_qty < 35
                THEN 35 - cte.avail_qty
                ELSE 0
             END
        ELSE 0
    END AS "LOW_CNT_&%"
FROM
    cte;

Your main query only need to refer to the CTE (again, based on what you've shown), and can (only) refer to the prjoection of the CTE, incuding the calculated columns. It can't see the underlying tables, but shouldn't need to.

Or with an inline view instead, the principal is the same:

SELECT
    total_qty,
    avail_qty,
    pct_avail,
    CASE
        WHEN avail_qty < 35
        THEN CASE
                WHEN total_qty < 35
                THEN 35 - avail_qty
                ELSE 0
             END
        ELSE 0
    END AS "LOW_CNT_&%"
FROM
(
    SELECT 
        all_nbr.total_qty,
        NVL (avail_nbr.avail_qty, 0) AS avail_qty,
        100 * TRUNC ( (NVL (avail_nbr.avail_qty, 0) / all_nbr.total_qty), 2) AS pct_avail,
    FROM
    ...
);

Collectives™ on Stack Overflow

Simplifying a SQL Select Statement in Oracle SQL Developer

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related