Using CASE disables the link. Why?

Hi!

I devised a system which allows users to view the scanned image of any selected invoice at the click of a mouse.

Here is the system:

(1) I created a UDF under the category Marketing Documents with the following properties:

Title : SupInv (short for Supplier's Invoice)

Description : Scanned Docs

Type : General

Structure : Link

(2) All suppliers' Invoices are scanned and stored in folder X.

(3) I open any AP Invoice in SAP, and link its PDF scan. All other invoices are similarly linked to their scans

(4) I wrote a simple query which lists a range of suppliers' invoices. An important field is the UDF T0.\[U_SupInv].

(5) When a user runs the query and clicks on the UDF, the scanned PDF pops up.

(6) So far so good.

(7) Except that if we forget to attach a Scan, the link is blank as expected. But if we click on the blank, it opens the folder X.

I do not want this to happen. I want the link to be disabled if it is blank, so that nothing happens if we click on it.

(8) So I modified my SQL as follows:

Original SQL:

SELECT ...

T0.\[U_SupInv] as 'Link'

Amended SQL:

SELECT ...

CASE

WHEN T0.\[U_SupInv] IS NULL THEN ' '

WHEN T0.\[U_SupInv] IS NOT NULL THEN T0.\[U_SupInv]

END AS 'Link'

(9) But when I do this, none of the links works. The scanned image does not pop up for ANY invoice on the list.

(10) Where have I gone wrong? I want all links to be live, but I want blanks to be disabled.

(11) Please note that the UDF T0.\[U_SupInv] is defined as a link field. Maybe we cannot use IS NULL with link fields?

Thanks

Leon Lai

.

.

.

Here is my original query:

SELECT
T0.[TaxDate]   AS 'Doc Dt',
'Invoice'      AS 'Txn Type',
T0.[DocNum]    AS 'Doc No.',
T0.[NumAtCard] AS 'Vendor Reference',
T0.[CardName]  AS 'Supplier Name',
T0.[DocTotal]  AS 'Rs',
T0.[U_SupInv]  AS 'Link'


FROM [dbo].[OPCH] T0
INNER JOIN [dbo].[PCH1] T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN [dbo].[OJDT] T5 ON T0.[TransID]  = T5.[TransID]

WHERE ... etc