SQL Server如何提及动态列值不应为null [英] SQL Server How to mention dynamic column value should not be null
问题描述
首先看到几个屏幕截图.
First see few screen shot.
查看第二个屏幕截图.其中2010 FYA和其余列为动态列.请参阅第一条记录，其中动态列值为NULL.现在告诉我如何在获取数据时提到动态列值不应该为null.
See the second screen shot. where 2010 FYA and rest of the columns are dynamic columns. see first record where dynamic column values are NULL. now tell me how could i mention dynamic column value should not be null when fetching data.
Set @AvgSql = @AvgSql+ 'Avg(CONVERT(decimal(20,6),['+@Period+'])) ['+@Period+'],'
通过这种方式，我将动态列明智的方式存储到 #TmpZacksCons临时表中.
In this way i am storing dynamic column wise vaue into #TmpZacksCons temporary table.
SET @sql='Insert Into #TmpZacksCons (Section, LineItem,Ord,
'+@PeriodCols+'
)
Select b.Section, b.LineItem,Max(Ord)+1 Ord,
'+@AvgSql+'
From #TmpAll_Broker_LI b
Group By b.Section, b.LineItem'
EXEC(@sql)
这是我的最终查询，该查询返回需要过滤空值的整个数据.空数据不应出现.
this is my final query which return whole data where i need to filter out null value. null data should not come.
SET @sql = '
Select XX.*,'''' scale,Isnull(AllowComma,''FALSE'') AllowComma,Isnull(AllowedDecimalPlace,''0'') AllowedDecimalPlace,
Isnull(AllowPercentageSign,''FALSE'') AllowPercentageSign,Isnull(CurrencySign,'''') CurrencySign,Isnull(BM_Denominator,'''') BM_Denominator
From
(
---- Broker Detail
Select AA.Section,AA.LineItem,Csm.DisplayInCSM ,AA.BrokerCode Broker,AA.BrokerName,'''' BM_Element,'''' BM_Code,AA.Ord,AA.[Revise Date],AA.LineItemId,
Csm.ID,[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[FGColor],[Indent],[Box],[HeadingSubHeading],
'+@PeriodCols+','+@PeriodColsComment +',LineItem_Comment,BrokerName_Comment,Date_Comment
From tblCSM_ModelDetails Csm LEFT OUTER JOIN (
Select b.*,L.ID LineItemId
From #TmpAll_Broker_LI b
INNER JOIN TblLineItemTemplate L ON TickerID='''+@TickerID+''' AND b.LineItem= L.LineItem
) AA ON Csm.LineItemId=AA.LineItemId
WHERE Csm.CSM_ID='+TRIM(CONVERT(CHAR(10),@CSM_Id))+' AND Csm.BMID=0 AND Type !=''SHEET''
UNION
----- Consensus
Select Section, b.LineItem,DisplayInCSM, '''' Broker,'''' BrokerName,'''' BM_Element,'''' BM_Code, Ord,'''' [Revise Date],L.ID LineItemID,
Csm.ID,[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[FGColor],[Indent],[Box],[HeadingSubHeading],
'+@PeriodCols+','+@PeriodColsComment +',LineItem_Comment,BrokerName_Comment,Date_Comment
From #TmpZacksCons b
INNER JOIN TblLineItemTemplate L ON TickerID='''+@TickerID+''' AND b.LineItem= L.LineItem
INNER JOIN tblCSM_ModelDetails Csm ON Csm.LineItemID=L.ID
WHERE Csm.CSM_ID='+TRIM(CONVERT(CHAR(10),@CSM_Id))+' AND Csm.BMID=0
---- Blue Metrics
UNION
Select Section, b.LineItem,DisplayInCSM,'''' Broker,'''' BrokerName,BM_Element,Code BM_Code, Ord,'''' [Revise Date],L.ID LineItemID,
Csm.ID,[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[FGColor],[Indent],[Box],[HeadingSubHeading],
'+@PeriodCols+','+@PeriodColsComment +',LineItem_Comment,BrokerName_Comment,Date_Comment
From #TmpBM b
INNER JOIN TblLineItemTemplate L ON TickerID='''+@TickerID+''' AND b.LineItem= L.LineItem
INNER JOIN tblCSM_ModelDetails Csm ON Csm.BMID=b.code AND Csm.LineItemID=L.ID
WHERE Csm.CSM_ID='+TRIM(CONVERT(CHAR(10),@CSM_Id))+'
AND Ord IS NOT NULL
) XX
Left Outer Join tblLiConfig ZZ
On XX.Section=ZZ.Section And XX.LineItem=ZZ.LI And ZZ.Ticker='''+@Ticker+'''
Order by ID,Ord,BM_Code,LineItem,BrokerName'
推荐答案
好的...很抱歉，但这是一个错误的答案.一个真的错误的答案.当您现在需要修复并且没有时间正确解决问题时，这就是答案的类型.
OK... I'm sorry, but this is a bad answer. A really bad answer. It's the type of answer when you need a fix right now, and don't have time to fix the problem properly.
真正的解决方法是将没有有效值的行排除在外，然后再将其合并到用于创建表的数据集中.但是，如果不更加了解您的流程/数据，我将无法做到这一点.相反，我有一个完整的hackjob.
The true fix is to exclude the rows that have no valid values before combining them into the data set used to create your table. However, without knowing your processes/data much more intimately, I cannot do this. Instead, I have a complete hackjob.
无论如何，据我所知
- 在表#TmpZacksCons 中
- 您有一些行，其中所有与日期有关的列都为NULL
- 您想排除那些
- 列名是动态设置的-它们在变量@PeriodCols中
您可以做的是从#TmpZacksCons中删除所有为NULL的行.当然，问题在于这些列是动态创建的.
What you can do is to delete the rows from #TmpZacksCons that have all NULLS. The problem is, of course, that those columns are dynamically created.
因此，在使用现有命令之后
So, after your existing command
SET @sql='Insert Into #TmpZacksCons (Section, LineItem,Ord,
'+@PeriodCols+'
)
Select b.Section, b.LineItem,Max(Ord)+1 Ord,
'+@AvgSql+'
From #TmpAll_Broker_LI b
Group By b.Section, b.LineItem'
EXEC(@sql)
我建议使用DELETE命令删除所有列均为NULL的地方.
I suggest a DELETE command to remove one where all the columns are NULL.
更新
(此新版本基于最新知识-我认为COALESCE不能返回NULL)
此处的hackjob基于我们不知道列名的事实，因此我们不能仅测试列1是否为空，列2是否为空，等等.因此，我正在使用
The hackjob here is based on the fact we don't know the column names, so we cannot just test if column 1 is null, column 2 is null, etc. So what I'm doing is using a COALESCE expression to find if all columns are NULL, as it will return the first non-NULL value in the list.
SET @sql='DELETE FROM #TmpZacksCons
WHERE COALESCE('+@PeriodCols+',NULL) IS NULL'
EXEC (@sql)
如果COALESCE表达式返回NULL，则表示所有列都具有NULL值.
If the COALESCE expression returns a NULL, it means all the columns have NULL values.
请注意，如果列列表只有1列，则COALESCE中还有其他NULL.
Note the additional NULL in the COALESCE is there in case the column list only has 1 column.
上一个版本-不再相关
So what I'm doing is to make a 'magic number' which represents that all columns are NULL via the COALESCE expression which finds the first non-NULL value in the list.
所以，在您执行上述命令后，我建议
SET @sql='DELETE FROM #TmpZacksCons
WHERE COALESCE('+@PeriodCols+',-99999.9999) = -99999.9999'
EXEC (@sql)
如果所有值均为NULL，则COALESCE将返回-99999.9999，然后触发删除.
上面的问题当然是，如果任何 actual 值等于该魔数-99999.9999，它可能会删除该行.
The problem with the above, of course, is that if any of the actual values equal that magic number -99999.9999, it may delete that row.
这篇关于SQL Server如何提及动态列值不应为null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
