Using REPLACE whilst selecting several columns in an SQL Query

Question

SELECT ComputerName, time, REPLACE(room, 'OU=CL_','') FROM ComputerInvTracking
ORDER BY CONVERT (datetime, time, 103) DESC

I am trying to select three columns from a table to generate a report, but would like to strip out some of the information contained in the 'room' column. The information I would like to strip is two different strings within the same field, the first of which is shown in the code above, the second of which I can't remember at the moment, but is just a string of characters.

The code above is what I have so far, but it doesn't work! I could be on completely the wrong track... I am getting this error:

[rsFieldReference] The Value expression for the textbox ‘room’ refers to the field ‘room’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Using MS SQL.

If anyone has any suggestions, it would be much appreciated!

How is it not working? Are you getting an error, or not the desired results? An example of what you have and want would be tremendously helpful. — Andrew
– Andrew, Commented Jan 15, 2014 at 15:48
Sorry! MS SQL Error is: [rsFieldReference] The Value expression for the textbox ‘room’ refers to the field ‘room’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope. — GiANTOnFire
– GiANTOnFire, Commented Jan 15, 2014 at 15:51
@GiANTOnFire That error seems to be from SSRS (Reporting Services). If you are using it, then you should think of add that info somewhere in your question — Lamak
– Lamak, Commented Jan 15, 2014 at 15:54

Jorge Campos · Accepted Answer · 2014-01-15 15:57:06Z

1

The error is just that you change your column name from room to REPLACE(room, 'OU=CL_','') so the tool that you are using is saying that it can't find the column room on your query. So just add an alias to it:

SELECT ComputerName, 
       time, 
       REPLACE(room, 'OU=CL_','') as room 
  FROM ComputerInvTracking
 ORDER BY CONVERT (datetime, time, 103) DESC

answered Jan 15, 2014 at 15:57

Jorge Campos

23.8k7 gold badges65 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GiANTOnFire Over a year ago

Thanks! tried that before, but i dont think I refreshed it properly so thought it hadn't worked. Thank you :)

Collectives™ on Stack Overflow

Using REPLACE whilst selecting several columns in an SQL Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related