API tools faq
paste
Advertisement
horozov86

Task - 6 SQL

horozov86
Aug 27th, 2024 (edited)
260
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Oracle 11 11.70 KB | None | 0 0
raw download clone embed print report
  1. SELECT
  2.  
  3. CONCAT(CONCAT(SPERSO.VORNAME, ' '),SPERSO.NAME) AS "Sales rep employee name"
  4. ,SPERSO.PERS_NR AS "Sales rep number"
  5. ,ASENDUNG.ABS_NAME1 AS "Name of the sender"
  6. ,ASENDUNG.EMP_NAME1 AS "Name of the receiver"
  7. ,SCUST.INFO AS "Reason for cancelation"
  8. ,ATOURCUSTOMFIELDV.CFBOOLEAN2 AS "CMR check box"
  9. ,asendung.check5
  10. ,ATASEND.TAINFO9
  11. ,AKopf.Auftragsnummer
  12. ,ASendung.Sendungsnummer
  13. ,ATour.tournummer
  14. ,ATour.EXTTOURNUMMER
  15. ,FrachtAG.NAME1
  16. ,ASendung.Info4
  17. ,(SELECT SUM(STATISTIKBETRAGRG) FROM akopf ak left join asdgabrechnung asd ON ak.nr = asd.akopfnr left join skunden ON ak.FRACHTAGNR = skunden.nr left join skufaktu ON skunden.nr = skufaktu.nr left join asendung ase ON ak.nr = ase.akopfnr left join atasend  AT ON ase.sendungid = AT.asendungid left join atour ON AT.tournr = atour.nr left join UDX_VIEW_CUSTOMREALLOCCNTOUR ON atour. nr = UDX_VIEW_CUSTOMREALLOCCNTOUR.tournr WHERE ak.nr = akopf.nr AND  ase.sendungid = asendung.sendungid  AND AT.nr = atasend.nr GROUP BY asendung.sendungid,AT.nr)
  18. ,(SELECT SUM(BETRAGRG) FROM akopf ak left join asdgabrechnung asd ON ak.nr = asd.akopfnr left join skunden ON ak.FRACHTAGNR = skunden.nr left join skufaktu ON skunden.nr = skufaktu.nr left join asendung ase ON ak.nr = ase.akopfnr left join atasend  AT ON ase.sendungid = AT.asendungid left join atour ON AT.tournr = atour.nr left join UDX_VIEW_CUSTOMREALLOCCNTOUR ON atour. nr = UDX_VIEW_CUSTOMREALLOCCNTOUR.tournr WHERE ak.nr = akopf.nr AND  ase.sendungid = asendung.sendungid  AND AT.nr = atasend.nr GROUP BY asendung.sendungid,AT.nr)
  19. ,CASE WHEN AKOPF.AUFTRAGSREFERENZ3 IS NULL THEN PVORGABEWGS.ISOWAEHRUNGSCODE ELSE AKOPF.AUFTRAGSREFERENZ3 END
  20. ,(SELECT SUM(STATISTIKBBETRAG/asd.ABRGRUPPEITEMSCOUNT ) FROM atasend AT left join asdgabrechnung asd ON AT.nr = asd.ataid left join asendung ase ON ase.sendungid = AT.asendungid  left join akopf  ak ON ase.akopfnr = ak.nr left join akondition ON asd.AKONDITION = akondition.nr left join skondi0 ON akondition.KONDITIONSNR = skondi0.nr left join ASDGABRECHNUNGPOS  asdp ON asd.AABRGRUPPE = asdp.ABRGRUPPE left join SLEISTUNGSARTEN s ON asdp.IDLEISTUNGSART = s.ID  WHERE  skondi0.nr NOT IN (910368,3996738) AND AT.nr = atasend.nr AND  ase.sendungid = asendung.sendungid AND UPPER(s.MATCHCODE) <> UPPER('sconto') AND UPPER(s.MATCHCODE) <> UPPER('ADJUSTMENT') AND UPPER(s.MATCHCODE) <> UPPER('CURRATE') AND akopf.nr = ak.nr GROUP BY AT.nr )
  21. ,(CASE WHEN (SELECT SUM(STATISTIKBETRAGRG) FROM akopf ak left join asdgabrechnung asd ON ak.nr = asd.akopfnr left join skunden ON ak.FRACHTAGNR = skunden.nr left join skufaktu ON skunden.nr = skufaktu.nr left join asendung ase ON ak.nr = ase.akopfnr left join atasend  AT ON ase.sendungid = AT.asendungid left join atour ON AT.tournr = atour.nr left join UDX_VIEW_CUSTOMREALLOCCNTOUR ON atour. nr = UDX_VIEW_CUSTOMREALLOCCNTOUR.tournr WHERE ak.nr = akopf.nr AND  ase.sendungid = asendung.sendungid  AND AT.nr = atasend.nr GROUP BY asendung.sendungid,AT.nr) IS NULL THEN 0 ELSE (SELECT SUM(STATISTIKBETRAGRG) FROM akopf ak left join asdgabrechnung asd ON ak.nr = asd.akopfnr left join skunden ON ak.FRACHTAGNR = skunden.nr left join skufaktu ON skunden.nr = skufaktu.nr left join asendung ase ON ak.nr = ase.akopfnr left join atasend  AT ON ase.sendungid = AT.asendungid left join atour ON AT.tournr = atour.nr left join UDX_VIEW_CUSTOMREALLOCCNTOUR ON atour. nr = UDX_VIEW_CUSTOMREALLOCCNTOUR.tournr WHERE ak.nr = akopf.nr AND  ase.sendungid = asendung.sendungid  AND AT.nr = atasend.nr GROUP BY asendung.sendungid,AT.nr)END - CASE WHEN (SELECT SUM(STATISTIKBBETRAG/asd.ABRGRUPPEITEMSCOUNT ) FROM atasend AT left join asdgabrechnung asd ON AT.nr = asd.ataid left join asendung ase ON ase.sendungid = AT.asendungid  left join akopf  ak ON ase.akopfnr = ak.nr left join akondition ON asd.AKONDITION = akondition.nr left join skondi0 ON akondition.KONDITIONSNR = skondi0.nr left join ASDGABRECHNUNGPOS  asdp ON asd.AABRGRUPPE = asdp.ABRGRUPPE left join SLEISTUNGSARTEN s ON asdp.IDLEISTUNGSART = s.ID  WHERE  skondi0.nr NOT IN (910368,3996738) AND AT.nr = atasend.nr AND  ase.sendungid = asendung.sendungid AND UPPER(s.MATCHCODE) <> UPPER('sconto')AND UPPER(s.MATCHCODE) <> UPPER('ADJUSTMENT') AND UPPER(s.MATCHCODE) <> UPPER('CURRATE') AND akopf.nr = ak.nr GROUP BY AT.nr )IS NULL THEN 0 ELSE (SELECT SUM(STATISTIKBBETRAG/asd.ABRGRUPPEITEMSCOUNT ) FROM atasend AT left join asdgabrechnung asd ON AT.nr = asd.ataid left join asendung ase ON ase.sendungid = AT.asendungid  left join akopf  ak ON ase.akopfnr = ak.nr left join akondition ON asd.AKONDITION = akondition.nr left join skondi0 ON akondition.KONDITIONSNR = skondi0.nr left join ASDGABRECHNUNGPOS  asdp ON asd.AABRGRUPPE = asdp.ABRGRUPPE left join SLEISTUNGSARTEN s ON asdp.IDLEISTUNGSART = s.ID  WHERE  skondi0.nr NOT IN (910368,3996738) AND AT.nr = atasend.nr AND  ase.sendungid = asendung.sendungid AND UPPER(s.MATCHCODE) <> UPPER('sconto')AND UPPER(s.MATCHCODE) <> UPPER('ADJUSTMENT') AND UPPER(s.MATCHCODE) <> UPPER('CURRATE') AND akopf.nr = ak.nr GROUP BY AT.nr ) END )
  22. ,akopf.AEXTERNAUFTRAGSNR
  23. ,(SELECT ISOALPHA2CODE FROM SLAND WHERE ATASend.VON_LAND = SLAND.LAND)
  24. ,ATASend.VON_PLZ
  25. ,ATASend.VON_ORT1
  26. ,(SELECT ISOALPHA2CODE FROM SLAND WHERE ATASend.NACH_LAND = SLAND.LAND)
  27. ,ATASend.NACH_PLZ
  28. ,ATASend.NACH_ORT1
  29. ,TO_DATE(TO_CHAR(ASendung.VONLADEDATUMZEIT,'hh24:mi'),'hh24:mi')
  30. ,TO_DATE(TO_CHAR(ASendung.BISLADEDATUMZEIT,'hh24:mi'),'hh24:mi')
  31. ,TO_DATE(TO_CHAR(ASendung.VONLADEDATUMZEIT, 'dd.mm.yyyy'), 'dd.mm.yyyy')
  32. ,TO_DATE(TO_CHAR(ASendung.BISLADEDATUMZEIT, 'dd.mm.yyyy'), 'dd.mm.yyyy')
  33. ,TO_DATE(TO_CHAR(ATASEND.LOADINGDATEPLANNEDSTART,'hh24:mi'),'hh24:mi')
  34. ,TO_DATE(TO_CHAR(ATASEND.LOADINGDATEPLANNEDEND,'hh24:mi'),'hh24:mi')
  35. ,TO_DATE(TO_CHAR(ATASEND.LOADINGDATEPLANNEDSTART, 'dd.mm.yyyy'), 'dd.mm.yyyy')
  36. ,TO_DATE(TO_CHAR(ATASEND.LOADINGDATEPLANNEDEND, 'dd.mm.yyyy'), 'dd.mm.yyyy')
  37. ,TO_DATE(TO_CHAR(Atasend.LADEDATUMSTARTIST,'dd.mm.yyyy'),'dd.mm.yyyy')
  38. ,TO_DATE(TO_CHAR(Atasend.LADEDATUMSTARTIST,'hh24:mi'),'hh24:mi')
  39. ,TO_DATE(TO_CHAR(Atasend.LADEDATUMENDEIST,'dd.mm.yyyy'),'dd.mm.yyyy')
  40. ,TO_DATE(TO_CHAR(Atasend.LADEDATUMENDEIST,'hh24:mi'),'hh24:mi')
  41. ,TO_DATE(TO_CHAR(ASendung.VONLIEFERDATUMZEIT,'hh24:mi'),'hh24:mi')
  42. ,TO_DATE(TO_CHAR(ASendung.BISLIEFERDATUMZEIT,'hh24:mi'),'hh24:mi')
  43. ,TO_DATE(TO_CHAR(ASendung.VONLIEFERDATUMZEIT,'dd.mm.yyyy'),'dd.mm.yyyy')
  44. ,TO_DATE(TO_CHAR(ASendung.BISLIEFERDATUMZEIT, 'dd.mm.yyyy'), 'dd.mm.yyyy')
  45. ,TO_DATE(TO_CHAR(ATASEND.DELIVERYDATEPLANNEDSTART,'hh24:mi'),'hh24:mi')
  46. ,TO_DATE(TO_CHAR(ATASEND.DELIVERYDATEPLANNEDEND,'hh24:mi'),'hh24:mi')
  47. ,TO_DATE(TO_CHAR(ATASEND.DELIVERYDATEPLANNEDSTART,'dd.mm.yyyy'),'dd.mm.yyyy')
  48. ,TO_DATE(TO_CHAR(ATASEND.DELIVERYDATEPLANNEDEND,'dd.mm.yyyy'),'dd.mm.yyyy')
  49. ,TO_DATE(TO_CHAR(ATASEND.LIEFERDATUMSTARTIST, 'dd.mm.yyyy'), 'dd.mm.yyyy')
  50. ,TO_DATE(TO_CHAR(ATASEND.LIEFERDATUMSTARTIST,'hh24:mi'),'hh24:mi')
  51. ,TO_DATE(TO_CHAR(Atasend.LIEFERDATUMENDEIST, 'dd.mm.yyyy'), 'dd.mm.yyyy')
  52. ,TO_DATE(TO_CHAR(Atasend.LIEFERDATUMENDEIST,'hh24:mi'),'hh24:mi')
  53. ,aSENDUNG.INFO16
  54. ,ATour.LKWKZ
  55. ,ATour.ANHAENGERKZ
  56. ,Unternehmer.Name1
  57. ,DECODE(ATASEND.LOCKSTATUS,0,'Free for planning',1,'In plan',2,'Planned',3,'Planning completed',4,'TO finished')
  58. ,HAENGERTYP.Bezeichnung
  59. ,sverkaufsart_sdg.bezeichnung
  60. ,STOURKATEGORIEN.BEZEICHNUNG
  61. ,ATASEND.TAINFO1
  62. ,ATASEND.TAINFO2
  63. ,ATASEND.TAINFO3
  64. ,ATASEND.TAINFO4
  65. ,ATASEND.TAINFO5
  66. ,ATASEND.TAINFO6
  67. ,ATASEND.TAINFO7
  68. ,ATASEND.TAINFO8
  69. ,Atour.TOURZUSATZINFO15
  70. ,suser.fullname
  71. ,SPVERKAEUFER.VORNAME ||' '|| SPVERKAEUFER.NAME
  72. ,TOURUSER.fullname
  73. ,SHierarchieorder.Bezeichnung
  74. ,SHierarchie.Bezeichnung
  75. ,(SELECT LISTAGG(T.T,';') within GROUP (ORDER BY t.t) FROM (SELECT DISTINCT AT.INHALT T , AT.ATASENDNR FROM ATAPOSITION AT)t WHERE  ATASEND.NR = T.ATASENDNR GROUP BY ATASEND.NR)
  76. ,(SELECT SUM(AT.ANZAHL) FROM ATAPOSITION AT WHERE ATASEND.NR = AT.ATASENDNR GROUP BY ATASEND.NR)
  77. ,(SELECT SUM(AT.GEWICHTT) FROM ATAPOSITION AT WHERE ATASEND.NR = AT.ATASENDNR GROUP BY ATASEND.NR)
  78. ,(SELECT SUM(AT.LAENGE) FROM ATAPOSITION AT WHERE ATASEND.NR = AT.ATASENDNR GROUP BY ATASEND.NR)
  79. ,(SELECT SUM(AT.BREITE) FROM ATAPOSITION AT WHERE ATASEND.NR = AT.ATASENDNR GROUP BY ATASEND.NR)
  80. ,(SELECT SUM(AT.HOEHE) FROM ATAPOSITION AT WHERE ATASEND.NR = AT.ATASENDNR GROUP BY ATASEND.NR)
  81. ,(SELECT SUM(AT.VOLMETER) FROM ATAPOSITION AT WHERE ATASEND.NR = AT.ATASENDNR GROUP BY ATASEND.NR)
  82. ,(SELECT SUM(AT.VOLMETER3) FROM ATAPOSITION AT WHERE ATASEND.NR = AT.ATASENDNR GROUP BY ATASEND.NR)
  83. ,( SELECT LISTAGG(T.T,';') within GROUP (ORDER BY t.t) FROM (SELECT DISTINCT AT.VERPACKUNG T , AT.ATASENDNR FROM ATAPOSITION AT)t WHERE  ATASEND.NR = T.ATASENDNR GROUP BY ATASEND.NR)
  84. ,ASendung.Gefahrgut
  85. ,(SELECT LISTAGG(T.T,';') within GROUP (ORDER BY t.t) FROM (SELECT DISTINCT AT.GGVSBAMNR T , AT.ATASENDNR FROM ATAPOSITION AT)t WHERE  ATASEND.NR = T.ATASENDNR GROUP BY ATASEND.NR)
  86. ,Asendung.Check0
  87. ,(SELECT LISTAGG(T.T,';') within GROUP (ORDER BY t.t) FROM (SELECT DISTINCT AT.floatfield1 T , AT.ATASENDNR FROM ATAPOSITION AT)t WHERE  ATASEND.NR = T.ATASENDNR GROUP BY ATASEND.NR)
  88. ,(SELECT LISTAGG(T.T,';') within GROUP (ORDER BY t.t) FROM (SELECT DISTINCT AT.floatfield2 T , AT.ATASENDNR FROM ATAPOSITION AT)t WHERE  ATASEND.NR = T.ATASENDNR GROUP BY ATASEND.NR)
  89. ,SCUSTOMTYPEVALUES.BEZEICHNUNG
  90. ,(SELECT LISTAGG(T.T,';') within GROUP (ORDER BY t.t) FROM (SELECT DISTINCT AT.GGVSUNNR T , AT.ATASENDNR FROM ATAPOSITION AT)t WHERE  ATASEND.NR = T.ATASENDNR GROUP BY ATASEND.NR)
  91. ,(SELECT LISTAGG(T.T,';') within GROUP (ORDER BY t.t) FROM (SELECT DISTINCT AT.GGVSKLASSE T , AT.ATASENDNR FROM ATAPOSITION AT)t WHERE  ATASEND.NR = T.ATASENDNR GROUP BY ATASEND.NR)
  92. ,(SELECT LISTAGG(T.T,';') within GROUP (ORDER BY t.t) FROM (SELECT DISTINCT AT.GGVSTCNAME T , AT.ATASENDNR FROM ATAPOSITION AT)t WHERE  ATASEND.NR = T.ATASENDNR GROUP BY ATASEND.NR)
  93. ,ATASend.km
  94. ,ATASEND.ABSINFO1
  95. ,ATASEND.EMPINFO1
  96. ,asendung.check6
  97. ,asendung.check1
  98. ,asendung.check3
  99. ,( SELECT info10 FROM SFUHRP WHERE atour.LKWNR = SFUHRP.FZGNR)
  100. FROM
  101. AKopf
  102. LEFT JOIN ASendung ON ASendung.AKopfNr = AKopf.Nr
  103. LEFT JOIN ATASend ON ATASend.ASendungID = ASendung.SendungID
  104. LEFT JOIN ATour ON ATASend.TourNr = ATour.Nr
  105. Left join SKunden FrachtAG ON  AKopf.FrachtAgNr = FrachtAG.Nr
  106. Left join SWAEHRUNG PVORGABEWGS ON  asendung.WAEHRUNGIDKUNDE = PVORGABEWGS.ID
  107. Left join SKunden Unternehmer ON  ATour.Unternehmer = Unternehmer.Nr
  108. Left join SFuhrp Haenger ON  ATour.AnhaengerNr = Haenger.FzgNr
  109. Left join SFZGTYP HAENGERTYP ON  HAENGER.FZGTYP = HAENGERTYP.NR
  110. Left join SVerkaufsart sverkaufsart_sdg ON  asendung.verkaufsartid = sverkaufsart_sdg.id
  111. Left join STOURKATEGORIEN ON  ATour.TOURKATEGORIE = STOURKATEGORIEN.ID
  112. Left join suser ON  suser.nr = akopf.userid
  113. Left join SPERSO SPVERKAEUFER ON  AKOPF.VERKAEUFER = SPVERKAEUFER.NR
  114. Left join SUSER TOURUSER ON  ATour.ERSTELLTVON = TOURUSER.NR
  115. Left join shierarchie shierarchieorder ON  AKOPF.HIERARCHIE = SHIERARCHIEORDER.ID
  116. Left join shierarchie ON  atour.Hierarchie = SHierarchie.ID
  117. LEFT JOIN shierarchie toorg ON ATASEND.Hierarchie = TOORG.ID
  118. Left join SCUSTOMTYPEVALUES ON asendung.SDGTYP11 = SCUSTOMTYPEVALUES.id
  119. Left join STransportart STransportart_Sdg ON ASendung.Transportart = STransportart_Sdg.ID
  120. left join UDX_VIEW_BPGROUP t ON FrachtAG.NR = t.KundenNr
  121. left join SHierarchie SHI ON akopf.ALTHIERARCHIE = SHi.ID
  122. LEFT JOIN SKUFAKTU ON FrachtAG.Nr = SKUFAKTU.NR
  123. LEFT JOIN SLAND ON SLAND.LAND = SKUFAKTU.USTIDLAND
  124. LEFT JOIN sbranche ON FrachtAG.BRANCHE = sbranche.id
  125. LEFT JOIN SCUSTOMTYPEVALUES scu ON Unternehmer.GPKATEGORIE6 = scu.id
  126. LEFT JOIN SCUSTOMTYPEVALUES sca ON FrachtAG.GPKATEGORIE6 = sca.id
  127. LEFT JOIN ATRANSPORTORDERCUSTOMFIELDV ON ATASEND.NR = ATRANSPORTORDERCUSTOMFIELDV.ID
  128. LEFT JOIN SPERSO ON SPERSO.NR = AKOPF.VERKAEUFER
  129. LEFT JOIN ATOURCUSTOMFIELDV ON ATOURCUSTOMFIELDV.ID = ATOUR.NR
  130. Left join SCUSTOMTYPEVALUES SCUST ON asendung.SDGTYP12 = SCUST.id
  131.  
  132. WHERE
  133. 0=0
  134. AND akopf.ordertype = 0
  135. AND akopf.INRECYCLEBIN = 0
  136. AND atour.TOURNUMMER = 285157;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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!