SHARE
TWEET

Untitled

a guest Dec 3rd, 2019 81 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT
  2. warenKorb.WARKBNTZ,
  3. warenKorb.WARKWARK,
  4. warenKorb.WARKWKAR,
  5. warenKorb.WARKVADR,
  6. warenKorb.WARKVSAR,
  7. warenKorb.WARKANGX,
  8. warenKorb.WARKSTAT,
  9. warenKorb.WARKWAEH,
  10. kontierung.KDKOKONT,
  11. kontierung.KDKOKOTX,
  12. kostenStelle.KDKSKOST,
  13. kostenStelle.KDKSTEXT,
  14. warenKorbData.WKDTKRVX,
  15. benutzer.BNTNNAM1,
  16. benutzer.BNTNNAM2,
  17. benutzerGruppe.GPSBGRPN,
  18. lieferant.LFNTNAM1,
  19. supplier_ociSupplier_table.lfsswert as supplier_ociSupplier,
  20. supplier_ocidublette_table.lfsswert as supplier_ocidublette,
  21. supplier_ocidulette_table.lfsswert as supplier_ocidulette,
  22. warenKorb.WARKSTAT,
  23. warenKorb.WARKWAEH,
  24. wkdtartx,
  25. wkdtbmng,
  26. wkdtccty,
  27. wkdtccid,
  28. round( warenKorbData.wkdtposw/sswaek00.waekumef,2) as article_value_line_in_euros,
  29. round( warenKorbData.wkdtppeh/sswaek00.waekumef,2) as article_value_per_einheit_in_euros,
  30. eclass_v51.ecdeklax as eclass_v51,
  31. eclass_v51.ecdename as eclass_v51_name,
  32. eclass_v51_1.ecdeklax as eclass_v51_1,
  33. eclass_v51_1.ecdename as eclass_v51_1_name,
  34. eclass_v51_2.ecdeklax as eclass_v51_2,
  35. eclass_v51_2.ecdename as eclass_v51_2_name,
  36. eclass_v51_3.ecdeklax as eclass_v51_3,
  37. eclass_v51_3.ecdename as eclass_v51_3_name,
  38. eclass_v71.ecdeklax as eclass_v71,
  39. eclass_v71.ecdename as eclass_v71_name,
  40. eclass_v71_1.ecdeklax as eclass_v71_1,
  41. eclass_v71_1.ecdename as eclass_v71_1_name,
  42. eclass_v71_2.ecdeklax as eclass_v71_2,
  43. eclass_v71_2.ecdename as eclass_v71_2_name,
  44. eclass_v71_3.ecdeklax as eclass_v71_3,
  45. eclass_v71_3.ecdename as eclass_v71_3_name,
  46. wkdtkutx,
  47. kunde.*,
  48. DUNS_Nummer_tbl.protwert as client_DUNS_Nummer,
  49. Handelsregisternummer_tbl.protwert as client_duns_Handelsregisternummer,
  50. Position_tbl.protwert as client_duns_position,
  51. Branche_tbl.protwert as client_Branche_code,
  52. Beschaeftigte_tbl.protwert as client_duns_Beschaeftigte,
  53. duns_description.duns_description as client_branch,
  54. supplier_LAND_table.lfsswert as supplier_LAND,
  55. supplier_Name_table.lfsswert as supplier_Name,
  56. supplier_intern_table.lfsswert as supplier_intern,
  57. supplier_language_table.lfsswert as supplier_language,
  58. supplier_lfntgroup_table.lfsswert as supplier_lfntgroup,
  59. supplier_mroSupplierNew_table.lfsswert as supplier_mroSupplierNew,
  60. supplier_schnittstelle_table.lfsswert as supplier_schnittstelle,
  61. supplier_systemlieferant_table.lfsswert as supplier_systemlieferant,
  62. supplier_ustID_table.lfsswert as supplier_ustID,
  63. lieferant.LFNTLFNT,
  64. lieferant.LFNTKUTX,
  65. lieferant.LFNTNAM1,
  66. lieferant.LFNTNAM2,
  67. lieferant.LFNTPLZ,
  68. lieferant.LFNTORT,
  69. lieferant.LFNTPSTF,
  70. lieferant.LFNTMBWR,
  71. lieferant.LFNTWAEH,
  72. CASE lieferant.LFNTLAND WHEN 'D' THEN 'DE' ELSE lieferant.LFNTLAND END as lfntland,
  73. warenKorbData.*,
  74. bestellungAdresse.*,
  75. betreuer.prottext as betreuer,
  76. konzname,
  77. plz.lat,
  78. plz.lon,
  79. CASE WHEN plz.lat IS NULL THEN 0 ELSE plz.lat END AS lat,
  80. CASE WHEN plz.lon IS NULL THEN 0 ELSE plz.lon END AS lon,
  81. STR_TO_DATE(
  82.        CONCAT('',WARKDBST, lpad(WARKZBST,6,0)),
  83.     '%Y%m%d%H%i%s'
  84.  ) as order_timestamp
  85. FROM utf8.sswark00 AS warenKorb
  86. INNER JOIN utf8.sswkdt00 AS warenKorbData ON warenKorb.WARKWARK = warenKorbData.WKDTWARK AND warenKorbData.WKDTLFNT = warenKorb.WARKLFNT
  87. INNER JOIN utf8.sskund00 AS kunde ON warkkund=kundkund AND kundadra=3
  88. INNER JOIN utf8.sswkla00 AS bestellungAdresse ON WKLAWARK = WARKWARK
  89. left join utf8.ssprot00 as betreuer on protwert=kundkund and protkeyx='Y' and kundadra=3 and kundlfnt=0
  90. left join utf8.sskonz00 on konzkund=kundkund and kundadra=3 and kundlfnt=0
  91. LEFT JOIN utf8.s4_plz_geo_code AS plz ON plz.country_code = bestellungAdresse.WKLALAND AND plz.zip_code = bestellungAdresse.WKLAPLZ
  92. LEFT JOIN utf8.sslfnt00 AS lieferant ON warenKorbData.WKDTLFNT = lieferant.LFNTLFNT
  93. LEFT JOIN utf8.sskdks00 AS kostenStelle ON warenKorbData.WKDTCCID = kostenStelle.KDKSKOST AND kostenStelle.KDKSKUND = warenKorb.WARKKUND
  94. LEFT JOIN utf8.sskdko00 AS kontierung ON warenKorbData.WKDTCCTY = kontierung.KDKOKONT AND kontierung.KDKOBNTZ = 0 AND kontierung.KDKOKUND = warenKorb.WARKKUND
  95. LEFT JOIN utf8.ssbntn00 AS benutzer ON benutzer.BNTNBNTZ = warenKorb.WARKBNTZ
  96. LEFT JOIN utf8.ssgpsb00 AS benutzerGruppe ON benutzerGruppe.GPSBGRPN = benutzer.BNTNBNTZ AND benutzerGruppe.GPSBKUND = benutzer.BNTNKUND
  97. left join ssklkl00 as v51cls on v51cls.klklkund=0 and v51cls.klkllfnt=lfntlfnt and v51cls.klklartx=wkdtartx and v51cls.klklvers='5.1'
  98. left join ssecde00 as eclass_v51 on eclass_v51.ecdevers=v51cls.klklvers and eclass_v51.ecdeklax=v51cls.klklklax and eclass_v51.ecdespra='de' and eclass_v51.ecdeland='DE'
  99. left join ssecde00 as eclass_v51_1 on eclass_v51_1.ecdevers=v51cls.klklvers and eclass_v51_1.ecdeklax=concat(substring(v51cls.klklklax,1,2),'000000') and eclass_v51_1.ecdespra='de' and eclass_v51_1.ecdeland='DE'
  100. left join ssecde00 as eclass_v51_2 on eclass_v51_2.ecdevers=v51cls.klklvers and eclass_v51_2.ecdeklax=concat(substring(v51cls.klklklax,1,4),'0000') and eclass_v51_2.ecdespra='de' and eclass_v51_2.ecdeland='DE'
  101. left join ssecde00 as eclass_v51_3 on eclass_v51_3.ecdevers=v51cls.klklvers and eclass_v51_3.ecdeklax=concat(substring(v51cls.klklklax,1,4),'0000') and eclass_v51_3.ecdespra='de' and eclass_v51_3.ecdeland='DE'
  102. left join ssecde00 as eclass_v51_4 on eclass_v51_4.ecdevers=v51cls.klklvers and eclass_v51_4.ecdeklax=concat(substring(v51cls.klklklax,1,6),'00') and eclass_v51_4.ecdespra='de' and eclass_v51_4.ecdeland='DE'
  103. left join ssklkl00 as v71cls on v71cls.klklkund=0 and v71cls.klkllfnt=lfntlfnt and v71cls.klklartx=wkdtartx and v71cls.klklvers='7.1'
  104. left join ssecde00 as eclass_v71 on eclass_v71.ecdevers=v71cls.klklvers and eclass_v71.ecdeklax=v71cls.klklklax and eclass_v71.ecdespra='de' and eclass_v71.ecdeland='DE'
  105. left join ssecde00 as eclass_v71_1 on eclass_v71_1.ecdevers=v71cls.klklvers and eclass_v71_1.ecdeklax=concat(substring(v71cls.klklklax,1,2),'000000') and eclass_v71_1.ecdespra='de' and eclass_v71_1.ecdeland='DE'
  106. left join ssecde00 as eclass_v71_2 on eclass_v71_2.ecdevers=v71cls.klklvers and eclass_v71_2.ecdeklax=concat(substring(v71cls.klklklax,1,4),'0000') and eclass_v71_2.ecdespra='de' and eclass_v71_2.ecdeland='DE'
  107. left join ssecde00 as eclass_v71_3 on eclass_v71_3.ecdevers=v71cls.klklvers and eclass_v71_3.ecdeklax=concat(substring(v71cls.klklklax,1,6),'00') and eclass_v71_3.ecdespra='de' and eclass_v71_3.ecdeland='DE'
  108. left join ssprot00 as DUNS_Nummer_tbl  on DUNS_Nummer_tbl.protdatu=kunde.kundkund and DUNS_Nummer_tbl.protkeyx='DU' and DUNS_Nummer_tbl.prottext='DUNS-Nummer'
  109. left join ssprot00 as Handelsregisternummer_tbl on Handelsregisternummer_tbl.protdatu=kunde.kundkund and Handelsregisternummer_tbl.protkeyx='DU' and Handelsregisternummer_tbl.prottext='Handelsregisternummer'
  110. left join ssprot00 as Position_tbl on Position_tbl.protdatu=kunde.kundkund and Position_tbl.protkeyx='DU' and Position_tbl.prottext='Position'
  111. left join ssprot00 as Branche_tbl on Branche_tbl.protdatu=kunde.kundkund and Branche_tbl.protkeyx='DU' and Branche_tbl.prottext='Branche'
  112. left join ssprot00 as Beschaeftigte_tbl on Beschaeftigte_tbl.protdatu=kunde.kundkund and Beschaeftigte_tbl.protkeyx='DU' and Beschaeftigte_tbl.prottext='Beschaeftigte'
  113. left join duns_description on duns_id = Branche_tbl.protwert
  114. left join ssbnsp00 on bnspbntz = warkbntz
  115. left join sskdsp00 on kdspkund = kundkund
  116. left join sswaek00 on waekwaeh=WARKWAEH and waekabdt=substring(warkdbst,1,6)
  117. left join sslfss00 as supplier_LAND_table on supplier_LAND_table.lfsslfnt=lfntlfnt and supplier_LAND_table.lfsskeyx='LAND'
  118. left join sslfss00 as supplier_Name_table on supplier_Name_table.lfsslfnt=lfntlfnt and supplier_Name_table.lfsskeyx='Name'
  119. left join sslfss00 as supplier_intern_table on supplier_intern_table.lfsslfnt=lfntlfnt and supplier_intern_table.lfsskeyx='intern'
  120. left join sslfss00 as supplier_language_table on supplier_language_table.lfsslfnt=lfntlfnt and supplier_language_table.lfsskeyx='language'
  121. left join sslfss00 as supplier_lfntgroup_table on supplier_lfntgroup_table.lfsslfnt=lfntlfnt and supplier_lfntgroup_table.lfsskeyx='lfntgroup'
  122. left join sslfss00 as supplier_mroSupplierNew_table on supplier_mroSupplierNew_table.lfsslfnt=lfntlfnt and supplier_mroSupplierNew_table.lfsskeyx='mroSupplierNew'
  123. left join sslfss00 as supplier_schnittstelle_table on supplier_schnittstelle_table.lfsslfnt=lfntlfnt and supplier_schnittstelle_table.lfsskeyx='schnittstelle'
  124. left join sslfss00 as supplier_systemlieferant_table on supplier_systemlieferant_table.lfsslfnt=lfntlfnt and supplier_systemlieferant_table.lfsskeyx='systemlieferant'
  125. left join sslfss00 as supplier_ustID_table on supplier_ustID_table.lfsslfnt=lfntlfnt and supplier_ustID_table.lfsskeyx='ustID'
  126. left join sslfss00 as supplier_geloescht_table on supplier_geloescht_table.lfsslfnt=lfntlfnt and supplier_geloescht_table.lfsskeyx='geloescht'
  127. left join sslfss00 as supplier_ociSupplier_table on supplier_ociSupplier_table.lfsslfnt=lfntlfnt and supplier_ociSupplier_table.lfsskeyx='ociSupplier'
  128. left join sslfss00 as supplier_ocidublette_table on supplier_ocidublette_table.lfsslfnt=lfntlfnt and supplier_ocidublette_table.lfsskeyx='ocidublette'
  129. left join sslfss00 as supplier_ocidulette_table on supplier_ocidulette_table.lfsslfnt=lfntlfnt and supplier_ocidulette_table.lfsskeyx='ocidulette'
  130. WHERE warenKorb.WARKWKAR IN (2,3)
  131. AND bnspbntz IS NULL
  132. AND kdspkund IS NULL
  133. warenKorb.warkdbst > 20191000
  134. warenKorb.warkdbst < 20191100
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top