1

In the given Scenario we have several lots of ingrediances we mix to indermediate product lots. These intermediate product lots are used for further intermediate products or for finished product lots. There are an different amount of indermediate steps between the ingrediances and the finished product.

To find all lots that are used in a given finished product lot I'd like to write an Oracle SQL like:

SELECT 
    something
FROM (
    SELECT 
        something_else
    FROM DB_TABLE
    START WITH DB_TABLE.OUTPUT = START_VALUE
    CONNECT BY NOCYCLE PRIOR DB_TABLE.OUTPUT = DB_TABLE.INPUT 
    ) QRY_NAME  
WHERE START_VALUE = '1400929801'

I know, that this example above doesn't work but it shows what im looking for.
So, I need to define the start with value in the where clause of the outer query - but don't have clue how.
The query will be enbedded in a software, where users are only able to define the where clause.

Cabation
  • 11
  • 4
  • 1
    Why do you need the nested query? – Aleksej Mar 10 '16 at 16:13
  • Maybe you should explain *why* you need to define the start value in the outer query. Sounds like an [XY problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem/66378#66378) at the moment. – Alex Poole Mar 10 '16 at 16:20
  • There are several joins at the outer query. I don't really need it nested but to reduce the cardinality. – Cabation Mar 10 '16 at 16:21
  • 2
    Is the entire outer query just generating the start value(s) then? If so it could potentially be a subquery (`start with input in (select start_value from ...)` or CTE, but depends what else you are doing. – Alex Poole Mar 10 '16 at 16:29
  • Other option, put the user-defined where into an initial WITH userdata as (SELECT something FROM ....) then the inner query can START WITH input in (select something from userdata). – Michael Broughton Mar 10 '16 at 20:09

1 Answers1

0

Assuming that you need to have the starting value in the external query for some reason, you can try the following:

SELECT 
    something
FROM (
    SELECT 
        something_else, connect_by_root(DB_TABLE.INPUT) as root
    FROM DB_TABLE
    CONNECT BY NOCYCLE PRIOR DB_TABLE.INPUT =  DB_TABLE.OUTPUT 
    ) QRY_NAME  
WHERE root = '1400929801'

It simply executes your query, adding a column to keep the root value, so that you can use it as a filter in the external query.

However, if you simply need the external query to limit the number of results, you can have the starting value in the inner query, and then use the outer one to only limit the result set with some criteria; for example:

SELECT 
        something
    FROM (
        SELECT 
            something_else
        FROM DB_TABLE
        START WITH DB_TABLE.INPUT = '1400929801'
        CONNECT BY NOCYCLE PRIOR DB_TABLE.INPUT =  DB_TABLE.OUTPUT 
        ) QRY_NAME  
    WHERE /* some condition */

Depending on your needs, maybe you can even put your limiting criteria in the inner query, without the outer one, but it depends on how you want to limit the result set

Aleksej
  • 22,443
  • 5
  • 33
  • 38
  • The first of your code fits what I need. But the inner query is about millions of records I've filtered by the start with clause. So with your first code I loose the filter and a lot of performance. Any suggestions about that? – Cabation Mar 10 '16 at 16:47
  • With your second code there is still the same challenge: How to get the "start with " value to the where clause? – Cabation Mar 10 '16 at 16:53
  • If you have the `START WITH` clause, your results are correctly limited, with no performance issue, so the question still is: why do you need to use an external query to limit the result set? – Aleksej Mar 11 '16 at 08:40
  • Sorry for not being clear. Even if I don't have a external query I don't know a way to define the `START WITH` value with the where clause. In the software the complete SQL is capsuled exapt of choosing columns of the outer query to define filter criteria. These is transformed to an additional where clause at the end of the SQL. By the way thats why the "With clause" doesn't help either. – Cabation Mar 11 '16 at 08:54