[SQL] problemino con subquery

[arconotto2]

Ciao a tutti,
ho un problema con sql su una subquery:
se faccio un

select distinct *
from ({subquery}) as bla

mi viene una cosa diversa da quello che esce con questo codice:

insert into prova
{subquery}

select distinct *
from prova

io ho l'impressione che il problema sia dovuto al comando distinct... è possibile??

Grazie mille!

[arconotto2]

vi allego due file con i risultati delle due query, così magari riuscite a capire meglio e a darmi una mano...

queste le due query nei due file, come vi avevo detto l'unica differenza sta ne distinct:

SELECT DISTINCT
TOP 100 PERCENT tutto.data, tutto.ora, ISNULL(UP_DI0228_NORD_C.UP_DI0228_NORD_C, 0) AS UP_DI0228_NORD_C,
ISNULL(UP_DI0228_NORD_C.pUP_DI0228_NORD_C, 0) AS pUP_DI0228_NORD_C, ISNULL(UP_IIISALTO_1.UP_IIISALTO_1, 0) AS UP_IIISALTO_1,
ISNULL(UP_IIISALTO_1.pUP_IIISALTO_1, 0) AS pUP_IIISALTO_1, ISNULL(UPV_RTEPOEMSLTTO.UPV_RTEPOEMSLTTO, 0) AS UPV_RTEPOEMSLTTO,
ISNULL(UPV_RTEPOEMSLTTO.pUPV_RTEPOEMSLTTO, 0) AS pUPV_RTEPOEMSLTTO, ISNULL(UPV_SWGMOEMSLTTO.UPV_SWGMOEMSLTTO, 0)
AS UPV_SWGMOEMSLTTO, ISNULL(UPV_SWGMOEMSLTTO.pUPV_SWGMOEMSLTTO, 0) AS pUPV_SWGMOEMSLTTO,
ISNULL(UPV_RTEDOEMSLTTO.UPV_RTEDOEMSLTTO, 0) AS UPV_RTEDOEMSLTTO, ISNULL(UPV_RTEDOEMSLTTO.pUPV_RTEDOEMSLTTO, 0)
AS pUPV_RTEDOEMSLTTO
FROM (SELECT *
FROM trade.PCEprofili
WHERE data BETWEEN '20080806' AND '20080806') tutto LEFT OUTER JOIN
(SELECT prof.data, prof.ora, SUM(isnull(prof.MWh, 0)) AS UP_DI0228_NORD_C, isnull(prof.prezzo, 0) AS pUP_DI0228_NORD_C
FROM trade.PCEanaunita uni INNER JOIN
trade.PCEassocunitaprofili uprof ON uni.idunita = uprof.idunita INNER JOIN
trade.PCEprofili prof ON prof.idprofilo = uprof.idprofilo
WHERE uni.idunita = 8 AND prof.data BETWEEN '20080806' AND '20080806'
GROUP BY prof.data, prof.ora, isnull(prof.prezzo, 0)) UP_DI0228_NORD_C ON tutto.data = UP_DI0228_NORD_C.data AND
tutto.ora = UP_DI0228_NORD_C.ora LEFT OUTER JOIN
(SELECT prof.data, prof.ora, SUM(isnull(prof.MWh, 0)) AS UP_IIISALTO_1, isnull(prof.prezzo, 0) AS pUP_IIISALTO_1
FROM trade.PCEanaunita uni INNER JOIN
trade.PCEassocunitaprofili uprof ON uni.idunita = uprof.idunita INNER JOIN
trade.PCEprofili prof ON prof.idprofilo = uprof.idprofilo
WHERE uni.idunita = 9 AND prof.data BETWEEN '20080806' AND '20080806'
GROUP BY prof.data, prof.ora, isnull(prof.prezzo, 0)) UP_IIISALTO_1 ON tutto.data = UP_IIISALTO_1.data AND
tutto.ora = UP_IIISALTO_1.ora LEFT OUTER JOIN
(SELECT prof.data, prof.ora, SUM(isnull(prof.MWh, 0)) AS UPV_RTEPOEMSLTTO, isnull(prof.prezzo, 0) AS pUPV_RTEPOEMSLTTO
FROM trade.PCEanaunita uni INNER JOIN
trade.PCEassocunitaprofili uprof ON uni.idunita = uprof.idunita INNER JOIN
trade.PCEprofili prof ON prof.idprofilo = uprof.idprofilo
WHERE uni.idunita = 10 AND prof.data BETWEEN '20080806' AND '20080806'
GROUP BY prof.data, prof.ora, isnull(prof.prezzo, 0)) UPV_RTEPOEMSLTTO ON tutto.data = UPV_RTEPOEMSLTTO.data AND
tutto.ora = UPV_RTEPOEMSLTTO.ora LEFT OUTER JOIN
(SELECT prof.data, prof.ora, SUM(isnull(prof.MWh, 0)) AS UPV_SWGMOEMSLTTO, isnull(prof.prezzo, 0) AS pUPV_SWGMOEMSLTTO
FROM trade.PCEanaunita uni INNER JOIN
trade.PCEassocunitaprofili uprof ON uni.idunita = uprof.idunita INNER JOIN
trade.PCEprofili prof ON prof.idprofilo = uprof.idprofilo
WHERE uni.idunita = 11 AND prof.data BETWEEN '20080806' AND '20080806'
GROUP BY prof.data, prof.ora, isnull(prof.prezzo, 0)) UPV_SWGMOEMSLTTO ON tutto.data = UPV_SWGMOEMSLTTO.data AND
tutto.ora = UPV_SWGMOEMSLTTO.ora LEFT OUTER JOIN
(SELECT prof.data, prof.ora, SUM(isnull(prof.MWh, 0)) AS UPV_RTEDOEMSLTTO, isnull(prof.prezzo, 0) AS pUPV_RTEDOEMSLTTO
FROM trade.PCEanaunita uni INNER JOIN
trade.PCEassocunitaprofili uprof ON uni.idunita = uprof.idunita INNER JOIN
trade.PCEprofili prof ON prof.idprofilo = uprof.idprofilo
WHERE uni.idunita = 12 AND prof.data BETWEEN '20080806' AND '20080806'
GROUP BY prof.data, prof.ora, isnull(prof.prezzo, 0)) UPV_RTEDOEMSLTTO ON tutto.data = UPV_RTEDOEMSLTTO.data AND
tutto.ora = UPV_RTEDOEMSLTTO.ora
ORDER BY tutto.data, tutto.ora


---------------------------------------------------


SELECT TOP 100 PERCENT tutto.data, tutto.ora, ISNULL(UP_DI0228_NORD_C.UP_DI0228_NORD_C, 0) AS UP_DI0228_NORD_C,
ISNULL(UP_DI0228_NORD_C.pUP_DI0228_NORD_C, 0) AS pUP_DI0228_NORD_C, ISNULL(UP_IIISALTO_1.UP_IIISALTO_1, 0) AS UP_IIISALTO_1,
ISNULL(UP_IIISALTO_1.pUP_IIISALTO_1, 0) AS pUP_IIISALTO_1, ISNULL(UPV_RTEPOEMSLTTO.UPV_RTEPOEMSLTTO, 0) AS UPV_RTEPOEMSLTTO,
ISNULL(UPV_RTEPOEMSLTTO.pUPV_RTEPOEMSLTTO, 0) AS pUPV_RTEPOEMSLTTO, ISNULL(UPV_SWGMOEMSLTTO.UPV_SWGMOEMSLTTO, 0)
AS UPV_SWGMOEMSLTTO, ISNULL(UPV_SWGMOEMSLTTO.pUPV_SWGMOEMSLTTO, 0) AS pUPV_SWGMOEMSLTTO,
ISNULL(UPV_RTEDOEMSLTTO.UPV_RTEDOEMSLTTO, 0) AS UPV_RTEDOEMSLTTO, ISNULL(UPV_RTEDOEMSLTTO.pUPV_RTEDOEMSLTTO, 0)
AS pUPV_RTEDOEMSLTTO
FROM (SELECT *
FROM trade.PCEprofili
WHERE data BETWEEN '20080806' AND '20080806') tutto LEFT OUTER JOIN
(SELECT prof.data, prof.ora, SUM(isnull(prof.MWh, 0)) AS UP_DI0228_NORD_C, isnull(prof.prezzo, 0) AS pUP_DI0228_NORD_C
FROM trade.PCEanaunita uni INNER JOIN
trade.PCEassocunitaprofili uprof ON uni.idunita = uprof.idunita INNER JOIN
trade.PCEprofili prof ON prof.idprofilo = uprof.idprofilo
WHERE uni.idunita = 8 AND prof.data BETWEEN '20080806' AND '20080806'
GROUP BY prof.data, prof.ora, isnull(prof.prezzo, 0)) UP_DI0228_NORD_C ON tutto.data = UP_DI0228_NORD_C.data AND
tutto.ora = UP_DI0228_NORD_C.ora LEFT OUTER JOIN
(SELECT prof.data, prof.ora, SUM(isnull(prof.MWh, 0)) AS UP_IIISALTO_1, isnull(prof.prezzo, 0) AS pUP_IIISALTO_1
FROM trade.PCEanaunita uni INNER JOIN
trade.PCEassocunitaprofili uprof ON uni.idunita = uprof.idunita INNER JOIN
trade.PCEprofili prof ON prof.idprofilo = uprof.idprofilo
WHERE uni.idunita = 9 AND prof.data BETWEEN '20080806' AND '20080806'
GROUP BY prof.data, prof.ora, isnull(prof.prezzo, 0)) UP_IIISALTO_1 ON tutto.data = UP_IIISALTO_1.data AND
tutto.ora = UP_IIISALTO_1.ora LEFT OUTER JOIN
(SELECT prof.data, prof.ora, SUM(isnull(prof.MWh, 0)) AS UPV_RTEPOEMSLTTO, isnull(prof.prezzo, 0) AS pUPV_RTEPOEMSLTTO
FROM trade.PCEanaunita uni INNER JOIN
trade.PCEassocunitaprofili uprof ON uni.idunita = uprof.idunita INNER JOIN
trade.PCEprofili prof ON prof.idprofilo = uprof.idprofilo
WHERE uni.idunita = 10 AND prof.data BETWEEN '20080806' AND '20080806'
GROUP BY prof.data, prof.ora, isnull(prof.prezzo, 0)) UPV_RTEPOEMSLTTO ON tutto.data = UPV_RTEPOEMSLTTO.data AND
tutto.ora = UPV_RTEPOEMSLTTO.ora LEFT OUTER JOIN
(SELECT prof.data, prof.ora, SUM(isnull(prof.MWh, 0)) AS UPV_SWGMOEMSLTTO, isnull(prof.prezzo, 0) AS pUPV_SWGMOEMSLTTO
FROM trade.PCEanaunita uni INNER JOIN
trade.PCEassocunitaprofili uprof ON uni.idunita = uprof.idunita INNER JOIN
trade.PCEprofili prof ON prof.idprofilo = uprof.idprofilo
WHERE uni.idunita = 11 AND prof.data BETWEEN '20080806' AND '20080806'
GROUP BY prof.data, prof.ora, isnull(prof.prezzo, 0)) UPV_SWGMOEMSLTTO ON tutto.data = UPV_SWGMOEMSLTTO.data AND
tutto.ora = UPV_SWGMOEMSLTTO.ora LEFT OUTER JOIN
(SELECT prof.data, prof.ora, SUM(isnull(prof.MWh, 0)) AS UPV_RTEDOEMSLTTO, isnull(prof.prezzo, 0) AS pUPV_RTEDOEMSLTTO
FROM trade.PCEanaunita uni INNER JOIN
trade.PCEassocunitaprofili uprof ON uni.idunita = uprof.idunita INNER JOIN
trade.PCEprofili prof ON prof.idprofilo = uprof.idprofilo
WHERE uni.idunita = 12 AND prof.data BETWEEN '20080806' AND '20080806'
GROUP BY prof.data, prof.ora, isnull(prof.prezzo, 0)) UPV_RTEDOEMSLTTO ON tutto.data = UPV_RTEDOEMSLTTO.data AND
tutto.ora = UPV_RTEDOEMSLTTO.ora
ORDER BY tutto.data, tutto.ora

[arconotto2]

query con distinct

[arconotto2]

query senza distinct

[newuser]

*edit*

Ho sbagliato risposta...

[newuser]

Prova a togliere il predicato TOP 100 PERCENT, non mi sembra che abbia senso perchè stai chiedendo di restituire il 100% dei record risultanti dalla query.

[arconotto2]

quella è una formula che mette automaticamente sql enterprise manager, ho già provato a toglierla ma non cambia nulla...

[arconotto2]

grazie!

[newuser]

I risultati delle query sono inconsistenti nel campo UP_DI0228_NORD_C.UP_DI0228_NORD_C. Penso che dovresti controllare cosa c'è in quella tabella per vedere dove è il problema.