CODEWITHSUNDEEP
sql-server-2008select
DNR
DNR

Reputation: 3736

Putting together a tricky SQL query

I am working on a SQL 2008 machine and cannot seem to get the query to work.
My SQL query is :

select q.Document DOC from references q, equiprates e where e.MachineID=q.UnitID'

The rows retruned by q.Document is:

5570_RESTAURANT.pdf  
5650_RESTAURANT.pdf  
5110_RESTAURANT.pdf

However, I need the table rows to be as follows:

Restaurant Document

<a href="Javascript:ViewPDFDoc('5570_RESTAURANT.pdf')" class="Link">5570_RESTAURANT.pdf</a>  
<a href="Javascript:ViewPDFDoc('5650_RESTAURANT.pdf')" class="Link">5570_RESTAURANT.pdf</a>  
<a href="Javascript:ViewPDFDoc('5110_RESTAURANT.pdf')" class="Link">5570_RESTAURANT.pdf</a>

So I am trying to format my selecct string as follows:

Select @sSQL = 'select q.Document DOC, ''<a href="Javascript:ViewFile('''''+q.Document+''''''')" class="Link">''+q.Document+''</a>'' ''Restaurant Document'',
from references q, equiprates e 
where e.MachineID=q.UnitID'

My error message is:

Msg 4104, Level 16, State 1, Line 3

The multi-part identifier "q.Document" could not be bound.

Any ideas how to resolve this?
I tried google, but no luck.

Upvotes: 1

Views: 791

Answers (2)

anon
anon

Reputation:

Your single quotes are just wrong (I also recommend shifting to more modern INNER JOIN syntax). But why can't the application simply add the HTML around the DOC column? Seems wasteful (never mind more complex, obviously) to add all that HTML at the server, and send all those bytes over the wire.

DECLARE @sSQL NVARCHAR(MAX);

SET @sSQL = N'SELECT 
  DOC = q.Document, 
  [Restaurant Document] = ''<a href="Javascript:ViewFile('''''' 
    + q.Document + '''''');" class="Link">'' 
    + q.Document + ''</a>''
  FROM references AS q 
  INNER JOIN equiprates AS e
  ON q.UnitID = e.MachineID';

PRINT @sSQL;

Upvotes: 2

heximal
heximal

Reputation: 10517

try just

select 
  '<a href="Javascript:ViewPDFDoc(''' + q.Document + ''')" class="Link">' + q.Document + '</a>'
from
  references q, equiprates e 
where 
  e.MachineID=q.UnitID

but remember, it's very bad programming style. it's better when the data model and data view are separated from each other

Upvotes: 1

Related Questions