on 11-17-2016 7:07 AM
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]'
Hi Ravi,
The error may be in different places:
Regards,
Johan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.