May 9, 2013 at 5:00 am
Truncate table DWProd.dbo.DIM_MATERIEL
INSERT INTO DWProd.dbo.DIM_MATERIEL
(ID_MATERIEL,
LIB_MATERIEL,
CODE_MATERIEL,
TYPE_MATERIEL,
MARQUE_MATERIEL,
MODELE_MATERIEL,
PUISSANCE,
VALEUR_ACHAT,
FOURNISSEUR,
DATE_ACHAT_MATERIEL,
DATE_DEBUT,
IMMATRICULATION,
ID_EXP,
NOM_EXP,
PROPRE_DNE_LOCATION
)
SELECT MATERIEL.ID_MATERIEL,
MATERIEL.LIB_MATERIEL,
CODE_MATERIEL,
MATERIEL.LIB_TYPE_MATERIEL AS TYPE_MATERILE,
MATERIEL.LIB_MARQUE_MATERIEL AS MARQUE_MATERIEL,
MATERIEL.MODELE_MATERIEL,
CONVERT(real,ISNULL(MATERIEL.puiss,0)) AS PUISSANCE,
CONVERT(real, ISNULL(MATERIEL.Valeur,0)) AS VALEUR_ACHAT,
MATERIEL.Fourn AS FOURNISSEUR,
CONVERT(datetime,MATERIEL.Datach) AS DATE_ACHAT_MATERIEL,
MATERIEL.D_MATERIEL AS DATE_DEBUT,
MATERIEL.Immatr AS IMMATRICULATION,
MATERIEL.ID_EXP,
NOM_EXP,
[PDNEL]
FROM
SOURCE.AGE.MATERIEL MATERIEL INNER JOIN
SOURCE.AGE.EXP EXP
on EXP.ID_EXP=MATERIEL.ID_EXP
END
GO
May 9, 2013 at 5:22 am
El_Mou (5/9/2013)
Truncate table DWProd.dbo.DIM_MATERIELINSERT INTO DWProd.dbo.DIM_MATERIEL
(ID_MATERIEL,
LIB_MATERIEL,
CODE_MATERIEL,
TYPE_MATERIEL,
MARQUE_MATERIEL,
MODELE_MATERIEL,
PUISSANCE,
VALEUR_ACHAT,
FOURNISSEUR,
DATE_ACHAT_MATERIEL,
DATE_DEBUT,
IMMATRICULATION,
ID_EXP,
NOM_EXP,
PROPRE_DNE_LOCATION
)
SELECT MATERIEL.ID_MATERIEL,
MATERIEL.LIB_MATERIEL,
CODE_MATERIEL,
MATERIEL.LIB_TYPE_MATERIEL AS TYPE_MATERILE,
MATERIEL.LIB_MARQUE_MATERIEL AS MARQUE_MATERIEL,
MATERIEL.MODELE_MATERIEL,
CONVERT(real,ISNULL(MATERIEL.puiss,0)) AS PUISSANCE,
CONVERT(real, ISNULL(MATERIEL.Valeur,0)) AS VALEUR_ACHAT,
MATERIEL.Fourn AS FOURNISSEUR,
CONVERT(datetime,MATERIEL.Datach) AS DATE_ACHAT_MATERIEL,
MATERIEL.D_MATERIEL AS DATE_DEBUT,
MATERIEL.Immatr AS IMMATRICULATION,
MATERIEL.ID_EXP,
NOM_EXP,
[PDNEL]
FROM
SOURCE.AGE.MATERIEL MATERIEL INNER JOIN
SOURCE.AGE.EXP EXP
on EXP.ID_EXP=MATERIEL.ID_EXP
END
GO
The above query doesn't have SET LANGUAGE statement (or any other SET statement), so it can not possibly produce an error:
when i do it they give me this message :
Msg 156, Level 15, State 1, Procedure PS_DIM_MATERIEL, Line 35
Incorrect syntax near the keyword 'SET'.
May 9, 2013 at 5:31 am
this is the new query with SET statement
CREATE PROCEDURE [dbo].[PS_DIM_MATERIEL]
AS
BEGIN
Truncate table DWProd.dbo.DIM_MATERIEL
INSERT INTO DWProd.dbo.DIM_MATERIEL
(ID_MATERIEL,
LIB_MATERIEL,
CODE_MATERIEL,
TYPE_MATERIEL,
MARQUE_MATERIEL,
MODELE_MATERIEL,
PUISSANCE,
VALEUR_ACHAT,
FOURNISSEUR,
DATE_ACHAT_MATERIEL,
DATE_DEBUT,
IMMATRICULATION,
ID_EXP,
NOM_EXP,
PROPRE_DNE_LOCATION
)
SELECT MATERIEL.ID_MATERIEL,
MATERIEL.LIB_MATERIEL,
CODE_MATERIEL,
MATERIEL.LIB_TYPE_MATERIEL AS TYPE_MATERILE,
MATERIEL.LIB_MARQUE_MATERIEL AS MARQUE_MATERIEL,
MATERIEL.MODELE_MATERIEL,
CONVERT(real,ISNULL(MATERIEL.puiss,0)) AS PUISSANCE,
CONVERT(real, ISNULL(MATERIEL.Valeur,0)) AS VALEUR_ACHAT,
MATERIEL.Fourn AS FOURNISSEUR,
SET LANGUAGE French
CONVERT(datetime,MATERIEL.Datach) AS DATE_ACHAT_MATERIEL,
MATERIEL.D_MATERIEL AS DATE_DEBUT,
MATERIEL.Immatr AS IMMATRICULATION,
MATERIEL.ID_EXP,
NOM_EXP,
[PDNEL]
FROM
SOURCE.AGE.MATERIEL MATERIEL INNER JOIN
SOURCE.AGE.EXP EXP
on EXP.ID_EXP=MATERIEL.ID_EXP
END
GO
and this is the message error :
Msg 156, Level 15, State 1, Procedure PS_DIM_MATERIEL, Line 36
Incorrect syntax near the keyword 'SET'.
Msg 156, Level 15, State 1, Procedure PS_DIM_MATERIEL, Line 37
Incorrect syntax near the keyword 'CONVERT'.
May 9, 2013 at 6:03 am
You cannot put SET in the middle of SELECT.
It is a separate statement and should be placed before INSERT!
Also, if you have some dates in English, then CONVERT will fail for this dates. As I've said before, dates in different languages should be processed separately. Please check my sample where it checks for date convertibility in the specific language.
So, if you have dates in two languages you will need to do two separate inserts with checking if date is convertable (using ISDATE function).
May 9, 2013 at 8:12 am
thanks you so much EUGENE for your precious help its done now for all the table who had similar message error
but still have another error with a dimension_time generate with this script 🙁 if i revolve it i can move to the next step )
CREATE PROCEDURE [dbo].[PS_DIM_TEMPS] /*(@Date_debut varchar(10), @Date_fin varchar(10))*/
AS
truncate table DWProd.dbo.DIM_TEMPS
declare @D_DEBUT datetime
declare @D_FIN datetime
declare @Date_Complete datetime
declare @Annee VARCHAR(4)
declare @Annee2 int
declare @campagne VARCHAR(5)
declare @trimestre VARCHAR(6)
declare @quinzaine varchar(1)
declare @mois VARCHAR(6)
declare @ordre_mois VARCHAR(6)
declare @mois_complet VARCHAR(12)
declare @semaine_annee VARCHAR(7)
declare @jour VARCHAR(2)
declare @jour_semaine VARCHAR(9)
declare@anneeS VARCHAR(4)
BEGIN
SET @D_DEBUT = cast('01-10-2009' as datetime)
SET @D_FIN = cast('30-09-2013' as datetime)
/*set @D_DEBUT = @Date_debut
set @D_FIN = @Date_fin*/
WHILE @D_DEBUT<@D_FIN BEGIN
set @Date_Complete= @D_DEBUT
set @Annee =datename(YY,@D_DEBUT)
set @trimestre =datepart(Q,@D_DEBUT)
set @mois = datepart(MM,@D_DEBUT)
set @ordre_mois = case @mois
when '10' then 1
when '11' then 2
when '12' then 3
when '1' then 4
when '2' then 5
when '3' then 6
when '4' then 7
when '5' then 8
when '6' then 9
when '7' then 10
when '8' then 11
when '9' then 12
end
set @mois_complet =datename(MONTH,@D_DEBUT)
set @semaine_annee = datepart(WW,@D_DEBUT)
set @jour = datepart(DD,@D_DEBUT)
set @jour_semaine = datepart(DW,@D_DEBUT)
if (@jour<16)
set @quinzaine =1
else
set @quinzaine =2
set @anneeS=@annee+1
if (@jour>0 and @mois>9) set @campagne=substring(@annee,3,2)+'/'+substring(@anneeS,3,2)
if (@jour>0 and @mois>9) set @annee2=@anneeS
INSERT INTO DIM_TEMPS(DATEID,ANNEE,TRIMESTRE,
MOIS,LIB_MOIS,SEMAINE,
JOUR,JOUR_SEMAINE,
QUINZAINE,CAMPAGNE,ANNEE_CAMP,ORDRE_MOIS)
VALUES(@Date_Complete,@Annee,CONVERT(int,@trimestre),
CONVERT(int,@mois),@mois_complet,CONVERT(int,@semaine_annee),
CONVERT(int,@jour),CONVERT(int,@jour_semaine),
CONVERT(int,@quinzaine),@campagne,@annee2,CONVERT(int,@ordre_mois))
set @D_DEBUT =dateadd(d,1,@D_DEBUT)
END
Update DIM_TEMPS set DECADE=case When day(DATEID) between 1 and 10 then convert(varchar(2),month(DATEID))+'-D1'
When day(DATEID) between 11 and 20 then convert(varchar(2),month(DATEID))+'-D2'
When day(DATEID) between 21 and 31 then convert(varchar(2),month(DATEID))+'-D3' END
END
GO
and this is the message error :
Msg 242, Level 16, State 3, Procedure PS_DIM_TEMPS, Line 29
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
(0 row(s) affected)
(1 row(s) affected)
May 9, 2013 at 9:25 am
I don't understand much of French, but I can tell you that what you are trying to do is 99% possible to achieve in a single insert statement without any loop.
Error clearly tells you, that one of the string value you generating cannot be converted to datetime.
If you could provide requirements of what you are trying to do in your proc, we might be able to help you better.
May 10, 2013 at 2:33 am
before i expose whats the requirement of this script to thank you Eugene for your help
May 10, 2013 at 3:41 am
so the first month of the year in this enterprise is October(10) and finish September (9)
( that's called CAMPAGNE its mean Crop year if i can trust Google translate 😀 )
@D_DEBUT = start_date
@D_FIN = end_date
date_complete : its a complete date ( DAY-MONTH-YEAR) = DATEID
Annee : year = ANNEE
trimestre : quarter of year = TRIMESTRE
mois : number of the month in the year calendar = MOIS
mois_complet : name of the month = LIB_MOIS
semaine_annee : number of the week in the year calendar = SEMAINE
jour : day = JOUR
jour_semaine : day of the week ( i think its the number of the day in a week ) = JOUR_SEMAINE
quinzaine : fifteen day in month ( month divided by 2 ) = QUINZAINE
campagne : campaign or crop year begin Oct and finish Sept = CAMPAGNE
annee2 : the year of the crop year ( in this format 12/13 yearbegin/yearfinish ) = ANNEE_CAMP
ordre_mois : the order of the months in the crop year 10 - 11 - 12 -1 ... = ORDRE_MOIS
in the end of the script hey put a concatenation with date complete with D1 D2 D3 every D is 10 day in the order
my internship supervisor is on vacation and i tried to expose the requirement so i hope its a apparent now for you
May 10, 2013 at 5:04 am
Ok, please see how the same thing can be done wihtou using any loop:
CREATE PROCEDURE [dbo].[PS_DIM_TEMPS] /*(@Date_debut varchar(10), @Date_fin varchar(10))*/
AS
truncate table DWProd.dbo.DIM_TEMPS
declare @D_DEBUT datetime
declare @D_FIN datetime
BEGIN
/*set @D_DEBUT = @Date_debut
set @D_FIN = @Date_fin*/
SET @D_DEBUT = cast('2009-10-01' as datetime)
SET @D_FIN = cast('2013-09-30' as datetime)
-- the following CTE will produce required rtange of dates!
-- please note: it's based on your exiosting logic which excludes D_FIN from range!
;WITH E1(N) AS ( SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) V(N)) -- 10E+1 or 10 rows
,E2(N) AS (SELECT 1 FROM E1 a, E1 b) --10E+2 or 100 rows
,E4(N) AS (SELECT 1 FROM E2 a, E2 b) --10E+4 or 10,000 rows max (covers around 27 years)
,cteDays(D) AS (SELECT TOP (DATEDIFF(d, @D_DEBUT, @D_FIN)) DATEADD(d,(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))-1, @D_DEBUT) FROM E4)
INSERT INTO DIM_TEMPS(DATEID,ANNEE,TRIMESTRE, MOIS, LIB_MOIS, SEMAINE, JOUR,JOUR_SEMAINE
,QUINZAINE,CAMPAGNE,ANNEE_CAMP,ORDRE_MOIS)
SELECT D AS Date_Complete
,DATENAME(YEAR, D) AS Annee
,DATEPART(QUARTER, D) AS trimestre
,DATEPART(MONTH, D) AS mois
,DATENAME(MONTH, D) AS mois_complete
,DATEPART(WEEK, D) AS semaine_annee
,DATEPART(DAY, D) AS jour
,DATEPART(WEEKDAY, D) AS jour_semaine
,CASE WHEN DATEPART(DAY, D) < 16 THEN 1 ELSE 2 END AS quinzaine
,CASE WHEN DATEPART(MONTH, D) >9
THEN RIGHT(CAST(YEAR(D) AS CHAR(4)),2) + '/'
+ RIGHT(CAST(YEAR(D) + 1 AS CHAR(4)),2)
ELSE RIGHT(CAST(YEAR(D) - 1 AS CHAR(4)),2) + '/'
+ RIGHT(CAST(YEAR(D) AS CHAR(4)),2)
END AS campagne
,CASE WHEN DATEPART(MONTH, D) > 9
THEN YEAR(D) + 1
ELSE YEAR(D)
END AS annee2
,DATEPART(MONTH, D) + 3 - ((DATEPART(MONTH, D)+2)/12) * 12 AS ordre_mois
FROM cteDays
Update DIM_TEMPS set DECADE=case When day(DATEID) between 1 and 10 then convert(varchar(2),month(DATEID))+'-D1'
When day(DATEID) between 11 and 20 then convert(varchar(2),month(DATEID))+'-D2'
When day(DATEID) between 21 and 31 then convert(varchar(2),month(DATEID))+'-D3' END
END
GO
The above code will be way faster then loop based one.
Now about datatypes (possible conversion errors). You did not supplied DDL of DIM_TEMPS table, so I could only guess required datatype where explicit conversion was maid.
Now please check with your language requirements for month names (mois_complete)
If they need to be in French, you will need to make sure that your database default language is French, or use "SET LANGUAGE French" before INSERT as I did show to you before.
May 10, 2013 at 5:30 am
yes i set the language i did it but still having the same message error
its supposed work fine ( of course with setting the language in conversion datetime as you show me ) because they are imported from the production environement like they works there
the 4 SP's who doesnt work yesterday had just a problem with language in conversion datatime type no more .
im supposed to change nothing on the script , my objective is to test the new features SS2012 and make a comparative study between BI tools MS SQL server 2012 and IBM Cognos 10.1
and the Microsoft Dynamics is on the is in the process of replacing another ERP so they would see if the SSQL 2012 can be more productive then others BI tools ( COGNOS ) associated with Microsoft Dynamics ( Already validated )
May 10, 2013 at 5:36 am
El_Mou (5/10/2013)
yes i set the language i did it but still having the same message errorits supposed work fine ( of course with setting the language in conversion datetime as you show me ) because they are imported from the production environement like they works there
the 4 SP's who doesnt work yesterday had just a problem with language in conversion datatime type no more .
im supposed to change nothing on the script , my objective is to test the new features SS2012 and make a comparative study between BI tools MS SQL server 2012 and IBM Cognos 10.1
and the Microsoft Dynamics is on the is in the process of replacing another ERP so they would see if the SSQL 2012 can be more productive then others BI tools ( COGNOS ) associated with Microsoft Dynamics ( Already validated )
If you don't want to change anything, you will need to find which particular value causing you a problem. So, you will need add some sort of validation of values before they used in INSERT.
May 10, 2013 at 5:39 am
Check DDL of your DIM_TEMPS table. Some varchar values you are inserting fail to convert into datetime, so it will be related to datetime columns of this table.
May 10, 2013 at 7:45 am
what i said about SP script's is valable for the table destination they came from the production on the same DW
but effectively your right i think i see something wrong in the Time_table
im gonna see that brb
May 10, 2013 at 9:14 am
i verify and i cant find the values :s im really lost !
this is the table DIM_TEMPS :
CREATE TABLE [dbo].[DIM_TEMPS](
[DATEID] [datetime] NOT NULL,
[JOUR] [int] NULL,
[JOUR_SEMAINE] [int] NULL,
[SEMAINE] [int] NULL,
[QUINZAINE] [int] NULL,
[MOIS] [int] NULL,
[ORDRE_MOIS] [int] NULL,
[LIB_MOIS] [varchar](12) NULL,
[TRIMESTRE] [int] NULL,
[ANNEE] [varchar](50) NULL,
[CAMPAGNE] [varchar](8) NULL,
[ANNEE_CAMP] [int] NULL,
[DECADE] [varchar](40) NULL,
CONSTRAINT [PK_DIM_TEMPS] PRIMARY KEY CLUSTERED
(
[DATEID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
and this is the PS_DIM_TEMPS :
ALTER PROCEDURE [dbo].[PS_DIM_TEMPS] /*(@Date_debut varchar(10), @Date_fin varchar(10))*/
AS
truncate table DWHProdVeg.dbo.DIM_TEMPS
declare @D_DEBUT datetime
declare @D_FIN datetime
declare @Date_Complete datetime
declare @Annee VARCHAR(4)
declare @Annee2 int
declare @campagne VARCHAR(5)
declare @trimestre VARCHAR(6)
declare @quinzaine varchar(1)
declare @mois VARCHAR(6)
declare @ordre_mois VARCHAR(6)
declare @mois_complet VARCHAR(12)
declare @semaine_annee VARCHAR(7)
declare @jour VARCHAR(2)
declare @jour_semaine VARCHAR(9)
declare@anneeS VARCHAR(4)
BEGIN
SET @D_DEBUT = cast('01-10-2009' as datetime)
SET @D_FIN = cast('30-09-2013' as datetime)
/*set @D_DEBUT = @Date_debut
set @D_FIN = @Date_fin*/
WHILE @D_DEBUT<@D_FIN BEGIN
set @Date_Complete= @D_DEBUT
set @Annee =datename(YY,@D_DEBUT)
set @trimestre =datepart(Q,@D_DEBUT)
set @mois = datepart(MM,@D_DEBUT)
set @ordre_mois = case @mois
when '10' then 1
when '11' then 2
when '12' then 3
when '1' then 4
when '2' then 5
when '3' then 6
when '4' then 7
when '5' then 8
when '6' then 9
when '7' then 10
when '8' then 11
when '9' then 12
end
set @mois_complet =datename(MONTH,@D_DEBUT)
set @semaine_annee = datepart(WW,@D_DEBUT)
set @jour = datepart(DD,@D_DEBUT)
set @jour_semaine = datepart(DW,@D_DEBUT)
if (@jour<16)
set @quinzaine =1
else
set @quinzaine =2
set @anneeS=@annee+1
if (@jour>0 and @mois>9) set @campagne=substring(@annee,3,2)+'/'+substring(@anneeS,3,2)
if (@jour>0 and @mois>9) set @annee2=@anneeS
SET LANGUAGE FRENCH
INSERT INTO DIM_TEMPS(DATEID,ANNEE,TRIMESTRE,
MOIS,LIB_MOIS,SEMAINE,
JOUR,JOUR_SEMAINE,
QUINZAINE,CAMPAGNE,ANNEE_CAMP,ORDRE_MOIS)
VALUES(@Date_Complete,@Annee,CONVERT(int,@trimestre),
CONVERT(int,@mois),@mois_complet,CONVERT(int,@semaine_annee),
CONVERT(int,@jour),CONVERT(int,@jour_semaine),
CONVERT(int,@quinzaine),@campagne,@annee2,CONVERT(int,@ordre_mois))
set @D_DEBUT =dateadd(d,1,@D_DEBUT)
END
Update DIM_TEMPS set DECADE=case When day(DATEID) between 1 and 10 then convert(varchar(2),month(DATEID))+'-D1'
When day(DATEID) between 11 and 20 then convert(varchar(2),month(DATEID))+'-D2'
When day(DATEID) between 21 and 31 then convert(varchar(2),month(DATEID))+'-D3' END
END
i spent a week on this , how im gonna spend on SSDT and all new features and i should give a presentation next week about my progress :s :s :s :s :s :s
May 10, 2013 at 9:30 am
ITS DONE NOW ! thanks for your help EUGENE
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply