/scripts/ahub.form.attachments.js
0

SQL Query Error on Conversion

Nov 17, 2016 at 07:07 AM

29

avatar image

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]'

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Johan Hakkesteegt Nov 17, 2016 at 07:26 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Skip to Content