Advertisement
Guest User

Untitled

a guest
Feb 24th, 2017
112
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 3.00 KB | None | 0 0
  1. SELECT a.CODSOC, a.SERIE, a.NUMFACT, a.NRCOLIS, a.ANOCOLIS, a.NUMSOC, a.NRRVCDIS, IFNULL(IFNULL(NULLIF(a.PARCELID, ''), l.PARCELID), m.PARCELID) AS PARCELID, a.CDXEETT
  2. , CASE a.TYPPAYT
  3. WHEN '04' THEN
  4. a.MOCOBRAR
  5. ELSE 0
  6. END AS MOCOBRAR
  7. , a.PESOCOLIS
  8. , COALESCE(h.TELEFH, '') AS TELEFH
  9. , COALESCE(k.TELEFE, '') AS TELEFE
  10. , COALESCE(d.EMAIL, '') AS EMAIL
  11. , a.NOMECLI, a.MOR1CLI, a.MOR2CLI, a.MOR3CLI, a.MOR4CLI, a.CODPOS, a.LOCCDPOS
  12. , COALESCE(e.TEXTLIV, '') AS TEXTLIV
  13. , (a.MOCOBRAR + a.MO_PAGO) AS TOTAL
  14. , a.PAISLIV
  15. FROM VR500401 a
  16. INNER JOIN VJ210101 b
  17. ON b.CODSOC = a.CODSOC
  18. AND b.NUMSOC = a.NUMSOC
  19. INNER JOIN VJ210201 c
  20. ON c.NUMIND = b.NUMIND
  21. LEFT JOIN VJ002001 d
  22. ON d.CODSOC = a.CODSOC
  23. AND d.NUMSOC = a.NUMSOC
  24. LEFT JOIN VR500901 e
  25. ON e.CODSOC = a.CODSOC
  26. AND e.NUMCDE = a.NUMCDE
  27. LEFT JOIN (
  28. SELECT f.NUMIND, g.NTEL AS TELEFH
  29. FROM (
  30. SELECT NUMIND, MIN(RRN(VJ001901)) AS RANK
  31. FROM VJ001901
  32. WHERE TIP_TEL = 1
  33. GROUP BY NUMIND, TIP_TEL) f
  34. INNER JOIN (
  35. SELECT NTEL, RRN(VJ001901) AS RANK
  36. FROM VJ001901) g
  37. ON g.RANK = f.RANK) h
  38. ON h.NUMIND = b.NUMIND
  39. LEFT JOIN (
  40. SELECT i.NUMIND, j.NTEL AS TELEFE
  41. FROM (
  42. SELECT NUMIND, MIN(RRN(VJ001901)) AS RANK
  43. FROM VJ001901
  44. WHERE TIP_TEL = 2
  45. GROUP BY NUMIND, TIP_TEL) i
  46. INNER JOIN (
  47. SELECT NTEL, RRN(VJ001901) AS RANK
  48. FROM VJ001901) j
  49. ON j.RANK = i.RANK) k
  50. ON k.NUMIND = b.NUMIND
  51. LEFT JOIN (
  52. SELECT NUMFACT, TRACKNBR AS PARCELID
  53. FROM VR502205
  54. INNER JOIN VR501502
  55. ON VR501502.CODSOC = VR502205.CODSOC
  56. AND VR501502.NUMCDE = VR502205.NUMCDE
  57. AND VR501502.NUMLIG = VR502205.NUMLIG
  58. WHERE TRACKNBR <> '') l
  59. ON a.NUMFACT = l.NUMFACT
  60. LEFT JOIN (
  61. SELECT TH0010.NUMFACT AS NUMFACT, TRACKNBR AS PARCELID
  62. FROM TR5022
  63. INNER JOIN TH0009
  64. ON TH0009.CODSOC = TR5022.CODSOC
  65. AND TH0009.NUMCDE = TR5022.NUMCDE
  66. INNER JOIN TH0010
  67. ON TH0010.CODSOC = TR5022.CODSOC
  68. AND TH0010.NUMLIG = TR5022.NUMLIG
  69. WHERE TRACKNBR <> '') m
  70. ON a.NUMFACT = m.NUMFACT
  71. WHERE a.CODSOC = '1' AND a.SERIE = '_serie' AND a.NUMFACT IN (_numfact)
  72. GROUP BY a.CODSOC, a.SERIE, a.NUMFACT, a.NRCOLIS, a.ANOCOLIS, a.NUMSOC, a.NRRVCDIS,
  73. IFNULL(IFNULL(NULLIF(a.PARCELID, ''), l.PARCELID), m.PARCELID), a.CDXEETT,
  74. CASE a.TYPPAYT
  75. WHEN '04' THEN
  76. a.MOCOBRAR
  77. ELSE 0
  78. END, a.PESOCOLIS, TELEFH, TELEFE, EMAIL, a.NOMECLI, a.MOR1CLI, a.MOR2CLI, a.MOR3CLI, a.MOR4CLI, a.CODPOS, a.LOCCDPOS, COALESCE(e.TEXTLIV, ''), (a.MOCOBRAR + a.MO_PAGO), a.PAISLIV;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement