cancel
Showing results for 
Search instead for 
Did you mean: 

Using CASE disables the link. Why?

leon_laikan
Participant
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

You may try this to bypass the CASE and link dilemma:

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].[PCH1] T1
LEFT JOIN [dbo].[OPCH] T0 ON T0.[DocEntry] = T1.[DocEntry]

Thanks,

Gordon

leon_laikan
Participant
0 Kudos

Hi Gordon,

Thanks for your reply.

Unfortunately it still gives the same results as my original query (without CASE)

Running the Query still returns a number of rows.

Some rows have links in the 'Link' column (e.g
10.0.0.9\Y\10393.pdf) and if I click on this, the pdf is displayed. OK

Some rows have a blank in the 'Link' column (because my staff have omitted to make the link) . Unfortunately, when I click on this blank, the folder X opens, inviting the user to select one of the long list of pdf files which are displayed. It is these blank fields which I want to disable.

Thanks

Leon Lai

Former Member
0 Kudos

Have you tried ISNULL(T0.U_SupInv,'') instead Case?

leon_laikan
Participant
0 Kudos

Hi,

I tried ISNULL as you suggested.

It gives the same result as CASE,

i.e all the links and blanks are displayed in the 'Link' column, but none of these links work. They are all dead!

It appears that ISNULL works for fields defined as text, but not for fields defined as link ?

Thanks

Leon Lai

Former Member
0 Kudos

That means the selective sentences will not be compatible with link type. You only option is to create two query to separate them.

leon_laikan
Participant
0 Kudos

Hi Gordon,

I did as you suggested: break the query into 2 parts: one with the following WHERE clause:

WHERE T0.\[U_SupInv] is not null

The links are live!

and the other with the following WHERE clause:

WHERE T0.\[U_SupInv] IS NULL

In this half, I can modify the query with ISNULL to disable the links.

Then I can use UNION ALL to join the 2 halves, and I will have achieved my aim.

In principle, the question is answered, and I will close the thread by tomorrow.

Thanks a lot and best regards,

Leon Lai

Former Member
0 Kudos

After UNION ALL, the link may not work anymore. That is why I have not given you this option in the first place.

leon_laikan
Participant
0 Kudos

Hi

Aye! Aye! Aye!

I have forgotten that! The golden arrow which disappears!

I have tried the 2 queries separately and they work. But I have not tested UNION ALL yet!

So, there is no solution?

Thanks

Former Member
0 Kudos

You may try to replace null with an empty folder if possible. I have never tried.

leon_laikan
Participant
0 Kudos

Never mind!

The problem is not very serious. Even if somebody clicks on a blank field and selects a pdf file, he cannot update my SAP table.

I was just looking for an elegant solution to my problem.

Thanks again

Leon Lai

Former Member
0 Kudos

That is what I think too. However, you have the gut to find the best solution. It is a good spirit for your future.

Answers (0)