June 16, 2008 at 12:14 pm
I am getting an error when I convert varchar to bigint. Is there a better way to do this?
INSERT INTO #ORDER_INFO (
INVOICE_REFERENCE_NUM,
MaxActivityNumber,
MinActivityNumber,
MultAcct
)
SELECT
Tr.INVOICE_REFERENCE_NUM,
Max(Trans.GL_ACCOUNT) as MaxACtivityNumber,
Min(Trans.GL_ACCOUNT) as MinACtivityNumber,
convert(bigint, Max(Trans.GL_ACCOUNT)) - convert(bigint, Min(Trans.GL_ACCOUNT)) as MultAcct FROM Trans
INNER JOIN vwTransRefNumb tr ON Trans.TRANS_NUMBER = tr.TRANS_NUMBER
WHERE --Trans.BATCH_NUM = @Batch_Num AND
Tr.INVOICE_REFERENCE_NUM in (
SELECT
Tr.INVOICE_REFERENCE_NUM
FROM Trans
INNER JOIN vwTransRefNumb tr ON Trans.TRANS_NUMBER = tr.TRANS_NUMBER
WHERE Trans.BATCH_NUM = @Batch_Num
Group By Tr.INVOICE_REFERENCE_NUM
)
AND Trans.TRANSACTION_TYPE = 'DIST'
and GL_ACCOUNT <> '12001'
and GL_ACCOUNT <> ''
AND Trans.Description NOT like '%Discount%'
Group By Tr.INVOICE_REFERENCE_NUM
Thanks,
Bhavna
June 17, 2008 at 7:21 am
Try this:
select GL_ACCOUNT
from Trans
where isnumeric(GL_ACCOUNT) = 0
Most likely, that will find the fields you're having a problem with.
You can either add "and isnumeric(GL_ACCOUNT) = 1" to your Where clause, or you can clean up any non-numeric data in that column. You'll need to decide what to do on that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 8, 2008 at 2:52 pm
That worked!
Thank you,
Bhavna
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply