Column In View Parses with Single Quotes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Column In View Parses with Single Quotes

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2012-10-12 : 06:27:29

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-12 : 07:05:27

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

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2012-10-12 : 07:25:09

Got it

Because of this line:

Column4 AS 'Column2'

Just changed it to

Column4 AS Column2

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-12 : 07:31:14

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources