nested joins check

[jobo]

I guess You are dealing with design data maybe from Solid Edge based on Oracle DB mixed with 2 other Sources, measuring and production database?
However, it doesn’t look like there is a fine grained consolidation area using well designed tables, indices or API, but more a „pool“ of import / export / ETL sources.
So maybe a lot of fragments, poorly indexed ....

Tweaking (of existing, working select statements or aproach to newly create them)
Imagine a vehicle, vehicle modules, module parts down to single components and the other way round from components up to a vehicle (type).
Coming from a big denormalized table (as an abstract idea) containing all that information above one can assume, that a good approach would be using few criteria with big impact to minimize query results. This is the way, the database tries to optimize query paths.
With a few million records given in such a table, the selection of a specific vehicle type would entail a very large limitation of the data, same thing happens again filtering a specific module or module part.

This approach seems to be a good deal and also offers a “natural“ or “human” attitude looking to the final component list.

Now in theory the nice thing is, that You don‘t have to take care about which filter comes first. The database should know best. However, such a system naturally requires fixed points for such decisions, like key fields, indexes on key fields, quantity statistics for the individual tables, and so on.
When assembling a select statement, adding join after join, You will notice the runtime behaviour of each additional join. You're on a roll when an additional join speeds up the query (because it significantly limits the amount of data)! At least the next join shouldn't be a big worsening.
Avoid aggregations (and sorts) on large base quantities, which have to be filtered again later anyway. Avoid (usual and old fashioned) SQL workarounds for features that this DB engine can handle better (CTE, recursive Queries, Window Functions, Pivot, Unpivot, ..)
Avoid nasty and error prone preprocessing of import data (ETL), just do an easy and roughly filtered data import, followed by comfortable, exact and fast filtering in SQL.
To be clear, I think the most basic point is not the combination of joins, but parameterization (filtering) of data.

When using (or constructing) views I always do full selects, querying nearly all columns, building useful, reusable data layers. A view as predefined Selectstatemen doesn’t need to save resources, neither horizontal nor vertical, it saves thinking. For example building a view on some genealogy tables showing all fathers with their sons I just put the join logic there, I don’t do filtering on age or century. It’s perfectly okay doing this when finally using the view. And I don’t remove columns in the view to make it faster or something, this happens in select statement like needed.
To emphasize this idea of using views I admit, that some of the resulting views are incredible slow when getting selected unfiltered. This is not intended, but it’s no harm as long as these views are used the way they should be used. (Best way to ensure this is making use of privileges and or combinations with functions, returning (PK)ID or even packages providing kind of session variables used for dimension filtering.

But first of all, assure correct data selection, then start thinking about speed, elegance or comfort.

(Mit freier Unterstützung von DeepL)

[piedad]

Hi Jo,
I really appreciate your support. I got my Problem solved after formatting my code in the way you propose, so I could read it better and Analyse the whole flow. At the end the Problem was not originated by the joins:a Station stop to send the key I was using to link the tables.It was tough to find for a new one in a new Firma and a new programming languge. I am using Delphi through QDA9, in this case I had to automate (with the help of automatic batches) some useful Reports. This was the main reason for the compact Code. It is a pity, I cannot Change the Definition of the columns, I put column# for you guys but in the real life they are called such#...
Thankes a lot and best regards Piedad

[jobo]

Well, I never understood why people neglegt formating in SQL Statements. Looks a little like masochism. There are even online tools for formatting SQL statements. The suggestion above was done automatically and pimped a little by hand, a matter of seconds.

And I don't know anything about QDA9, but with reference to the naming, You don't have to start with changing column definitions.
Just make use of views. Not only to rename columns, use them as interface. It's so simple!

Code:

create view GetUsefulNames as
select 
  column3 as Useful_PKID,
  column1 as Useful_Name,
  column2 as Useful_Quantity,
  column9 as Useful_Class,
  trim(column8) as Useful_AlwaysClean_Code
  ..
  from dat_messung

You can use it vor any Report and other stuff within your control. And you could pass it to others.
The use of views in oracle is quite powerful, you can even update, as long as the pk Column is included. Even my example would be updatable would be updatable except the trimmed column.

Of course You could add a restriction there in any view, limiting view result to certain criteria. And you can update even this views containg a where clause (But caution, you can do uptdates violating the criterias, use an extra clause in where condition to prohibit conflicting updates)

And if you know it is and will be a reporting view in future, You can do a lot of preprocessing in some baseline views.

Just give it try as soon as soon as You got a little picture of the things in the new "Firma".