Select as another name 2

[VonFranzken]

I have a table with serveral fields. One of the fields is called srctable or source table for short. This field stored the information about which table the record came from. It saves that information as 'dbo.activity', dbo.goals, etc.

Is there a way that I can query the table so that the srctable field will show Activites for all dbo.activity records and Goals for all dbo.goals records.

Thank you kindly,
Steve

 

[swampBoogie]

select case srctable 
       when 'dbo.activity' then  'Activities'
       when 'dbo.goal' then 'Goals' 
       when '...' then '...'
       else '...'
       end
...

 

[VonFranzken]

Ok great and thanks for all your help. How would I fit your statement below in lets say my select statement below?

SELECT *
FROM admin.src_cache_cah"+tfm_SQLstr+"
ORDER BY eventdate_cah DESC

 

[VonFranzken]

SELECT *
FROM admin.src_cache_cah"+tfm_SQLstr+"
when 'dbo.activity' then 'Activities'
when 'dbo.goal' then 'Goals'
else '...'
end
ORDER BY eventdate_cah DESC

Incorrect syntax near when is what I got.

 

[JamesLean]

SELECT *,
  case srctable
       when 'dbo.activity' then  'Activities'
       when 'dbo.goal' then 'Goals'
       when '...' then '...'
       else '...'
       end
FROM admin.src_cache_cah...

--James

 

[VonFranzken]

I got an error saying SQL does not support the case construct?

Steve

 

[JamesLean]

Are you using MS SQL Server?

--James

 

[VonFranzken]

SELECT *,
case srctable
when 'dbo.activity' then 'Activities'
when 'dbo.goal' then 'Goals'
when 'dbo.technician' then 'Technician Report'
else '...'
end
FROM admin.src_cache_cah

I get

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'srctable'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'srctable'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'srctable'.

 

[JamesLean]

It doesn't look like the column that contains this data is actually called srctable?

--James

 

[VonFranzken]

Ok that time the query ran sucessfully, however should I be seeing the chages in query analyzer in terms of dbo.activity displayed as Activities?

If so i dont.
thanks again for your help really.

Steve

 

[VonFranzken]

Ok it worked in terms of showing up in query analyzer, however when I call the recordset from within my asp page,
and display it it still shows up as dbo.activity etc...even though the statement you wrote works when I test it. any idea why?

 

[VonFranzken]

Ok I know what it wants, it wants a column name.

How do I assign a column name to this statement so that is shows up as a column name?

 

[ronspree]

SELECT *,
  case srctable
       when 'dbo.activity' then  'Activities'
       when 'dbo.goal' then 'Goals'
       when 'dbo.technician' then 'Technician Report'
       else '...'
       end
  [b]As ColumnName[/b]
FROM admin.src_cache_cah