Skip to Content

query give error:Arithmetic overflow error converting expression to data type datetime.

SELECT distinct 'Bill' as Type, T0.DocNum AS'Invoice', T1.DocNum AS'Receipt', T0.DocDate AS'InvoiceDt',case when T0.DocTotal >0 then T0.DocTotal else T0.DocTotal end as Total,

T0.PaidToDate ,

G.DocDate AS'ReceiptDt', G.NoDocSum, T0.CardCode, T0.CardName,case when dbo.ocrd .qrygroup14='Y' then 'Dealers' when dbo.ocrd .qrygroup35='Y' then 'Traders' End PropertyType ,

dbo.OHEM.firstName + ' ' + dbo.OHEM.lastName AS SaleExecutiveName, dbo.OCRG.GroupName,OSLP.slpName

FROM (select NoDocSum,DocDate,DocNum,docentry from dbo.ORCT where CANCELED ='N' ) G INNER JOIN

dbo.RCT1 AS T1 ON G.DocNum = T1.DocNum right outer join

dbo.OINV AS T0 ON T0.ReceiptNum = G.docentry inner join dbo.ocrd on T0.CardCode = dbo.ocrd.CardCode

LEFT OUTER JOIN

dbo.OCRG ON dbo.OCRD.GroupCode = dbo.OCRG.GroupCode LEFT OUTER JOIN

dbo.OHEM ON dbo.OCRD.DfTcnician = dbo.OHEM.empID LEFT OUTER JOIN

OSLP on dbo.OCRD.slpcode=OSLP.slpcode

inner join ojdt T3 on T0.TransId=T3.TransId

inner join jdt1 T4 on (T4.TransId=T3.TransId

and T4.shortname= T0.cardcode )

WHERE (T0.CANCELED ='N') AND (T0.DocStatus ='o') AND (T0.DocTotal - T0.PaidToDate > 0)

and dbo.ocrd.cardType='c'

union all

SELECT distinct 'Bill' as Type, T0.DocNum AS 'Invoice', T1.DocNum AS 'Receipt' , T0.DocDate AS'InvoiceDt',case when T0.DocTotal >0 then T0.DocTotal else T0.DocTotal end as Total , T1.DocTotal as PaidToDate,case when dbo.ocrd .qrygroup14='Y' then 'Dealers' when dbo.ocrd .qrygroup35='Y' then 'Traders' End PropertyType ,

T1.DocDate AS 'ReceiptDt', null as'NoDocSum', T0.CardCode, T0.CardName,

dbo.OHEM.firstName + ' ' + dbo.OHEM.lastName AS SaleExecutiveName, dbo.OCRG.GroupName,OSLP.slpName

FROM dbo.ORIN T1 inner join dbo.rin1 T3 on T3.docentry=T1.docentry INNER JOIN

dbo.OINV AS T0 ON T3.BaseRef = T0.DocNum and T0.CardCode = T1.CardCode inner join dbo.ocrd on T0.CardCode = dbo.ocrd.CardCode

LEFT OUTER JOIN

dbo.OCRG ON dbo.OCRD.GroupCode = dbo.OCRG.GroupCode LEFT OUTER JOIN

dbo.OHEM ON dbo.OCRD.DfTcnician = dbo.OHEM.empID LEFT OUTER JOIN

OSLP on dbo.OCRD.slpcode=OSLP.slpcode

WHERE (T0.CANCELED ='N') AND (T0.DocStatus ='o') AND (T0.DocTotal - T1.DocTotal > 0) and dbo.ocrd.cardType='c' and T3.BaseType=15

union all

SELECT distinct 'Receipt' as Type, dbo.ORCT.DocNum AS 'Invoice', T0.Docnum,dbo.ORCT.DocDate AS 'Invoice Dt' ,case when dbo.ORCT.cashsum >0 then dbo.ORCT.cashsum

