Legacy-Definition sicher vergleichen
Diese Detailansicht zeigt den unveränderten Legacy-Dateistand. Von hier aus lässt sich die Definition als Draft in die Workbench übernehmen und dort weiter bearbeiten, ohne das Original im Dateisystem anzutasten.
draft
Raw YAML
Dateiinhalt
Der Inhalt wird read-only aus dem Legacy-Dateisystem geladen. Bearbeitbar wird er erst nach dem Import in die Workbench.
Engine: Import
Source_Config: oster400
Target_Config: importEngine
Mysql_Engine: InnoDB
Default_Charset: latin1
Source_Connection_Type: database
Source_Database_Connection_Service: DatabaseConnectionService
Queries:
- Query:
GetQuery: SELECT
dyn_articles.firma,
dyn_articles.set_ordernumber AS ordernumber,
MIN(coalesce(CAST(stock.witten AS INT), 0)) AS witten,
MIN(coalesce(CAST(stock.bottrop AS INT), 0)) AS bottrop,
MIN(coalesce(CAST(stock.recklinghausen AS INT), 0)) AS recklinghausen,
MIN(coalesce(CAST(stock.haan AS INT), 0)) AS haan,
MIN(coalesce(CAST(stock.leverkusen AS INT), 0)) AS leverkusen
FROM (
SELECT
b.firma,
b.ordernumber,
b.LBAPNR,
coalesce(CAST(b.Bestand_Witten AS INT), 0) AS witten,
coalesce(CAST(b.Bestand_Bottrop AS INT), 0) AS bottrop,
coalesce(CAST(b.Bestand_Recklinghausen AS INT), 0) AS recklinghausen,
coalesce(CAST(b.Bestand_Haan AS INT), 0) AS haan,
coalesce(CAST(b.Bestand_Leverkusen AS INT), 0) AS leverkusen
FROM (
SELECT
t1.LBFIRM AS firma,
t1.LBARTE AS ordernumber,
t1.LBAPNR,
t1.Bestand AS Bestand_Witten,
t2.Bestand AS Bestand_Bottrop,
t3.Bestand AS Bestand_Recklinghausen,
t4.Bestand AS Bestand_Leverkusen,
t5.Bestand AS Bestand_Haan
FROM (
SELECT
LBFIRM,
LBARTE,
LBAPNR,
COALESCE(SUM(LBLMNG), 0) AS Bestand
FROM IWMV2R1DTA.LBST00
WHERE LBFIRM = 3 AND (LBLAGN = 410 OR LBLAGN = 450) AND LBSTAT = 'A'
GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
HAVING ((LBARTE > 0) AND (LBDISP = 'K'))) t1
LEFT JOIN (
SELECT
LBFIRM,
LBARTE,
LBAPNR,
COALESCE(SUM(LBLMNG), 0) AS Bestand
FROM IWMV2R1DTA.LBST00
WHERE LBFIRM = 3 AND (LBLAGN = 810 OR LBLAGN = 850) AND LBSTAT = 'A'
GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
HAVING ((LBARTE > 0) AND (LBDISP = 'K'))) t2
ON t1.LBARTE = t2.LBARTE AND t1.LBAPNR = t2.LBAPNR
LEFT JOIN (
SELECT
LBFIRM,
LBARTE,
LBAPNR,
COALESCE(SUM(LBLMNG), 0) AS Bestand
FROM IWMV2R1DTA.LBST00
WHERE LBFIRM = 3 AND (LBLAGN = 600 OR LBLAGN = 615 OR LBLAGN = 610) AND LBSTAT = 'A'
GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
HAVING ((LBARTE > 0) AND (LBDISP = 'K'))
) t3
ON t1.LBARTE = t3.LBARTE AND t1.LBAPNR = t3.LBAPNR
LEFT JOIN (
SELECT
LBFIRM,
LBARTE,
LBAPNR,
COALESCE(SUM(LBLMNG), 0) AS Bestand
FROM IWMV2R1DTA.LBST00
WHERE LBFIRM = 3 AND (LBLAGN = 661 OR LBLAGN = 665 OR LBLAGN = 667) AND LBSTAT = 'A'
GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
HAVING ((LBARTE > 0) AND (LBDISP = 'K'))
) t4
ON t1.LBARTE = t4.LBARTE AND t1.LBAPNR = t4.LBAPNR
LEFT JOIN (
SELECT
LBFIRM,
LBARTE,
LBAPNR,
COALESCE(SUM(LBLMNG), 0) AS Bestand
FROM IWMV2R1DTA.LBST00
WHERE LBFIRM = 3 AND (LBLAGN = 500 OR LBLAGN = 550 OR LBLAGN = 510) AND LBSTAT = 'A'
GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
HAVING ((LBARTE > 0) AND (LBDISP = 'K'))
) t5
ON t1.LBARTE = t5.LBARTE AND t1.LBAPNR = t5.LBAPNR
UNION
SELECT
t1.LBFIRM AS firma,
t1.LBARTE AS ordernumber,
t1.LBAPNR,
t1.Bestand AS Bestand_Witten,
t2.Bestand AS Bestand_Bottrop,
t3.Bestand AS Bestand_Recklinghausen,
t4.Bestand AS Bestand_Leverkusen,
t5.Bestand AS Bestand_Haan
FROM (
SELECT
LBFIRM,
LBARTE,
LBAPNR,
COALESCE(SUM(LBLMNG), 0) AS Bestand
FROM IWMV2R1DTA.LBST00
WHERE LBFIRM = 1 AND (LBLAGN = 100 OR LBLAGN = 110 OR LBLAGN = 150) AND LBSTAT = 'A'
GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
HAVING ((LBARTE > 0) AND (LBDISP = 'K'))) t1
LEFT JOIN (
SELECT
LBFIRM,
LBARTE,
LBAPNR,
COALESCE(SUM(LBLMNG), 0) AS Bestand
FROM IWMV2R1DTA.LBST00
WHERE LBFIRM = 1 AND (LBLAGN = 700 OR LBLAGN = 710 OR LBLAGN = 750) AND LBSTAT = 'A'
GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
HAVING ((LBARTE > 0) AND (LBDISP = 'K'))) t2
ON t1.LBARTE = t2.LBARTE AND t1.LBAPNR = t2.LBAPNR
LEFT JOIN (
SELECT
LBFIRM,
LBARTE,
LBAPNR,
COALESCE(SUM(LBLMNG), 0) AS Bestand
FROM IWMV2R1DTA.LBST00
WHERE LBFIRM = 1 AND (LBLAGN = 600 OR LBLAGN = 310 OR LBLAGN = 315) AND LBSTAT = 'A'
GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
HAVING ((LBARTE > 0) AND (LBDISP = 'K'))
) t3
ON t1.LBARTE = t3.LBARTE AND t1.LBAPNR = t3.LBAPNR
LEFT JOIN (
SELECT
LBFIRM,
LBARTE,
LBAPNR,
COALESCE(SUM(LBLMNG), 0) AS Bestand
FROM IWMV2R1DTA.LBST00
WHERE
LBFIRM = 1 AND (LBLAGN = 360 OR LBLAGN = 361 OR LBLAGN = 950 OR LBLAGN = 365) AND
LBSTAT = 'A'
GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
HAVING ((LBARTE > 0) AND (LBDISP = 'K'))
) t4
ON t1.LBARTE = t4.LBARTE AND t1.LBAPNR = t4.LBAPNR
LEFT JOIN (
SELECT
LBFIRM,
LBARTE,
LBAPNR,
COALESCE(SUM(LBLMNG), 0) AS Bestand
FROM IWMV2R1DTA.LBST00
WHERE
LBFIRM = 1 AND (LBLAGN = 250 OR LBLAGN = 210 OR LBLAGN = 200) AND LBSTAT = 'A'
GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP
HAVING ((LBARTE > 0) AND (LBDISP = 'K'))
) t5
ON t1.LBARTE = t5.LBARTE AND t1.LBAPNR = t5.LBAPNR) b
WHERE CONCAT(CONCAT(b.firma, '-'), b.ordernumber) NOT IN (
SELECT CONCAT(CONCAT(ar.ARFIRM, '-'), ar.ARARTE) AS ordernumber
FROM IWMV2R1DTA.ARTS00 ar
WHERE ar.ARATAR = 'D'
)
) stock
RIGHT JOIN (
SELECT
ARARTE AS set_ordernumber,
VSARTK AS ordernumber,
VSFIRM AS firma
FROM IWMV2R1DTA.VSET00 vset
LEFT JOIN IWMV2R1DTA.ARTS00 ar
ON vset.VSARTE = ar.ARARTE
WHERE ARATAR = 'D') dyn_articles
ON stock.ordernumber = dyn_articles.ordernumber AND stock.firma = dyn_articles.firma
GROUP BY dyn_articles.firma, dyn_articles.set_ordernumber
TargetDatapool: iwm_articles_stock_exhibition_dynsets
Metadata
Erkannte Konfiguration
Diese Sicht basiert auf der aktuell lesbaren Legacy-Datei und hilft beim Vergleichen vor dem Import.
| Engine | Import |
|---|---|
| Source_Config | oster400 |
| Target_Config | importEngine |
| Mysql_Engine | InnoDB |
| Default_Charset | latin1 |
| Source_Connection_Type | database |
| Source_Database_Connection_Service | DatabaseConnectionService |
| Queries | [ { "Query": { "GetQuery": "SELECT dyn_articles.firma, dyn_articles.set_ordernumber AS ordernumber, MIN(coalesce(CAST(stock.witten AS INT), 0)) AS witten, MIN(coalesce(CAST(stock.bottrop AS INT), 0)) AS bottrop, MIN(coalesce(CAST(stock.recklinghausen AS INT), 0)) AS recklinghausen, MIN(coalesce(CAST(stock.haan AS INT), 0)) AS haan, MIN(coalesce(CAST(stock.leverkusen AS INT), 0)) AS leverkusen FROM ( SELECT b.firma, b.ordernumber, b.LBAPNR, coalesce(CAST(b.Bestand_Witten AS INT), 0) AS witten, coalesce(CAST(b.Bestand_Bottrop AS INT), 0) AS bottrop, coalesce(CAST(b.Bestand_Recklinghausen AS INT), 0) AS recklinghausen, coalesce(CAST(b.Bestand_Haan AS INT), 0) AS haan, coalesce(CAST(b.Bestand_Leverkusen AS INT), 0) AS leverkusen FROM ( SELECT t1.LBFIRM AS firma, t1.LBARTE AS ordernumber, t1.LBAPNR, t1.Bestand AS Bestand_Witten, t2.Bestand AS Bestand_Bottrop, t3.Bestand AS Bestand_Recklinghausen, t4.Bestand AS Bestand_Leverkusen, t5.Bestand AS Bestand_Haan FROM ( SELECT LBFIRM, LBARTE, LBAPNR, COALESCE(SUM(LBLMNG), 0) AS Bestand FROM IWMV2R1DTA.LBST00 WHERE LBFIRM = 3 AND (LBLAGN = 410 OR LBLAGN = 450) AND LBSTAT = 'A' GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP HAVING ((LBARTE > 0) AND (LBDISP = 'K'))) t1 LEFT JOIN ( SELECT LBFIRM, LBARTE, LBAPNR, COALESCE(SUM(LBLMNG), 0) AS Bestand FROM IWMV2R1DTA.LBST00 WHERE LBFIRM = 3 AND (LBLAGN = 810 OR LBLAGN = 850) AND LBSTAT = 'A' GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP HAVING ((LBARTE > 0) AND (LBDISP = 'K'))) t2 ON t1.LBARTE = t2.LBARTE AND t1.LBAPNR = t2.LBAPNR LEFT JOIN ( SELECT LBFIRM, LBARTE, LBAPNR, COALESCE(SUM(LBLMNG), 0) AS Bestand FROM IWMV2R1DTA.LBST00 WHERE LBFIRM = 3 AND (LBLAGN = 600 OR LBLAGN = 615 OR LBLAGN = 610) AND LBSTAT = 'A' GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP HAVING ((LBARTE > 0) AND (LBDISP = 'K')) ) t3 ON t1.LBARTE = t3.LBARTE AND t1.LBAPNR = t3.LBAPNR LEFT JOIN ( SELECT LBFIRM, LBARTE, LBAPNR, COALESCE(SUM(LBLMNG), 0) AS Bestand FROM IWMV2R1DTA.LBST00 WHERE LBFIRM = 3 AND (LBLAGN = 661 OR LBLAGN = 665 OR LBLAGN = 667) AND LBSTAT = 'A' GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP HAVING ((LBARTE > 0) AND (LBDISP = 'K')) ) t4 ON t1.LBARTE = t4.LBARTE AND t1.LBAPNR = t4.LBAPNR LEFT JOIN ( SELECT LBFIRM, LBARTE, LBAPNR, COALESCE(SUM(LBLMNG), 0) AS Bestand FROM IWMV2R1DTA.LBST00 WHERE LBFIRM = 3 AND (LBLAGN = 500 OR LBLAGN = 550 OR LBLAGN = 510) AND LBSTAT = 'A' GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP HAVING ((LBARTE > 0) AND (LBDISP = 'K')) ) t5 ON t1.LBARTE = t5.LBARTE AND t1.LBAPNR = t5.LBAPNR UNION SELECT t1.LBFIRM AS firma, t1.LBARTE AS ordernumber, t1.LBAPNR, t1.Bestand AS Bestand_Witten, t2.Bestand AS Bestand_Bottrop, t3.Bestand AS Bestand_Recklinghausen, t4.Bestand AS Bestand_Leverkusen, t5.Bestand AS Bestand_Haan FROM ( SELECT LBFIRM, LBARTE, LBAPNR, COALESCE(SUM(LBLMNG), 0) AS Bestand FROM IWMV2R1DTA.LBST00 WHERE LBFIRM = 1 AND (LBLAGN = 100 OR LBLAGN = 110 OR LBLAGN = 150) AND LBSTAT = 'A' GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP HAVING ((LBARTE > 0) AND (LBDISP = 'K'))) t1 LEFT JOIN ( SELECT LBFIRM, LBARTE, LBAPNR, COALESCE(SUM(LBLMNG), 0) AS Bestand FROM IWMV2R1DTA.LBST00 WHERE LBFIRM = 1 AND (LBLAGN = 700 OR LBLAGN = 710 OR LBLAGN = 750) AND LBSTAT = 'A' GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP HAVING ((LBARTE > 0) AND (LBDISP = 'K'))) t2 ON t1.LBARTE = t2.LBARTE AND t1.LBAPNR = t2.LBAPNR LEFT JOIN ( SELECT LBFIRM, LBARTE, LBAPNR, COALESCE(SUM(LBLMNG), 0) AS Bestand FROM IWMV2R1DTA.LBST00 WHERE LBFIRM = 1 AND (LBLAGN = 600 OR LBLAGN = 310 OR LBLAGN = 315) AND LBSTAT = 'A' GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP HAVING ((LBARTE > 0) AND (LBDISP = 'K')) ) t3 ON t1.LBARTE = t3.LBARTE AND t1.LBAPNR = t3.LBAPNR LEFT JOIN ( SELECT LBFIRM, LBARTE, LBAPNR, COALESCE(SUM(LBLMNG), 0) AS Bestand FROM IWMV2R1DTA.LBST00 WHERE LBFIRM = 1 AND (LBLAGN = 360 OR LBLAGN = 361 OR LBLAGN = 950 OR LBLAGN = 365) AND LBSTAT = 'A' GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP HAVING ((LBARTE > 0) AND (LBDISP = 'K')) ) t4 ON t1.LBARTE = t4.LBARTE AND t1.LBAPNR = t4.LBAPNR LEFT JOIN ( SELECT LBFIRM, LBARTE, LBAPNR, COALESCE(SUM(LBLMNG), 0) AS Bestand FROM IWMV2R1DTA.LBST00 WHERE LBFIRM = 1 AND (LBLAGN = 250 OR LBLAGN = 210 OR LBLAGN = 200) AND LBSTAT = 'A' GROUP BY LBFIRM, LBARTE, LBAPNR, LBDISP HAVING ((LBARTE > 0) AND (LBDISP = 'K')) ) t5 ON t1.LBARTE = t5.LBARTE AND t1.LBAPNR = t5.LBAPNR) b WHERE CONCAT(CONCAT(b.firma, '-'), b.ordernumber) NOT IN ( SELECT CONCAT(CONCAT(ar.ARFIRM, '-'), ar.ARARTE) AS ordernumber FROM IWMV2R1DTA.ARTS00 ar WHERE ar.ARATAR = 'D' ) ) stock RIGHT JOIN ( SELECT ARARTE AS set_ordernumber, VSARTK AS ordernumber, VSFIRM AS firma FROM IWMV2R1DTA.VSET00 vset LEFT JOIN IWMV2R1DTA.ARTS00 ar ON vset.VSARTE = ar.ARARTE WHERE ARATAR = 'D') dyn_articles ON stock.ordernumber = dyn_articles.ordernumber AND stock.firma = dyn_articles.firma GROUP BY dyn_articles.firma, dyn_articles.set_ordernumber", "TargetDatapool": "iwm_articles_stock_exhibition_dynsets" } } ] |