Sql-server – Uexpected values inserted in table

sql server

I have a table called FunctionWeights which contains data as follows:

FunctionWeightsTable

My query is as follows:

select @constVal = FunctionWeight from dbo.FunctionWeights where FunctionWeights.FunctionName = 'MatchCity'
Insert into #Temp2 select RowNumber,PercentMatch,@constVal, PercentMatch * @constVal from dbo.MatchCity(@City)
select * from #Temp2

select @constVal = FunctionWeight from dbo.FunctionWeights where FunctionWeights.FunctionName = 'MatchSSN'
Insert into #Temp2 select RowNumber,PercentMatch, @constVal, PercentMatch * @constVal from dbo.MatchSSN(@SSN)
select * from #Temp2

and so on……………. for all function names.

Data-types for columns of #Temp2 are as follows:

Rownumber int not null,
ValFromFunc float(3)null,
FuncWeight float(3) null,
Percentage float(3) not null

However, The #Temp2 table after every insert has only 1 in FunctionWeight column for all the functionNames matched as shown above in queries.

The snapshot of #Temp2 is as follows after insert for any function Name i.e FunctionWeight does not change as it should.

enter image description here

Best Answer

If the FuncWeight field in the #Temp2 table is truly defined as float(3), then the most likely problem is that the datatype of @constVal is DECIMAL(x, 0) or just DECIMAL. Either of those would round up the 0.7 values to be just 1, though the 0.3 and 0.35 values would round down to 0.

Nothing in that posted code or data indicates what could force all values of FunctionWeight to come out as 1. Since some of the FunctionWeight values are 1, I have to assume that the "snapshot of #Temp2" image was taken after testing FunctionName values that have non-1 values for FunctionWeight.

Another possibility is that we are not being shown all rows in the FunctionWeights table and that there is duplication of the FunctionName values where the duplicated rows have a value of 1 for FuncWeight. In that case the last record read by the select @constVal = FunctionWeight from dbo.FunctionWeights query would be the value stored in @constVal, though that would not always be the "duplicated" row since there is no ORDER BY on the query.

Related Solutions

Sql-server – How to get a boolean value into a SELECT operation based on another table

I'd do it with a CASE statement:

select
    m.*,
    hasAttachments = CASE WHEN EXISTS(select * from Attachment where messageId = M.messageId) then 1 else 0 end
from Message M

select distinct 
    m.*,
    hasAttachments = CASE WHEN a.AttachmentId is not null then 1 else 0 end
from Message m
left join Attachment a on a.MessageId = m.MessageId

Sql-server – SSIS Data Flow Task Excel to SQL table NULL value will not work for small INT datatype

My original answer was working (changing int to nvarchar) but I ran into another column which contained dates in the excel source file where some cells contained the string "NULL" (I did not want to have dates in a NVARCHAR column.) When SSIS got to this date column it was generating an error because it could not convert the string "NULL" to a date. It was not properly reading the "NULL" as a NULL value, instead it was reading it as a string. The way I was able to resolve this issue is by adding a derived column component to the package that replaced the date field by using the following expression

(DT_WSTR,255)Auth_from_date == "NULL" ? NULL(DT_WSTR,50) : (DT_WSTR,255)Auth_from_date

this is just an IF statement that will change the string "NULL" to an actual NULL value. The date is then passed thru a data conversion component and converted into DATETIME format.

Best Answer

Related Solutions

Sql-server – How to get a boolean value into a SELECT operation based on another table

Sql-server – SSIS Data Flow Task Excel to SQL table NULL value will not work for small INT datatype

Related Question