Question

在我陈述案情时插入案件期间,由于我的情况,分册将自动在任何固定号码后面再加0。

例如:

INSERT INTO test
            (test_date,
             testno,
             hours)
 SELECT  20-OCT-2010 ,
        1234 ,
       CASE
         WHEN Extract(DAY FROM( endtime - starttime )) >= 1 THEN (
         Extract(DAY FROM(
                         endtime - starttime ))
         * 24 + Extract
         (HOUR FROM(
       endtime - starttime
       )) )
         WHEN starttime IS NULL
              AND endtime IS NULL THEN 0
         ELSE ( Extract(HOUR FROM( endtime - starttime )) )
       END
       ||  
       || CASE
            WHEN endtime IS NULL
                 AND starttime IS NULL THEN 0
            ELSE Extract(MINUTE FROM ( endtime - starttime )) / 60
          END AS hours
FROM   testtime

我从上述发言中取得了以下成果:

Test_Date         TestNo           Hours
20-OCT-2010        1234            140

预期结果:

Test_Date         TestNo           Hours
20-OCT-2010        1234            14

My data type for hours is number (4,2) My error is due to that if the starttime and endtime for minute is null, it will auto add an 0 behind it. Even i change the 0 to null, it will still add 0 behind.

可否知道还有另一种方式来分配零价值?

增编

Answer 1

不要压缩小时数和小时数,而是逐个逐个计算,你应当增加。例如,3个小时和0分钟为3 + 0 = 3/code>个小时,而不是3 <> ><0> = 30/code>小时。



换言之,<<>条码>改为<>条码>。

友情链接