Select anidados
Publicado por Paco (2 intervenciones) el 04/10/2011 19:05:58
Hola, a ver tengo un problema con una consulta SQL con varias subconsultas. Necesito sacar por columnas unos campos calculados que estan en uan tabla agrupados por un campo.
Si lanzo esta SQL con dos select anidados solo o 3, funciona (tarda mucho, pero me saca lo que quiero.. si sabeis alguna otra forma..). El problema esta en cuando al lanzo completo que me dice que devuelve mas de un valor la subconsulta.
A ver si me podeis ayudar gracias...
select F.numfact,
(select SUM(F.totlinea) from IFMSFA_LINPDEFP_V F
where F.ffactura between '01/09/2011' and '01/09/2011' and F.gserreal in ('13010','13020','13030','13040')and f.rconc='AL') as 'FUNGIBLES',
(select SUM(F.totlinea) from IFMSFA_LINPDEFP_V F
where F.ffactura between '01/09/2011' and '01/09/2011' and F.gserreal in ('13010','13020','13030','13040')and f.rconc='AN') as 'ANESTESIA',
(select SUM(F.totlinea) from IFMSFA_LINPDEFP_V F
where F.ffactura between '01/09/2011' and '01/09/2011' and F.gserreal in ('13010','13020','13030','13040')and f.rconc='DQ') as 'D.QUIROFANO',
(select SUM(F.totlinea) from IFMSFA_LINPDEFP_V F
where F.ffactura between '01/09/2011' and '01/09/2011' and F.gserreal in ('13010','13020','13030','13040')and f.rconc='ES') as 'ESTANCIAS',
(select SUM(F.totlinea) from IFMSFA_LINPDEFP_V F
where F.ffactura between '01/09/2011' and '01/09/2011' and F.gserreal in ('13010','13020','13030','13040')and f.rconc='FA') as 'FARMACOS',
(select SUM(F.totlinea) from IFMSFA_LINPDEFP_V F
where F.ffactura between '01/09/2011' and '01/09/2011' and F.gserreal in ('13010','13020','13030','13040')and f.rconc='HM') as 'H.MEDICOS',
(select SUM(F.totlinea) from IFMSFA_LINPDEFP_V F
where F.ffactura between '01/09/2011' and '01/09/2011' and F.gserreal in ('13010','13020','13030','13040')and f.rconc='PR') as 'PRESTACIONES',
(select cf.dconc from IFMSFA_LINPDEFP_V F left join IFMSFA_CONCEPTOS cf on f.rconc=cf.rconc and f.econc=cf.econc
where F.ffactura between '01/09/2011' and '01/09/2011' and F.gserreal in ('13010','13020','13030','13040')and f.rconc='PR') as 'DESC.CONCEPTO',sum(F.totlinea)
from IFMSFA_LINPDEFP_V F
where F.ffactura between '01/09/2011' and '01/09/2011'
and F.gserreal in ('13010','13020','13030','13040')
group by F.numfact
Si lanzo esta SQL con dos select anidados solo o 3, funciona (tarda mucho, pero me saca lo que quiero.. si sabeis alguna otra forma..). El problema esta en cuando al lanzo completo que me dice que devuelve mas de un valor la subconsulta.
A ver si me podeis ayudar gracias...
select F.numfact,
(select SUM(F.totlinea) from IFMSFA_LINPDEFP_V F
where F.ffactura between '01/09/2011' and '01/09/2011' and F.gserreal in ('13010','13020','13030','13040')and f.rconc='AL') as 'FUNGIBLES',
(select SUM(F.totlinea) from IFMSFA_LINPDEFP_V F
where F.ffactura between '01/09/2011' and '01/09/2011' and F.gserreal in ('13010','13020','13030','13040')and f.rconc='AN') as 'ANESTESIA',
(select SUM(F.totlinea) from IFMSFA_LINPDEFP_V F
where F.ffactura between '01/09/2011' and '01/09/2011' and F.gserreal in ('13010','13020','13030','13040')and f.rconc='DQ') as 'D.QUIROFANO',
(select SUM(F.totlinea) from IFMSFA_LINPDEFP_V F
where F.ffactura between '01/09/2011' and '01/09/2011' and F.gserreal in ('13010','13020','13030','13040')and f.rconc='ES') as 'ESTANCIAS',
(select SUM(F.totlinea) from IFMSFA_LINPDEFP_V F
where F.ffactura between '01/09/2011' and '01/09/2011' and F.gserreal in ('13010','13020','13030','13040')and f.rconc='FA') as 'FARMACOS',
(select SUM(F.totlinea) from IFMSFA_LINPDEFP_V F
where F.ffactura between '01/09/2011' and '01/09/2011' and F.gserreal in ('13010','13020','13030','13040')and f.rconc='HM') as 'H.MEDICOS',
(select SUM(F.totlinea) from IFMSFA_LINPDEFP_V F
where F.ffactura between '01/09/2011' and '01/09/2011' and F.gserreal in ('13010','13020','13030','13040')and f.rconc='PR') as 'PRESTACIONES',
(select cf.dconc from IFMSFA_LINPDEFP_V F left join IFMSFA_CONCEPTOS cf on f.rconc=cf.rconc and f.econc=cf.econc
where F.ffactura between '01/09/2011' and '01/09/2011' and F.gserreal in ('13010','13020','13030','13040')and f.rconc='PR') as 'DESC.CONCEPTO',sum(F.totlinea)
from IFMSFA_LINPDEFP_V F
where F.ffactura between '01/09/2011' and '01/09/2011'
and F.gserreal in ('13010','13020','13030','13040')
group by F.numfact
Valora esta pregunta


0