Results 1 to 5 of 5
  1. #1
    Join Date
    May 2006
    Posts
    6

    Unanswered: SQL-Abfrage gesucht

    Hallo,
    ich habe 3 Tabellen:
    insert into IFF
    (obj_item, contxt, IFF_IX, erfasszeit)
    values
    (1,1,100, 20000101);
    insert into IFF
    (obj_item, contxt, IFF_IX, erfasszeit)
    values
    (1,1,5, 20000105);

    insert into Status
    (obj_item, contxt, Status_IX, erfasszeit)
    values
    (1,1, 1, 20000102);
    insert into Status
    (obj_item, contxt, Status_IX, erfasszeit)
    values
    (1,1, 100, 20000103);
    insert into Status
    (obj_item, contxt, Status_IX, erfasszeit)
    values
    (1,1, 3, 20000105);

    insert into LOC
    (obj_item, contxt, LOC_IX, LOC_ID, erfasszeit)
    values
    (1,1, 1, 1, 20000104);
    insert into LOC
    (obj_item, contxt, LOC_IX, LOC_ID, erfasszeit)
    values
    (1,1, 2, 2, 20000106);

    Ich brauche eine Abfrage, die mir folgendes Ergebnis liefert:
    obj_item contxt erfasszeit IFF_IX Status_IX LOC_IX LOC_ID
    1 1 20000101 100
    1 1 20000102 100 1
    1 1 20000103 100 100
    1 1 20000104 100 100 1 1
    1 1 20000105 5 3 1 1
    1 1 20000106 5 3 2 2

    Immer wenn eine Zustandsänderung eintritt, soll ein neuer Ergebnissatz entstehen mit der Erfasszeit dieser Änderung.
    Bei 20000101 gibt es nur ein IFF (IFF ist IMMER der 1. Eintrag).
    Bei 20000102 hat sich nur der Status geändert, IFF ist gleich geblieben. Der neue Wert 1 (Status_IX) und der alten Wert 100 (IFF_IX) müssen zu sehen sein.
    Bei 20000105 hat sich sowohl der Status als auch IFF geändert. Darum müssen die neuen Werte 5 (IFF_IX) und 3 (Status_IX), aber die alte Werte 1(LOC_IX) und 1 (LOC_ID) für die nicht betroffene Tabelle LOC zu sehen sein.
    obj_item und contxt müssen für ALLE Tabellen identisch sein.

    Vielen Dank.
    Doreen

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    This is an english speaking forum, which you could have noticed easily by reading any of the existing posts...
    Immer wenn eine Zustandsänderung eintritt, soll ein neuer Ergebnissatz entstehen mit der Erfasszeit dieser Änderung.
    Translation: Whenever a status change happens, a new result-row should be created with the creation time of this change.

    I'm not sure I understand your request. This sentence refers to inserts that should happen automatically when a status change happens, yet you are asking for a query? Or do you need a query and a trigger?

    obj_item und contxt müssen für ALLE Tabellen identisch sein.
    Translation: obj_item and contxt must be the same for ALL tables

    Does that mean (obj_item, contxt) is the primary key for all those tables?

    Bei 20000102 hat sich nur der Status geändert, IFF ist gleich geblieben
    Translation: At 20000102 only the status changed, IFF stayed identically.

    How do you "know" that for 20000102 only the status changed? Is that because there is no entry for 20000102 in the IFF table?

    Bei 20000105 hat sich sowohl der Status als auch IFF geändert. Darum müssen die neuen Werte 5 (IFF_IX) und 3 (Status_IX), aber die alte Werte 1(LOC_IX) und 1 (LOC_ID) für die nicht betroffene Tabelle LOC zu sehen sein.
    Translation: At 20000105 the status and IFF changed. Therefor the new values 5 (IFF_IX) and 3 (Status_IX) must be visible, but not(?) the old values 1 (LOC_IX) and 1 (LOC_ID) for the tables that are not affected.
    I'm not sure I understand this, especially because the german sentence is not complete. There seems to be a word missing in the second part (aber die alte Werte...)

    I don't think I fully understood your problem, but a first shot query (that retrieves something similar to your sample output) would be this:
    Code:
    select ar.obj_item, 
           ar.contxt, 
           ar.erfasszeit, 
           iff.iff_ix,
           status.status_ix, 
           loc.loc_ix
    from 
    (
      select obj_item, contxt, erfasszeit from iff
      union 
      select obj_item, contxt, erfasszeit from status
      union 
      select obj_item, contxt, erfasszeit from loc
    ) ar
    left join iff on (ar.obj_item = iff.obj_item and ar.contxt = iff.contxt and ar.erfasszeit = iff.erfasszeit)
    left join status on (ar.obj_item = iff.obj_item and ar.contxt = status.contxt and ar.erfasszeit = status.erfasszeit)
    left join loc on (ar.obj_item = loc.obj_item and ar.contxt = loc.contxt and ar.erfasszeit = loc.erfasszeit)
    order by 1,2,3
    The difference to your sample output is, that values that "stay the same" (because of missing entries) are not displayed at all.
    But it might be a starting point for you.

    I think it could be refined to fill the gaps using analytical functions.

  3. #3
    Join Date
    May 2006
    Posts
    6
    Sorry, my mistake.
    My question in English:

    Every time the status changes a new result set with a current timestamp is created.
    At 20000101 exists only one IFF (IFF is ALWAYS the first entry)
    At 20000102 only the status changed, the IFF is the same. The new value 1 (Status_IX) and the old value 100 (IFF_IX) have to be visible.
    At 20000105 the status and IFF have changed. Therefore the NEW values 5 (IFF_IX) and 3 (Status_IX) and the OLD values for 1 (LOC_IX) and 1 (LOC_ID) of the table LOC - which has not been affected - have to be visible.
    obj_item and contxt have to be identically for ALL tables.


    I only need the select-statement.
    I think also I need analytical functions, but I don't find the rigth statement.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by dreinhardt
    I only need the select-statement.
    Is the result of my query good enough as a starting point?

    With the exception that repeating values are shown as "empty" it basically does what you need (if I understood you correctly).

    If you need that SQL for a report it is probably not useable that way for you.

    If you are using the result inside a program, then it might actually be enough for you.

  5. #5
    Join Date
    May 2006
    Posts
    6
    Thanks. With your starting point statement and some views I created my SQL-Statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •