avatar image
Former Member

SQL Query Error on Conversion

Hi ,

I tried to run this query but got error as" Conversion Failed while converting nvarchar to int

select a.transid,SUBSTRING(CONVERT(VARCHAR(11), a.refdate, 113), 4, 8)as [Doc Month],
d.DocDate as [Doc Date],ocrd.fathercard,
d.cardcode ,d.cardname ,d.docnum as ARBillno,e.seriesname,

[Serv Chrgs - ECS]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='4080101001')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='4080101001') else 0 end,

[Reimbursement - Fees]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='1040214133')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='1040214133') else 0 end,

[Other Reimbursement]=case when (Select sum(c.Credit) from jdt1 c where a.transid=c.transid and c.account='1040214041')is not null then (Select sum(c.Credit) from jdt1 c where a.transid=c.transid and c.account='1040214041') else 0 end,
[Reimbursement - Others]=case when (Select sum(c.Credit) from jdt1 c where a.transid=c.transid and c.account='1040214136')is not null then (Select sum(c.Credit) from jdt1 c where a.transid=c.transid and c.account='1040214136') else 0 end,
[Reimbursement - Register Purchase]=case when (Select sum(c.Credit) from jdt1 c where a.transid=c.transid and c.account='1040214134')is not null then (Select sum(c.Credit) from jdt1 c where a.transid=c.transid and c.account='1040214134') else 0 end,
[ST Payable @ 14%]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105103')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105103')else 0 end,
[ST Payable @ 12%]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105091')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105091')else 0 end,
[ST Payable @ 10%]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105093')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105093')else 0 end,
[SB Cess Payable @ .5%]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105112')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105112')else 0 end,
[Edu Cess Payable @ 2%]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105031')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105031')else 0 end,
[S&H Edu Cess Payable @1%]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105061')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105061')else 0 end,
[ST @ 12% - BAS]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105092')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105092') else 0 end,
[ST @ 10% - BAS]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105094')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105094') else 0 end,
[SB Cess Payable @ .5% BAS]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105113')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105113')else 0 end,
[Edu Cess @ 2% on ST]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105032')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105032') else 0 end,
[S&H Edu Cess @ 1% on ST]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105062')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105062') else 0 end,
[Round Off]=(case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='4030603001')is not null then (Select sum(c.credit)from jdt1 c where a.transid=c.transid and c.account='4030603001') else 0 end)+
(case when (Select sum(c.Credit) from jdt1 c where a.transid=c.transid and c.account='5081503001')is not null then (Select sum(c.Credit) from jdt1 c where a.transid=c.transid and c.account='5081503001')else 0 end)+
(case when (Select sum(c.Credit) from jdt1 c where a.transid=c.transid and c.account='4030603002')is not null then (Select sum(c.Credit) from jdt1 c where a.transid=c.transid and c.account='4030603002')else 0 end),
d.vatsum as ServiceTaxAmount,
d.wtsum as TDS, (d.doctotal +D.wtsum) as TotalBillAmount,(d.comments) as [Naration]

from ojdt a
inner JOIN OINV d on d.TransID=a.TransId
INNER JOIN nnm1 e ON e.series=d.series
left join orin on orin.numatcard=d.docnum
inner join ocrd on ocrd.cardcode=d.cardcode

where a.TransType = 13


and CAST(d.docdate AS datetime) BETWEEN '[%0]' AND '[%1]' and d.project='[%2]'

UNION ALL

select a.transid,SUBSTRING(CONVERT(VARCHAR(11), a.refdate, 113), 4, 8)as [Doc Month],
d.DocDate as [Doc Date],ocrd.fathercard,
d.cardcode ,d.cardname ,d.docnum as ARBillno,e.seriesname,

[Serv Chrgs - ECS]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='4080101001')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='4080101001') else 0 end *-1,

[Reimbursement - Fees]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='1040214133')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='1040214133') else 0 end*-1,

[Other Reimbursement]=case when (Select sum(c.Credit) from jdt1 c where a.transid=c.transid and c.account='1040214041')is not null then (Select sum(c.Credit) from jdt1 c where a.transid=c.transid and c.account='1040214041') else 0 end*-1,
[Reimbursement - Others]=case when (Select sum(c.Credit) from jdt1 c where a.transid=c.transid and c.account='1040214136')is not null then (Select sum(c.Credit) from jdt1 c where a.transid=c.transid and c.account='1040214136') else 0 end *-1,
[Reimbursement - Register Purchase]=case when (Select sum(c.Credit) from jdt1 c where a.transid=c.transid and c.account='1040214134')is not null then (Select sum(c.Credit) from jdt1 c where a.transid=c.transid and c.account='1040214134') else 0 end *-1,
[ST Payable @ 14%]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105103')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105103')else 0 end*-1,
[ST Payable @ 12%]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105091')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105091')else 0 end *-1,
[ST Payable @ 10%]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105093')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105093')else 0 end *-1,
[SB Cess Payable @ .5%]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105112')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105112')else 0 end*-1,
[Edu Cess Payable @ 2%]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105031')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105031')else 0 end *-1,
[S&H Edu Cess Payable @1%]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105061')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105061')else 0 end *-1,
[ST @ 12% - BAS]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105092')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105092') else 0 end *-1,
[ST @ 10% - BAS]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105094')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105094') else 0 end *-1,
[SB Cess Payable @ .5% BAS]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105113')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105113')else 0 end*-1,
[Edu Cess @ 2% on ST]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105032')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105032') else 0 end *-1,
[S&H Edu Cess @ 1% on ST]=case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105062')is not null then (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='2030105062') else 0 end *-1,
[Round Off]=((case when (Select sum(c.Credit)from jdt1 c where a.transid=c.transid and c.account='4030603001')is not null then (Select sum(c.credit)from jdt1 c where a.transid=c.transid and c.account='4030603001') else 0 end)+
(case when (Select sum(c.Credit) from jdt1 c where a.transid=c.transid and c.account='5081503001')is not null then (Select sum(c.Credit) from jdt1 c where a.transid=c.transid and c.account='5081503001')else 0 end)+
(case when (Select sum(c.Credit) from jdt1 c where a.transid=c.transid and c.account='4030603002')is not null then (Select sum(c.Credit) from jdt1 c where a.transid=c.transid and c.account='4030603002')else 0 end)*-1),
d.vatsum *-1 as ServiceTaxAmount ,
d.wtsum *-1 as TDS, (d.doctotal +D.wtsum) *-1 as TotalBillAmount,(d.comments) as [Naration]

from ojdt a
inner JOIN OINV d on d.TransID=a.TransId
INNER JOIN nnm1 e ON e.series=d.series
left join orin on orin.numatcard=d.docnum
inner join ocrd on ocrd.cardcode=d.cardcode

where a.TransType = 13



and CAST(d.docdate AS datetime) <'[%0]' and d.project='[%2]'

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Nov 17, 2016 at 07:26 AM

    Hi Ravi,

    The error may be in different places:

    • orin.numatcard=d.docnum: NumAtCard is nvarchar and DocNum is int. You should use ISNULL and CAST / CONVERT in this join, and if any NumAtCard field in the ORIN table contains an empty string, or even a single none numeric character, it will become very difficult to get this join to work.
    • You are using UNION ALL: perhaps one of the case statement columns above the union all does not explicitly match the data type of the corresponding column below the union all

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

Skip to Content