[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

Why does the following record appear when executing the Stored Procedure ?

Could you explain why I get a record to display under the following conditions:
I would have thought that it would have not passed my conditional test.

The record has the following field values:

DateLost is 8/1/2000 (as per the output file) This is the value from tblCustomers

BondsFS = 5 (as per the output file) This is the value from tblStatesAll for the state of DE (Delaware). I placed the following statement in my WHERE clause:
tblStatesAll.StateFS = 'DE'.

IntYearSP = 2005 (as per the Debugger). This value is passed along to @RptYear. The value is selected from a list box and assigned to IntYearSP.


In the stored procedure, I created the following variable:
@DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll --> I assume this would return 6/30/2000 (6/30/2005 - 5)

Thus, I would have thought that DateLost (8/1/2000) <= (6/30/2000) would be false. Yet the record with a Date Lost value of 8/1/2000 is in the output file.

My stored procedure is as follows:

CREATE PROCEDURE dbo.procFlTestFI

@RptYear int
AS

DECLARE @DateFI AS DATETIME

SELECT @DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll

SELECT Null AS Title, UPPER(tblCustomers.FirstName) AS [First Name], UPPER(tblCustomers.MiddleInitial) AS [MI],
UPPER(tblCustomers.LastName) AS [Last Name], UPPER(tblCustomers.SecondNameFirst) AS [Second Name First],
UPPER(tblCustomers.SecondNameMid) AS [Second Name Mid], UPPER(tblCustomers.SecondNameLast) AS [Second Name Last],
Null AS Relation, UPPER(tblCustomers.Address1) AS [Address 1], UPPER (tblCustomers.Address2) AS [Address 2],
UPPER(tblCustomers.Address3) AS [Address 3], UPPER(tblCustomers.Address4) AS [Address 4], UPPER(tblCustomers.City) AS City,
UPPER(tblCustomers.State) AS State, tblCustomers.Zip AS Zip, tblCustomers.SSN, Null AS [DDA Number], Null AS [Check/Cert Number],
tblProducts.CUSIP, tblProducts.SecurityName AS [Security Name], Null AS [Sub-Issue],
Right(tblCustomers.OfficeNumber,3) + ' ' + tblCustomers.CustomerNumber AS [Account Number],
tblProducts.PropertyType AS [Property Type], Null AS [Property Status],
CASE WHEN tblProducts.CashBalance IS NULL THEN tblProducts.Quantity ELSE tblProducts.CashBalance END AS [Check/Cert Amount],tblStatesAll.BondsFS,tblCustomers.DateLost
 
FROM tblStatesAll INNER JOIN (tblCustomers INNER JOIN tblProducts ON tblCustomers.CustomerNumber = tblProducts.CustomerNumber)
ON tblStatesAll.StateFS <> tblCustomers.State
WHERE
               (tblProducts.PropertyType='FIXED INCOME' AND tblCustomers.DateLost <= @DateFI AND tblProducts.IRACode Is Null  AND tblStatesAll.StateFS = 'DE' AND LEN(Zip) = 0)
             

ORDER BY tblCustomers.State
GO

The Access application calls the Stored Procedure as per the following:

With com
       .CommandType = adCmdStoredProc
       .CommandText = "dbo.procFlTestFI"
       .Parameters.Append .CreateParameter("RptYear", adInteger, adParamInput, 4, intYearSP)
       .ActiveConnection = cn
       Set rstQueryFS = .Execute
End With
0
zimmer9
Asked:
zimmer9
  • 2
1 Solution
 
ABaruhCommented:
Even though your @DateFI is datetime, try doing an additional cast in your WHERE...

WHERE
               (tblProducts.PropertyType='FIXED INCOME' AND cast(tblCustomers.DateLost as datetime) <= cast(@DateFI as datetime) AND tblProducts.IRACode Is Null  AND tblStatesAll.StateFS = 'DE' AND cast(LEN(Zip) as int) = 0)
0
 
rafranciscoCommented:
I believe this one is the one giving you the error.  Since you are not qualifying from which state you are getting the BondsFS, this can be a 3 and not 5, which will give you're @DateFI a value of '2002-06-30' instead of '2000-06-30'.

SELECT @DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll

Make sure you specify from which state to get the BondsFS.
0
 
rafranciscoCommented:
Change this:

SELECT @DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll

to this:

SELECT @DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll
WHERE State = 'DE'
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now