SQL:ISNULL函数具有不同类型的参数 [英] SQL: ISNULL function with different type of parameters
问题描述
在SQL Server中, ISNULL()函数必须具有相同类型的参数。
In SQL Server, ISNULL() function has to same type of parameters.
check_expression
check_expression
要检查表达式是否为NULL。 check_expression可以是任何类型的
。
Is the expression to be checked for NULL. check_expression can be of any type.
replacement_value
replacement_value
是NULL。
replacement_value 必须与check_expresssion 具有相同的类型。
Is the expression to be returned if check_expression is NULL. replacement_value must have the same type as check_expresssion.
参数类型?我想使用 date
和 string
参数像这样 ISNULL - ')
How can I use it with different type of parameter? I want to use with date
and string
parameter like this ISNULL(A.DATE, '-')
注:类型 A.DATE
是 datetime
。
EDIT-1 :我的完整查询0 row:
EDIT-1: My full query which is getting 0 row:
SELECT A.pkey as KREDİ, A.SUMMARY , D.BayiStatu AS STATU, D.Sorumlu AS SORUMLU, C.BayiSonuc as SONUC, ISNULL( CONVERT(VARCHAR(25), A.CREATED, 112) , '-' ), ISNULL( CONVERT(VARCHAR(25), A.RESOLUTIONDATE, 112) , '-' ), dbo.CUSTVAL(11931, A.ID, 'S') AS BAYİ, ISNULL( CONVERT(VARCHAR(25), dbo.GetLastStatuTime(A.ID), 112) , '-' ) AS SON_STATU_TAR,j2.SUMMARY, ISNULL( CONVERT(VARCHAR(25), j2.CREATED, 112) , '-' ), ISNULL( CONVERT(VARCHAR(25), j2.RESOLUTIONDATE, 112) , '-' ), j3.SUMMARY, ISNULL( CONVERT(VARCHAR(25), j3.CREATED, 112) , '-' ), ISNULL( CONVERT(VARCHAR(25), j3.RESOLUTIONDATE, 112) , '-' )
FROM AspNetServicesDB.dbo.SONUC_MAP C, JİRA.resolution E, jira.issuestatus B, AspNetServicesDB.dbo.STATU_MAP D, Jira.jiraissue A
INNER JOIN Jira.issuelink i
ON i.SOURCE = A.ID and i.SEQUENCE = 0
INNER JOIN Jira.jiraissue As j2
ON i.DESTINATION =j2.ID
LEFT JOIN Jira.issuelink i2
ON i2.SOURCE = A.ID and i2.SEQUENCE = 1
LEFT JOIN Jira.jiraissue As j3
ON i2.DESTINATION = j3.ID
WHERE A.issuestatus = B.ID
AND 'BAŞARAN OTOMATİV' = dbo.CUSTVAL(11931, A.ID, 'S')
AND B.pname = D.JiraStatu collate Turkish_CS_AI
AND A.issuetype != 11
AND A.RESOLUTION = E.ID
AND E.pname = C.JiraSonuc collate Turkish_CS_AI
EDIT-2 / p>
EDIT-2: But this working
select ISNULL( CONVERT(VARCHAR(25), A.RESOLUTIONDATE, 112) , '-' )
FROM Jira.jiraissue A
可能是 JOIN
?
推荐答案
你不能。 ISNULL函数本身用作查询结果列,或在最终是查询结果中的列的表达式中使用。列中的所有字段/行必须具有相同的数据类型。所以你必须选择。
You can't. The ISNULL function is used by itself as a query result column, or in an expression that eventually is a column in the query result. All fields/rows in a column must have the same data type. So you'll have to choose.
一个解决方案是将DATE转换为字符串,因此结果总是一个字符串,但我觉得最好的解决方案是对空日期返回NULL,并让表示层决定NULL日期是否应显示为 -
,以及以什么格式显示非空日期(客户端区域设置设置)。
One solution would be to cast the DATE to string, so the result is always a string, but i feel the best solution would be to return NULL for empty dates and let the presentation layer decide whether or not the NULL dates should be shown as -
and in what format the non-null dates should be displayed (client locale settings).
对于表示层,我的意思是任何显示或输出这些数据,可以是网页,CSV导出器,报告工具等。
With presentation layer, I mean anything that displays or outputs this data, which can be a web page, a CSV exporter, a reporting tool, whatever.
这篇关于SQL:ISNULL函数具有不同类型的参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!