Error converting data type varchar to bigint

Question

Post reply

Error converting data type varchar to bigint

dba321

Hall of Fame

Points: 3917
More actions
March 16, 2006 at 8:26 am

#365569

For some reason, and I know all the fields are the same datatype of varchar, I get the error:
Error converting data type varchar to bigint
for my sql statement:

SELECT h.mnt_HeaderID as hdr_HeaderID,
            h.BatchDate +
            h.NotUsed +
            h.TransactionCode +
            h.GrossBatchTotal +
            h.NetBatchTotal +
            h.BatchTransactionCount +
            h.PNet_ID +
            h.PartnerCode +
            h.Filler as headerrow,
            h.PNet_ID as FilePath

FROM mnt_Header h

UNION ALL

(SELECT m.mnt_HeaderID as mnt_HeaderID,
            m.TransactionDate +
            m.TransactionTime +
            m.AccountNumber +
            m.TransactionCode +
            m.FieldCode +
            m.NewValue +
            m.InternalExternalFlag +
            m.PNetID +
            m.RecovererID +
            m.LoanCode +
            m.NotUsed as mnt_Row,
            '    '
            FROM mnt_MaintenanceRows m)
UNION ALL
(SELECT p.mnt_HeaderID as pmt_HeaderID,
            p.TransactionDate +
            p.TransactionTime +
            p.AccountNumber +
            p.TransactionCode +
            p.TransactionAmount +
            p.InterestFlag +
            p.SelfDirectedFlag +
            p.TransactionDesc +
            p.NetPaymentAmount +
            p.CommissionPercent +
            p.InternalExternalFlag +
            p.PNetID +
            p.RecovererID +
            p.RMSLoanCode +
            p.Filler as pmt_Row,
            '    '
            FROM mnt_PaymentRows p)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

dba321 Hall of Fame Points: 3917 More actions · Answer 1

disregard this post, I overlooked a bigint in one of my tables!

Ray M SSC-Insane Points: 21093 More actions · Answer 2

Your concatenating all fields together. I'd bet one of those fields is a bigint. and when concatenating to a string you need to cast it into a string first.

Double check it.

dba321 Hall of Fame Points: 3917 More actions · Answer 3

thanks, cast will work for me later, good idea, if any of the fields change datatype and I still want to do a UNION.