[NEWSboard IBMi Forum]
  1. #1
    Registriert seit
    May 2009
    Beiträge
    52

    SQL Rätsel zum WochenEnde - Kombinationen ermitteln

    Aufgabe:
    Wir möchten wissen welche Teilekombinationen wie oft vorkommen.
    Als Beispiel haben wir den Auftrag AA150002206 der hat die gleichen Teile (TEIL1202450550 und TEIL1202470550) wie der Auftrag AN150037024, AN160007280,...)

    Aber vorsicht es kann Aufträge geben (AN160010019) welcher 3 Teile haben könnte!
    Also die Kombination aus 3 Teilen (...2480557 ...2550557 ...2580557) gilt hier!

    Daten:
    with tmp (Auftrag, Fertigungsauftrag, Teilenummer) as ( values
    ('AN160007548', '4711', 'TEIL1202480550'),
    ('AN160010602', '4712', 'TEIL1202480550'),
    ('AN160011460', '4713', 'TEIL1202480550'),
    ('AN160011863', '4714', 'TEIL1202480550'),
    ('AN160012865', '4715', 'TEIL1202480550'),
    ('AN160001605', '4716', 'TEIL1202480557'),
    ('AN160008730', '4717', 'TEIL1202480557'),
    ('AN160009457', '4718', 'TEIL1202480557'),
    ('AN160009383', '4719', 'TEIL1202560550'),
    ('AN160009468', '4720', 'TEIL1202560550'),
    ('AN160010881', '4721', 'TEIL1202560550'),
    ('AN160011506', '4722', 'TEIL1202560550'),
    ('AA160000195', '4723', 'TEIL1202460129'),
    ('AA160000195', '4724', 'TEIL1202470129'),
    ('AA160000310', '4725', 'TEIL1202460129'),
    ('AA160000310', '4726', 'TEIL1202470129'),
    ('AN160008509', '4727', 'TEIL1202490550'),
    ('AN160011380', '4728', 'TEIL1202490550'),
    ('AN160011731', '4729', 'TEIL1202490550'),
    ('AN150044553', '4730', 'TEIL1202490557'),
    ('AN160008758', '4731', 'TEIL1202490557'),
    ('AN160012958', '4732', 'TEIL1202490557'),
    ('AN160000404', '4733', 'TEIL1202470129'),
    ('AN160000404', '4734', 'TEIL1202580129'),
    ('AN160008753', '4735', 'TEIL1202450550'),
    ('AN160008753', '4736', 'TEIL1202480550'),
    ('AN160009822', '4737', 'TEIL1202450550'),
    ('AN160009822', '4738', 'TEIL1202480550'),
    ('AN160010442', '4739', 'TEIL1202450550'),
    ('AN160010442', '4740', 'TEIL1202480550'),
    ('AN160010989', '4741', 'TEIL1202450550'),
    ('AN160010989', '4742', 'TEIL1202480550'),
    ('AN160011490', '4743', 'TEIL1202450550'),
    ('AN160011490', '4744', 'TEIL1202480550'),
    ('AN160011758', '4745', 'TEIL1202450550'),
    ('AN160011758', '4746', 'TEIL1202480550'),
    ('AN160010900', '4747', 'TEIL1202450557'),
    ('AN160010900', '4748', 'TEIL1202560557'),
    ('AN160010021', '4749', 'TEIL1202480550'),
    ('AN160010021', '4750', 'TEIL1202570550'),
    ('AN160012837', '4751', 'TEIL1202480550'),
    ('AN160012837', '4752', 'TEIL1202570550'),
    ('AN160003196', '4753', 'TEIL1202460550'),
    ('AN160003196', '4754', 'TEIL1202480550'),
    ('AN160008991', '4755', 'TEIL1202460550'),
    ('AN160008991', '4756', 'TEIL1202480550'),
    ('AN160010253', '4757', 'TEIL1202460550'),
    ('AN160010253', '4758', 'TEIL1202480550'),
    ('AN160011833', '4759', 'TEIL1202460550'),
    ('AN160011833', '4760', 'TEIL1202480550'),
    ('AN160012758', '4761', 'TEIL1202460550'),
    ('AN160012758', '4762', 'TEIL1202480550'),
    ('AN160013057', '4763', 'TEIL1202480557'),
    ('AN160013057', '4764', 'TEIL1202490557'),
    ('AN160012213', '4765', 'TEIL1202560432'),
    ('AN160009388', '4766', 'TEIL1202450550'),
    ('AN160009388', '4767', 'TEIL1202560550'),
    ('AN160008115', '4768', 'TEIL1202450321'),
    ('AN160008115', '4769', 'TEIL1202470321'),
    ('AN160013060', '4770', 'TEIL1202450321'),
    ('AN160013060', '4771', 'TEIL1202470321'),
    ('AN160006322', '4772', 'TEIL1202450129'),
    ('AN160006322', '4773', 'TEIL1202470129'),
    ('AN160009516', '4774', 'TEIL1202470129'),
    ('AN160009516', '4775', 'TEIL1202570129'),
    ('AN160008501', '4776', 'TEIL1202450550'),
    ('AN160008501', '4777', 'TEIL1202490550'),
    ('AN160008627', '4778', 'TEIL1202450550'),
    ('AN160008627', '4779', 'TEIL1202490550'),
    ('AN160008988', '4780', 'TEIL1202450550'),
    ('AN160008988', '4781', 'TEIL1202490550'),
    ('AN160008131', '4782', 'TEIL1202460557'),
    ('AN160008131', '4783', 'TEIL1202480557'),
    ('AN160009607', '4784', 'TEIL1202460557'),
    ('AN160009607', '4785', 'TEIL1202480557'),
    ('AN160010210', '4786', 'TEIL1202460557'),
    ('AN160010210', '4787', 'TEIL1202480557'),
    ('AN160010901', '4788', 'TEIL1202480550'),
    ('AN160010901', '4789', 'TEIL1202580550'),
    ('AN160013096', '4790', 'TEIL1202480550'),
    ('AN160013096', '4791', 'TEIL1202580550'),
    ('AN160010062', '4792', 'TEIL1202490432'),
    ('AN160006030', '4793', 'TEIL1202460550'),
    ('AN160006030', '4794', 'TEIL1202490550'),
    ('AN160007787', '4795', 'TEIL1202460550'),
    ('AN160007787', '4796', 'TEIL1202490550'),
    ('AN160008384', '4797', 'TEIL1202460550'),
    ('AN160008384', '4798', 'TEIL1202490550'),
    ('AN160010677', '4799', 'TEIL1202460550'),
    ('AN160010677', '4800', 'TEIL1202490550'),
    ('AN160011800', '4801', 'TEIL1202450557'),
    ('AN160011800', '4802', 'TEIL1202490557'),
    ('AN160009597', '4803', 'TEIL1202490432'),
    ('AN160009597', '4804', 'TEIL1202580432'),
    ('AA150002206', '4805', 'TEIL1202450550'),
    ('AA150002206', '4806', 'TEIL1202470550'),
    ('AN150037024', '4807', 'TEIL1202450550'),
    ('AN150037024', '4808', 'TEIL1202470550'),
    ('AN160007280', '4809', 'TEIL1202450550'),
    ('AN160007280', '4810', 'TEIL1202470550'),
    ('AN160007545', '4811', 'TEIL1202450432'),
    ('AN160007545', '4812', 'TEIL1202470432'),
    ('AN160007919', '4813', 'TEIL1202450432'),
    ('AN160007919', '4814', 'TEIL1202470432'),
    ('AN160008171', '4815', 'TEIL1202450550'),
    ('AN160008171', '4816', 'TEIL1202470550'),
    ('AN160008630', '4817', 'TEIL1202450550'),
    ('AN160008630', '4818', 'TEIL1202470550'),
    ('AN160008631', '4819', 'TEIL1202450550'),
    ('AN160008631', '4820', 'TEIL1202470550'),
    ('AN160008655', '4821', 'TEIL1202450550'),
    ('AN160008655', '4822', 'TEIL1202470550'),
    ('AN160008690', '4823', 'TEIL1202450550'),
    ('AN160008690', '4824', 'TEIL1202470550'),
    ('AN160009727', '4825', 'TEIL1202450550'),
    ('AN160009727', '4826', 'TEIL1202470550'),
    ('AN160009973', '4827', 'TEIL1202450550'),
    ('AN160009973', '4828', 'TEIL1202470550'),
    ('AN160010259', '4829', 'TEIL1202450550'),
    ('AN160010259', '4830', 'TEIL1202470550'),
    ('AN160010524', '4831', 'TEIL1202450432'),
    ('AN160010524', '4832', 'TEIL1202470432'),
    ('AN160010860', '4833', 'TEIL1202450550'),
    ('AN160010860', '4834', 'TEIL1202470550'),
    ('AN160011152', '4835', 'TEIL1202450550'),
    ('AN160011152', '4836', 'TEIL1202470550'),
    ('AN160011663', '4837', 'TEIL1202450550'),
    ('AN160011663', '4838', 'TEIL1202470550'),
    ('AN160012021', '4839', 'TEIL1202450550'),
    ('AN160012021', '4840', 'TEIL1202470550'),
    ('AN160012567', '4841', 'TEIL1202450550'),
    ('AN160012567', '4842', 'TEIL1202470550'),
    ('AN160013069', '4843', 'TEIL1202450550'),
    ('AN160013069', '4844', 'TEIL1202470550'),
    ('AN160007361', '4845', 'TEIL1202470550'),
    ('AN160007361', '4846', 'TEIL1202570550'),
    ('AN160010756', '4847', 'TEIL1202470550'),
    ('AN160010756', '4848', 'TEIL1202570550'),
    ('AN160013006', '4849', 'TEIL1202470550'),
    ('AN160013006', '4850', 'TEIL1202570550'),
    ('AN160009826', '4851', 'TEIL1202550550'),
    ('AN160009826', '4852', 'TEIL1202570550'),
    ('AA160000308', '4853', 'TEIL1202460550'),
    ('AA160000308', '4854', 'TEIL1202470550'),
    ('AN160008965', '4855', 'TEIL1202460550'),
    ('AN160008965', '4856', 'TEIL1202550550'),
    ('AN160008615', '4857', 'TEIL1202450557'),
    ('AN160008615', '4858', 'TEIL1202470557'),
    ('AN160009253', '4859', 'TEIL1202450557'),
    ('AN160009253', '4860', 'TEIL1202470557'),
    ('AN160010339', '4861', 'TEIL1202450557'),
    ('AN160010339', '4862', 'TEIL1202470557'),
    ('AN160010367', '4863', 'TEIL1202450557'),
    ('AN160010367', '4864', 'TEIL1202470557'),
    ('AN160012498', '4865', 'TEIL1202450557'),
    ('AN160012498', '4866', 'TEIL1202470557'),
    ('AN160007450', '4867', 'TEIL1202480432'),
    ('AN160007450', '4868', 'TEIL1202570432'),
    ('AN160011330', '4869', 'TEIL1202470557'),
    ('AN160011330', '4870', 'TEIL1202580557'),
    ('AN160011489', '4871', 'TEIL1202480129'),
    ('AN160011489', '4872', 'TEIL1202570129'),
    ('AN160010817', '4873', 'TEIL1202460557'),
    ('AN160010817', '4874', 'TEIL1202470557'),
    ('AN160011518', '4875', 'TEIL1202460557'),
    ('AN160011518', '4876', 'TEIL1202470557'),
    ('AN160010019', '4877', 'TEIL1202480557'),
    ('AN160010019', '4878', 'TEIL1202550557'),
    ('AN160010019', '4879', 'TEIL1202580557'),
    ('AN160011417', '4880', 'TEIL1202470550'),
    ('AN160011417', '4881', 'TEIL1202580550'),
    ('AN160010189', '4882', 'TEIL1202450129'),
    ('AN160010189', '4883', 'TEIL1202560129'),
    ('AN160010024', '4884', 'TEIL1202450432'),
    ('AN160010024', '4885', 'TEIL1202490432'),
    ('AN160011913', '4886', 'TEIL1202450432'),
    ('AN160011913', '4887', 'TEIL1202490432')


    )
    select * from tmp;



  2. #2
    Registriert seit
    May 2009
    Beiträge
    52
    Meine Lösung:

    Damit ich quasi eine Kombination ermitteln kann benötige ich quasi ein "Zwischen Teil" welches die Kombis representiert = Hash. Jeder Hash ist eindeutig somit ist die Summe daraus ebenfalls Eindeutig. => false ^^

    TMP habe ich in qtemp.a gesichert = leichter:

    with Calc (HAuftrag, HSUM) as (
    select Auftrag , sum(hash(Teilenummer)) as HSUM from qtemp.a
    group by Auftrag

    )
    select HAuftrag, Hsum, Fertigungsauftrag, teilenummer, count(*) as Anzahl_Kombi from Calc
    inner join qtemp.a on Auftrag = HAuftrag
    group by grouping sets ((HAuftrag, Hsum, Fertigungsauftrag, teilenummer), (hsum))
    order by HSUM, HAuftrag


    Vielleicht liege ich ja falsch, eure Meinung dazu wäre interessant.

    Schönes WochenEnde..Grüße
    Last edited by MGJ79; 26-02-16 at 13:09. Grund: Ich bin ein Esel

  3. #3
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Ein HASH ist nicht unbedingt eindeutig, hier kann es durchaus sog. "Kollisionen" geben, die bei einer Hashliste dann zu einer verketteten Liste der Kollisionen führt.
    Während ein einzelner Hash geringe Kollisionen haben kann, erhöht sich die Kollisionsrate bei SUM(HASH) drastisch. Warum?
    Die Zahl 6 setzt sich zusammen aus 6 oder 5+1 oder 4+2 oder 3+3 oder 4+1+1 oder ....

    Eine Lösung per SQL kann ich somit auch nicht finden.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  4. #4
    Registriert seit
    May 2009
    Beiträge
    52
    Das ist richtig. Nachdem auch noch ein Integerwert zwischen 0 und 1023 rauskommt...hmm.

  5. #5
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Da hast du dir schon eine schwierige Aufgabe rausgesucht.
    Schau mal hier, zu den Möglichkeiten:
    http://de.numberempire.com/combinatorialcalculator.php
    Bei z.B. 5000 Produkten und Aufträgen mit je 10 Artikeln ergibt das ca. 10 hoch 36 Möglichkeiten.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  6. #6
    Registriert seit
    Oct 2013
    Beiträge
    9
    Per selbst gestrickter SQL-Function kann man die Teilenummer je Auftrag sortiert nebeneinanderstellen, dann kann man die exakt gleichen Zusammensetzungen schon mal mittels Group greifen.
    "AA150002206", "TEIL1202450550, TEIL1202470550"
    usw.
    Hilft aber im ersten Schritt nur für exakt gleiche.

    Leider ist im Beispiel noch ein Syntaxfehler, aber so in etwa haben wir vergleichbare Functions im Einsatz:
    Code:
    CREATE FUNCTION qtemp.buildTeileString (     P#AUFTRAG VARCHAR(11) ) 
        RETURNS VARCHAR(200)   
        LANGUAGE SQL 
        SPECIFIC QTEMP.BUILDTEILESTRING
        NOT DETERMINISTIC 
        READS SQL DATA 
        CALLED ON NULL INPUT 
        SET OPTION  ALWBLK = *ALLREAD , 
        ALWCPYDTA = *OPTIMIZE , 
        COMMIT = *NONE , 
        DECRESULT = (31, 31, 00) , 
        DFTRDBCOL = *NONE , 
        DYNDFTCOL = *NO , 
        DYNUSRPRF = *USER , 
        SRTSEQ = *HEX   
        BEGIN 
    DECLARE TEXT    VARCHAR ( 200 ) ; 
    SET TEXT = NULL ; 
      
    FOR SELTEILE AS C1 CURSOR FOR 
      
    SELECT    DISTINCT TEILENUMMER AS TEIL
    FROM    qtemp.a
    WHERE    AUFTRAG = P#AUFTRAG
    order by TEILENUMMER
      
    DO 
       IF TEXT IS NULL THEN   
          SET TEXT = TEIL ;
       ELSE 
          SET TEXT = TEXT CONCAT ', ' CONCAT TEIL; 
       END IF ; 
    END FOR ; 
      
    RETURN TEXT ; 
      
    END  ;

  7. #7
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Das kann man auch mit einer rekursiven CTE erreichen um die Teilenummern zu verketten.
    Anschließend mach man einen Union Select und zerlegt diese Kette wieder:
    Beispiel für 3 Teile á 10 Stellen

    select Teile, count(*) from (
    select Teile from CTE
    union all
    select substr(Teile, 1, 10) from CTE
    union all
    select substr(Teile, 11, 10) from CTE
    union all
    select substr(Teile, 21, 10) from CTE
    union all
    select substr(Teile, 1, 20) from CTE
    union all
    select substr(Teile, 11, 20) from CTE
    ) x
    group by teile
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  8. #8
    cbe is offline [professional_User]
    Registriert seit
    May 2005
    Beiträge
    392
    kann man sicher eleganter machen, aber wie wärs mit sowas:

    Code:
    with tmp (auf, fert, teil) as ( values 
     ('AN160007548', '4711', 'TEIL1202480550'),
     ('AN160010602', '4712', 'TEIL1202480550'),
     ('AN160011460', '4713', 'TEIL1202480550'),
     ('AN160011863', '4714', 'TEIL1202480550'),
     ('AN160012865', '4715', 'TEIL1202480550'),
     ('AN160001605', '4716', 'TEIL1202480557'),
     ('AN160008730', '4717', 'TEIL1202480557'),
     ('AN160009457', '4718', 'TEIL1202480557'),
     ('AN160009383', '4719', 'TEIL1202560550'),
     ('AN160009468', '4720', 'TEIL1202560550'),
     ('AN160010881', '4721', 'TEIL1202560550'),
     ('AN160011506', '4722', 'TEIL1202560550'),
     ('AA160000195', '4723', 'TEIL1202460129'),
     ('AA160000195', '4724', 'TEIL1202470129'),
     ('AA160000310', '4725', 'TEIL1202460129'),
     ('AA160000310', '4726', 'TEIL1202470129'),
     ('AN160008509', '4727', 'TEIL1202490550'),
     ('AN160011380', '4728', 'TEIL1202490550'),
     ('AN160011731', '4729', 'TEIL1202490550')  )
    , data  as (select distinct auf, teil from tmp order by 1, 2)
    , t1 (auf, teil, teile ) as  (select auf, teil,  '                                                                                                                                                                ' from data
     union all select t2.auf, t2.teil, t2.teil concat ',' concat t1.teile  from t1, data as t2  
        where  t1.auf=t2.auf and t2.teil>substr(t1.teile, 1, 14))
    , help as (select auf, max(teile) teile from t1 group by auf order by auf)
    select teile, count(*), min(auf), max(auf) from help group by teile order by 2 desc
    (habe die Daten hier etwas gekürzt)

    Der lange leere String steht für einen längeren String, ich weiß gerade nicht auswendig, wie man den per cast o.ä. in SQL definiert

    Gruß, Christian

    PS. nette kleine Knobelaufgabe zum Wochenende. Gibt's das jetzt jede Woche?

  9. #9
    KM is offline [professional_User]
    Registriert seit
    Apr 2003
    Beiträge
    1.005
    wie wäre es mit folgendem kleinen SQL-Statement:

    Code:
            with tmp (Auftrag, Fertigungsauftrag, Teilenummer) as ( values
            ('AN160007548', '4711', 'TEIL1202480550'),
            ('AN160010602', '4712', 'TEIL1202480550'),
            ('AN160011460', '4713', 'TEIL1202480550'),
            ('AN160011863', '4714', 'TEIL1202480550'),
            ('AN160012865', '4715', 'TEIL1202480550'),
            ('AN160001605', '4716', 'TEIL1202480557'),
            ('AN160008730', '4717', 'TEIL1202480557'),
            ('AN160009457', '4718', 'TEIL1202480557'),
            ('AN160009383', '4719', 'TEIL1202560550'),
            ('AN160009468', '4720', 'TEIL1202560550'),
            ('AN160010881', '4721', 'TEIL1202560550'),
            ('AN160011506', '4722', 'TEIL1202560550'),
            ('AA160000195', '4723', 'TEIL1202460129'),
            ('AA160000195', '4724', 'TEIL1202470129'),
            ('AA160000310', '4725', 'TEIL1202460129'),
            ('AA160000310', '4726', 'TEIL1202470129'),
            ('AN160008509', '4727', 'TEIL1202490550'),
            ('AN160011380', '4728', 'TEIL1202490550'),
            ('AN160011731', '4729', 'TEIL1202490550'),
            ('AN150044553', '4730', 'TEIL1202490557'),
            ('AN160008758', '4731', 'TEIL1202490557'),
            ('AN160012958', '4732', 'TEIL1202490557'),
            ('AN160000404', '4733', 'TEIL1202470129'),
            ('AN160000404', '4734', 'TEIL1202580129'),
            ('AN160008753', '4735', 'TEIL1202450550'),
            ('AN160008753', '4736', 'TEIL1202480550'),
            ('AN160009822', '4737', 'TEIL1202450550'),
            ('AN160009822', '4738', 'TEIL1202480550'),
            ('AN160010442', '4739', 'TEIL1202450550'),
            ('AN160010442', '4740', 'TEIL1202480550'),
            ('AN160010989', '4741', 'TEIL1202450550'),
            ('AN160010989', '4742', 'TEIL1202480550'),
            ('AN160011490', '4743', 'TEIL1202450550'),
            ('AN160011490', '4744', 'TEIL1202480550'),
            ('AN160011758', '4745', 'TEIL1202450550'),
            ('AN160011758', '4746', 'TEIL1202480550'),
            ('AN160010900', '4747', 'TEIL1202450557'),
            ('AN160010900', '4748', 'TEIL1202560557'),
            ('AN160010021', '4749', 'TEIL1202480550'),
            ('AN160010021', '4750', 'TEIL1202570550'),
            ('AN160012837', '4751', 'TEIL1202480550'),
            ('AN160012837', '4752', 'TEIL1202570550'),
            ('AN160003196', '4753', 'TEIL1202460550'),
            ('AN160003196', '4754', 'TEIL1202480550'),
            ('AN160008991', '4755', 'TEIL1202460550'),
            ('AN160008991', '4756', 'TEIL1202480550'),
            ('AN160010253', '4757', 'TEIL1202460550'),
            ('AN160010253', '4758', 'TEIL1202480550'),
            ('AN160011833', '4759', 'TEIL1202460550'),
            ('AN160011833', '4760', 'TEIL1202480550'),
            ('AN160012758', '4761', 'TEIL1202460550'),
            ('AN160012758', '4762', 'TEIL1202480550'),
            ('AN160013057', '4763', 'TEIL1202480557'),
            ('AN160013057', '4764', 'TEIL1202490557'),
            ('AN160012213', '4765', 'TEIL1202560432'),
            ('AN160009388', '4766', 'TEIL1202450550'),
            ('AN160009388', '4767', 'TEIL1202560550'),
            ('AN160008115', '4768', 'TEIL1202450321'),
            ('AN160008115', '4769', 'TEIL1202470321'),
            ('AN160013060', '4770', 'TEIL1202450321'),
            ('AN160013060', '4771', 'TEIL1202470321'),
            ('AN160006322', '4772', 'TEIL1202450129'),
            ('AN160006322', '4773', 'TEIL1202470129'),
            ('AN160009516', '4774', 'TEIL1202470129'),
            ('AN160009516', '4775', 'TEIL1202570129'),
            ('AN160008501', '4776', 'TEIL1202450550'),
            ('AN160008501', '4777', 'TEIL1202490550'),
            ('AN160008627', '4778', 'TEIL1202450550'),
            ('AN160008627', '4779', 'TEIL1202490550'),
            ('AN160008988', '4780', 'TEIL1202450550'),
            ('AN160008988', '4781', 'TEIL1202490550'),
            ('AN160008131', '4782', 'TEIL1202460557'),
            ('AN160008131', '4783', 'TEIL1202480557'),
            ('AN160009607', '4784', 'TEIL1202460557'),
            ('AN160009607', '4785', 'TEIL1202480557'),
            ('AN160010210', '4786', 'TEIL1202460557'),
            ('AN160010210', '4787', 'TEIL1202480557'),
            ('AN160010901', '4788', 'TEIL1202480550'),
            ('AN160010901', '4789', 'TEIL1202580550'),
            ('AN160013096', '4790', 'TEIL1202480550'),
            ('AN160013096', '4791', 'TEIL1202580550'),
            ('AN160010062', '4792', 'TEIL1202490432'),
            ('AN160006030', '4793', 'TEIL1202460550'),
            ('AN160006030', '4794', 'TEIL1202490550'),
            ('AN160007787', '4795', 'TEIL1202460550'),
            ('AN160007787', '4796', 'TEIL1202490550'),
            ('AN160008384', '4797', 'TEIL1202460550'),
            ('AN160008384', '4798', 'TEIL1202490550'),
            ('AN160010677', '4799', 'TEIL1202460550'),
            ('AN160010677', '4800', 'TEIL1202490550'),
            ('AN160011800', '4801', 'TEIL1202450557'),
            ('AN160011800', '4802', 'TEIL1202490557'),
            ('AN160009597', '4803', 'TEIL1202490432'),
            ('AN160009597', '4804', 'TEIL1202580432'),
            ('AA150002206', '4805', 'TEIL1202450550'),
            ('AA150002206', '4806', 'TEIL1202470550'),
            ('AN150037024', '4807', 'TEIL1202450550'),
            ('AN150037024', '4808', 'TEIL1202470550'),
            ('AN160007280', '4809', 'TEIL1202450550'),
            ('AN160007280', '4810', 'TEIL1202470550'),
            ('AN160007545', '4811', 'TEIL1202450432'),
            ('AN160007545', '4812', 'TEIL1202470432'),
            ('AN160007919', '4813', 'TEIL1202450432'),
            ('AN160007919', '4814', 'TEIL1202470432'),
            ('AN160008171', '4815', 'TEIL1202450550'),
            ('AN160008171', '4816', 'TEIL1202470550'),
            ('AN160008630', '4817', 'TEIL1202450550'),
            ('AN160008630', '4818', 'TEIL1202470550'),
            ('AN160008631', '4819', 'TEIL1202450550'),
            ('AN160008631', '4820', 'TEIL1202470550'),
            ('AN160008655', '4821', 'TEIL1202450550'),
            ('AN160008655', '4822', 'TEIL1202470550'),
            ('AN160008690', '4823', 'TEIL1202450550'),
            ('AN160008690', '4824', 'TEIL1202470550'),
            ('AN160009727', '4825', 'TEIL1202450550'),
            ('AN160009727', '4826', 'TEIL1202470550'),
            ('AN160009973', '4827', 'TEIL1202450550'),
            ('AN160009973', '4828', 'TEIL1202470550'),
            ('AN160010259', '4829', 'TEIL1202450550'),
            ('AN160010259', '4830', 'TEIL1202470550'),
            ('AN160010524', '4831', 'TEIL1202450432'),
            ('AN160010524', '4832', 'TEIL1202470432'),
            ('AN160010860', '4833', 'TEIL1202450550'),
            ('AN160010860', '4834', 'TEIL1202470550'),
            ('AN160011152', '4835', 'TEIL1202450550'),
            ('AN160011152', '4836', 'TEIL1202470550'),
            ('AN160011663', '4837', 'TEIL1202450550'),
            ('AN160011663', '4838', 'TEIL1202470550'),
            ('AN160012021', '4839', 'TEIL1202450550'),
            ('AN160012021', '4840', 'TEIL1202470550'),
            ('AN160012567', '4841', 'TEIL1202450550'),
            ('AN160012567', '4842', 'TEIL1202470550'),
            ('AN160013069', '4843', 'TEIL1202450550'),
            ('AN160013069', '4844', 'TEIL1202470550'),
            ('AN160007361', '4845', 'TEIL1202470550'),
            ('AN160007361', '4846', 'TEIL1202570550'),
            ('AN160010756', '4847', 'TEIL1202470550'),
            ('AN160010756', '4848', 'TEIL1202570550'),
            ('AN160013006', '4849', 'TEIL1202470550'),
            ('AN160013006', '4850', 'TEIL1202570550'),
            ('AN160009826', '4851', 'TEIL1202550550'),
            ('AN160009826', '4852', 'TEIL1202570550'),
            ('AA160000308', '4853', 'TEIL1202460550'),
            ('AA160000308', '4854', 'TEIL1202470550'),
            ('AN160008965', '4855', 'TEIL1202460550'),
            ('AN160008965', '4856', 'TEIL1202550550'),
            ('AN160008615', '4857', 'TEIL1202450557'),
            ('AN160008615', '4858', 'TEIL1202470557'),
            ('AN160009253', '4859', 'TEIL1202450557'),
            ('AN160009253', '4860', 'TEIL1202470557'),
            ('AN160010339', '4861', 'TEIL1202450557'),
            ('AN160010339', '4862', 'TEIL1202470557'),
            ('AN160010367', '4863', 'TEIL1202450557'),
            ('AN160010367', '4864', 'TEIL1202470557'),
            ('AN160012498', '4865', 'TEIL1202450557'),
            ('AN160012498', '4866', 'TEIL1202470557'),
            ('AN160007450', '4867', 'TEIL1202480432'),
            ('AN160007450', '4868', 'TEIL1202570432'),
            ('AN160011330', '4869', 'TEIL1202470557'),
            ('AN160011330', '4870', 'TEIL1202580557'),
            ('AN160011489', '4871', 'TEIL1202480129'),
            ('AN160011489', '4872', 'TEIL1202570129'),
            ('AN160010817', '4873', 'TEIL1202460557'),
            ('AN160010817', '4874', 'TEIL1202470557'),
            ('AN160011518', '4875', 'TEIL1202460557'),
            ('AN160011518', '4876', 'TEIL1202470557'),
            ('AN160010019', '4877', 'TEIL1202480557'),
            ('AN160010019', '4878', 'TEIL1202550557'),
            ('AN160010019', '4879', 'TEIL1202580557'),
            ('AN160011417', '4880', 'TEIL1202470550'),
            ('AN160011417', '4881', 'TEIL1202580550'),
            ('AN160010189', '4882', 'TEIL1202450129'),
            ('AN160010189', '4883', 'TEIL1202560129'),
            ('AN160010024', '4884', 'TEIL1202450432'),
            ('AN160010024', '4885', 'TEIL1202490432'),
            ('AN160011913', '4886', 'TEIL1202450432'),
            ('AN160011913', '4887', 'TEIL1202490432')
            ),
            tmp2 (t2_Auftrag, t2_Teilenummer, curr, prev) as (
            SELECT auftrag, teilenummer,
            ROW_NUMBER() OVER (PARTITION BY auftrag ORDER BY teilenummer) AS curr,
            ROW_NUMBER() OVER (PARTITION BY auftrag ORDER BY teilenummer) -1 AS prev
            FROM tmp),
            tmp3 (t3_Auftrag, t3_Teilenummer) as (
            SELECT t2_auftrag, MAX(TRIM(TRIM(LEADING ',' FROM CAST(SYS_CONNECT_BY_PATH(TRIM(t2_teilenummer), ', ') AS VARCHAR(1024)))))
            FROM tmp2
            START WITH curr = 1
            CONNECT BY t2_auftrag = PRIOR t2_auftrag AND prev = PRIOR curr
            GROUP BY t2_auftrag)
            select t3_teilenummer as "Kombination", count(t3_auftrag) as "Anzahl Aufträge"
            from tmp3
            group by t3_teilenummer
            ORDER BY t3_teilenummer
    Gruß,
    KM

  10. #10
    cbe is offline [professional_User]
    Registriert seit
    May 2005
    Beiträge
    392
    Hallo KM,
    Dein Ergebnis sieht recht ähnlich aus, ist ja ein gutes Zeichen
    Und da ist einiges für mich Neues drin - die Syntax mit OVER und START kenne ich noch gar nicht. Muss ich mir mal anschauen...
    Gruß, Christian

  11. #11
    Registriert seit
    Aug 2001
    Beiträge
    2.869
    Wie wär's den damit:
    Code:
    with tmp (Auftrag, Fertigungsauftrag, Teilenummer) as ( values .... ),
     y as (Select Dense_Rank() Over (Partition by Auftrag Order By TeileNummer) 
                    lfd, tmp.*
             From tmp) 
    Select Auftrag, max(Case When lfd = 1 Then TeileNummer End),
                    max(Case When lfd = 2 Then TeileNummer End),
                    max(Case When lfd = 3 Then TeileNummer End) 
       from y
       Group By Auftrag
       Having Max(lfd) = 3
       Order By Auftrag;
    Birgitta
    Birgitta Hauser

    Anwendungsmodernisierung, Beratung, Schulungen, Programmierung im Bereich RPG, SQL und Datenbank
    IBM Champion seit 2020 - 4. Jahr in Folge
    Birgitta Hauser - Modernization - Education - Consulting on IBM i

Similar Threads

  1. Programmname im CL ermitteln
    By harkne in forum NEWSboard Programmierung
    Antworten: 1
    Letzter Beitrag: 23-12-15, 15:27
  2. DB2 Datenbankgröße ermitteln
    By IBSCPO in forum IBM i Hauptforum
    Antworten: 3
    Letzter Beitrag: 07-04-15, 18:22
  3. Quellenart ermitteln
    By Tschabo in forum NEWSboard Programmierung
    Antworten: 3
    Letzter Beitrag: 30-10-14, 13:54
  4. IFS Share Name ermitteln
    By camouflage in forum NEWSboard Programmierung
    Antworten: 9
    Letzter Beitrag: 21-08-14, 17:23
  5. Satzlänge in CL ermitteln
    By kschmidt in forum IBM i Hauptforum
    Antworten: 5
    Letzter Beitrag: 19-06-01, 18:35

Tags for this Thread

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • You may not post attachments
  • You may not edit your posts
  •