SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Column In View Parses with Single Quotes
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Grifter
Constraint Violating Yak Guru

263 Posts

Posted - 10/12/2012 :  06:27:29  Show Profile  Reply with Quote
Hi

When I put my query that I wish to use as a view into the view designer and click into the designer window it parses one of the columns I am using and puts single quote round it - why?

This is then causing data loss as I run a procedure with an update statement looking at these views and joining to this column, so instead of the column values being what I set they are all the values of the name of the column.

Query is as below but with names etc replaced by mock up columns, conditions and joins. I never built this and am trying to enhance it for some more requirements in absence of the original designer. It's to show a sum, total and percentage

The column that is bold is the one that when I put in view designer it changes to a column name to a column value - i.e. sinlge quotes appear round it.




SELECT   Column1 AS Alias1
	,Column2 AS Alias2
	,Column3
	,Sum1
	,Total1, 
         CASE WHEN Sum1 = 0 THEN 0 ELSE 
         round(Sum1 / Total1 * 100, 2) 
         END AS Win Rate
FROM    (SELECT [Column1]
		, Column4 AS 'Column2'
		, Column3
		, SUM(ISNULL(ColumnOrderValue, 0)) AS Sum1
		, (
				SELECT ROUND(SUM(ISNULL(ColumnOrderValue, 0)), 2) AS Order$
				FROM dbo.RawData AS r2
				WHERE Condition1
				AND ([Column1] = r1.[Column1]) 
				AND (Column3 = r1.Column3) 
				AND (Column4 = r1.Column4) 
				GROUP BY Column1, Column4
			) AS Total1
          FROM dbo.RawDAta AS r1
          WHERE Condition1
          AND Condition2
          GROUP BY [Column1], Column4, Column3) AS t1


So instead of my data returning like this:


Alias2
=======
Value1
Value2
Value3


It is like this:


Alias2
=======
Column2
Column2
Column2

Edited by - Grifter on 10/12/2012 06:33:27

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 10/12/2012 :  07:05:27  Show Profile  Reply with Quote
To get this

Alias2
=======
Column2
Column2
Column2

put red marked quotations...................

SELECT Column1 AS Alias1
,'Column2' AS Alias2
,Column3
,Sum1
,Total1,
CASE WHEN Sum1 = 0 THEN 0 ELSE
round(Sum1 / Total1 * 100, 2)
END AS Win Rate
FROM (SELECT [Column1]
, Column4 AS 'Column2'
, Column3
, SUM(ISNULL(ColumnOrderValue, 0)) AS Sum1
, (
SELECT ROUND(SUM(ISNULL(ColumnOrderValue, 0)), 2) AS Order$
FROM dbo.RawData AS r2
WHERE Condition1
AND ([Column1] = r1.[Column1])
AND (Column3 = r1.Column3)
AND (Column4 = r1.Column4)
GROUP BY Column1, Column4
) AS Total1
FROM dbo.RawDAta AS r1
WHERE Condition1
AND Condition2
GROUP BY [Column1], Column4, Column3) AS t1


--
Chandu
Go to Top of Page

Grifter
Constraint Violating Yak Guru

263 Posts

Posted - 10/12/2012 :  07:25:09  Show Profile  Reply with Quote
Got it

Because of this line:

Column4 AS 'Column2'

Just changed it to

Column4 AS Column2

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 10/12/2012 :  07:31:14  Show Profile  Reply with Quote
quote:
Originally posted by Grifter

Got it

Because of this line:

Column4 AS 'Column2'

Just changed it to

Column4 AS Column2





The alternate solution is my earlier post

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000