else dbo.ORCT.checksum end as Total, ( case when dbo.ORCT.cashsum >0 then dbo.ORCT.cashsum

else dbo.ORCT.checksum end -dbo.ORCT.OpenBal) as PaidToDate, T0.DocDate AS 'Receipt Dt' , dbo.ORCT.NoDocSum, dbo.ORCT.CardCode,case when dbo.ocrd .qrygroup14='Y' then 'Dealers' when dbo.ocrd .qrygroup35='Y' then 'Traders' End PropertyType , dbo.ORCT.CardName,

dbo.OHEM.firstName + ' ' + dbo.OHEM.lastName AS SaleExecutiveName, dbo.OCRG.GroupName,OSLP.slpName

FROM dbo.ORCT

left outer join

dbo.RCT1 AS T1 ON dbo.ORCT.DocNum = T1.DocNum

left outer join

dbo.OINV AS T0 ON T0.ReceiptNum = dbo.ORCT.DocEntry

inner join

dbo.ocrd on dbo.ORCT.CardCode = dbo.ocrd.CardCode

LEFT OUTER JOIN

dbo.OCRG ON dbo.OCRD.GroupCode = dbo.OCRG.GroupCode

LEFT OUTER JOIN

dbo.OHEM ON dbo.OCRD.DfTcnician = dbo.OHEM.empID

LEFT OUTER JOIN

OSLP on dbo.OCRD.slpcode=OSLP.slpcode

inner join

ojdt T3 on dbo.ORCT.TransId=T3.TransId

inner join

jdt1 T4 on (T4.TransId=T3.TransId and T4.shortname= dbo.ORCT .cardcode )

WHERE

(dbo.ORCT.CANCELED = 'N') and

dbo.ORCT.OpenBal>0

