Pivot table query !!!

Hanen
Hanen Member Posts: 281
edited 2009-11-18 in SQL General
Hello,

I'm using a pivot table query and in the result I have NULL amount, how can I do to get 0 and to replace the NULL values with 0 :(
Help :oops:

 
        
      SET @query = 
      ' 
    Select  
	Customer
	,"Agence" 
	,"Id Commercial" 
        ,"Commercial "
        ,"Adress" 
	,"Postal Code "  
	,"Country" 
        , ' + @cols+ ' 
    From 
		#detail  
		PIVOT (sum(Amount) FOR Period IN (' + @col_pv + ')) as pv 
	Order By Commercial
      ' 

      EXEC(@query)
Regards

Hanen TALBI

Comments

  • kriki
    kriki Member, Moderator Posts: 9,121
    Use 
    ISNULL("Some expression",0)
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Hanen
    Hanen Member Posts: 281
    I've tried that but it didn't work, I got an error message telling that it can't be used here....
    :(
    Regards

    Hanen TALBI
  • stryk
    stryk Member Posts: 645
    What did you change?

    This should work (even though I cannot test): 
            
      SET @query = 
      ' 
    Select  
   Customer
   ,"Agence" 
   ,"Id Commercial" 
        ,"Commercial "
        ,"Adress" 
   ,"Postal Code "  
   ,"Country" 
        , ' + @cols+ ' 
    From 
      #detail  
      PIVOT (isnull(sum(Amount), 0) FOR Period IN (' + @col_pv + ')) as pv 
   Order By Commercial
      ' 

      EXEC(@query)
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • Hanen
    Hanen Member Posts: 281
    I've used exactly as you said, as I mentionned it didin't work, here is the error message :

    Msg 195, Level 15, State 1, Line 26
    'isnull' is not a recognized aggregate function.


    :( Really I need your heeeeeeeeeeelp
    Regards

    Hanen TALBI
  • stryk
    stryk Member Posts: 645
    Sorry ... as mentioned, I could not test ... I was just guessing ...

    So maybe when you fill your table "#detail" maybe you could take care about these NULL values, thus before pivoting? How do you insert the records into this table?
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • Hanen
    Hanen Member Posts: 281
    Here is all the code : 
    set ansi_nulls off 
go
DECLARE @periods TABLE (per VARCHAR(10)) 
DECLARE @cols NVARCHAR(3000) 
DECLARE @col NVARCHAR(3000) 
DECLARE @pers NVARCHAR(3000) 
DECLARE @period VARCHAR(1000) 
DECLARE @col_pv VARCHAR(2000) 
DECLARE @query1 VARCHAR(3000) 
DECLARE @query2 VARCHAR(3000)
DECLARE @SQL VARCHAR(3000) 
DECLARE @file_name      varchar(100)
DECLARE @debut as smalldatetime
DECLARE @fin   as smalldatetime
DECLARE @CustomerNo1 AS VARCHAR(20),  
@CustomerNo2 AS VARCHAR(20),
@MinDate AS  varchar(10), 
@MaxDate AS varchar(10)
--Juste pour test à éliminer après
SET @MinDate   = '01/01/09'
SET @MaxDate   = '11/11/09'
SET @CustomerNo1 = 'C-0000001'
SET @CustomerNo2  = 'C-0006000'

SET @debut = cast(convert (varchar(10),@MinDate,112) as smalldatetime)
SET @fin = cast(convert (varchar(10),@MaxDate,112) as smalldatetime)


/************************ Création des colonnes (Périodes)*********************************/      
        
SET @period = '200901'
WHILE @period <= cast(CONVERT(char(6),@fin, 112)as NVARCHAR(100))
      BEGIN 
          SET @period = cast(CONVERT(char(6),@debut, 112)as NVARCHAR(100))
           INSERT INTO @periods VALUES (@period) 	
            set @debut=DATEADD(month,1,@debut)  
      END -- Fin Begin While


SELECT @col_pv = STUFF((    SELECT   
                                      '],[p'  + p.per
                              FROM  @periods AS p 
                              FOR XML PATH('') 
                         ), 1, 12, '') + ']'  

SELECT  @cols = STUFF(( SELECT   
	                                '],0) ,isnull([p' + p.per 
	                        FROM  @periods  AS p 
	                        FOR XML PATH('') 
	                      ), 1, 27, '') + '],0)' 


--SELECT @col =   STUFF(( ISNULL(@col+ ', ', '0') + @cols), 1, 1, '')


/***************************** Création de la table ********************************/

      
IF OBJECT_ID('tempdb.dbo.#detail') IS not null 
              DROP TABLE #detail 
        
      
CREATE TABLE #detail 
      ( 
      Client VARCHAR(20) ,
      --,Nom VARCHAR(50)
      Agence Varchar (100) 
      ,"Id Commercial" VARCHAR(100)
      ,COMMERCIAL VARCHAR(100)
        ,Montant DECIMAL 
      ,SIREN varchar(60)
      ,SIRET varchar(60)
        ,"ID CANAL ERP" varchar(20)   
        ,"ID COMPTE ERP" varchar(100)
        ,"ID COMPTE ERP ACTUEL" varchar(100)  
        ,"NGroupe" varchar(100)
      ,"ID COMPTE" varchar(100)      
      ,"ID CANAL" varchar(15)
      ,"Nom Canal" varchar(50)
      ,CTYPE INTEGER      
      ,GRILLE varchar(20)  
        ,"Tar Spe" varchar(10)
        ,Adresse varchar(50)
      ,Adresse2 varchar(50)
      ,"Code Postal"  varchar(20)
      ,Ville varchar(30)
      ,Period varchar(100)
      )
      
       
  
      INSERT INTO #detail 
      
SELECT 
           HDR."Sell-to Customer No_"  Client,
           'Agence'=CASE
                   WHEN 
                          COMMERCIAL.NomCommercialDistrib='' and COMMERCIAL.NTiersCommercialDistrib=''
                   THEN COMMERCIAL.NomRegroupement
                   WHEN 
                          COMMERCIAL.NTiersCommercialDistrib='' and COMMERCIAL.NomCommercialDistrib<>''
                   THEN COMMERCIAL.NomRegroupement+'#'+ COMMERCIAL.NomCommercialDistrib
                   WHEN 
                          COMMERCIAL.NomCommercialDistrib='' and COMMERCIAL.NTiersCommercialDistrib<>'' 
                   THEN COMMERCIAL.NomRegroupement+'/'+ COMMERCIAL.NTiersCommercialDistrib

                   else COMMERCIAL.NomRegroupement+'/'+ COMMERCIAL.NTiersCommercialDistrib+'#'+ COMMERCIAL.NomCommercialDistrib
         END 
,SUBCONT.Salesperson "ID COMMERCIAL"
,cast( COMMERCIAL."Name" as varchar(100))  As "COMMERCIAL"          
,HDR.Montant
,CUST."Trade Registration No_" SIREN
,CUST."Name_Company Supplement"+CUST."Trade Registration No_" SIRET
                  
,'ID CANAL ERP'=CASE
                             WHEN CUST.Typologie =(select "Parameter Value Entier" From  dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
                                     where "Parameter Value 1" like'%CANAL%FACTURATION%')--3
                             THEN CUST."ID ERP"
                END


,B."ID COMPTE ERP"

,'ID COMPTE ERP ACTUEL'=CASE
                                         WHEN CUST.Typologie =(select "Parameter Value Entier" From  dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
                                     where "Parameter Value 1" like'%CANAL%FACTURATION%')--3
                                         THEN CUST."ID COMPTE ERP"
                        END
,D."NGroupe"

,F."ID COMPTE"


,'ID CANAL'=CASE
                        WHEN  CUST.Typologie =(select "Parameter Value Entier" From  dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
                                     where "Parameter Value 1" like'%CANAL%FACTURATION%')--3
                        THEN  CUST.REF
            END   


,'Nom Canal'=CASE
                         WHEN CUST.Typologie =(select "Parameter Value Entier" From  dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
                                     where "Parameter Value 1" like'%CANAL%FACTURATION%')--3
                         THEN CUST.Name
             END

,'CTYPE'=CASE
                   WHEN  CUST.Typologie =(select "Parameter Value Entier" From  dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
                                     where "Parameter Value 1" like'%CANAL%FACTURATION%')--3
                   THEN  CUST."Type CF"
         END 


,CHR.Code GRILLE -- selon les Clients DE TYPOLOGIE Customer Type


,"Tar Spe" --les Clients de TYPOLOGIE TARIFF MODEL



,CUST."Invoice Address" Adresse
,CUST."Invoice Address 2" Adresse2
,CUST."Invoice Post Code" "Code Postal"              
,CUST."Invoice City" Ville
,isnull('p'+cast(CONVERT(char(6),HDR."Posting Date", 112)as NVARCHAR(100)) ,0) Period



FROM 
      dbo."CORIOLIS_V1$Sales Invoice Header" HDR 
      INNER Join @periods ord ON cast(CONVERT(char(6),HDR."Posting Date", 112)as NVARCHAR(100)) = per
      ,dbo."CORIOLIS_V1$Salesperson_Purchaser" COMMERCIAL
      ,dbo."CORIOLIS_V1$Subcontract" SUBCONT
      ,dbo."CORIOLIS_V1$Customer" CUST
      ,dbo."CORIOLIS_V1$Customer_Model Chronology" CHR,

                        /***********   FILTARGE SUR LES Client DE TYPOLOGIE TARIFF MODEL ******************/     
     (select distinct "Tar Spe",A."Customer No_",A."Customer Type"
        from 
                    (select distinct "Tar Spe",Tar."Customer No_",CHR."Customer Type"
                    from

                             (select 'oui' As "Tar Spe","Customer No_"
                              From dbo."CORIOLIS_V1$Customer_Model Chronology" 
                              where "Customer Type" =(select "Parameter Value Entier" from dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
                                                            where "Parameter Value 1" like '%TARIFF%MODEL%')
                             )Tar,

                 dbo."CORIOLIS_V1$Customer_Model Chronology" CHR

                  Union

                  select distinct "Tar Spe",Tar1."Customer No_",CHR."Customer Type"
                  from

                        (
						select 'non' As "Tar Spe","Customer No_"
                        From dbo."CORIOLIS_V1$Customer_Model Chronology"
                        where "Customer Type" <>	(
													select "Parameter Value Entier" 
													from dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
                                                    where "Parameter Value 1" like '%TARIFF%MODEL%'
													)
						and "Customer No_" not in 
													(
													select "Customer No_" 
													From dbo."CORIOLIS_V1$Customer_Model Chronology" 
													where "Customer Type" =	   
																(
																  select "Parameter Value Entier" 
                                                                  from dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
                                                                  where "Parameter Value 1" like '%TARIFF%MODEL%'
																)
													)
						)Tar1,
						dbo."CORIOLIS_V1$Customer_Model Chronology" CHR
						)A,
                        dbo."CORIOLIS_V1$Customer" CUST,
                        dbo."CORIOLIS_V1$Customer_Model Chronology" CHR
                        WHERE A."Customer Type"=(
										SELECT "Parameter Value Entier" 
                                        From  dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
                                        WHERE "Parameter Value 1" like'%CUSTOMER_TYPE%'
												)
)Tarif
,(
select      CUST.No_ NumB,A.Num NumA, CUST."ID ERP" as "ID COMPTE ERP"
from		dbo."CORIOLIS_V1$Customer" CUST,
            (     
			select		CUST1.No_ Num, CUST1."Parent Customer No_" , CUST1."ID ERP" 
            from		dbo."CORIOLIS_V1$Customer"  CUST1
            where		CUST1."Parent Customer No_" <> ''
						AND  CUST1.Typologie=
						(	
						select "Parameter Value Entier" 
						From  dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
						where "Parameter Value 1" like'%CANAL%FACTURATION%'
						)--3
             group by CUST1.No_  , CUST1."Parent Customer No_" ,CUST1."ID ERP"  
            ) A
where CUST.No_ =A."Parent Customer No_"
) B
,
(
SELECT    distinct CUST.No_ NumD,C.Num NumC  ,CUST.REF as "NGroupe"
FROM       dbo."CORIOLIS_V1$Customer"  CUST
		,(
			select	CUST1.No_ Num, CUST1."Parent Customer No_", CUST1.REF 
			from	dbo."CORIOLIS_V1$Customer"  CUST1
			where	CUST1."Parent Customer No_" <> ''
					and CUST1.Typologie = (	select "Parameter Value Entier" 
											From  dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
											where "Parameter Value 1" like'%COMPTE%Client%'
										   ) --2
			group by CUST1.No_  , CUST1."Parent Customer No_" ,CUST1.REF  
		)C
WHERE  CUST.No_ =C."Parent Customer No_" 
)D    
,(
select		distinct CUST.No_ NumF ,E.Num NumE,CUST.REF as "ID COMPTE"
from		dbo."CORIOLIS_V1$Customer" CUST,
		    (   
			select	CUST1.No_ Num, CUST1."Parent Customer No_", CUST1.REF 
            from	dbo."CORIOLIS_V1$Customer" CUST1
            where	CUST1."Parent Customer No_" <> ''and
					CUST1.Typologie =	(	select "Parameter Value Entier" 
											From  dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
											where "Parameter Value 1" like'%CANAL%FACTURATION%'
										 )--3 
			group by CUST1.No_  , CUST1."Parent Customer No_" ,CUST1.REF 
			)E
where CUST.No_ =E."Parent Customer No_"  
)F

------------------------ Les Conditions et les jointures ---------------------------
WHERE 
            SUBCONT."Customer No_"=HDR."Sell-to Customer No_"       
            and HDR."Sell-to Customer No_"=CUST.No_
            and COMMERCIAL.Code=SUBCONT.Salesperson
            and CHR."Customer No_"=SUBCONT."Customer No_"
            and CHR."Customer Type"= 
									--Condition Customer Type--						
									(
									select "Parameter Value Entier" 
									From  dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
                                    where "Parameter Value 1" like'%CUSTOMER_TYPE%'
									)
            and CUST.No_=CHR."Customer No_"
            and CUST.No_=SUBCONT."Customer No_"
			and CUST.No_ between @CustomerNo1 and @CustomerNo2
			and HDR."Posting Date" between @MinDate and @MaxDate
        	and CUST.No_=Tarif."Customer No_"
			and HDR."Sell-to Customer No_"=CUST.No_
			and HDR."Sell-to Customer No_"=Tarif."Customer No_"
--- les jointures que g corrigé pour le sales header pour n'afficher que les canaux de facturation
            and B.NumA= HDR."Sell-to Customer No_"
            and F.NumE= HDR."Sell-to Customer No_"  
---les jointure entre les CF,Cpte Clt            
            and D.NumC=B.NumB
            and D.NumC=F.NumF
            and B.NumB=F.NumF

----------------------------------------- Groupement ---------------------------------------------
GROUP BY 
                   HDR."Sell-to Customer No_"
                  ,HDR."Bill-to Name" 
                  ,SUBCONT."Salesperson"
                  ,COMMERCIAL."Name"
                  ,COMMERCIAL.NomRegroupement
                  ,COMMERCIAL.NTiersCommercialDistrib
                  ,COMMERCIAL.NomCommercialDistrib
                  ,CUST.Typologie
                  ,CUST.REF
                  ,CUST."Trade Registration No_"
                  ,CUST."Invoice Address 2"
                  ,CUST."Invoice Address"
                  ,CHR.Code
                  ,CUST."ID ERP"
                  ,CUST.Name
                  ,CUST."Invoice City" 
                  ,CUST."Invoice Post Code"
                  ,CUST."ID COMPTE ERP" 
                  ,CUST."Type CF"
                  --,CHR."Customer Type"
                  ,CUST."Name_Company Supplement"
                  ,CUST."Trade Registration No_"
                  ,HDR.Montant
                  ,cast(CONVERT(char(6),HDR."Posting Date", 112)as NVARCHAR(100))
                  ,B."ID COMPTE ERP"
                  ,B.NumB
                  ,D.NGroupe
                  ,F."ID COMPTE"
                  ,per
                  ,Tarif."Tar Spe"
declare @x as int 
set  @x=0 
     
      SET @query1 = 
      ' 
    Select  
		Client
		,"Agence" 
		,"Id Commercial" 
        ,"COMMERCIAL "
        ,"SIREN"
		,"SIRET" 
        ,"ID CANAL ERP"    
        ,"ID COMPTE ERP"
        ,"ID COMPTE ERP ACTUEL"   
        ,"NGroupe" 
		,"ID COMPTE"        
		,"ID CANAL" 
		,"Nom Canal" 
		,"CTYPE"       
		,"GRILLE"  
        ,"Tar Spe" 
        ,"Adresse" 
		,"Adresse2" 
		,"Code Postal"  
		,"Ville" 

	    , '     + @cols + ' 
      
     From 
		#detail  
		PIVOT ( sum(Montant)  FOR Period IN (' + @col_pv + ')) as pv 
	Order By COMMERCIAL
      ' 
print @query1
EXEC(@query1)
    :cry: I need help, I'm loosing time :(
    Regards

    Hanen TALBI
  • stryk
    stryk Member Posts: 645
    Does this little change help? When inserting the data into #detail)

    -- here is the ISNULL change:
    --,HDR.Montant
    ,ISNULL(HDR.Montant, 0)

    All Code: 
    set ansi_nulls off 
go
DECLARE @periods TABLE (per VARCHAR(10)) 
DECLARE @cols NVARCHAR(3000) 
DECLARE @col NVARCHAR(3000) 
DECLARE @pers NVARCHAR(3000) 
DECLARE @period VARCHAR(1000) 
DECLARE @col_pv VARCHAR(2000) 
DECLARE @query1 VARCHAR(3000) 
DECLARE @query2 VARCHAR(3000)
DECLARE @SQL VARCHAR(3000) 
DECLARE @file_name      varchar(100)
DECLARE @debut as smalldatetime
DECLARE @fin   as smalldatetime
DECLARE @CustomerNo1 AS VARCHAR(20),  
@CustomerNo2 AS VARCHAR(20),
@MinDate AS  varchar(10), 
@MaxDate AS varchar(10)
--Juste pour test à éliminer après
SET @MinDate   = '01/01/09'
SET @MaxDate   = '11/11/09'
SET @CustomerNo1 = 'C-0000001'
SET @CustomerNo2  = 'C-0006000'

SET @debut = cast(convert (varchar(10),@MinDate,112) as smalldatetime)
SET @fin = cast(convert (varchar(10),@MaxDate,112) as smalldatetime)


/************************ Création des colonnes (Périodes)*********************************/      
        
SET @period = '200901'
WHILE @period <= cast(CONVERT(char(6),@fin, 112)as NVARCHAR(100))
      BEGIN 
          SET @period = cast(CONVERT(char(6),@debut, 112)as NVARCHAR(100))
           INSERT INTO @periods VALUES (@period)    
            set @debut=DATEADD(month,1,@debut)  
      END -- Fin Begin While


SELECT @col_pv = STUFF((    SELECT   
                                      '],[p'  + p.per
                              FROM  @periods AS p 
                              FOR XML PATH('') 
                         ), 1, 12, '') + ']'  

SELECT  @cols = STUFF(( SELECT   
                                   '],0) ,isnull([p' + p.per 
                           FROM  @periods  AS p 
                           FOR XML PATH('') 
                         ), 1, 27, '') + '],0)' 


--SELECT @col =   STUFF(( ISNULL(@col+ ', ', '0') + @cols), 1, 1, '')


/***************************** Création de la table ********************************/

      
IF OBJECT_ID('tempdb.dbo.#detail') IS not null 
              DROP TABLE #detail 
        
      
CREATE TABLE #detail 
      ( 
      Client VARCHAR(20) ,
      --,Nom VARCHAR(50)
      Agence Varchar (100) 
      ,"Id Commercial" VARCHAR(100)
      ,COMMERCIAL VARCHAR(100)
        ,Montant DECIMAL 
      ,SIREN varchar(60)
      ,SIRET varchar(60)
        ,"ID CANAL ERP" varchar(20)   
        ,"ID COMPTE ERP" varchar(100)
        ,"ID COMPTE ERP ACTUEL" varchar(100)  
        ,"NGroupe" varchar(100)
      ,"ID COMPTE" varchar(100)      
      ,"ID CANAL" varchar(15)
      ,"Nom Canal" varchar(50)
      ,CTYPE INTEGER      
      ,GRILLE varchar(20)  
        ,"Tar Spe" varchar(10)
        ,Adresse varchar(50)
      ,Adresse2 varchar(50)
      ,"Code Postal"  varchar(20)
      ,Ville varchar(30)
      ,Period varchar(100)
      )
      
       
  
      INSERT INTO #detail 
      
SELECT 
           HDR."Sell-to Customer No_"  Client,
           'Agence'=CASE
                   WHEN 
                          COMMERCIAL.NomCommercialDistrib='' and COMMERCIAL.NTiersCommercialDistrib=''
                   THEN COMMERCIAL.NomRegroupement
                   WHEN 
                          COMMERCIAL.NTiersCommercialDistrib='' and COMMERCIAL.NomCommercialDistrib<>''
                   THEN COMMERCIAL.NomRegroupement+'#'+ COMMERCIAL.NomCommercialDistrib
                   WHEN 
                          COMMERCIAL.NomCommercialDistrib='' and COMMERCIAL.NTiersCommercialDistrib<>'' 
                   THEN COMMERCIAL.NomRegroupement+'/'+ COMMERCIAL.NTiersCommercialDistrib

                   else COMMERCIAL.NomRegroupement+'/'+ COMMERCIAL.NTiersCommercialDistrib+'#'+ COMMERCIAL.NomCommercialDistrib
         END 
,SUBCONT.Salesperson "ID COMMERCIAL"
,cast( COMMERCIAL."Name" as varchar(100))  As "COMMERCIAL" 

-- here is the ISNULL change:         
--,HDR.Montant 
,ISNULL(HDR.Montant, 0) 


,CUST."Trade Registration No_" SIREN
,CUST."Name_Company Supplement"+CUST."Trade Registration No_" SIRET
                  
,'ID CANAL ERP'=CASE
                             WHEN CUST.Typologie =(select "Parameter Value Entier" From  dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
                                     where "Parameter Value 1" like'%CANAL%FACTURATION%')--3
                             THEN CUST."ID ERP"
                END


,B."ID COMPTE ERP"

,'ID COMPTE ERP ACTUEL'=CASE
                                         WHEN CUST.Typologie =(select "Parameter Value Entier" From  dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
                                     where "Parameter Value 1" like'%CANAL%FACTURATION%')--3
                                         THEN CUST."ID COMPTE ERP"
                        END
,D."NGroupe"

,F."ID COMPTE"


,'ID CANAL'=CASE
                        WHEN  CUST.Typologie =(select "Parameter Value Entier" From  dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
                                     where "Parameter Value 1" like'%CANAL%FACTURATION%')--3
                        THEN  CUST.REF
            END   


,'Nom Canal'=CASE
                         WHEN CUST.Typologie =(select "Parameter Value Entier" From  dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
                                     where "Parameter Value 1" like'%CANAL%FACTURATION%')--3
                         THEN CUST.Name
             END

,'CTYPE'=CASE
                   WHEN  CUST.Typologie =(select "Parameter Value Entier" From  dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
                                     where "Parameter Value 1" like'%CANAL%FACTURATION%')--3
                   THEN  CUST."Type CF"
         END 


,CHR.Code GRILLE -- selon les Clients DE TYPOLOGIE Customer Type


,"Tar Spe" --les Clients de TYPOLOGIE TARIFF MODEL



,CUST."Invoice Address" Adresse
,CUST."Invoice Address 2" Adresse2
,CUST."Invoice Post Code" "Code Postal"              
,CUST."Invoice City" Ville
,isnull('p'+cast(CONVERT(char(6),HDR."Posting Date", 112)as NVARCHAR(100)) ,0) Period



FROM 
      dbo."CORIOLIS_V1$Sales Invoice Header" HDR 
      INNER Join @periods ord ON cast(CONVERT(char(6),HDR."Posting Date", 112)as NVARCHAR(100)) = per
      ,dbo."CORIOLIS_V1$Salesperson_Purchaser" COMMERCIAL
      ,dbo."CORIOLIS_V1$Subcontract" SUBCONT
      ,dbo."CORIOLIS_V1$Customer" CUST
      ,dbo."CORIOLIS_V1$Customer_Model Chronology" CHR,

                        /***********   FILTARGE SUR LES Client DE TYPOLOGIE TARIFF MODEL ******************/     
     (select distinct "Tar Spe",A."Customer No_",A."Customer Type"
        from 
                    (select distinct "Tar Spe",Tar."Customer No_",CHR."Customer Type"
                    from

                             (select 'oui' As "Tar Spe","Customer No_"
                              From dbo."CORIOLIS_V1$Customer_Model Chronology" 
                              where "Customer Type" =(select "Parameter Value Entier" from dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
                                                            where "Parameter Value 1" like '%TARIFF%MODEL%')
                             )Tar,

                 dbo."CORIOLIS_V1$Customer_Model Chronology" CHR

                  Union

                  select distinct "Tar Spe",Tar1."Customer No_",CHR."Customer Type"
                  from

                        (
                  select 'non' As "Tar Spe","Customer No_"
                        From dbo."CORIOLIS_V1$Customer_Model Chronology"
                        where "Customer Type" <>   (
                                       select "Parameter Value Entier" 
                                       from dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
                                                    where "Parameter Value 1" like '%TARIFF%MODEL%'
                                       )
                  and "Customer No_" not in 
                                       (
                                       select "Customer No_" 
                                       From dbo."CORIOLIS_V1$Customer_Model Chronology" 
                                       where "Customer Type" =      
                                                (
                                                  select "Parameter Value Entier" 
                                                                  from dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
                                                                  where "Parameter Value 1" like '%TARIFF%MODEL%'
                                                )
                                       )
                  )Tar1,
                  dbo."CORIOLIS_V1$Customer_Model Chronology" CHR
                  )A,
                        dbo."CORIOLIS_V1$Customer" CUST,
                        dbo."CORIOLIS_V1$Customer_Model Chronology" CHR
                        WHERE A."Customer Type"=(
                              SELECT "Parameter Value Entier" 
                                        From  dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
                                        WHERE "Parameter Value 1" like'%CUSTOMER_TYPE%'
                                    )
)Tarif
,(
select      CUST.No_ NumB,A.Num NumA, CUST."ID ERP" as "ID COMPTE ERP"
from      dbo."CORIOLIS_V1$Customer" CUST,
            (     
         select      CUST1.No_ Num, CUST1."Parent Customer No_" , CUST1."ID ERP" 
            from      dbo."CORIOLIS_V1$Customer"  CUST1
            where      CUST1."Parent Customer No_" <> ''
                  AND  CUST1.Typologie=
                  (   
                  select "Parameter Value Entier" 
                  From  dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
                  where "Parameter Value 1" like'%CANAL%FACTURATION%'
                  )--3
             group by CUST1.No_  , CUST1."Parent Customer No_" ,CUST1."ID ERP"  
            ) A
where CUST.No_ =A."Parent Customer No_"
) B
,
(
SELECT    distinct CUST.No_ NumD,C.Num NumC  ,CUST.REF as "NGroupe"
FROM       dbo."CORIOLIS_V1$Customer"  CUST
      ,(
         select   CUST1.No_ Num, CUST1."Parent Customer No_", CUST1.REF 
         from   dbo."CORIOLIS_V1$Customer"  CUST1
         where   CUST1."Parent Customer No_" <> ''
               and CUST1.Typologie = (   select "Parameter Value Entier" 
                                 From  dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
                                 where "Parameter Value 1" like'%COMPTE%Client%'
                                 ) --2
         group by CUST1.No_  , CUST1."Parent Customer No_" ,CUST1.REF  
      )C
WHERE  CUST.No_ =C."Parent Customer No_" 
)D    
,(
select      distinct CUST.No_ NumF ,E.Num NumE,CUST.REF as "ID COMPTE"
from      dbo."CORIOLIS_V1$Customer" CUST,
          (   
         select   CUST1.No_ Num, CUST1."Parent Customer No_", CUST1.REF 
            from   dbo."CORIOLIS_V1$Customer" CUST1
            where   CUST1."Parent Customer No_" <> ''and
               CUST1.Typologie =   (   select "Parameter Value Entier" 
                                 From  dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
                                 where "Parameter Value 1" like'%CANAL%FACTURATION%'
                               )--3 
         group by CUST1.No_  , CUST1."Parent Customer No_" ,CUST1.REF 
         )E
where CUST.No_ =E."Parent Customer No_"  
)F

------------------------ Les Conditions et les jointures ---------------------------
WHERE 
            SUBCONT."Customer No_"=HDR."Sell-to Customer No_"       
            and HDR."Sell-to Customer No_"=CUST.No_
            and COMMERCIAL.Code=SUBCONT.Salesperson
            and CHR."Customer No_"=SUBCONT."Customer No_"
            and CHR."Customer Type"= 
                           --Condition Customer Type--                  
                           (
                           select "Parameter Value Entier" 
                           From  dbo."CORIOLIS_V1$Paramétrage Etat Filaire"
                                    where "Parameter Value 1" like'%CUSTOMER_TYPE%'
                           )
            and CUST.No_=CHR."Customer No_"
            and CUST.No_=SUBCONT."Customer No_"
         and CUST.No_ between @CustomerNo1 and @CustomerNo2
         and HDR."Posting Date" between @MinDate and @MaxDate
           and CUST.No_=Tarif."Customer No_"
         and HDR."Sell-to Customer No_"=CUST.No_
         and HDR."Sell-to Customer No_"=Tarif."Customer No_"
--- les jointures que g corrigé pour le sales header pour n'afficher que les canaux de facturation
            and B.NumA= HDR."Sell-to Customer No_"
            and F.NumE= HDR."Sell-to Customer No_"  
---les jointure entre les CF,Cpte Clt            
            and D.NumC=B.NumB
            and D.NumC=F.NumF
            and B.NumB=F.NumF

----------------------------------------- Groupement ---------------------------------------------
GROUP BY 
                   HDR."Sell-to Customer No_"
                  ,HDR."Bill-to Name" 
                  ,SUBCONT."Salesperson"
                  ,COMMERCIAL."Name"
                  ,COMMERCIAL.NomRegroupement
                  ,COMMERCIAL.NTiersCommercialDistrib
                  ,COMMERCIAL.NomCommercialDistrib
                  ,CUST.Typologie
                  ,CUST.REF
                  ,CUST."Trade Registration No_"
                  ,CUST."Invoice Address 2"
                  ,CUST."Invoice Address"
                  ,CHR.Code
                  ,CUST."ID ERP"
                  ,CUST.Name
                  ,CUST."Invoice City" 
                  ,CUST."Invoice Post Code"
                  ,CUST."ID COMPTE ERP" 
                  ,CUST."Type CF"
                  --,CHR."Customer Type"
                  ,CUST."Name_Company Supplement"
                  ,CUST."Trade Registration No_"
                  ,HDR.Montant
                  ,cast(CONVERT(char(6),HDR."Posting Date", 112)as NVARCHAR(100))
                  ,B."ID COMPTE ERP"
                  ,B.NumB
                  ,D.NGroupe
                  ,F."ID COMPTE"
                  ,per
                  ,Tarif."Tar Spe"
declare @x as int 
set  @x=0 
     
      SET @query1 = 
      ' 
    Select  
      Client
      ,"Agence" 
      ,"Id Commercial" 
        ,"COMMERCIAL "
        ,"SIREN"
      ,"SIRET" 
        ,"ID CANAL ERP"    
        ,"ID COMPTE ERP"
        ,"ID COMPTE ERP ACTUEL"   
        ,"NGroupe" 
      ,"ID COMPTE"        
      ,"ID CANAL" 
      ,"Nom Canal" 
      ,"CTYPE"       
      ,"GRILLE"  
        ,"Tar Spe" 
        ,"Adresse" 
      ,"Adresse2" 
      ,"Code Postal"  
      ,"Ville" 

       , '     + @cols + ' 
      
     From 
      #detail  
      PIVOT ( sum(Montant)  FOR Period IN (' + @col_pv + ')) as pv 
   Order By COMMERCIAL
      ' 
print @query1
EXEC(@query1)
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • Hanen
    Hanen Member Posts: 281
    Sorry, but it didn't work, I've tried all that I know but no way I know there is a solution, a developper should never say it's impossible to do sthg.
    No way.     #-o
    Regards

    Hanen TALBI
  • stryk
    stryk Member Posts: 645
    Could you execute the query step-by-step until the #detail table is filled, befgore pivoting? Then check which columns are containing NULL values ... then maybe you could deal with that ...
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool

Categories