on 08-20-2014 9:19 AM
I am having error when I use the range of july but other date range are okay.
here is error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'Vital Ventures Mgt. Corp.' to data type int.
Select A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P From (SELECT CONVERT(nvarchar,a.TaxDate,101) A,a.LicTradNum B,a.CardCode C,ISNULL(a.CardName,' ') D,Case When a.DocType='I' Then a.Comments When a.DocType='S' then (Select top 1 (dscription) from INV1 where docEntry=d.docentry and VatGroup=d.VatGroup)End E,ISNULL(f.SeriesName,'')+Cast(a.DocNum as nvarchar) F,Case When a.DocType='I' Then a.Max1099 When a.DocType='S' Then SUM(d.PriceAfVat) End G,0 H
,Case when e.Name NOT LIKE '%VAT EXEMPT' And e.Name NOT LIKE '%Zero Rated' Then Case When a.DocType='I' Then a.Max1099-a.VatSum When a.DocType='S' Then SUM(d.LineTotal) End Else 0 End I
,Case when e.Name LIKE '%Zero Rated' Then Case When a.DocType='I' Then a.Max1099-a.VatSum When a.DocType='S' Then SUM(d.LineTotal) End Else 0 End J
,Case when e.Name LIKE '%VAT EXEMPT' Then Case When a.DocType='I' Then a.Max1099-a.VatSum When a.DocType='S' Then SUM(d.LineTotal) End Else 0 End K
,Case when e.Name NOT LIKE '%VAT EXEMPT' And e.Name NOT LIKE '%Zero Rated' Then Case When a.DocType='I' Then a.vatSum When a.DocType='S' Then SUM(d.vatSum) End Else 0 End L,'' M,a.DocType N,'SI' O,a.DocNum P
from OINV a inner join OCRD c on a.CardCode=c.CardCode inner join INV1 d on a.DocEntry=d.DocEntry left join NNM1 f on a.Series=f.Series
inner join OVTG e on d.VatGroup=e.Code Where a.DocDate Between '07/01/2014' AND '07/31/2014' and (f.SeriesName = 'SI' or a.Series = '-1') GROUP BY d.VatGroup,CONVERT(nvarchar,a.TaxDate,101),a.LicTradNum,a.CardCode, a.CardName,a.Doctype,a.comments,a.docnum,a.Max1099,e.name,a.vatsum,d.docentry,ISNULL(f.SeriesName,'')
UNION ALL
SELECT CONVERT(nvarchar,a.TaxDate,101) A,a.LicTradNum B,a.CardCode C,ISNULL(a.CardName,' ') D,Case When a.DocType='I' Then a.Comments When a.DocType='S' Then (Select top 1 (dscription) from RIN1 where docEntry=d.docentry and VatGroup=d.VatGroup) End E,ISNULL(g.SeriesName,'')+Cast(a.DocNum as nvarchar) F,Case When a.DocType='I' Then a.Max1099*-1 When a.DocType='S' Then SUM(d.PriceAfVat*-1) End G,Case When f.AcctName='SALES DISCOUNTS (Trading)' Then SUM(d.LineTotal) Else 0 End H
,Case when e.Name NOT LIKE '%VAT EXEMPT' And e.Name NOT LIKE '%Zero Rated' Then Case When a.DocType='I' Then (a.Max1099-a.VatSum)*-1 When a.DocType='S' Then SUM(d.LineTotal*-1) End Else 0 End I
,Case when e.Name LIKE '%Zero Rated' Then Case When a.DocType='I' Then (a.Max1099-a.VatSum)*-1 When a.DocType='S' Then SUM(d.LineTotal*-1) End Else 0 End J
,Case when e.Name LIKE '%VAT EXEMPT' Then Case When a.DocType='I' Then (a.Max1099-a.VatSum)*-1 When a.DocType='S' Then SUM(d.LineTotal*-1) End Else 0 End K
,Case when e.Name NOT LIKE '%VAT EXEMPT' And e.Name NOT LIKE '%Zero Rated' Then Case When a.DocType='I' Then a.vatSum*-1 When a.DocType='S' Then SUM(d.vatSum*-1) End Else 0 End L,'' M,a.DocType N,'SR/D' O,a.DocNum P
from ORIN a inner join OCRD c on a.CardCode=c.CardCode inner join RIN1 d on a.DocEntry=d.DocEntry inner join OACT f ON d.acctCode=f.acctCode left join NNM1 g on a.Series=g.Series
inner join OVTG e on d.VatGroup=e.Code Where a.DocDate Between '07/01/2014' AND '07/31/2014' and (g.SeriesName = 'SR/D' or a.Series = '-1') GROUP BY d.VatGroup,CONVERT(nvarchar,a.TaxDate,101),a.LicTradNum,a.CardCode, a.CardName,a.Doctype,a.comments,a.docnum,a.Max1099,e.name,a.vatsum,d.docentry,f.AcctName,ISNULL(g.SeriesName,'')
UNION ALL SELECT CONVERT(nvarchar,a.TaxDate,101) A,f.LicTradNum B,c.CardCode C,ISNULL(c.CardName,' ') D,c.comments E,'OR'+Cast(a.Ref1 as nvarchar) F,Case When d.Debit<>0 Then SUM(d.basesum + d.debit)*-1 Else SUM(d.basesum + d.credit) End G,0 H
,Case When d.debit<>0 then d.BaseSum*-1 Else d.BaseSum End I,0 J,0 K,Case When d.debit<>0 then d.debit*-1 Else d.Credit End L,'' M,'S' N,'OR' O,a.Ref1 P
from OJDT a inner join ORCT c on a.Ref1=c.DocNum inner join JDT1 d on a.TransId=d.TransId Inner Join OCRD f on c.CardCode=f.Cardcode
inner join OVTG e on d.VatGroup=e.Code Where a.TaxDate Between '07/01/2014' AND '07/31/2014' and d.VatGroup='Output-S' and a.TransType=30 GROUP BY d.VatGroup,CONVERT(nvarchar,a.TaxDate,101),f.LicTradNum,c.CardCode, c.CardName,c.comments,a.Ref1,a.loctotal,d.credit,d.debit,d.baseSum
) A Group By A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P Order By O,P
My problem is solved. It seems that a.Ref1=c.Comments is the culprit. Some items in Ref1 has string values So I converted my c.Comments to nvarchar like this.
a.Ref1=Convert(nvarchar,c.Comments)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Raphael, try this:
Select A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P From (
SELECT CONVERT(nvarchar,a.TaxDate,101) A,a.LicTradNum B,a.CardCode C,ISNULL(a.CardName,' ') D,Case When a.DocType='I' Then a.Comments When a.DocType='S' then (Select top 1 (dscription) from INV1 where docEntry=d.docentry and VatGroup=d.VatGroup)End E,ISNULL(f.SeriesName,'')+Cast(a.DocNum as nvarchar) F,Case When a.DocType='I' Then a.Max1099 When a.DocType='S' Then SUM(d.PriceAfVat) End G,0 H
,Case when e.Name NOT LIKE '%VAT EXEMPT' And e.Name NOT LIKE '%Zero Rated' Then Case When a.DocType='I' Then a.Max1099-a.VatSum When a.DocType='S' Then SUM(d.LineTotal) End Else 0 End I
,Case when e.Name LIKE '%Zero Rated' Then Case When a.DocType='I' Then a.Max1099-a.VatSum When a.DocType='S' Then SUM(d.LineTotal) End Else 0 End J
,Case when e.Name LIKE '%VAT EXEMPT' Then Case When a.DocType='I' Then a.Max1099-a.VatSum When a.DocType='S' Then SUM(d.LineTotal) End Else 0 End K
,Case when e.Name NOT LIKE '%VAT EXEMPT' And e.Name NOT LIKE '%Zero Rated' Then Case When a.DocType='I' Then a.vatSum When a.DocType='S' Then SUM(d.vatSum) End Else 0 End L
,'' M,a.DocType N,'SI' O, convert(varchar(254),a.DocNum) P
from OINV a
inner join OCRD c on a.CardCode=c.CardCode
inner join INV1 d on a.DocEntry=d.DocEntry
left join NNM1 f on a.Series=f.Series
inner join OVTG e on d.VatGroup=e.Code
Where a.DocDate Between '07/01/2014' AND '07/31/2014' and (f.SeriesName = 'SI' or a.Series = '-1')
GROUP BY d.VatGroup,CONVERT(nvarchar,a.TaxDate,101),a.LicTradNum,a.CardCode, a.CardName,a.Doctype,a.comments,a.docnum,a.Max1099,e.name,a.vatsum,d.docentry,ISNULL(f.SeriesName,'')
UNION ALL
SELECT CONVERT(nvarchar,a.TaxDate,101) A,a.LicTradNum B,a.CardCode C,ISNULL(a.CardName,' ') D,Case When a.DocType='I' Then a.Comments When a.DocType='S' Then (Select top 1 (dscription) from RIN1 where docEntry=d.docentry and VatGroup=d.VatGroup) End E,ISNULL(g.SeriesName,'')+Cast(a.DocNum as nvarchar) F,Case When a.DocType='I' Then a.Max1099*-1 When a.DocType='S' Then SUM(d.PriceAfVat*-1) End G,Case When f.AcctName='SALES DISCOUNTS (Trading)' Then SUM(d.LineTotal) Else 0 End H
,Case when e.Name NOT LIKE '%VAT EXEMPT' And e.Name NOT LIKE '%Zero Rated' Then Case When a.DocType='I' Then (a.Max1099-a.VatSum)*-1 When a.DocType='S' Then SUM(d.LineTotal*-1) End Else 0 End I
,Case when e.Name LIKE '%Zero Rated' Then Case When a.DocType='I' Then (a.Max1099-a.VatSum)*-1 When a.DocType='S' Then SUM(d.LineTotal*-1) End Else 0 End J
,Case when e.Name LIKE '%VAT EXEMPT' Then Case When a.DocType='I' Then (a.Max1099-a.VatSum)*-1 When a.DocType='S' Then SUM(d.LineTotal*-1) End Else 0 End K
,Case when e.Name NOT LIKE '%VAT EXEMPT' And e.Name NOT LIKE '%Zero Rated' Then Case When a.DocType='I' Then a.vatSum*-1 When a.DocType='S' Then SUM(d.vatSum*-1) End Else 0 End L,'' M,a.DocType N,'SR/D' O
,convert(varchar(254),a.DocNum) P
from ORIN a
inner join OCRD c on a.CardCode=c.CardCode
inner join RIN1 d on a.DocEntry=d.DocEntry
inner join OACT f ON d.acctCode=f.acctCode
left join NNM1 g on a.Series=g.Series
inner join OVTG e on d.VatGroup=e.Code
Where a.DocDate Between '07/01/2014' AND '07/31/2014' and (g.SeriesName = 'SR/D' or a.Series = '-1')
GROUP BY d.VatGroup,CONVERT(nvarchar,a.TaxDate,101),a.LicTradNum,a.CardCode, a.CardName,a.Doctype,a.comments,a.docnum,a.Max1099,e.name,a.vatsum,d.docentry,f.AcctName,ISNULL(g.SeriesName,'')
UNION ALL
SELECT CONVERT(nvarchar,a.TaxDate,101) A,f.LicTradNum B,c.CardCode C,ISNULL(c.CardName,' ') D,c.comments E,'OR'+Cast(a.Ref1 as nvarchar) F,Case When d.Debit<>0 Then SUM(d.basesum + d.debit)*-1 Else SUM(d.basesum + d.credit) End G,0 H
,Case When d.debit<>0 then d.BaseSum*-1 Else d.BaseSum End I,0 J,0 K,Case When d.debit<>0 then d.debit*-1 Else d.Credit End L,'' M,'S' N,'OR' O,
a.Ref1 P
from OJDT a
inner join ORCT c on a.Ref1=c.DocNum
inner join JDT1 d on a.TransId=d.TransId
Inner Join OCRD f on c.CardCode=f.Cardcode
inner join OVTG e on d.VatGroup=e.Code
Where a.TaxDate Between '07/01/2014' AND '07/31/2014' and d.VatGroup='Output-S' and a.TransType=30 GROUP BY d.VatGroup,CONVERT(nvarchar,a.TaxDate,101),f.LicTradNum,c.CardCode, c.CardName,c.comments,a.Ref1,a.loctotal,d.credit,d.debit,d.baseSum
) A Group By A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P Order By O,P
Regards,
JC.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Have you checked my reply?
Thanks & Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
You may try to replace all those dates to something like '20140701'.
Thanks,
Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Raphael...
Try This
Select A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P From (SELECT a.TaxDate A,a.LicTradNum B,a.CardCode C,ISNULL(a.CardName,' ') D,Case When a.DocType='I' Then a.Comments When a.DocType='S' then (Select top 1 (dscription) from INV1 where docEntry=d.docentry and VatGroup=d.VatGroup)End E,ISNULL(f.SeriesName,'')+Cast(a.DocNum as nvarchar) F,Case When a.DocType='I' Then a.Max1099 When a.DocType='S' Then SUM(d.PriceAfVat) End G,0 H
,Case when e.Name NOT LIKE '%VAT EXEMPT' And e.Name NOT LIKE '%Zero Rated' Then Case When a.DocType='I' Then a.Max1099-a.VatSum When a.DocType='S' Then SUM(d.LineTotal) End Else 0 End I
,Case when e.Name LIKE '%Zero Rated' Then Case When a.DocType='I' Then a.Max1099-a.VatSum When a.DocType='S' Then SUM(d.LineTotal) End Else 0 End J
,Case when e.Name LIKE '%VAT EXEMPT' Then Case When a.DocType='I' Then a.Max1099-a.VatSum When a.DocType='S' Then SUM(d.LineTotal) End Else 0 End K
,Case when e.Name NOT LIKE '%VAT EXEMPT' And e.Name NOT LIKE '%Zero Rated' Then Case When a.DocType='I' Then a.vatSum When a.DocType='S' Then SUM(d.vatSum) End Else 0 End L,'' M,a.DocType N,'SI' O,a.DocNum P
from OINV a inner join OCRD c on a.CardCode=c.CardCode inner join INV1 d on a.DocEntry=d.DocEntry left join NNM1 f on a.Series=f.Series
inner join OVTG e on d.VatGroup=e.Code Where a.DocDate Between '07/01/2014' AND '07/31/2014' and (f.SeriesName = 'SI' or a.Series = '-1') GROUP BY d.VatGroup,a.TaxDate,a.LicTradNum,a.CardCode, a.CardName,a.Doctype,a.comments,a.docnum,a.Max1099,e.name,a.vatsum,d.docentry,ISNULL(f.SeriesName,'')
UNION ALL
SELECT a.TaxDate A,a.LicTradNum B,a.CardCode C,ISNULL(a.CardName,' ') D,Case When a.DocType='I' Then a.Comments When a.DocType='S' Then (Select top 1 (dscription) from RIN1 where docEntry=d.docentry and VatGroup=d.VatGroup) End E,ISNULL(g.SeriesName,'')+Cast(a.DocNum as nvarchar) F,Case When a.DocType='I' Then a.Max1099*-1 When a.DocType='S' Then SUM(d.PriceAfVat*-1) End G,Case When f.AcctName='SALES DISCOUNTS (Trading)' Then SUM(d.LineTotal) Else 0 End H
,Case when e.Name NOT LIKE '%VAT EXEMPT' And e.Name NOT LIKE '%Zero Rated' Then Case When a.DocType='I' Then (a.Max1099-a.VatSum)*-1 When a.DocType='S' Then SUM(d.LineTotal*-1) End Else 0 End I
,Case when e.Name LIKE '%Zero Rated' Then Case When a.DocType='I' Then (a.Max1099-a.VatSum)*-1 When a.DocType='S' Then SUM(d.LineTotal*-1) End Else 0 End J
,Case when e.Name LIKE '%VAT EXEMPT' Then Case When a.DocType='I' Then (a.Max1099-a.VatSum)*-1 When a.DocType='S' Then SUM(d.LineTotal*-1) End Else 0 End K
,Case when e.Name NOT LIKE '%VAT EXEMPT' And e.Name NOT LIKE '%Zero Rated' Then Case When a.DocType='I' Then a.vatSum*-1 When a.DocType='S' Then SUM(d.vatSum*-1) End Else 0 End L,'' M,a.DocType N,'SR/D' O,a.DocNum P
from ORIN a inner join OCRD c on a.CardCode=c.CardCode inner join RIN1 d on a.DocEntry=d.DocEntry inner join OACT f ON d.acctCode=f.acctCode left join NNM1 g on a.Series=g.Series
inner join OVTG e on d.VatGroup=e.Code Where a.DocDate Between '07/01/2014' AND '07/31/2014' and (g.SeriesName = 'SR/D' or a.Series = '-1') GROUP BY d.VatGroup,a.TaxDate,a.LicTradNum,a.CardCode, a.CardName,a.Doctype,a.comments,a.docnum,a.Max1099,e.name,a.vatsum,d.docentry,f.AcctName,ISNULL(g.SeriesName,'')
UNION ALL SELECT a.TaxDate A,f.LicTradNum B,c.CardCode C,ISNULL(c.CardName,' ') D,c.comments E,'OR'+Cast(a.Ref1 as nvarchar) F,Case When d.Debit<>0 Then SUM(d.basesum + d.debit)*-1 Else SUM(d.basesum + d.credit) End G,0 H
,Case When d.debit<>0 then d.BaseSum*-1 Else d.BaseSum End I,0 J,0 K,Case When d.debit<>0 then d.debit*-1 Else d.Credit End L,'' M,'S' N,'OR' O,a.Ref1 P
from OJDT a inner join ORCT c on a.Ref1=c.DocNum inner join JDT1 d on a.TransId=d.TransId Inner Join OCRD f on c.CardCode=f.Cardcode
inner join OVTG e on d.VatGroup=e.Code Where a.TaxDate Between '07/01/2014' AND '07/31/2014' and d.VatGroup='Output-S' and a.TransType=30 GROUP BY d.VatGroup,a.TaxDate,f.LicTradNum,c.CardCode, c.CardName,c.comments,a.Ref1,a.loctotal,d.credit,d.debit,d.baseSum
) A Group By A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P Order By O,P
Regards
Kennedy
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Please check date values for this 'Vital Ventures Mgt. Corp. Some data is entered incorrectly.
Thanks & Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
89 | |
7 | |
6 | |
3 | |
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.