UNION Statement help - 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

General SQL Server Forums

New to SQL Server Programming

UNION Statement help

Author

Topic

j0shua
Starting Member

40 Posts

Posted - 2008-10-27 : 03:48:31

hi, i encountered a problem with the union statement. As you can see, The first select statement, i have rounded off data in the DEBIT and CREDIT, in the second select statement, i have non-rounded off data in the DEBIT and CREDIT. I noticed when i used the UNION statement, it automatically rounded off the one that is not. What i need is the non-rounded off being retained in the data. Both fields in the select statement are of the same data type which is decimal(9). Please Help me. Thank you.

select acnt_code as [code], account,
[source]='Credit Memo',
fcCreditNo as [reference],
[date] as [datetrans],
[Description],
isnull(debit,0) as [debit],
isnull(credit,0) as [credit],
fcMemo

from dbo.creditmemo_accountability
where fxKeyCompany = '87D534F7-C698-4713-B433-FDB51F9A72E3'
and (datepart(month,[date]) >= 9)
and (datepart(month,[date]) <= 9)
and (datepart(year, [date]) = 2008)

UNION

select acnt_code as [code],
account,
[source]='Inv',
fcInvoiceNo as [reference],
[date] as [datetrans],
[Description],
isnull(debit,0) as [debit],
isnull(credit,0) as [credit],
fcMemo
from dbo.invoice_accountability
where fxKeyCompany = '87D534F7-C698-4713-B433-FDB51F9A72E3'
and (datepart(month,[date]) >= 9)
and (datepart(month,[date]) <= 9)
and (datepart(year, [date]) = 2008)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 04:01:34

can you try swapping select statements? first as second and second as first seperated by UNION?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-27 : 04:59:41

Or use CAST to explicit have the precision you want.

E 12°55'05.63"
N 56°04'39.26"

j0shua
Starting Member

40 Posts

Posted - 2008-10-27 : 06:07:56

quote:
Originally posted by visakh16

can you try swapping select statements? first as second and second as first seperated by UNION?

I tried, but it produced the same "rounded off" results.

j0shua
Starting Member

40 Posts

Posted - 2008-10-27 : 06:08:42

[quote]Originally posted by Peso

Or use CAST to explicit have the precision you want.

let me try, how can i cast to explicit have the precision i want?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 06:13:38

quote:
Originally posted by j0shua

quote:
Originally posted by visakh16

can you try swapping select statements? first as second and second as first seperated by UNION?

I tried, but it produced the same "rounded off" results.

then try explicit casting as Peso suggested.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-27 : 06:14:27

Well you'd do something like:


select acnt_code as [code], account,
[source]='Credit Memo',
fcCreditNo as [reference],
[date] as [datetrans],
[Description],
CAST(isnull(debit,0) AS DECIMAL(9, x))  as [debit],

Also, you say the type is DECIMAL(9) -- that's meaningless, you should specift a scale and a precision.

Something like DECIMAL(9,4) 9 scale (total numerals), 4 precision (places after the decimal .)

-------------
Charlie

j0shua
Starting Member

40 Posts

Posted - 2008-10-27 : 06:47:39

quote:
Originally posted by Transact Charlie

Well you'd do something like:

select acnt_code as [code], account,
[source]='Credit Memo',
fcCreditNo as [reference],
[date] as [datetrans],
[Description],
CAST(isnull(debit,0) AS DECIMAL(9, x)) as [debit],

Also, you say the type is DECIMAL(9) -- that's meaningless, you should specift a scale and a precision.

Something like DECIMAL(9,4) 9 scale (total numerals), 4 precision (places after the decimal .)

-------------
Charlie