Hola,
NEcesito hacer una subquery que no consigo hacer que funcione.
SELECT DISTINCT '31-08-2021' AS FechaInicio, 'aa-mm-aaaa' AS FechaFin, c.cb_emi AS ISIN, c.cb_rep AS TipoFondo, c.cb_fxl AS FndFxlCr, c.cb_fxl AS FndFxlIic, c.cb_mng AS Gestor, c.cb_cdp AS Comision, s.Grp AS Grupo, s.Sgp AS Subgrupo, ic.SecNam AS Tipo, c.cb_des AS Descripcion, c.cb_gar AS OrdenPrelacion, c.cod1 AS TupoCupon, e.tau AS Cupon, CASE WHEN ((c.cb_ech < a.bdt) or a.bdt is null) THEN c.cb_ech ELSE a.bdt END AS VtoProx, c.RskZon AS ZonaRiesgo, c.RskDev AS DivisaRiesgo, d.pay AS Pais, s.Ccy AS Divisa, s.InvYea+s.CyeYea AS ValAdqSAA
FROM ScgDta s JOIN ctr c ON (s.Ctr = c.cc_ctr)
JOIN isu i ON (c.cb_eme = i.cod)
JOIN cie ci ON (i.pays = ci.ctp_cod)
LEFT JOIN dev d ON (ci.rc_nmct = d.pa2)
JOIN IcbSec ic ON (s.IcbNum = ic.SecNum)
LEFT JOIN ech_bnd e ON (c.cc_ctr = (SELECT e.ctr WHERE ((e.deb <= 'aa-mm-dddd') AND (e.fin > 'aa-mm-dddd'))))
LEFT JOIN (SELECT a2.ctr, MIN(a2.bdt) AS bdt FROM amo_bnd a2
WHERE ((a2.oty = 'C') AND (a2.cty = 'A') AND (a2.bdt > 'aa-mm-dddd')) GROUP BY a2.ctr) a ON (c.cc_ctr = a.ctr)
JOIN (SELECT Als AS Als, SUM(MktValEdt) AS Patrimonio FROM ScgDta s
WHERE ((s.Als = 'tblar04') AND (s.Dat = 'aa-mm-dddd') AND (s.MonDat = '31-08-2021')) GROUP BY Als) AS PATR ON (s.Als = PATR.Als)
WHERE ((s.Dat = 'aa-mm-dddd') AND (s.MonDat = '31-08-2021') AND (s.Als = 'tblar04')) ORDER BY s.Grp, s.Sgp
Necesito que la fecha (aa-mm-dddd) se consigue de la siguiente query (FEC.Cierre):
SELECT Als, MAX(Dat) AS Cierre FROM RddCloDat FEC WHERE Als='tblar04' Group By Als
como puedo poner en la Query que la variable ScgEdt tiene que ser igual a FEC.Cierre??
Es decir:
<variable id="ScgEdt" type="STRING" value="FEC.Cierre"