filter behaving differently from query analyser versus when called through sproc
-
I am at my wits end to figure out why when i run the below code from within SSMS it returns no record but when it is used within the sproc it always fails the filter check?
Note: @DetailRecordcount value is 5 and record count is 0000000007. but like I said when I ran just the query - there is no other dependent variables involved - it won't return a record, so in conquence no record is inserted into the LoadErrors table, but this is run through the sproc it will insert a record because it fails in the where filter. Any you guys have any ideas?
The one variable used is set by this query:
SELECT @DetailRecordcount = COUNT(1),
FROM Sap.Details
INSERT INTO Sap.LoadErrors
(ErrorRecordId,
ColumnName,
ColumnValue,
ErrorType,
EtlLoadId)
SELECT
TrailerId AS ErrorRecordId,
'RecordCount' AS ColumnName,
ISNULL(RecordCount, '') AS ColumnValue,
'BZ' AS ErrorType,
EtlLoadId
FROM
Sap.TrailersImport
WHERE RecordCount <> @DetailRecordcount + 2
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128)) -
TeraByteMe (11/15/2013)
I am at my wits end to figure out why when i run the below code from within SSMS it returns no record but when it is used within the sproc it always fails the filter check?Note: @DetailRecordcount value is 5 and record count is 0000000007. but like I said when I ran just the query - there is no other dependent variables involved - it won't return a record, so in conquence no record is inserted into the LoadErrors table, but this is run through the sproc it will insert a record because it fails in the where filter. Any you guys have any ideas?
The one variable used is set by this query:
SELECT @DetailRecordcount = COUNT(1),
FROM Sap.Details
INSERT INTO Sap.LoadErrors
(ErrorRecordId,
ColumnName,
ColumnValue,
ErrorType,
EtlLoadId)
SELECT
TrailerId AS ErrorRecordId,
'RecordCount' AS ColumnName,
ISNULL(RecordCount, '') AS ColumnValue,
'BZ' AS ErrorType,
EtlLoadId
FROM
Sap.TrailersImport
WHERE RecordCount <> @DetailRecordcount + 2
What are the datatypes involved here?
@DetailRecordcount value is 5 and record count is 0000000007
This makes me think that RecordCount is varchar?
Also, you said "it fails in the where filter". What does that mean? Do you get an error message or are you seeing rows inserted when you think you shouldn't?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Using the term "Where Filter" is a pretty common way of referring to the WHERE clause conditiional filter. It maybe shorthand'ish but I don't see how anyone without putting any effort into thought could miss its intended meaning? When you look at the code there is only one filter condition in the where clause.
Forget I asked...
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128)) -
I think Sean was unclear on the specific failure you were encountering, not unclear on the phrase "where filter". No need to be snippy.
"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura -
TeraByteMe (11/15/2013)
Using the term "Where Filter" is a pretty common way of referring to the WHERE clause conditiional filter. It maybe shorthand'ish but I don't see how anyone without putting any effort into thought could miss its intended meaning? When you look at the code there is only one filter condition in the where clause.Forget I asked...
Umm yeah...
Did you miss this part?
Do you get an error message or are you seeing rows inserted when you think you shouldn't?
Perhaps if you had actually answered the two questions I posted back instead of getting irritated we would already have your resolution.
Let's try a more direct approach.
What is the datatype of RecordCount?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
TeraByteMe (11/15/2013)
Using the term "Where Filter" is a pretty common way of referring to the WHERE clause conditiional filter. It maybe shorthand'ish but I don't see how anyone without putting any effort into thought could miss its intended meaning? When you look at the code there is only one filter condition in the where clause.Forget I asked...
No need to get snarky with someone who is trying to help you for free.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply