查看和动态SQL [英] view and dynamic sql

问题描述

我将Circle详细信息存储在一个视图中，将每个Circle GeoId存储在另一个视图中...现在，我必须通过Circle和GeoId从另一个表中同时从两个视图中获取结果.

I am storing Circle details in one view and for each circle GeoId''s in another view... Now I have to get results from both views from another table by Circle and GeoId''s.

with
vwArea
AS
(
    select CircleName,CircleCode,SalesGeoTag,SalesGeoID
    from SALESGEO d where SalesGeoType='C'
),

vwSalesGeo
AS
(
    SELECT vwArea.CircleName,
        (SELECT CONVERT(VARCHAR,SalesGeoID) + ',' FROM SALESGEO WHERE SalesGeoTag LIKE vwArea.SalesGeoTag + '.%' FOR XML PATH(''))SalesGeoId
    FROM vwArea
)

我得到的结果是

加尔各答23,24,25,26,766,767,768,769,770,771，
西孟加拉邦27,28,29,30,1486,1487,1488,1489,1490,1491,1492，
古吉拉特邦32,33,34,35,128,129,130​​,131,132,133,134,135,136,137，

I am getting results as

Kolkata 23,24,25,26,766,767,768,769,770,771,
West Bengal 27,28,29,30,1486,1487,1488,1489,1490,1491,1492,
Gujarat 32,33,34,35,128,129,130,131,132,133,134,135,136,137,

SELECT vwArea.CircleName,
(SELECT COUNT(POSID) FROM vwData WHERE SalesGeoID IN (vwSalesGeo.SalesGeoID))POSID    
FROM vwSalesGeo LEFT JOIN vwArea ON vwArea.CircleName = vwSalesGeo.CircleName
WHERE vwArea.CircleName IS NOT NULL

我收到错误''将nvarchar值转换为int时转换失败''.我想知道这是逗号分隔ID的问题.
我该怎么做.

I am getting error ''conversion failed when converting the nvarchar value to int''. I want to know that it is problem with comma seperated Id.
How can I do this.

推荐答案

您不能使用

WHERE SalesGeoID IN (vwSalesGeo.SalesGeoID))POSID

.

逗号分隔的字符串(vwSalesGeo.SalesGeoID)不能用作IN(条件)语句的条件.您将必须创建一个拆分函数以返回值的行. 此示例 [ ^ ]和您可以使用Google的其他几个功能都不错. 忘记了，您将不得不将返回值强制转换为INT.[/编辑]

另一个选择是重写vwSalesGeo以返回多行，每行包含Geolocation和int.然后重写最终选择以根据需要使用分组.

.

A comma-separated string (vwSalesGeo.SalesGeoID) cannot be used as condition for IN (condition) statement. You will have to create a split function to return rows of values. This example[^] and several others you can Google are good. Forgot to mention you will then have to cast the returned value as INT.[/Edit]

Another option is to rewrite your vwSalesGeo to return multiple rows each containing Geolocation and the int. Then rewrite your final select to use grouping if required.

这篇关于查看和动态SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！