Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
- , CASE a.TYPPAYT
- WHEN '04' THEN
- a.MOCOBRAR
- ELSE 0
- END AS MOCOBRAR
- , a.PESOCOLIS
- , COALESCE(h.TELEFH, '') AS TELEFH
- , COALESCE(k.TELEFE, '') AS TELEFE
- , COALESCE(d.EMAIL, '') AS EMAIL
- , a.NOMECLI, a.MOR1CLI, a.MOR2CLI, a.MOR3CLI, a.MOR4CLI, a.CODPOS, a.LOCCDPOS
- , COALESCE(e.TEXTLIV, '') AS TEXTLIV
- , (a.MOCOBRAR + a.MO_PAGO) AS TOTAL
- , a.PAISLIV
- FROM VR500401 a
- INNER JOIN VJ210101 b
- ON b.CODSOC = a.CODSOC
- AND b.NUMSOC = a.NUMSOC
- INNER JOIN VJ210201 c
- ON c.NUMIND = b.NUMIND
- LEFT JOIN VJ002001 d
- ON d.CODSOC = a.CODSOC
- AND d.NUMSOC = a.NUMSOC
- LEFT JOIN VR500901 e
- ON e.CODSOC = a.CODSOC
- AND e.NUMCDE = a.NUMCDE
- LEFT JOIN (
- SELECT f.NUMIND, g.NTEL AS TELEFH
- FROM (
- SELECT NUMIND, MIN(RRN(VJ001901)) AS RANK
- FROM VJ001901
- WHERE TIP_TEL = 1
- GROUP BY NUMIND, TIP_TEL) f
- INNER JOIN (
- SELECT NTEL, RRN(VJ001901) AS RANK
- FROM VJ001901) g
- ON g.RANK = f.RANK) h
- ON h.NUMIND = b.NUMIND
- LEFT JOIN (
- SELECT i.NUMIND, j.NTEL AS TELEFE
- FROM (
- SELECT NUMIND, MIN(RRN(VJ001901)) AS RANK
- FROM VJ001901
- WHERE TIP_TEL = 2
- GROUP BY NUMIND, TIP_TEL) i
- INNER JOIN (
- SELECT NTEL, RRN(VJ001901) AS RANK
- FROM VJ001901) j
- ON j.RANK = i.RANK) k
- ON k.NUMIND = b.NUMIND
- LEFT JOIN (
- SELECT NUMFACT, TRACKNBR AS PARCELID
- FROM VR502205
- INNER JOIN VR501502
- ON VR501502.CODSOC = VR502205.CODSOC
- AND VR501502.NUMCDE = VR502205.NUMCDE
- AND VR501502.NUMLIG = VR502205.NUMLIG
- WHERE TRACKNBR <> '') l
- ON a.NUMFACT = l.NUMFACT
- LEFT JOIN (
- SELECT TH0010.NUMFACT AS NUMFACT, TRACKNBR AS PARCELID
- FROM TR5022
- INNER JOIN TH0009
- ON TH0009.CODSOC = TR5022.CODSOC
- AND TH0009.NUMCDE = TR5022.NUMCDE
- INNER JOIN TH0010
- ON TH0010.CODSOC = TR5022.CODSOC
- AND TH0010.NUMLIG = TR5022.NUMLIG
- WHERE TRACKNBR <> '') m
- ON a.NUMFACT = m.NUMFACT
- WHERE a.CODSOC = '1' AND a.SERIE = '_serie' AND a.NUMFACT IN (_numfact)
- GROUP BY a.CODSOC, a.SERIE, a.NUMFACT, a.NRCOLIS, a.ANOCOLIS, a.NUMSOC, a.NRRVCDIS,
- IFNULL(IFNULL(NULLIF(a.PARCELID, ''), l.PARCELID), m.PARCELID), a.CDXEETT,
- CASE a.TYPPAYT
- WHEN '04' THEN
- a.MOCOBRAR
- ELSE 0
- 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