Tuesday, March 27, 2012

Error 8618 ( GROUP BY clause exceeds 8000) with SQLServer 2000

Hi all,
Hope someone can help,
I've been trying to access my DB using sqlxml.
But I keep getting this message :
"The query processor could not produce a query plan from the optimizer
because
the total length of all the columns in the GROUP BY or ORDER BY clause
exceeds 8
000 bytes."
I'm running SQL Server 2000 SP3 on a Win Server 2003 box.
I read about the bug on SQL Server 7.0. Is it possible that the same
bug affects version 8.0
The schema I use is rather complex, but I doubt that this is causing
this error message. Also, when I take a look at the SQL that is
generated, the order by is done on the column numbers and the list of
numbers doesn't make up 80000 characters...
Here are the first 2 (out of 19) of the generated sql that causes this
error message:
select 1 as TAG,0 as parent,_Q4._TMNDAHBOAPGA as
[CompteurEnergie!1!CCIAG_g_PrimaryKey!hide],_Q1._TNNDAHBOAPGA as
[CompteurEnergie!1!Antenne_g_PrimaryKey!hide],_Q1._TNNDAHBOAPGA as
[CompteurEnergie!1!Antenne_g_PrimaryKey!hide],_Q3._TONDAHBOAPGA as
[CompteurEnergie!1!SousStationHP_g_PrimaryKey!hide],_Q3._TONDAHBOAPGA
as [CompteurEnergie!1!SousStationHP_g_PrimaryKey!hide],_Q2._THFEAHBOAPGA
as [CompteurEnergie!1!CompteurEnergie_g_PrimaryKey!hide],_Q2._THFEAAJABOGA
as [CompteurEnergie!1!Code!element],_Q2._THFEAKONAPGA as
[CompteurEnergie!1!Observation!element],_Q2._THFEAHBOAPGA as
[CompteurEnergie!1!PrimaryKey!hide],NULL as
[InterventionCompteurEnergie!2!Nature!element],NULL as
[InterventionCompteurEnergie!2!DateIntervention!element],NULL as
[InterventionCompteurEnergie!2!Observation!element],NULL as
[InterventionCompteurEnergie!2!Panne!element],NULL as
[InterventionCompteurEnergie!2!Rapport!element],NULL as
[InterventionCompteurEnergie!2!PrimaryKey!hide],NULL as
[InterventionEchangeur!3!Nature!element],NULL as
[InterventionEchangeur!3!DateIntervention!element],NULL as
[InterventionEchangeur!3!Observation!element],NULL as
[InterventionEchangeur!3!PrimaryKey!hide],NULL as
[InterventionProdEauChaudeSanitaire!4!Nature!element],NULL as
[InterventionProdEauChaudeSanitaire!4!DateIntervention!element],NULL
as [InterventionProdEauChaudeSanitaire!4!Observation!element],NULL as
[InterventionProdEauChaudeSanitaire!4!IndexEauChaudeSanitaire!element],NULL
as [InterventionProdEauChaudeSanitaire!4!PrimaryKey!hide],NULL as
[Renovation!5!Nature!element],NULL as
[Renovation!5!DateIntervention!element],NULL as
[Renovation!5!Observation!element],NULL as
[Renovation!5!PrevisionTvx!element],NULL as
[Renovation!5!LongueurCable!element],NULL as
[Renovation!5!PrimaryKey!hide],NULL as [DateDepose!6!_g_5!hide],NULL
as [DateDepose!6!],NULL as [IndexDepose!7!_g_6!hide],NULL as
[IndexDepose!7!],NULL as [VolumeDepose!8!_g_7!hide],NULL as
[VolumeDepose!8!],NULL as [TypeTvx!9!_g_8!hide],NULL as
[TypeTvx!9!],NULL as [PxIntegrateur!10!_g_9!hide],NULL as
[PxIntegrateur!10!],NULL as [TvxTuyauterieDate!11!_g_10!hide],NULL as
[TvxTuyauterieDate!11!],NULL as [Installateur!12!_g_11!hide],NULL as
[Installateur!12!],NULL as [PxTuyauterie!13!_g_12!hide],NULL as
[PxTuyauterie!13!],NULL as [PxCalo!14!_g_13!hide],NULL as
[PxCalo!14!],NULL as [PxElec!15!_g_14!hide],NULL as [PxElec!15!],NULL
as [PorteFusible!16!_g_15!hide],NULL as [PorteFusible!16!],NULL as
[PlusValue30m!17!_g_16!hide],NULL as [PlusValue30m!17!],NULL as
[AvenantAjout!18!PrimaryKey!hide],NULL as
[Fournisseur!19!NomFournisseur!element],NULL as
[Fournisseur!19!PrimaryKey!hide],NULL as
[TypeContrat!20!_g_19!hide],NULL as [TypeContrat!20!],NULL as
[Numero!21!_g_20!hide],NULL as [Numero!21!],NULL as
[DateReception!22!_g_21!hide],NULL as [DateReception!22!],NULL as
[Observation!23!_g_22!hide],NULL as [Observation!23!],NULL as
[DateContrat!24!_g_23!hide],NULL as [DateContrat!24!],NULL as
[MESOfficielle!25!_g_24!hide],NULL as [MESOfficielle!25!],NULL as
[MESComptage!26!_g_25!hide],NULL as [MESComptage!26!],NULL as
[AvenantSupp!27!PrimaryKey!hide],NULL as
[Fournisseur!28!NomFournisseur!element],NULL as
[Fournisseur!28!PrimaryKey!hide],NULL as
[TypeContrat!29!_g_28!hide],NULL as [TypeContrat!29!],NULL as
[Numero!30!_g_29!hide],NULL as [Numero!30!],NULL as
[DateReception!31!_g_30!hide],NULL as [DateReception!31!],NULL as
[Observation!32!_g_31!hide],NULL as [Observation!32!],NULL as
[DateSuppression!33!_g_32!hide],NULL as [DateSuppression!33!],NULL as
[Periode!34!_g_33!hide],NULL as [Periode!34!],NULL as
[Fournisseur!35!NomFournisseur!element],NULL as
[Fournisseur!35!PrimaryKey!hide],NULL as
[TypeContrat!36!_g_35!hide],NULL as [TypeContrat!36!],NULL as
[EnCharge!37!Qui!element],NULL as [EnCharge!37!PrimaryKey!hide],NULL
as [MiseEnService!38!_g_37!hide],NULL as [MiseEnService!38!],NULL as
[DateDernierControl!39!_g_38!hide],NULL as
[DateDernierControl!39!],NULL as [AnneeFabrication!40!_g_39!hide],NULL
as [AnneeFabrication!40!],NULL as
[ContratFournisseur!41!DateSignature!element],NULL as
[ContratFournisseur!41!PrimaryKey!hide],NULL as
[Telerelevage!42!TypeCarte!element],NULL as
[Telerelevage!42!TypeConcentrateur!element],NULL as
[Telerelevage!42!Protocole!element],NULL as
[Telerelevage!42!AdresseCarte!element],NULL as
[Telerelevage!42!Utilisation!element],NULL as
[Telerelevage!42!NumTelephone!element],NULL as
[Telerelevage!42!TypeLigne!element],NULL as
[Telerelevage!42!TypeModem!element],NULL as
[Telerelevage!42!TypeRegulateur!element],NULL as
[Telerelevage!42!PrimaryKey!hide],NULL as
[Mesureur!43!Code!element],NULL as
[Mesureur!43!Observation!element],NULL as
[Mesureur!43!PrimaryKey!hide],NULL as
[InterventionCompteurEnergie!44!Nature!element],NULL as
[InterventionCompteurEnergie!44!DateIntervention!element],NULL as
[InterventionCompteurEnergie!44!Observation!element],NULL as
[InterventionCompteurEnergie!44!Panne!element],NULL as
[InterventionCompteurEnergie!44!Rapport!element],NULL as
[InterventionCompteurEnergie!44!PrimaryKey!hide],NULL as
[InterventionEchangeur!45!Nature!element],NULL as
[InterventionEchangeur!45!DateIntervention!element],NULL as
[InterventionEchangeur!45!Observation!element],NULL as
[InterventionEchangeur!45!PrimaryKey!hide],NULL as
[InterventionProdEauChaudeSanitaire!46!Nature!element],NULL as
[InterventionProdEauChaudeSanitaire!46!DateIntervention!element],NULL
as [InterventionProdEauChaudeSanitaire!46!Observation!element],NULL as
[InterventionProdEauChaudeSanitaire!46!IndexEauChaudeSanitaire!element],NULL
as [InterventionProdEauChaudeSanitaire!46!PrimaryKey!hide],NULL as
[Renovation!47!Nature!element],NULL as
[Renovation!47!DateIntervention!element],NULL as
[Renovation!47!Observation!element],NULL as
[Renovation!47!PrevisionTvx!element],NULL as
[Renovation!47!LongueurCable!element],NULL as
[Renovation!47!PrimaryKey!hide],NULL as
[DateDepose!48!_g_47!hide],NULL as [DateDepose!48!],NULL as
[IndexDepose!49!_g_48!hide],NULL as [IndexDepose!49!],NULL as
[VolumeDepose!50!_g_49!hide],NULL as [VolumeDepose!50!],NULL as
[TypeTvx!51!_g_50!hide],NULL as [TypeTvx!51!],NULL as
[PxIntegrateur!52!_g_51!hide],NULL as [PxIntegrateur!52!],NULL as
[TvxTuyauterieDate!53!_g_52!hide],NULL as [TvxTuyauterieDate!53!],NULL
as [Installateur!54!_g_53!hide],NULL as [Installateur!54!],NULL as
[PxTuyauterie!55!_g_54!hide],NULL as [PxTuyauterie!55!],NULL as
[PxCalo!56!_g_55!hide],NULL as [PxCalo!56!],NULL as
[PxElec!57!_g_56!hide],NULL as [PxElec!57!],NULL as
[PorteFusible!58!_g_57!hide],NULL as [PorteFusible!58!],NULL as
[PlusValue30m!59!_g_58!hide],NULL as [PlusValue30m!59!],NULL as
[AvenantAjout!60!PrimaryKey!hide],NULL as
[Fournisseur!61!NomFournisseur!element],NULL as
[Fournisseur!61!PrimaryKey!hide],NULL as
[TypeContrat!62!_g_61!hide],NULL as [TypeContrat!62!],NULL as
[Numero!63!_g_62!hide],NULL as [Numero!63!],NULL as
[DateReception!64!_g_63!hide],NULL as [DateReception!64!],NULL as
[Observation!65!_g_64!hide],NULL as [Observation!65!],NULL as
[DateContrat!66!_g_65!hide],NULL as [DateContrat!66!],NULL as
[MESOfficielle!67!_g_66!hide],NULL as [MESOfficielle!67!],NULL as
[MESComptage!68!_g_67!hide],NULL as [MESComptage!68!],NULL as
[AvenantSupp!69!PrimaryKey!hide],NULL as
[Fournisseur!70!NomFournisseur!element],NULL as
[Fournisseur!70!PrimaryKey!hide],NULL as
[TypeContrat!71!_g_70!hide],NULL as [TypeContrat!71!],NULL as
[Numero!72!_g_71!hide],NULL as [Numero!72!],NULL as
[DateReception!73!_g_72!hide],NULL as [DateReception!73!],NULL as
[Observation!74!_g_73!hide],NULL as [Observation!74!],NULL as
[DateSuppression!75!_g_74!hide],NULL as [DateSuppression!75!],NULL as
[Periode!76!_g_75!hide],NULL as [Periode!76!],NULL as
[Fournisseur!77!NomFournisseur!element],NULL as
[Fournisseur!77!PrimaryKey!hide],NULL as
[TypeContrat!78!_g_77!hide],NULL as [TypeContrat!78!],NULL as
[EnCharge!79!Qui!element],NULL as [EnCharge!79!PrimaryKey!hide],NULL
as [Reference!80!_g_79!hide],NULL as [Reference!80!],NULL as
[DateDePose!81!_g_80!hide],NULL as [DateDePose!81!],NULL as
[EchangeStandardMesureur!82!_g_81!hide],NULL as
[EchangeStandardMesureur!82!],NULL as [Position!83!_g_82!hide],NULL as
[Position!83!],NULL as [Prix!84!_g_83!hide],NULL as [Prix!84!],NULL as
[DateAffectation!85!_g_84!hide],NULL as [DateAffectation!85!],NULL as
[Marque!86!_g_85!hide],NULL as [Marque!86!],NULL as
[Modele!87!_g_86!hide],NULL as [Modele!87!],NULL as
[TypeDeMesureur!88!Type!element],NULL as
[TypeDeMesureur!88!Diametre!element],NULL as
[TypeDeMesureur!88!QDemarrage!element],NULL as
[TypeDeMesureur!88!QMin!element],NULL as
[TypeDeMesureur!88!QNominal!element],NULL as
[TypeDeMesureur!88!QMaxAccidentel!element],NULL as
[TypeDeMesureur!88!PressionMaxi!element],NULL as
[TypeDeMesureur!88!TemperatureMax!element],NULL as
[TypeDeMesureur!88!PImpu!element],NULL as
[TypeDeMesureur!88!Positionnement!element],NULL as
[TypeDeMesureur!88!GammeDeMesure!element],NULL as
[TypeDeMesureur!88!TypeFonct!element],NULL as
[TypeDeMesureur!88!Pression!element],NULL as
[TypeDeMesureur!88!PrimaryKey!hide],NULL as
[Integrateur!89!NumSerie!element],NULL as
[Integrateur!89!DatePosePile!element],NULL as
[Integrateur!89!Marque!element],NULL as
[Integrateur!89!PrimaryKey!hide],NULL as
[TypeIntegrateur!90!Type!element],NULL as
[TypeIntegrateur!90!DeltaTemperatureMin!element],NULL as
[TypeIntegrateur!90!DeltaTemperatureMax!element],NULL as
[TypeIntegrateur!90!PlageTemperature!element],NULL as
[TypeIntegrateur!90!PrimaryKey!hide],NULL as
[DateDepose!6!xsi:nil],NULL as
[DateDepose!48!xsi:nil],N'http://www.w3.org/2001/XMLSchema-instance'
as [CompteurEnergie!1!xmlns:xsi] from (select _QB0.PrimaryKey AS
_TMNDAHBOAPGA from CCIAG _QB0) _Q4,(select _QB0.Nom AS
_TNNDIGDINNHA,_QB0.Code AS _TNNDAAJABOGA,_QB0.PrimaryKey AS
_TNNDAHBOAPGA,_QB0.ForeignKey AS _TNNDAPNNAPGA from Antenne _QB0)
_Q1,(select _QB0.ForeignKey AS _TONDAPNNAPGA,_QB0.PrimaryKey AS
_TONDAHBOAPGA,_QB0.Observation AS _TONDAKONAPGA,_QB0.Alimentation AS
_TONDIMABBOGA,_QB0.Acces AS _TONDAPPABOGA,_QB0.Fonctionnement AS
_TONDICPABOGA,_QB0.PSouscriteECS AS _TONDAPCOAPGA,_QB0.PSouscrite AS
_TONDIJNABOGA,_QB0.NbLogtOuSurface AS _TONDAHONAPGA,_QB0.Secteur AS
_TONDABMABOGA,_QB0.DateMESInstallation AS
_TONDIELABOGA,_QB0.Designation AS _TONDAKKABOGA,_QB0.CodeVisu AS
_TONDANJABOGA,_QB0.Code AS _TONDAAJABOGA from SousStationHP _QB0)
_Q3,(select _QB0.ForeignKey AS _THFEAPNNAPGA,_QB0.AnneeFabrication AS
_THFEICEMCOGA,_QB0.DateDernierControl AS
_THFEIEDMCOGA,_QB0.MiseEnService AS _THFEAMCOAPGA,_QB0.TypeContrat AS
_THFEAEBOAPGA,_QB0.PrimaryKey AS _THFEAHBOAPGA,_QB0.Observation AS
_THFEAKONAPGA,_QB0.Code AS _THFEAAJABOGA from CompteurEnergie _QB0)
_Q2 WHERE (CONVERT(nvarchar(4000),_Q1._TNNDAAJABOGA,126) IS NOT NULL
AND (CONVERT(nvarchar(4000),_Q1._TNNDAAJABOGA,126) = N'80') AND
CONVERT(nvarchar(4000),_Q2._THFEAAJABOGA,126) IS NOT NULL AND
(CONVERT(nvarchar(4000),_Q2._THFEAAJABOGA,126) = N'V 03200')) and
_Q3._TONDAHBOAPGA=_Q2._THFEAPNNAPGA and
_Q1._TNNDAHBOAPGA=_Q3._TONDAPNNAPGA and
_Q4._TMNDAHBOAPGA=_Q1._TNNDAPNNAPGA
union all
select 2,1,_Q4._TMNDAHBOAPGA,_Q1._TNNDAHBOAPGA,_Q1._TNNDAHBOAPGA,_Q3._TONDAHBOAPGA,_Q3._TONDAHBOAPGA,_Q2._THFEAHBOAPGA,NULL,NULL,_Q2._THFEAHBOAPGA,_Q5._TIFEALNNAPGA,_Q5._TIFEIEAOAOGA,_Q5._TIFEAKONAPGA,_Q5._TIFEILPBBOGA,_Q5._TIFEAIACBOGA,_Q5._TIFEAHBOAPGA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
ULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N
LL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NU
L,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
from (select _QB0.Rapport AS _TIFEAIACBOGA,_QB0.Panne AS
_TIFEILPBBOGA,_QB0.Observation AS _TIFEAKONAPGA,_QB0.DateIntervention
AS _TIFEIEAOAOGA,_QB0.Nature AS _TIFEALNNAPGA,_QB0.PrimaryKey AS
_TIFEAHBOAPGA,_QB0.ForeignKey AS _TIFEAPNNAPGA from
InterventionCompteurEnergie _QB0) _Q5,(select _QB0.PrimaryKey AS
_TMNDAHBOAPGA from CCIAG _QB0) _Q4,(select _QB0.Nom AS
_TNNDIGDINNHA,_QB0.Code AS _TNNDAAJABOGA,_QB0.PrimaryKey AS
_TNNDAHBOAPGA,_QB0.ForeignKey AS _TNNDAPNNAPGA from Antenne _QB0)
_Q1,(select _QB0.ForeignKey AS _TONDAPNNAPGA,_QB0.PrimaryKey AS
_TONDAHBOAPGA,_QB0.Observation AS _TONDAKONAPGA,_QB0.Alimentation AS
_TONDIMABBOGA,_QB0.Acces AS _TONDAPPABOGA,_QB0.Fonctionnement AS
_TONDICPABOGA,_QB0.PSouscriteECS AS _TONDAPCOAPGA,_QB0.PSouscrite AS
_TONDIJNABOGA,_QB0.NbLogtOuSurface AS _TONDAHONAPGA,_QB0.Secteur AS
_TONDABMABOGA,_QB0.DateMESInstallation AS
_TONDIELABOGA,_QB0.Designation AS _TONDAKKABOGA,_QB0.CodeVisu AS
_TONDANJABOGA,_QB0.Code AS _TONDAAJABOGA from SousStationHP _QB0)
_Q3,(select _QB0.ForeignKey AS _THFEAPNNAPGA,_QB0.AnneeFabrication AS
_THFEICEMCOGA,_QB0.DateDernierControl AS
_THFEIEDMCOGA,_QB0.MiseEnService AS _THFEAMCOAPGA,_QB0.TypeContrat AS
_THFEAEBOAPGA,_QB0.PrimaryKey AS _THFEAHBOAPGA,_QB0.Observation AS
_THFEAKONAPGA,_QB0.Code AS _THFEAAJABOGA from CompteurEnergie _QB0)
_Q2 WHERE (CONVERT(nvarchar(4000),_Q1._TNNDAAJABOGA,126) IS NOT NULL
AND (CONVERT(nvarchar(4000),_Q1._TNNDAAJABOGA,126) = N'80') AND
CONVERT(nvarchar(4000),_Q2._THFEAAJABOGA,126) IS NOT NULL AND
(CONVERT(nvarchar(4000),_Q2._THFEAAJABOGA,126) = N'V 03200')) and
_Q3._TONDAHBOAPGA=_Q2._THFEAPNNAPGA and
_Q1._TNNDAHBOAPGA=_Q3._TONDAPNNAPGA and
_Q4._TMNDAHBOAPGA=_Q1._TNNDAPNNAPGA and
_Q2._THFEAHBOAPGA=_Q5._TIFEAPNNAPGA
union all
select ....
Thanks if anyone can help (hope so :-)
PhilBytes per Group By or Order by of 8060 no longer exists for SQL Server 2000
(it was in SQL Server 7), however the limit of 8060 Bytes still exists for
Bytes per row.
"Phil" <pwdng@.hotmail.com> wrote in message
news:c657108d.0312100946.5e2e47a2@.posting.google.com...
> Hi all,
> Hope someone can help,
> I've been trying to access my DB using sqlxml.
> But I keep getting this message :
> "The query processor could not produce a query plan from the optimizer
> because
> the total length of all the columns in the GROUP BY or ORDER BY clause
> exceeds 8
> 000 bytes."
> I'm running SQL Server 2000 SP3 on a Win Server 2003 box.
> I read about the bug on SQL Server 7.0. Is it possible that the same
> bug affects version 8.0
> The schema I use is rather complex, but I doubt that this is causing
> this error message. Also, when I take a look at the SQL that is
> generated, the order by is done on the column numbers and the list of
> numbers doesn't make up 80000 characters...
> Here are the first 2 (out of 19) of the generated sql that causes this
> error message:
> select 1 as TAG,0 as parent,_Q4._TMNDAHBOAPGA as
> [CompteurEnergie!1!CCIAG_g_PrimaryKey!hide],_Q1._TNNDAHBOAPGA as
> [CompteurEnergie!1!Antenne_g_PrimaryKey!hide],_Q1._TNNDAHBOAPGA as
> [CompteurEnergie!1!Antenne_g_PrimaryKey!hide],_Q3._TONDAHBOAPGA as
> [CompteurEnergie!1!SousStationHP_g_PrimaryKey!hide],_Q3._TONDAHBOAPGA
> as [CompteurEnergie!1!SousStationHP_g_PrimaryKey!hide],_Q2._THFEAHBOAPGA
> as [CompteurEnergie!1!CompteurEnergie_g_PrimaryKey!hide],_Q2._THFEAAJABOGA
> as [CompteurEnergie!1!Code!element],_Q2._THFEAKONAPGA as
> [CompteurEnergie!1!Observation!element],_Q2._THFEAHBOAPGA as
> [CompteurEnergie!1!PrimaryKey!hide],NULL as
> [InterventionCompteurEnergie!2!Nature!element],NULL as
> [InterventionCompteurEnergie!2!DateIntervention!element],NULL as
> [InterventionCompteurEnergie!2!Observation!element],NULL as
> [InterventionCompteurEnergie!2!Panne!element],NULL as
> [InterventionCompteurEnergie!2!Rapport!element],NULL as
> [InterventionCompteurEnergie!2!PrimaryKey!hide],NULL as
> [InterventionEchangeur!3!Nature!element],NULL as
> [InterventionEchangeur!3!DateIntervention!element],NULL as
> [InterventionEchangeur!3!Observation!element],NULL as
> [InterventionEchangeur!3!PrimaryKey!hide],NULL as
> [InterventionProdEauChaudeSanitaire!4!Nature!element],NULL as
> [InterventionProdEauChaudeSanitaire!4!DateIntervention!element],NULL
> as [InterventionProdEauChaudeSanitaire!4!Observation!element],NULL as
>
[InterventionProdEauChaudeSanitaire!4!IndexEauChaudeSanitaire!element],NULL
> as [InterventionProdEauChaudeSanitaire!4!PrimaryKey!hide],NULL as
> [Renovation!5!Nature!element],NULL as
> [Renovation!5!DateIntervention!element],NULL as
> [Renovation!5!Observation!element],NULL as
> [Renovation!5!PrevisionTvx!element],NULL as
> [Renovation!5!LongueurCable!element],NULL as
> [Renovation!5!PrimaryKey!hide],NULL as [DateDepose!6!_g_5!hide],NULL
> as [DateDepose!6!],NULL as [IndexDepose!7!_g_6!hide],NULL as
> [IndexDepose!7!],NULL as [VolumeDepose!8!_g_7!hide],NULL as
> [VolumeDepose!8!],NULL as [TypeTvx!9!_g_8!hide],NULL as
> [TypeTvx!9!],NULL as [PxIntegrateur!10!_g_9!hide],NULL as
> [PxIntegrateur!10!],NULL as [TvxTuyauterieDate!11!_g_10!hide],NULL as
> [TvxTuyauterieDate!11!],NULL as [Installateur!12!_g_11!hide],NULL as
> [Installateur!12!],NULL as [PxTuyauterie!13!_g_12!hide],NULL as
> [PxTuyauterie!13!],NULL as [PxCalo!14!_g_13!hide],NULL as
> [PxCalo!14!],NULL as [PxElec!15!_g_14!hide],NULL as [PxElec!15!],NULL
> as [PorteFusible!16!_g_15!hide],NULL as [PorteFusible!16!],NULL as
> [PlusValue30m!17!_g_16!hide],NULL as [PlusValue30m!17!],NULL as
> [AvenantAjout!18!PrimaryKey!hide],NULL as
> [Fournisseur!19!NomFournisseur!element],NULL as
> [Fournisseur!19!PrimaryKey!hide],NULL as
> [TypeContrat!20!_g_19!hide],NULL as [TypeContrat!20!],NULL as
> [Numero!21!_g_20!hide],NULL as [Numero!21!],NULL as
> [DateReception!22!_g_21!hide],NULL as [DateReception!22!],NULL as
> [Observation!23!_g_22!hide],NULL as [Observation!23!],NULL as
> [DateContrat!24!_g_23!hide],NULL as [DateContrat!24!],NULL as
> [MESOfficielle!25!_g_24!hide],NULL as [MESOfficielle!25!],NULL as
> [MESComptage!26!_g_25!hide],NULL as [MESComptage!26!],NULL as
> [AvenantSupp!27!PrimaryKey!hide],NULL as
> [Fournisseur!28!NomFournisseur!element],NULL as
> [Fournisseur!28!PrimaryKey!hide],NULL as
> [TypeContrat!29!_g_28!hide],NULL as [TypeContrat!29!],NULL as
> [Numero!30!_g_29!hide],NULL as [Numero!30!],NULL as
> [DateReception!31!_g_30!hide],NULL as [DateReception!31!],NULL as
> [Observation!32!_g_31!hide],NULL as [Observation!32!],NULL as
> [DateSuppression!33!_g_32!hide],NULL as [DateSuppression!33!],NULL as
> [Periode!34!_g_33!hide],NULL as [Periode!34!],NULL as
> [Fournisseur!35!NomFournisseur!element],NULL as
> [Fournisseur!35!PrimaryKey!hide],NULL as
> [TypeContrat!36!_g_35!hide],NULL as [TypeContrat!36!],NULL as
> [EnCharge!37!Qui!element],NULL as [EnCharge!37!PrimaryKey!hide],NULL
> as [MiseEnService!38!_g_37!hide],NULL as [MiseEnService!38!],NULL as
> [DateDernierControl!39!_g_38!hide],NULL as
> [DateDernierControl!39!],NULL as [AnneeFabrication!40!_g_39!hide],NULL
> as [AnneeFabrication!40!],NULL as
> [ContratFournisseur!41!DateSignature!element],NULL as
> [ContratFournisseur!41!PrimaryKey!hide],NULL as
> [Telerelevage!42!TypeCarte!element],NULL as
> [Telerelevage!42!TypeConcentrateur!element],NULL as
> [Telerelevage!42!Protocole!element],NULL as
> [Telerelevage!42!AdresseCarte!element],NULL as
> [Telerelevage!42!Utilisation!element],NULL as
> [Telerelevage!42!NumTelephone!element],NULL as
> [Telerelevage!42!TypeLigne!element],NULL as
> [Telerelevage!42!TypeModem!element],NULL as
> [Telerelevage!42!TypeRegulateur!element],NULL as
> [Telerelevage!42!PrimaryKey!hide],NULL as
> [Mesureur!43!Code!element],NULL as
> [Mesureur!43!Observation!element],NULL as
> [Mesureur!43!PrimaryKey!hide],NULL as
> [InterventionCompteurEnergie!44!Nature!element],NULL as
> [InterventionCompteurEnergie!44!DateIntervention!element],NULL as
> [InterventionCompteurEnergie!44!Observation!element],NULL as
> [InterventionCompteurEnergie!44!Panne!element],NULL as
> [InterventionCompteurEnergie!44!Rapport!element],NULL as
> [InterventionCompteurEnergie!44!PrimaryKey!hide],NULL as
> [InterventionEchangeur!45!Nature!element],NULL as
> [InterventionEchangeur!45!DateIntervention!element],NULL as
> [InterventionEchangeur!45!Observation!element],NULL as
> [InterventionEchangeur!45!PrimaryKey!hide],NULL as
> [InterventionProdEauChaudeSanitaire!46!Nature!element],NULL as
> [InterventionProdEauChaudeSanitaire!46!DateIntervention!element],NULL
> as [InterventionProdEauChaudeSanitaire!46!Observation!element],NULL as
>
[InterventionProdEauChaudeSanitaire!46!IndexEauChaudeSanitaire!element],NULL
> as [InterventionProdEauChaudeSanitaire!46!PrimaryKey!hide],NULL as
> [Renovation!47!Nature!element],NULL as
> [Renovation!47!DateIntervention!element],NULL as
> [Renovation!47!Observation!element],NULL as
> [Renovation!47!PrevisionTvx!element],NULL as
> [Renovation!47!LongueurCable!element],NULL as
> [Renovation!47!PrimaryKey!hide],NULL as
> [DateDepose!48!_g_47!hide],NULL as [DateDepose!48!],NULL as
> [IndexDepose!49!_g_48!hide],NULL as [IndexDepose!49!],NULL as
> [VolumeDepose!50!_g_49!hide],NULL as [VolumeDepose!50!],NULL as
> [TypeTvx!51!_g_50!hide],NULL as [TypeTvx!51!],NULL as
> [PxIntegrateur!52!_g_51!hide],NULL as [PxIntegrateur!52!],NULL as
> [TvxTuyauterieDate!53!_g_52!hide],NULL as [TvxTuyauterieDate!53!],NULL
> as [Installateur!54!_g_53!hide],NULL as [Installateur!54!],NULL as
> [PxTuyauterie!55!_g_54!hide],NULL as [PxTuyauterie!55!],NULL as
> [PxCalo!56!_g_55!hide],NULL as [PxCalo!56!],NULL as
> [PxElec!57!_g_56!hide],NULL as [PxElec!57!],NULL as
> [PorteFusible!58!_g_57!hide],NULL as [PorteFusible!58!],NULL as
> [PlusValue30m!59!_g_58!hide],NULL as [PlusValue30m!59!],NULL as
> [AvenantAjout!60!PrimaryKey!hide],NULL as
> [Fournisseur!61!NomFournisseur!element],NULL as
> [Fournisseur!61!PrimaryKey!hide],NULL as
> [TypeContrat!62!_g_61!hide],NULL as [TypeContrat!62!],NULL as
> [Numero!63!_g_62!hide],NULL as [Numero!63!],NULL as
> [DateReception!64!_g_63!hide],NULL as [DateReception!64!],NULL as
> [Observation!65!_g_64!hide],NULL as [Observation!65!],NULL as
> [DateContrat!66!_g_65!hide],NULL as [DateContrat!66!],NULL as
> [MESOfficielle!67!_g_66!hide],NULL as [MESOfficielle!67!],NULL as
> [MESComptage!68!_g_67!hide],NULL as [MESComptage!68!],NULL as
> [AvenantSupp!69!PrimaryKey!hide],NULL as
> [Fournisseur!70!NomFournisseur!element],NULL as
> [Fournisseur!70!PrimaryKey!hide],NULL as
> [TypeContrat!71!_g_70!hide],NULL as [TypeContrat!71!],NULL as
> [Numero!72!_g_71!hide],NULL as [Numero!72!],NULL as
> [DateReception!73!_g_72!hide],NULL as [DateReception!73!],NULL as
> [Observation!74!_g_73!hide],NULL as [Observation!74!],NULL as
> [DateSuppression!75!_g_74!hide],NULL as [DateSuppression!75!],NULL as
> [Periode!76!_g_75!hide],NULL as [Periode!76!],NULL as
> [Fournisseur!77!NomFournisseur!element],NULL as
> [Fournisseur!77!PrimaryKey!hide],NULL as
> [TypeContrat!78!_g_77!hide],NULL as [TypeContrat!78!],NULL as
> [EnCharge!79!Qui!element],NULL as [EnCharge!79!PrimaryKey!hide],NULL
> as [Reference!80!_g_79!hide],NULL as [Reference!80!],NULL as
> [DateDePose!81!_g_80!hide],NULL as [DateDePose!81!],NULL as
> [EchangeStandardMesureur!82!_g_81!hide],NULL as
> [EchangeStandardMesureur!82!],NULL as [Position!83!_g_82!hide],NULL as
> [Position!83!],NULL as [Prix!84!_g_83!hide],NULL as [Prix!84!],NULL as
> [DateAffectation!85!_g_84!hide],NULL as [DateAffectation!85!],NULL as
> [Marque!86!_g_85!hide],NULL as [Marque!86!],NULL as
> [Modele!87!_g_86!hide],NULL as [Modele!87!],NULL as
> [TypeDeMesureur!88!Type!element],NULL as
> [TypeDeMesureur!88!Diametre!element],NULL as
> [TypeDeMesureur!88!QDemarrage!element],NULL as
> [TypeDeMesureur!88!QMin!element],NULL as
> [TypeDeMesureur!88!QNominal!element],NULL as
> [TypeDeMesureur!88!QMaxAccidentel!element],NULL as
> [TypeDeMesureur!88!PressionMaxi!element],NULL as
> [TypeDeMesureur!88!TemperatureMax!element],NULL as
> [TypeDeMesureur!88!PImpu!element],NULL as
> [TypeDeMesureur!88!Positionnement!element],NULL as
> [TypeDeMesureur!88!GammeDeMesure!element],NULL as
> [TypeDeMesureur!88!TypeFonct!element],NULL as
> [TypeDeMesureur!88!Pression!element],NULL as
> [TypeDeMesureur!88!PrimaryKey!hide],NULL as
> [Integrateur!89!NumSerie!element],NULL as
> [Integrateur!89!DatePosePile!element],NULL as
> [Integrateur!89!Marque!element],NULL as
> [Integrateur!89!PrimaryKey!hide],NULL as
> [TypeIntegrateur!90!Type!element],NULL as
> [TypeIntegrateur!90!DeltaTemperatureMin!element],NULL as
> [TypeIntegrateur!90!DeltaTemperatureMax!element],NULL as
> [TypeIntegrateur!90!PlageTemperature!element],NULL as
> [TypeIntegrateur!90!PrimaryKey!hide],NULL as
> [DateDepose!6!xsi:nil],NULL as
> [DateDepose!48!xsi:nil],N'http://www.w3.org/2001/XMLSchema-instance'
> as [CompteurEnergie!1!xmlns:xsi] from (select _QB0.PrimaryKey AS
> _TMNDAHBOAPGA from CCIAG _QB0) _Q4,(select _QB0.Nom AS
> _TNNDIGDINNHA,_QB0.Code AS _TNNDAAJABOGA,_QB0.PrimaryKey AS
> _TNNDAHBOAPGA,_QB0.ForeignKey AS _TNNDAPNNAPGA from Antenne _QB0)
> _Q1,(select _QB0.ForeignKey AS _TONDAPNNAPGA,_QB0.PrimaryKey AS
> _TONDAHBOAPGA,_QB0.Observation AS _TONDAKONAPGA,_QB0.Alimentation AS
> _TONDIMABBOGA,_QB0.Acces AS _TONDAPPABOGA,_QB0.Fonctionnement AS
> _TONDICPABOGA,_QB0.PSouscriteECS AS _TONDAPCOAPGA,_QB0.PSouscrite AS
> _TONDIJNABOGA,_QB0.NbLogtOuSurface AS _TONDAHONAPGA,_QB0.Secteur AS
> _TONDABMABOGA,_QB0.DateMESInstallation AS
> _TONDIELABOGA,_QB0.Designation AS _TONDAKKABOGA,_QB0.CodeVisu AS
> _TONDANJABOGA,_QB0.Code AS _TONDAAJABOGA from SousStationHP _QB0)
> _Q3,(select _QB0.ForeignKey AS _THFEAPNNAPGA,_QB0.AnneeFabrication AS
> _THFEICEMCOGA,_QB0.DateDernierControl AS
> _THFEIEDMCOGA,_QB0.MiseEnService AS _THFEAMCOAPGA,_QB0.TypeContrat AS
> _THFEAEBOAPGA,_QB0.PrimaryKey AS _THFEAHBOAPGA,_QB0.Observation AS
> _THFEAKONAPGA,_QB0.Code AS _THFEAAJABOGA from CompteurEnergie _QB0)
> _Q2 WHERE (CONVERT(nvarchar(4000),_Q1._TNNDAAJABOGA,126) IS NOT NULL
> AND (CONVERT(nvarchar(4000),_Q1._TNNDAAJABOGA,126) = N'80') AND
> CONVERT(nvarchar(4000),_Q2._THFEAAJABOGA,126) IS NOT NULL AND
> (CONVERT(nvarchar(4000),_Q2._THFEAAJABOGA,126) = N'V 03200')) and
> _Q3._TONDAHBOAPGA=_Q2._THFEAPNNAPGA and
> _Q1._TNNDAHBOAPGA=_Q3._TONDAPNNAPGA and
> _Q4._TMNDAHBOAPGA=_Q1._TNNDAPNNAPGA
> union all
> select
2,1,_Q4._TMNDAHBOAPGA,_Q1._TNNDAHBOAPGA,_Q1._TNNDAHBOAPGA,_Q3._TONDAHBOAPGA,
_Q3._TONDAHBOAPGA,_Q2._THFEAHBOAPGA,NULL,NULL,_Q2._THFEAHBOAPGA,_Q5._TIFEALN
NAPGA,_Q5._TIFEIEAOAOGA,_Q5._TIFEAKONAPGA,_Q5._TIFEILPBBOGA,_Q5._TIFEAIACBOG
A,_Q5._TIFEAHBOAPGA,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N
ULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NU
LL,NULL,NULL,
>
ULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NU
LL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NUL
L,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N
ULL,NULL,NULL,NULL,N
>
LL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NUL
L,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N
ULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NU
LL,NULL,NULL,NULL,NU
>
L,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL
> from (select _QB0.Rapport AS _TIFEAIACBOGA,_QB0.Panne AS
> _TIFEILPBBOGA,_QB0.Observation AS _TIFEAKONAPGA,_QB0.DateIntervention
> AS _TIFEIEAOAOGA,_QB0.Nature AS _TIFEALNNAPGA,_QB0.PrimaryKey AS
> _TIFEAHBOAPGA,_QB0.ForeignKey AS _TIFEAPNNAPGA from
> InterventionCompteurEnergie _QB0) _Q5,(select _QB0.PrimaryKey AS
> _TMNDAHBOAPGA from CCIAG _QB0) _Q4,(select _QB0.Nom AS
> _TNNDIGDINNHA,_QB0.Code AS _TNNDAAJABOGA,_QB0.PrimaryKey AS
> _TNNDAHBOAPGA,_QB0.ForeignKey AS _TNNDAPNNAPGA from Antenne _QB0)
> _Q1,(select _QB0.ForeignKey AS _TONDAPNNAPGA,_QB0.PrimaryKey AS
> _TONDAHBOAPGA,_QB0.Observation AS _TONDAKONAPGA,_QB0.Alimentation AS
> _TONDIMABBOGA,_QB0.Acces AS _TONDAPPABOGA,_QB0.Fonctionnement AS
> _TONDICPABOGA,_QB0.PSouscriteECS AS _TONDAPCOAPGA,_QB0.PSouscrite AS
> _TONDIJNABOGA,_QB0.NbLogtOuSurface AS _TONDAHONAPGA,_QB0.Secteur AS
> _TONDABMABOGA,_QB0.DateMESInstallation AS
> _TONDIELABOGA,_QB0.Designation AS _TONDAKKABOGA,_QB0.CodeVisu AS
> _TONDANJABOGA,_QB0.Code AS _TONDAAJABOGA from SousStationHP _QB0)
> _Q3,(select _QB0.ForeignKey AS _THFEAPNNAPGA,_QB0.AnneeFabrication AS
> _THFEICEMCOGA,_QB0.DateDernierControl AS
> _THFEIEDMCOGA,_QB0.MiseEnService AS _THFEAMCOAPGA,_QB0.TypeContrat AS
> _THFEAEBOAPGA,_QB0.PrimaryKey AS _THFEAHBOAPGA,_QB0.Observation AS
> _THFEAKONAPGA,_QB0.Code AS _THFEAAJABOGA from CompteurEnergie _QB0)
> _Q2 WHERE (CONVERT(nvarchar(4000),_Q1._TNNDAAJABOGA,126) IS NOT NULL
> AND (CONVERT(nvarchar(4000),_Q1._TNNDAAJABOGA,126) = N'80') AND
> CONVERT(nvarchar(4000),_Q2._THFEAAJABOGA,126) IS NOT NULL AND
> (CONVERT(nvarchar(4000),_Q2._THFEAAJABOGA,126) = N'V 03200')) and
> _Q3._TONDAHBOAPGA=_Q2._THFEAPNNAPGA and
> _Q1._TNNDAHBOAPGA=_Q3._TONDAPNNAPGA and
> _Q4._TMNDAHBOAPGA=_Q1._TNNDAPNNAPGA and
> _Q2._THFEAHBOAPGA=_Q5._TIFEAPNNAPGA
> union all
> select ....
>
> Thanks if anyone can help (hope so :-)
> Philsql

No comments:

Post a Comment