March 10, 2009 at 8:53 am
I have the following Stored Procedure which is generating an Arithmetic overflow error converting expression to data type smalldatetime. "No problem," I'm foolishly thinking to myself. It is only happening when @SortField is passed a 2 (or we let it run with the default.)
As you can see, InvoicesNotMatched_Select doesn't do much except figure out how to call the function fn_UnmatchedInvoices. This is where the work is done and most likely where the error is being generated from. Right?
So, I determined which parameters were being passed in to fn_UnmatchedInvoices (through logic and observing a trace just to make sure) and called the function directly setting the order by manually. The error does not occur. That doesn't make any sense to me; if the Order By Case statement isn't working properly, wouldn't it fail completely? I tried calling the function selecting only the date and converting it, but it still works.
I'm at a loss and I'm not sure how to even proceed to figure out why this doesn't work. Any suggestions?
Chris
ALTER PROCEDURE [Purchasing].[InvoicesNotMatched_Select](
@PersonID uniqueIdentifier,
@SourceOrganizationID uniqueidentifier = NULL,
@SortField int = 2,
@SortAscending bit = 0,
@IncludePOSpendingLimit bit = 1,
@IncludePrice bit = 1,
@IncludeReceipt bit = 1)
AS
BEGIN
SET NOCOUNT ON;
IF @SortAscending = 1
BEGIN
IF @SortField IN (1)
BEGIN
SELECT top 1000 *
FROM [Purchasing].fn_UnmatchedInvoices(
@PersonId,
@SourceOrganizationId,
@IncludePOSpendingLimit,
@IncludePrice,
@IncludeReceipt)
ORDER BY CASE @SortField WHEN 1 THEN VendorInvoiceId
END
ASC
END
ELSE
BEGIN
SELECT top 1000 *
FROM [Purchasing].fn_UnmatchedInvoices(
@PersonId,
@SourceOrganizationId,
@IncludePOSpendingLimit,
@IncludePrice,
@IncludeReceipt)
ORDER BY CASE @SortField
WHEN 2 THEN PurchaseOrderId
WHEN 3 THEN VendorInvoiceDate
END
ASC
END
END
ELSE
BEGIN
IF @SortField IN (1)
BEGIN
SELECT top 1000 *
FROM [Purchasing].fn_UnmatchedInvoices(
@PersonId,
@SourceOrganizationId,
@IncludePOSpendingLimit,
@IncludePrice,
@IncludeReceipt)
ORDER BY CASE @SortField WHEN 1 THEN VendorInvoiceId
END
DESC
END
ELSE
BEGIN
SELECT top 1000 *
FROM [Purchasing].fn_UnmatchedInvoices(
@PersonId,
@SourceOrganizationId,
@IncludePOSpendingLimit,
@IncludePrice,
@IncludeReceipt)
ORDER BY CASE @SortField
WHEN 2 THEN PurchaseOrderId
WHEN 3 THEN VendorInvoiceDate
END
DESC
END
END
END
ALTER FUNCTION [Purchasing].[fn_UnmatchedInvoices] (
@PersonID uniqueIdentifier,
@SourceOrganizationID uniqueidentifier ,
@IncludePOSpendingLimit bit,
@IncludePrice bit,
@IncludeReceipt bit)
RETURNS
@UnmatchedInvoices TABLE (
TransactionId bigint,
SourceOrganizationName nvarchar(100),
TargetOrganizationName nvarchar(100),
InvoiceId bigint,
VendorInvoiceId nvarchar(50),
VendorInvoiceDate smalldatetime,
InvoiceTotal decimal(10,2),
InvoiceSubTotal decimal(10,2),
InvoiceTypeId int,
ReleaseForPayment bit,
InvoiceComment nvarchar(255),
InvoiceStatus nvarchar(25),
PurchaseOrderId bigint,
PurchaseOrderLineItemTotal decimal(10,2),
PurchaseOrderQuantity decimal(10,4),
ReceiptQuantity decimal(10,4),
TotalAllInvoicesForPO decimal(10,2))
AS
BEGIN
DECLARE @InvoiceTable AS TABLE(
TransactionId bigint,
SourceOrganizationName nvarchar(100),
TargetOrganizationName nvarchar(100),
InvoiceId bigint,
VendorInvoiceId nvarchar(50),
VendorInvoiceDate smalldatetime,
InvoiceTotal decimal(10,2),
InvoiceSubTotal decimal(10,2),
InvoiceTypeId int,
ReleaseForPayment bit,
InvoiceComment nvarchar(255),
InvoiceStatus nvarchar(25),
PurchaseOrderId bigint,
PurchaseOrderLineItemTotal decimal(10,2),
PurchaseOrderQuantity decimal(10,4),
ReceiptQuantity decimal(10,4))
DECLARE @POTable AS Table(
POId bigint,
TotalAllInvoicesForPO decimal(10,2))
INSERT INTO @InvoiceTable
SELECT t.in_TransactionBaseID as TransactionID,
sourceorg.tx_Name as SourceOrganizationName,
targetorg.tx_Name as TargetOrganizationName,
inv.in_DocumentBaseID as InvoiceID,
inv.tx_VendorInvoiceNumber as VendorInvoiceId,
inv.dt_VendorInvoiceDate as VendorInvoiceDate,
CAST(inv.dc_Total as decimal(10,2)) as InvoiceTotal,
CAST(inv.dc_SubTotal as decimal(10,2)) as InvoiceSubTotal,
inv.in_InvoiceTypeID as InvoiceTypeId,
inv.tf_ReleaseForPayment as ReleaseForPayment,
invbase.tx_Comment as InvoiceComment,
stat.tx_APApprovalStatusDesc as InvoiceStatus,
invbase.in_ParentDocumentID as PurchaseOrderID,
CAST(SUM(polib.dc_Quantity * (
CASE WHEN ISNULL(Exchange.tf_ExchangeItem,0) = 1
THEN COALESCE(polib.dc_tradePrice,0)
ELSE COALESCE(polib.dc_ListPrice, 0)
END)) as decimal(10,2)) As PurchaseOrderLineItemTotal,
SUM(polib.dc_Quantity) As PurchaseOrderQuantity,
CAST(COALESCE(receiptCount.ReceiptQuantity, 0) as decimal(10,4)) As ReceiptQuantity
FROM [Transaction].TD_TransactionBase t
INNER JOIN [Membership].TD_PersonOrganization_XREF org
on id_PersonID = @PersonID
and id_OrganizationID = t.id_SourceOrganizationID
INNER JOIN [Transaction].TD_DocumentBase invbase
on invbase.in_TransactionID = t.in_TransactionBaseID
and invbase.in_DocumentStateId IN (1, 2)
and invbase.in_CurrentStatus = 1
INNER JOIN [Transaction].TD_InvoiceDocumentExtension inv
on inv.in_DocumentBaseID = invbase.in_DocumentBaseID
INNER JOIN [Transaction].TD_DocumentBase podoc
on podoc.in_DocumentBaseID = invbase.in_ParentDocumentID
and podoc.in_DocumentStateId IN (1, 2)
and podoc.in_CurrentStatus = 1
INNER JOIN [Membership].TD_Organization sourceorg
on sourceorg.id_OrganizationID = t.id_SourceOrganizationID
INNER JOIN [Membership].TD_Organization targetorg
on targetorg.id_OrganizationID = invbase.id_TargetOrganizationID
INNER JOIN [Transaction].TD_LineItemBase polib
on polib.in_DocumentID = podoc.in_DocumentBaseID
and polib.in_CurrentStatus = 1
LEFT OUTER JOIN (
SELECT in_LineItemBaseId, tf_ExchangeItem
FROM [transaction].td_PartLineItemExtension) as exchange
on exchange.in_LineItemBaseID = polib.in_LineItemBaseId
LEFT OUTER JOIN (
SELECT receiptdoc.in_ParentDocumentID, SUM(receiptlib.dc_Quantity) as ReceiptQuantity
FROM [Transaction].TD_DocumentBase receiptdoc
INNER JOIN [Transaction].TD_LineItemBase receiptlib
on receiptlib.in_DocumentID = receiptdoc.in_DocumentBaseID
and receiptlib.in_CurrentStatus = 1
WHERE receiptdoc.tx_DocumentTypeDesc = 'ReceiptDocument'
and receiptdoc.in_CurrentStatus = 1
GROUP BY receiptdoc.in_ParentDocumentID) as receiptCount
on receiptCount.in_ParentDocumentID = podoc.in_DocumentBaseID
LEFT OUTER JOIN [Transaction].TL_APApprovalStatus stat
on stat.in_APApprovalStatusID = inv.in_APApprovalStatusID
WHERE inv.tf_MatchToPurchaseOrder = 0
AND (@SourceOrganizationID IS NULL or t.id_SourceOrganizationID = @SourceOrganizationID)
AND invbase.in_DocumentStateId <> 3
AND invBase.in_CurrentStatus <> 3
AND (
CASE @IncludePOSpendingLimit
WHEN 1 THEN COALESCE(inv.tf_MatchToPurchaseOrderSpendingViolation, 1)
ELSE 1
END = 0
OR CASE @IncludePrice
WHEN 1 THEN COALESCE(inv.tf_MatchToPurchaseOrderPrice, 0)
ELSE 1
END = 0
OR CASE @IncludeReceipt
WHEN 1 THEN COALESCE(inv.tf_MatchToPurchaseOrderReceipt, 0)
ELSE 1
END = 0)
GROUP BY
t.in_TransactionBaseID,
sourceorg.tx_Name, targetorg.tx_Name,
inv.in_DocumentBaseID,
inv.tx_VendorInvoiceNumber,
inv.dt_VendorInvoiceDate,
inv.dc_Total,
inv.dc_SubTotal,
inv.in_InvoiceTypeID,
inv.tf_ReleaseForPayment,
invbase.tx_Comment,
invbase.in_ParentDocumentID,
receiptCount.ReceiptQuantity,
stat.tx_APApprovalStatusDesc
ORDER BY inv.in_DocumentBaseID
INSERT INTO@POTable
SELECT po.in_DocumentBaseId AS POId,
SUM(inv.dc_Total) AS TotalAllInvoicesForPO
FROM [Transaction].[TD_InvoiceDocumentExtension] inv
INNER JOIN [Transaction].[TD_DocumentBase] doc
ON inv.in_DocumentBaseId = doc.in_DocumentBaseId
INNER JOIN [Transaction].[TD_PurchaseOrderDocumentExtension] po
ON doc.in_ParentDocumentID = po.in_DocumentBaseId
WHERE po.in_DocumentBaseId IN (
SELECT PurchaseOrderId FROM @InvoiceTable
)
GROUP BY po.in_DocumentBaseId
--return combined results
INSERT INTO @UnmatchedInvoices
SELECT TransactionId,
SourceOrganizationName,
TargetOrganizationName,
InvoiceId,
VendorInvoiceId,
VendorInvoiceDate,
InvoiceTotal,
InvoiceSubTotal,
InvoiceTypeId,
ReleaseForPayment,
InvoiceComment,
InvoiceStatus,
PurchaseOrderId,
PurchaseOrderLineItemTotal,
PurchaseOrderQuantity,
ReceiptQuantity,
po.TotalAllInvoicesForPO
FROM @POTable po
INNER JOIN @InvoiceTable inv ON po.POId = inv.PurchaseOrderId
RETURN
END
March 10, 2009 at 9:09 am
what line number of the function is it falling over on ?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 10, 2009 at 9:15 am
could you also let me know what datatype this field is:
inv.dt_VendorInvoiceDate
It seems to be from the following table.
TD_InvoiceDocumentExtension
That by the looks of things is the only field that gets added to a smalldatetime column. My guess is that you have a datetime field in your source table and one of those dates contains a time.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 10, 2009 at 10:05 am
It says line 65 which corresponds to the WHEN portion of the @SortAscending ELSE and the @SortField first IF; does that make sense? I reformatted the sproc for space, although looking at it, it didn't save much.
dt_VendorInvoiceDate (I just noticed how redundant that naming was! Why "dt_" if it's also ending in "Date"?) Anyway, that field is a smalldatetime as well.
This is why I'm so puzzled by this, everything seems to be set the way it should. And why would it not run in the sproc, but it runs just fine when I call the function?
I will, however, look at the dates in the table and ensure that they're all fine.
Chris
March 10, 2009 at 10:14 am
ok sorry I think I worked it out.
It's because of the return type of the case statement
The case state is expecting a small date time to be return based on precedence.
However you are return either a small date time or an int. This will fail sometimes if SQL can't convert your int to a small date time.
Here is another example.
DECLARE @SortField INT
DECLARE @tbl TABLE
(dt SMALLDATETIME,
num INT)
INSERT INTO @tbl
SELECT '2009-01-01',100000000
SET @SortField = 3
SELECT *
FROM @tbl
ORDER BY
CASE @SortField
WHEN 2 THEN dt
WHEN 3 THEN num
END
I guess you could get around this by allowing your case statement to return the column number rather the value...
I hope this helps
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 10, 2009 at 10:35 am
Excellent! That was amazing.
I replaced the VendorInvoiceDate with the number 6 and it seems to be working fine.
Thank you very much!
Chris
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply