SQL Number Field Help Needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Number Field Help Needed

Author

Topic

arorarohit
Starting Member

5 Posts

Posted - 2004-11-17 : 08:35:52

Hi,

I am very much newbie in the world of SQL. Need some help........
I have a table which has some rows of data with it, shown as:

G1 G2 VALUE
--- ---- ----------
ABCDE YY1 2911816459
ABCDE YY2 6378500289
ABCDE YY3 3656723
ABCDE YY4 6437834
.
.
.
.
ABCDE YY20 4988949

What I need in my resultset is a value grouped only on G1. When I do the Grouping this is what I get......

G1 VALUE
--- --------------
ABCDE 3.7584E+11

This result set has to be used as a flat file to be uploaded inanother system. The code behaves haywards because it no longer treats the value as a number. Even if it converts, it does multiply the value by 10^11. Which gives inaccurate results. Is there any way using which I can get the value in the form of '378541265987' .

Else, I am

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-17 : 08:44:40

try
select G1, sum(cast(value as bigint)) as value
from MYtable
group by G1

Go with the flow & have fun! Else fight the flow

arorarohit
Starting Member

5 Posts

Posted - 2004-11-17 : 08:47:33

Got the error: invalid CAST to a type that is not a nested table or VARRAY

Be Bachelor, Live life on own terms.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-17 : 08:55:50

u got that error where??

Go with the flow & have fun! Else fight the flow

arorarohit
Starting Member

5 Posts

Posted - 2004-11-17 : 09:03:26

Ok, This is the main SQL I am using:

SELECT DISTINCT
T1.Field1 G1,
T2.Field4 G2,
sum(t2.Field9) Value
FROM
t1, t2
WHERE
(
T1.NUMBER0=t2.NUMBER0
)
GROUP BY
T1.Field1,
T2.Field4
ORDER BY 2

The SQL used to run the Summary is:

SELECT DISTINCT
T1.Field1 G1,
-- T2.Field4 G2,
sum(t2.Field9) Value
FROM
t1, t2
WHERE
(
T1.NUMBER0=t2.NUMBER0
)
GROUP BY
T1.Field1
-- ,
-- T2.Field4
ORDER BY 2

When I try to use the CAST function as:

SELECT DISTINCT
T1.Field1 G1,
T2.Field4 G2,
sum(cast(t2.Field9 as bigint)) Value
FROM
t1, t2
WHERE
(
T1.NUMBER0=t2.NUMBER0
)
GROUP BY
T1.Field1,
T2.Field4
ORDER BY 2

The SQL prompt gives me the error as:

sum(cast(t2.Field9 as bigint)) Value
*
ERROR at line 10:
ORA-22907: invalid CAST to a type that is not a nested table or VARRAY

Pls help.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-17 : 09:28:06

aaaa that's oracle.... what's the oralce's variation of bigint?

Go with the flow & have fun! Else fight the flow

arorarohit
Starting Member

5 Posts

Posted - 2004-11-17 : 09:34:32

I have the least idea.....

arorarohit
Starting Member

5 Posts

Posted - 2004-11-17 : 09:57:11

USED TO_CHAR to get the data as a text value and the n format that in the program.
Thanks a lot for all the effort and help provided to me.

Regards,
Rohit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources