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 | WITH t AS ( SELECT '2000152001' chitemseq, '326' chitem1, '284' chitem2, '203' chitem3 UNION ALL SELECT '2000152002' , '40' , '70' , '20' UNION ALL SELECT '2000152003' , '5.39E+07' , '1.85E+08' , '1.17E+08' UNION ALL SELECT '2000152003' , '4.00E+05' , '8.01E+05' , '4.82E+05' UNION ALL SELECT '2000152003' , '6.95E+05' , '8.01E+05' , '4.82E+05' ) SELECT chitemseq , chitem1, chitem2, chitem3 , x , CASE chitemseq WHEN '2000152001' THEN CAST ( CAST (x AS NUMERIC (12, 1)) AS VARCHAR ) WHEN '2000152002' THEN CAST ( CAST (x AS NUMERIC (12, 0)) AS VARCHAR ) WHEN '2000152003' THEN REPLACE (ROUND(x, -5), 'e+0' , 'E+' ) END z FROM ( SELECT chitemseq , chitem1, chitem2, chitem3 , ( CAST (chitem1 AS FLOAT ) + CAST (chitem2 AS FLOAT ) + CAST (chitem3 AS FLOAT ) ) / 3 AS x FROM t ) a ; |
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 31 32 33 34 | WITH t AS ( SELECT '2000152001' chitemseq, '326' chitem1, '284' chitem2, '203' chitem3 UNION ALL SELECT '2000152002' , '40' , '70' , '20' UNION ALL SELECT '2000152003' , '5.39E+07' , '1.85E+08' , '1.17E+08' UNION ALL SELECT '2000152003' , '4.00E+06' , '8.01E+05' , '4.82E+05' UNION ALL SELECT '2000152003' , '6.95E+05' , '8.01E+05' , '4.82E+05' ) SELECT chitemseq , chitem1, chitem2, chitem3 , x , y , CASE chitemseq WHEN '2000152001' THEN CAST ( CAST (x AS NUMERIC (12, 1)) AS VARCHAR ) WHEN '2000152002' THEN CAST ( CAST (x AS NUMERIC (12, 0)) AS VARCHAR ) WHEN '2000152003' THEN CONCAT( CAST (x / POWER(10, y) AS NUMERIC (3,2)) , 'E+' , RIGHT (CONCAT( '0' , y), 2) ) END z FROM ( SELECT chitemseq , chitem1, chitem2, chitem3 , ( CAST (chitem1 AS FLOAT ) + CAST (chitem2 AS FLOAT ) + CAST (chitem3 AS FLOAT ) ) / 3 AS x , FLOOR(LOG10( ( CAST (chitem1 AS FLOAT ) + CAST (chitem2 AS FLOAT ) + CAST (chitem3 AS FLOAT ) ) / 3)) AS y FROM t ) a ; |