view and dynamic sql

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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.

SQL

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
)

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,

SQL

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

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.

Posted 23-Nov-10 0:05am

SatyaKeerthi15

Updated 23-Nov-10 0:12am

RaviRanjanKr

v2

Add a Solution

Comments

Sunasara Imdadhusen 23-Nov-10 6:14am

where is code, were you are converting varchar to int?

Sunasara Imdadhusen 23-Nov-10 6:15am

Please send snippet of code for conversion

Shahriar Iqbal Chowdhury/Galib 23-Nov-10 15:01pm

hi,
your last code block
SELECT COUNT(POSID) FROM vwData WHERE SalesGeoID IN (vwSalesGeo.SalesGeoID)
What is the data type of SalesGeoID?
post full table schema used in your posted sql's

SatyaKeerthi15 24-Nov-10 4:02am

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

SalesGeoID is integer. So I want to use dynamic sql. How to use them in this case

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Edbert P · Answer 1 · 2010-11-24T14:08:00

You can't use

WHERE SalesGeoID IN (vwSalesGeo.SalesGeoID))POSID

.

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. [Edit]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.