and dbo.ocrd.cardType='c'

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    Posted on Jul 26, 2012 at 09:27 AM

    Dear Friend,

    Try This

    SELECT distinct 'Bill' as Type, T0.DocNum AS'Invoice', T1.DocNum AS'Receipt', T0.DocDate AS'InvoiceDt',Total= isnull(case

    when T0.DocTotal >0 then T0.DocTotal else T0.DocTotal end,0) ,

    T0.PaidToDate ,

    G.DocDate AS'ReceiptDt', G.NoDocSum,

    T0.CardCode, T0.CardName,case when dbo.ocrd .qrygroup14='Y' then 'Dealers'

    when dbo.ocrd .qrygroup35='Y' then 'Traders' End PropertyType ,

    dbo.OHEM.firstName + ' ' + dbo.OHEM.lastName AS SaleExecutiveName, dbo.OCRG.GroupName,OSLP.slpName

    FROM (select NoDocSum,DocDate,DocNum,docentry from dbo.ORCT where CANCELED ='N' ) as G

    INNER JOIN dbo.RCT1 AS T1 ON G.DocNum = T1.DocNum right outer join

    dbo.OINV AS T0 ON T0.ReceiptNum = G.docentry inner join dbo.ocrd on T0.CardCode = dbo.ocrd.CardCode

    LEFT OUTER JOIN

    dbo.OCRG ON dbo.OCRD.GroupCode = dbo.OCRG.GroupCode LEFT OUTER JOIN

    dbo.OHEM ON dbo.OCRD.DfTcnician = dbo.OHEM.empID LEFT OUTER JOIN

    OSLP on dbo.OCRD.slpcode=OSLP.slpcode

    inner join ojdt T3 on T0.TransId=T3.TransId

    inner join jdt1 T4 on (T4.TransId=T3.TransId

    and T4.shortname= T0.cardcode )

    WHERE (T0.CANCELED ='N') AND (T0.DocStatus ='o') AND ((T0.DocTotal - T0.PaidToDate) > 0)

    and dbo.ocrd.cardType='c'

    union all

    SELECT distinct 'Bill' as Type, T0.DocNum AS 'Invoice', T1.DocNum AS 'Receipt' , T0.DocDate AS'InvoiceDt',

    case

    when T0.DocTotal >0 then T0.DocTotal else T0.DocTotal end as Total ,

    T1.DocTotal as PaidToDate,

    T1.DocDate AS 'ReceiptDt', null as'NoDocSum', T0.CardCode, T0.CardName,case when dbo.ocrd .qrygroup14='Y' then 'Dealers' when dbo.ocrd .qrygroup35='Y' then 'Traders' End PropertyType,

    dbo.OHEM.firstName + ' ' + dbo.OHEM.lastName AS SaleExecutiveName, dbo.OCRG.GroupName,OSLP.slpName

    FROM dbo.ORIN T1 inner join dbo.rin1 T3 on T3.docentry=T1.docentry INNER JOIN

    dbo.OINV AS T0 ON T3.BaseRef = T0.DocNum and T0.CardCode = T1.CardCode inner join dbo.ocrd on T0.CardCode = dbo.ocrd.CardCode

    LEFT OUTER JOIN

    dbo.OCRG ON dbo.OCRD.GroupCode = dbo.OCRG.GroupCode LEFT OUTER JOIN

    dbo.OHEM ON dbo.OCRD.DfTcnician = dbo.OHEM.empID LEFT OUTER JOIN

    OSLP on dbo.OCRD.slpcode=OSLP.slpcode

    WHERE (T0.CANCELED ='N') AND (T0.DocStatus ='o') AND ((T0.DocTotal - T1.DocTotal) > 0) and dbo.ocrd.cardType='c' and T3.BaseType=15

    union all

    SELECT distinct 'Receipt' as Type, dbo.ORCT.DocNum AS 'Invoice', T0.Docnum,dbo.ORCT.DocDate AS 'Invoice Dt' ,

    case when dbo.ORCT.cashsum >0 then dbo.ORCT.cashsum

    else dbo.ORCT.checksum end as Total, ( case when dbo.ORCT.cashsum >0 then dbo.ORCT.cashsum

    else dbo.ORCT.checksum end -dbo.ORCT.OpenBal) as PaidToDate,

    T0.DocDate AS 'Receipt Dt' ,

    dbo.ORCT.NoDocSum, dbo.ORCT.CardCode,case when dbo.ocrd .qrygroup14='Y' then 'Dealers'

    when dbo.ocrd .qrygroup35='Y' then 'Traders' End PropertyType , dbo.ORCT.CardName,

    dbo.OHEM.firstName + ' ' + dbo.OHEM.lastName AS SaleExecutiveName, dbo.OCRG.GroupName,OSLP.slpName

    FROM dbo.ORCT

    left outer join dbo.RCT1 AS T1 ON dbo.ORCT.DocNum = T1.DocNum

    left outer join dbo.OINV AS T0 ON T0.ReceiptNum = dbo.ORCT.DocEntry

    inner join dbo.ocrd on dbo.ORCT.CardCode = dbo.ocrd.CardCode

    LEFT OUTER JOIN dbo.OCRG ON dbo.OCRD.GroupCode = dbo.OCRG.GroupCode

    LEFT OUTER JOIN dbo.OHEM ON dbo.OCRD.DfTcnician = dbo.OHEM.empID

    LEFT OUTER JOIN OSLP on dbo.OCRD.slpcode=OSLP.slpcode

    inner join ojdt T3 on dbo.ORCT.TransId=T3.TransId

    inner join jdt1 T4 on (T4.TransId=T3.TransId and T4.shortname= dbo.ORCT .cardcode )

    WHERE

    (dbo.ORCT.CANCELED = 'N') and

    dbo.ORCT.OpenBal>0

    and dbo.ocrd.cardType='c'

    Regards

    Kennedy

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 26, 2012 at 07:38 AM

    Hi Vinayak,

    This query runs just fine on my system, so this is most likely a NULL value problem.

    The error message suggests that it is caused by one of the date fields, so please apply the ISNULL or COALESCE function to all date type fields in your query.

    Regards,

    Johan

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi,

      I am not going to correct the entire query for you, but I will give you an example:

      T0.DocDate AS 'InvoiceDt'

      should be something like this:

      ISNULL(T0.DocDate, GETDATE()) AS 'InvoiceDt'

      Now if T0.DocDate returns a NULL value, the ISNULL function will insert today's date.

      You will have to apply this same principle to all fields of the DateTime type.

      Regards,

      Johan

  • Posted on Jul 26, 2012 at 07:59 AM

    Hi,

    Try this:

    SELECT distinct 'Bill' as Type, T0.DocNum AS'Invoice', T1.DocNum AS'Receipt', T0.DocDate AS'InvoiceDt',

    case when T0.DocTotal >0 then T0.DocTotal else T0.DocTotal end as Total,

    T0.PaidToDate ,

    G.DocDate AS'ReceiptDt', G.NoDocSum, T0.CardCode, T0.CardName,case when dbo.ocrd .qrygroup14='Y' then 'Dealers'

    when dbo.ocrd .qrygroup35='Y' then 'Traders' End PropertyType ,

    dbo.OHEM.firstName + ' ' + dbo.OHEM.lastName AS SaleExecutiveName, dbo.OCRG.GroupName,OSLP.slpName

    FROM (select NoDocSum,DocDate,DocNum,docentry from dbo.ORCT where CANCELED ='N' ) G INNER JOIN

    dbo.RCT1 AS T1 ON G.DocNum = T1.DocNum right outer join

    dbo.OINV AS T0 ON T0.ReceiptNum = G.docentry inner join dbo.ocrd on T0.CardCode = dbo.ocrd.CardCode

    LEFT OUTER JOIN

    dbo.OCRG ON dbo.OCRD.GroupCode = dbo.OCRG.GroupCode LEFT OUTER JOIN

    dbo.OHEM ON dbo.OCRD.DfTcnician = dbo.OHEM.empID LEFT OUTER JOIN

    OSLP on dbo.OCRD.slpcode=OSLP.slpcode

    inner join ojdt T3 on T0.TransId=T3.TransId

    inner join jdt1 T4 on (T4.TransId=T3.TransId

    and T4.shortname= T0.cardcode )

    WHERE (T0.CANCELED ='N') AND (T0.DocStatus ='o') AND (T0.DocTotal - T1.CheckSum > 0)

    and dbo.ocrd.cardType='c'

    union all

    SELECT distinct 'Bill' as Type, T0.DocNum AS 'Invoice', T1.DocNum AS 'Receipt' , T0.DocDate AS'InvoiceDt',case when T0.DocTotal >0 then T0.DocTotal else T0.DocTotal end as Total , T1.DocTotal as PaidToDate,case when dbo.ocrd .qrygroup14='Y' then 'Dealers' when dbo.ocrd .qrygroup35='Y' then 'Traders' End PropertyType ,

    T1.DocDate AS 'ReceiptDt', null as'NoDocSum', T0.CardCode, T0.CardName,

    dbo.OHEM.firstName + ' ' + dbo.OHEM.lastName AS SaleExecutiveName, dbo.OCRG.GroupName,OSLP.slpName

    FROM dbo.ORIN T1 inner join dbo.rin1 T3 on T3.docentry=T1.docentry INNER JOIN

    dbo.OINV AS T0 ON T3.BaseRef = T0.DocNum and T0.CardCode = T1.CardCode inner join dbo.ocrd on T0.CardCode = dbo.ocrd.CardCode

    LEFT OUTER JOIN

    dbo.OCRG ON dbo.OCRD.GroupCode = dbo.OCRG.GroupCode LEFT OUTER JOIN

    dbo.OHEM ON dbo.OCRD.DfTcnician = dbo.OHEM.empID LEFT OUTER JOIN

    OSLP on dbo.OCRD.slpcode=OSLP.slpcode

    WHERE (T0.CANCELED ='N') AND (T0.DocStatus ='o') AND (T0.DocTotal - T1.DocTotal > 0) and dbo.ocrd.cardType='c' and T3.BaseType=15

    union all

    SELECT distinct 'Receipt' as Type, dbo.ORCT.DocNum AS 'Invoice', T0.Docnum,dbo.ORCT.DocDate AS 'Invoice Dt' ,case when dbo.ORCT.cashsum >0 then dbo.ORCT.cashsum

    else dbo.ORCT.checksum end as Total, ( case when dbo.ORCT.cashsum >0 then dbo.ORCT.cashsum

    else dbo.ORCT.checksum end -dbo.ORCT.OpenBal) as PaidToDate, T0.DocDate AS 'Receipt Dt' , dbo.ORCT.NoDocSum, dbo.ORCT.CardCode,case when dbo.ocrd .qrygroup14='Y' then 'Dealers' when dbo.ocrd .qrygroup35='Y' then 'Traders' End PropertyType , dbo.ORCT.CardName,

    dbo.OHEM.firstName + ' ' + dbo.OHEM.lastName AS SaleExecutiveName, dbo.OCRG.GroupName,OSLP.slpName

    FROM dbo.ORCT

    left outer join

    dbo.RCT1 AS T1 ON dbo.ORCT.DocNum = T1.DocNum

    left outer join

    dbo.OINV AS T0 ON T0.ReceiptNum = dbo.ORCT.DocEntry

    inner join

    dbo.ocrd on dbo.ORCT.CardCode = dbo.ocrd.CardCode

    LEFT OUTER JOIN

    dbo.OCRG ON dbo.OCRD.GroupCode = dbo.OCRG.GroupCode

    LEFT OUTER JOIN

    dbo.OHEM ON dbo.OCRD.DfTcnician = dbo.OHEM.empID

    LEFT OUTER JOIN

    OSLP on dbo.OCRD.slpcode=OSLP.slpcode

    inner join

    ojdt T3 on dbo.ORCT.TransId=T3.TransId

    inner join

    jdt1 T4 on (T4.TransId=T3.TransId and T4.shortname= dbo.ORCT .cardcode )

    WHERE

    (dbo.ORCT.CANCELED = 'N') and

    dbo.ORCT.OpenBal>0

    and dbo.ocrd.cardType='c'

    Thank you,

    Hari

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 26, 2012 at 08:31 AM

    Hi Vinayak,

    Try This

    SELECT distinct 'Bill' as Type, T0.DocNum AS'Invoice', T1.DocNum AS'Receipt', T0.DocDate AS'InvoiceDt',Total= isnull(case

    when T0.DocTotal >0 then T0.DocTotal else T0.DocTotal end,0) ,

    T0.PaidToDate ,

    G.DocDate AS'ReceiptDt', G.NoDocSum, T0.CardCode, T0.CardName,case when dbo.ocrd .qrygroup14='Y' then 'Dealers' when dbo.ocrd .qrygroup35='Y' then 'Traders' End PropertyType ,

    dbo.OHEM.firstName + ' ' + dbo.OHEM.lastName AS SaleExecutiveName, dbo.OCRG.GroupName,OSLP.slpName

    FROM (select NoDocSum,DocDate,DocNum,docentry from dbo.ORCT where CANCELED ='N' ) G INNER JOIN

    dbo.RCT1 AS T1 ON G.DocNum = T1.DocNum right outer join

    dbo.OINV AS T0 ON T0.ReceiptNum = G.docentry inner join dbo.ocrd on T0.CardCode = dbo.ocrd.CardCode

    LEFT OUTER JOIN

    dbo.OCRG ON dbo.OCRD.GroupCode = dbo.OCRG.GroupCode LEFT OUTER JOIN

    dbo.OHEM ON dbo.OCRD.DfTcnician = dbo.OHEM.empID LEFT OUTER JOIN

    OSLP on dbo.OCRD.slpcode=OSLP.slpcode

    inner join ojdt T3 on T0.TransId=T3.TransId

    inner join jdt1 T4 on (T4.TransId=T3.TransId

    and T4.shortname= T0.cardcode )

    WHERE (T0.CANCELED ='N') AND (T0.DocStatus ='o') AND ((T0.DocTotal - T0.PaidToDate) > 0)

    and dbo.ocrd.cardType='c'

    union all

    SELECT distinct 'Bill' as Type, T0.DocNum AS 'Invoice', T1.DocNum AS 'Receipt' , T0.DocDate AS'InvoiceDt',case when T0.DocTotal >0 then T0.DocTotal else T0.DocTotal end as Total , T1.DocTotal as PaidToDate,case when dbo.ocrd .qrygroup14='Y' then 'Dealers' when dbo.ocrd .qrygroup35='Y' then 'Traders' End PropertyType ,

    T1.DocDate AS 'ReceiptDt', null as'NoDocSum', T0.CardCode, T0.CardName,

    dbo.OHEM.firstName + ' ' + dbo.OHEM.lastName AS SaleExecutiveName, dbo.OCRG.GroupName,OSLP.slpName

    FROM dbo.ORIN T1 inner join dbo.rin1 T3 on T3.docentry=T1.docentry INNER JOIN

    dbo.OINV AS T0 ON T3.BaseRef = T0.DocNum and T0.CardCode = T1.CardCode inner join dbo.ocrd on T0.CardCode = dbo.ocrd.CardCode

    LEFT OUTER JOIN

    dbo.OCRG ON dbo.OCRD.GroupCode = dbo.OCRG.GroupCode LEFT OUTER JOIN

    dbo.OHEM ON dbo.OCRD.DfTcnician = dbo.OHEM.empID LEFT OUTER JOIN

    OSLP on dbo.OCRD.slpcode=OSLP.slpcode

    WHERE (T0.CANCELED ='N') AND (T0.DocStatus ='o') AND ((T0.DocTotal - T1.DocTotal) > 0) and dbo.ocrd.cardType='c' and T3.BaseType=15

    union all

    SELECT distinct 'Receipt' as Type, dbo.ORCT.DocNum AS 'Invoice', T0.Docnum,dbo.ORCT.DocDate AS 'Invoice Dt' ,case when dbo.ORCT.cashsum >0 then dbo.ORCT.cashsum

    else dbo.ORCT.checksum end as Total, ( case when dbo.ORCT.cashsum >0 then dbo.ORCT.cashsum

    else dbo.ORCT.checksum end -dbo.ORCT.OpenBal) as PaidToDate, T0.DocDate AS 'Receipt Dt' , dbo.ORCT.NoDocSum, dbo.ORCT.CardCode,case when dbo.ocrd .qrygroup14='Y' then 'Dealers' when dbo.ocrd .qrygroup35='Y' then 'Traders' End PropertyType , dbo.ORCT.CardName,

    dbo.OHEM.firstName + ' ' + dbo.OHEM.lastName AS SaleExecutiveName, dbo.OCRG.GroupName,OSLP.slpName

    FROM dbo.ORCT

    left outer join

    dbo.RCT1 AS T1 ON dbo.ORCT.DocNum = T1.DocNum

    left outer join

    dbo.OINV AS T0 ON T0.ReceiptNum = dbo.ORCT.DocEntry

    inner join

    dbo.ocrd on dbo.ORCT.CardCode = dbo.ocrd.CardCode

    LEFT OUTER JOIN

    dbo.OCRG ON dbo.OCRD.GroupCode = dbo.OCRG.GroupCode

    LEFT OUTER JOIN

    dbo.OHEM ON dbo.OCRD.DfTcnician = dbo.OHEM.empID

    LEFT OUTER JOIN

    OSLP on dbo.OCRD.slpcode=OSLP.slpcode

    inner join

    ojdt T3 on dbo.ORCT.TransId=T3.TransId

    inner join

    jdt1 T4 on (T4.TransId=T3.TransId and T4.shortname= dbo.ORCT .cardcode )

    WHERE

    (dbo.ORCT.CANCELED = 'N') and

    dbo.ORCT.OpenBal>0

    and dbo.ocrd.cardType='c'

    Regards

    Kennedy

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.