1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
| Humidite_moyenne :
LOAD dateliv
, humidite
,'Humidite_moyenne' as Humidite_moyenne;
SQL SELECT (Year(DAT_LIV) & '-' & Month(DAT_LIV)) as dateliv
, avg(CAST( Replace( Replace( REF2_F, 'Humidité: ', '' ), '%', '' ) as decimal)) as humidite
=> Erreur de conversion du type de données varchar en numeric.
FROM base.table N
GROUP BY (Year(DAT_LIV) & '-' & Month(DAT_LIV))
;
STORE Humidite_moyenne INTO $(QVD_EXTRACT_DIR)Humidite_moyenne.QVD;
DROP TABLE Humidite_moyenne;
/* AUTRES SYNTAXES DÉJA TESTÉES :
/* avg(CAST( Replace( Replace( REF2_F, 'Humidité: ', '' ), '%', '' ) as decimal)) as humidite */
/* avg(CONVERT(decimal(10,5), Replace( Replace( REF2_F, 'Humidité: ', '' ), '%', '' ))) as humidite */
/* avg(CONVERT(decimal(10,5), Replace( Replace( Replace( REF2_F, 'Humidité: ', '' ), '%', '' ), '.', ',' ))) as humidite */
/* avg( CAST( Replace( Replace( Replace( REF2_F, 'Humidité: ', '' ), '%', '' ), '.', ',' ) as DECIMAL(9,2) ) ) as humidite */
/* avg(CAST(Replace(Replace(REF2_F, 'Humidité: ', ''), '%', '') as float)) as humidite */
/* avg(Replace(Replace(REF2_F, 'Humidité: ', ''), '%', '')) as humidite */
/* date(DAT_LIV,'MM-YYYY') as dateliv */
/* avg(SubField(SubField(REF2_F, ' ', 2), '%', 1)/100) as humidite */
/* DATEADD(MONTH, DATEDIFF(MONTH, 0, DAT_LIV), 0) as dateliv */
/* avg(STRING_SPLIT(STRING_SPLIT(REF2_F , ' ')[1] , '%')[0]/100) as humidite */ |
Partager