nested joins check

[piedad]

Hallo guys,
I do not really know if either my measured data or my code are not ok. Could you take a look at my Code and give me your feedback, if it is ok to Programm it in this way?
thanks in Advance and Kind regards
Piedad

I have three Databases to join and extract data from:
1st global database with Electric measured data: DB1.Measurements with a Primary key: PRFNR and columns: ID_Long ,Voltage, current,Length, Count,ESD,...
2nd data base with measurement proceduresB1.Tools with a Primary key: PRFNR and columns ID_Long,tools,conditions
3rd Database Linedata: DB2.Data with Primary key ID_Long(just in one Station) and columns ID_short, machinenumber,Date, Status,para1,para2,para3,...
I Need to pick up the Long ID which is only posible to find in the Database DB1.Measurements which corresponds to several Shorts IDs to find in the DB2.Data.
I tried following Code and got some short ID Right results and some result with short ID's belonging to another Databases DB3 and 4.
this is my Code:

Code:

Select Distinct dm.column17 cell, dm.column3 auto_id,dm.column5,dm.column8 Mod_Status,dmZ.column3 BAT,dm7.column4 MOD_VST_ID,
dm7.column8 Mod_Status,dm7.ST_DATUM, dm7.column12 STATUS,DMZ.Date,DMDZ.IR,DMDZ.CAP,DMDZ.UOCV2,DMDZ.DOCV2,
DMDZ.UOCV3,DMDZ.DOCV3,DMDZ.UOCV4,DMDZ.DOCV4,DMDZ.SDR,DMDZ.UBS,DMDZ.DBS,DMDZ.WEIGHT,DMDZ.THICKNESS
 from Data  dm
 
Inner join (Select  PRFNR,column5,column6,column7,column15 from dat_messung  where column2= '1850'  and column5 is not null)dm4 on dm.column19 = dm4.column5
Inner join (Select column15,column5,column6,column7 from dat_messung  where column6 is not null)dm5 on dm4.column6 = dm5.column6 
Inner join (Select column15,column6,column7, PRFNR from dat_messung  where column6 is not null)dm6 on dm5.column7 = dm6.column6  
Inner join (Select DATUM ST_DATUM, column12 ST_Status, PRFNR,column7,column8 from dat_messung  where (column2 = '1330' or column2 = '1360')  
and column7 is not null)dm7 on dm6.column7 = dm7.column7
 
left join (Select Distinct PRFNR PRFNRZ,column3,DATUM Date from DB1.Tools)DMZ ON dm6.colum6 = DMZ.column3
LEFT JOIN(Select * from
     (SELECT Distinct  PRFNR PRFNRX, MNR,X1 FROM ACCU_ZELLE.dat_messungdaten where MNR In(1,2,3,4,5,6,7,8,9,10,11,12,13,14)) 
      PIVOT(Max(X1) For(MNR) IN(1 Voltage1,2 Voltage2,3 Current1,4 Current2,5 Resistane,6 Impedance,7 Width,8 Height,11 Length,12  WEIGHT,13 
      THICKNESS)))DMDZ ON DMZ.PRFNRZ = DMDZ.PRFNRX         
where (dm.DATUM > Sysdate - 60 )

[jobo]

even more formatting (quite usefull for understanding what's going on)

PHP-Quellcode:

Select Distinct dm.column17    cell,
                dm.column3     auto_id,
                dm.column5,
                dm.column8     Mod_Status,
                dmZ.column3    BAT,
                dm7.column4    MOD_VST_ID,
                dm7.column8    Mod_Status,
                dm7.ST_DATUM,
                dm7.column12   STATUS,
                DMZ.Date,    DMDZ.IR,    DMDZ.CAP,
                DMDZ.UOCV2,  DMDZ.DOCV2, DMDZ.UOCV3,
                DMDZ.DOCV3,  DMDZ.UOCV4, DMDZ.DOCV4,
                DMDZ.SDR,    DMDZ.UBS,   DMDZ.DBS,
                DMDZ.WEIGHT, DMDZ.THICKNESS
  from Data dm
 Inner join (Select PRFNR, column5, column6, column7, column15 from dat_messung
              where column2 = '1850' and column5 is not null) dm4
    on dm.column19 = dm4.column5
 Inner join (Select column15, column5, column6, column7  from dat_messung
              where column6 is not null) dm5
    on dm4.column6 = dm5.column6
 Inner join (Select column15, column6, column7, PRFNR
               from dat_messung where column6 is not null) dm6
    on dm5.column7 = dm6.column6
 Inner join (Select DATUM    ST_DATUM,
                    column12 ST_Status,
                    PRFNR,
                    column7,
                    column8
               from dat_messung
              where (column2 = '1330' or column2 = '1360') and column7 is not null) dm7
    on dm6.column7 = dm7.column7
  left join (Select Distinct PRFNR PRFNRZ, column3, DATUM Date
               from DB1.Tools) DMZ
    ON dm6.colum6 = DMZ.column3
  LEFT JOIN (Select *
               from (SELECT Distinct PRFNR PRFNRX, MNR, X1
                       FROM ACCU_ZELLE.dat_messungdaten
                      where MNR In
                            (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14))
             PIVOT(Max(X1)
                For(MNR) IN(1 Voltage1,
                            2 Voltage2,
                            3 Current1,
                            4 Current2,
                            5 Resistane,
                            6 Impedance,
                            7 Width,
                            8 Height,
                           11 Length,
                           12 WEIGHT,
                           13 THICKNESS))) DMDZ
    ON DMZ.PRFNRZ = DMDZ.PRFNRX
 where (dm.DATUM > Sysdate - 60)

Now, that's that. But Your Question cannot be answered!

There is no data model, there is no data, there are not even a lot of usefull Fieldnames, giving ideas...

It looks like oracle database sql. And Yes, You can do all these joins, nested or chained, no problem. You can do pivot and select * operations .. no problem.

And to be honest, I didn't do a syntax read. As long as You don't get (or mention) an error it's fine.

[hoika]

Hello,
do you really expect,
we can answer this question?

eg:

Inner join (Select PRFNR, column5, column6, column7, column15 from dat_messung
where column2 = '1850' and column5 is not null) dm4

What is column2, what is column5?

[piedad]

thank you for your answer, the formatting is really useful. Exactly what I was Looking for, some qualitative help. I did not pretend guys in this case that you solve my Task(f. i. Building a submarin).I just got a half of this messy lines on my desk without any Explanation in order to apply it on a complexer Production line in a big production fab, of Course I got some Errors I still Fighting with but I cannot explain everything, it would take a couple of days.
We use each production Line 3 Databases one for the electrical measurements, one for how to do this measurements and the third one is the live data comming from the fab.Getting fom the 1st. Data base to the third one is not posible to do it directly, this is the reason of stepping through the 2nd one to catch the Primary key to Access the third database. the column2 describe the different stations of the production flow. By some of them several modules are assemblied together and documented and in order to be able to identify wich modules belongs together from the beginning to the end a colleague develop this Kind of Code. I just have to take over and update it.

I could not find any Information About how concatenating/nesting the joins, that was the reason why I posted it herer after a lot of hours Debugging...

-->Could you be so Kind to tell me if the usual way to build the nested joins should be starting from up (Ready Object-f.i.a submarin: set of modules) till down or rather beginning from the end (Basic modul: f.i. a screw: )?

best regards
Piedad

[jobo]

Which way to start complex joins is a difficult question.
In general I would start with the answer:
It's oracle, the optimizer works quite well, so don't care about performance etc.
Just use a join style, which suits You and Your understanding of the model / use case.

If you have a working statement You can start tweaking.

Btw. it's also possible and very helpfull to build views, doing certain steps of abstraction, explanatione etc.
Starting with a better naming of columns, you can later maybe reuse some of them at a certain stage. This could lead to better consistency and "stability".

[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".