Insert query problem
in [Programming SQL]
|
Prev: Finding columns in tables with specific default constraints
Next: Function to compare two version strings
From: pmcguire on 17 Jul 2008 10:59 The following is the problem query. MetricValue nvarchar(50) Valid values for TargetValue can be NULL, '', or some other string. INSERT INTO @M (ParameterID, EntityID, TargetValue) SELECT M.ParameterID, M.EntityID, M.MetricValue FROM tblMetric M INNER JOIN @ins i ON M.ParameterID = i.ParameterID AND M.EntityID = i.EntityID INNER JOIN (SELECT ParameterID, EntityID, MetricDate AS TargetDate, MetricValue AS TargetValue FROM tblMetric WHERE MetricTypeID = @TargTypeID) TM ON i.EntityID = TM.EntityID AND i.ParameterID = TM.ParameterID AND i.TargetDate = TM.TargetDate WHERE MetricTypeID IN (@BaseTypeID, @MonTypeID) The problem is that when it is Null, @M.TargetValue ends up being ''. How can I avoid this? -- Pat
From: pmcguire on 17 Jul 2008 11:16 I should clarify -- The problem is that when TM.TargetValue is Null, @M.TargetValue ends up being ''. How can I avoid this? -- Pat -- Pat "pmcguire" wrote: > The following is the problem query. > > MetricValue nvarchar(50) > Valid values for TargetValue can be NULL, '', or some other string. > > INSERT INTO @M (ParameterID, EntityID, TargetValue) > SELECT M.ParameterID, M.EntityID, M.MetricValue > FROM tblMetric M > INNER JOIN @ins i ON M.ParameterID = i.ParameterID AND M.EntityID = > i.EntityID > INNER JOIN (SELECT ParameterID, EntityID, MetricDate AS TargetDate, > MetricValue AS TargetValue > FROM tblMetric > WHERE MetricTypeID = @TargTypeID) TM ON i.EntityID = TM.EntityID AND > i.ParameterID = TM.ParameterID AND i.TargetDate = TM.TargetDate > WHERE MetricTypeID IN (@BaseTypeID, @MonTypeID) > > The problem is that when it is Null, @M.TargetValue ends up being ''. How > can I avoid this? > -- > Pat
From: pmcguire on 17 Jul 2008 11:20
And, of course, the query had a typo. The query should be INSERT INTO @M (ParameterID, EntityID, TargetValue) SELECT M.ParameterID, M.EntityID, TM.MetricValue FROM tblMetric M INNER JOIN @ins i ON M.ParameterID = i.ParameterID AND M.EntityID = i.EntityID INNER JOIN (SELECT ParameterID, EntityID, MetricDate AS TargetDate, MetricValue AS TargetValue FROM tblMetric WHERE MetricTypeID = @TargTypeID) TM ON i.EntityID = TM.EntityID AND i.ParameterID = TM.ParameterID AND i.TargetDate = TM.TargetDate WHERE MetricTypeID IN (@BaseTypeID, @MonTypeID) -- Pat -- Pat "pmcguire" wrote: > I should clarify -- The problem is that when TM.TargetValue is Null, > @M.TargetValue ends up being ''. How > can I avoid this? > -- > Pat > -- > Pat > > > "pmcguire" wrote: > > > The following is the problem query. > > > > MetricValue nvarchar(50) > > Valid values for TargetValue can be NULL, '', or some other string. > > > > INSERT INTO @M (ParameterID, EntityID, TargetValue) > > SELECT M.ParameterID, M.EntityID, M.MetricValue > > FROM tblMetric M > > INNER JOIN @ins i ON M.ParameterID = i.ParameterID AND M.EntityID = > > i.EntityID > > INNER JOIN (SELECT ParameterID, EntityID, MetricDate AS TargetDate, > > MetricValue AS TargetValue > > FROM tblMetric > > WHERE MetricTypeID = @TargTypeID) TM ON i.EntityID = TM.EntityID AND > > i.ParameterID = TM.ParameterID AND i.TargetDate = TM.TargetDate > > WHERE MetricTypeID IN (@BaseTypeID, @MonTypeID) > > > > The problem is that when it is Null, @M.TargetValue ends up being ''. How > > can I avoid this? > > -- > > Pat |