I’m using the UD03 table to hold a relationship between Parent (upstream) and Child (downstream) jobs.
As seen here, with PHP, I can do any number of loops from any focused job in the chain, going both upstream and downstream, finding any and all linked jobs recursively.
[Problem:] I want to be able to give a BAQ one job number in the chain and have it return all the linked jobs so my users can easily load the whole set into the Job Scheduling Board, Job Entry, etc.
Has anyone had luck with recursive loops in BAQs that could have any number of levels? Is it even possible?
Any other creative ideas to the [Problem] above are welcome. (For example, I can easily get the job numbers into a delimited parameter string, but I haven’t been able to get the BAQ to parse it into a selection.)
As others have said… CTE is the answer… basically, it will take a three query BAQ:
Query 1: CTE query that selects the “anchor” record to start the cycle
Query 2: UNION ALL query that gathers all the records… this query uses query 1 as the anchor and gathers each layer, and will continue to do this until there are no additional layers
Query 3: TOP query that displays the above information.
Now that the CTE and UNIONALL queries must have the same column types (a requirement of CTE). but the TOP can have additional data that is gathered after you have retrieved all the details from the first two queries.