当前位置: 动力学知识库 > 问答 > 编程问答 >

rounding - Sybase round not working as expected

问题描述:

Below is my scenario. We are retrieving data from Sybase. We are using putty to achieve this since we do not have direct access to Sybase. From the inner query, i am getting value as 6.500000 for a metric. But in the outer query, when i apply Round function to this metric , it is becoming 6 instead of 7. Please help on this issue!

Below is my Inner query and the complete query. Have attached the data also for reference.

Inner Query

SELECT D.Col1,A.Col2,C.Col3,

SUM ( C.net_td_cnt ) AS td_cnt

FROM Tab1 A,Tab2 B,Tab3 C,Tab4 D

WHERE (A.Tab1 =B.Tab1 AND B.Tab2=C.yld_loc_cd AND D.Tab1=A.Tab1)

AND (D.Col1 IN ('Fil1') and A.Col2='Fil2'

AND ((C.Col3 BETWEEN 'Jul 6 2016' AND 'Jul 6 2016') OR

(C.Col3 BETWEEN 'Jul 6 2016' AND 'Jul 6 2016')))

GROUP BY D.Col1,A.Col2,C.Col3

UNION

SELECT C.Col1,A.Col2,D.Col3,

SUM ( D.net_td_cnt ) AS td_cnt

FROM Tab1 A,Tab2 B,Tab4 C,Tab5 D

WHERE ( A.Tab1 =B.Tab1 AND C.Tab1=A.Tab1 AND B.Tab2 =D.yld_loc_cd )

AND ( C.Col1 IN ('Fil1') and A.Col2='Fil2'

AND (D.Col3 BETWEEN 'Jul 6 2016' AND 'Jul 6 2016') )

GROUP BY C.Col1,A.Col2,D.Col3

Output:

Col1 |Col2 |Col3 |td_cnt

Fil1 |Fil2 |Jul 6 2016 12:00AM |6.500000

Full Query

SELECT A.Col1,A.Col2,A.Col3,

ROUND(A.td_cnt,0) AS td_cnt

FROM

(SELECT D.Col1,A.Col2,C.Col3,

SUM ( C.net_td_cnt ) AS td_cnt

FROM Tab1 A,Tab2 B,Tab3 C,Tab4 D

WHERE (A.Tab1 =B.Tab1 AND B.Tab2=C.yld_loc_cd AND D.Tab1=A.Tab1)

AND (D.Col1 IN ('Fil1') and A.Col2='Fil2'

AND ((C.Col3 BETWEEN 'Jul 6 2016' AND 'Jul 6 2016') OR

(C.Col3 BETWEEN 'Jul 6 2016' AND 'Jul 6 2016')))

GROUP BY D.Col1,A.Col2,C.Col3

UNION

SELECT C.Col1,A.Col2,D.Col3,

SUM ( D.net_td_cnt ) AS td_cnt

FROM Tab1 A,Tab2 B,Tab4 C,Tab5 D

WHERE ( A.Tab1 =B.Tab1 AND C.Tab1=A.Tab1 AND B.Tab2 =D.yld_loc_cd )

AND ( C.Col1 IN ('Cent') and A.Col2='Fil2'

AND (D.Col3 BETWEEN 'Jul 6 2016' AND 'Jul 6 2016') )

GROUP BY C.Col1,A.Col2,D.Col3

) A ORDER BY A.Col3

Output:

Col1 |Col2 |Col3 |td_cnt

Fil1 |Fil2 |Jul 6 2016 12:00AM |6

网友答案:

select round(6.500000,0) returns 7 in various versions of sybase ASE and IQ, what is your product version? Please run

select @@version

Kimon

分享给朋友:
您可能感兴趣的文章:
随机阅读: