Function Returning Links

In Apex you can create links out of your columns in a report. What you currently can't do is to put a condition behind this link and generate it upon that condition - if there is a link then it is there for all the rows. The only workarround for this is to create the link in your SQL. In that case your SQL may look quite clumsy and hard to read. Something like this:

SELECT CASE
          WHEN deptno = 10
             THEN    '<a href="javascript:popUp2('''
                  || 'f?p=&APP_ID.:500:&SESSION.::&DEBUG.::'
                  || 'P500_DEPTNO:'
                  || deptno
                  || ''', 700, 700);">'
                  || '<img src="#IMAGE_PREFIX#gobut.gif">'
                  || '</a>'
          WHEN deptno = 20
             THEN    '<a href="javascript:popUp2('''
                  || 'f?p=&APP_ID.:501:&SESSION.::&DEBUG.::'
                  || 'P501_DEPTNO:'
                  || deptno
                  || ''', 700, 700);">'
                  || '<img src="#IMAGE_PREFIX#go_search.gif">'
                  || '</a>'
          ELSE    '<a href="javascript:popUp2('''
               || 'f?p=&APP_ID.:502:&SESSION.::&DEBUG.::'
               || 'P502_DEPTNO:'
               || deptno
               || ''', 700, 700);">'
               || '<img src='
               || '"#WORKSPACE_IMAGES#go_button.gif">'
               || '</a>'
       END LINK,
       ename, job, hiredate
  FROM emp

The solution for this problem you can find here. Back in 2006 I created a function for generating links. This function can do the following for you:

- create a link based on a target page you provide, withouth having to code a full apex link,
- replace the link with an image if desired,
- sort the links based on any columns in your source,
- open the target page in the same window or in a popup window,
- add a request to your link,
- reset pagination if required,
- add a checksum to your link.

At the end, your query looks much better if you use that function:

SELECT CASE
          WHEN deptno = 10
             THEN return_link_fn (p_page         => 500,
                                  p_item         => 'P500_DEPTNO',
                                  p_value        => deptno,
                                  p_image_n      => 'gobut.gif'
                                 )
          WHEN deptno = 20
             THEN return_link_fn (p_page         => 501,
                                  p_item         => 'P501_DEPTNO',
                                  p_value        => deptno,
                                  p_image_n      => 'go_search.gif'
                                 )
             ELSE return_link_fn (p_page         => 502,
                                  p_item         => 'P502_DEPTNO',
                                  p_value        => deptno,
                                  p_image_n      => 'go_button.gif'
                                 )
       END LINK,
       ename, job, hiredate
  FROM emp