Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
How can I conditionally display entries in a column as links only if a query does not come up empty?

I have a table that looks like the following:
The SQL query I used for this interactive report (static ID = 'STEM') is as follows:
select MOCKSTEMS.WORD_ID, MOCKSTEMS.STEM_ID, MOCKSTEMS.LABSTEM, MOCKSTEMS.LABSTEMCATEGORY, MOCKLEMMAS.LEMMAFORM, MOCKSTEMS.LEMMA_ID, MOCKWORDS.ORIGINALWORD, MOCKSTEMS.CONTAINEDIN from MOCKSTEMS inner join MOCKWORDS on MOCKSTEMS.WORD_ID = MOCKWORDS.WORD_ID inner join MOCKLEMMAS on MOCKSTEMS.LEMMA_ID = MOCKLEMMAS.LEMMA_ID
There are 2 interactive reports on this page; the other one (static ID = 'MORPH') has the following query:
select MOCKSTEMS.WORD_ID, MOCKSTEMS.STEM_ID, MOCKMORPHS.MORPHEME_ID, MOCKMORPHS.LABMORPHEME, MOCKMORPHS.LABMORPHEMECATEGORY, MOCKLEMMAS.LEMMAFORM, MOCKMORPHS.LEMMA_ID, MOCKWORDS.ORIGINALWORD from MOCKMORPHS inner join MOCKSTEMS on MOCKMORPHS.STEM_ID = MOCKSTEMS.STEM_ID inner join MOCKWORDS on MOCKSTEMS.WORD_ID = MOCKWORDS.WORD_ID inner join MOCKLEMMAS on MOCKMORPHS.LEMMA_ID = MOCKLEMMAS.LEMMA_ID
Currently, the 'Lab Stem' column contains links that applies a filter on each of the 2 interactive reports on this page (page 11):
However, I only want to display the entry as a link (i.e. in blue text) if there are results in either the STEM interactive report or the MORPH interactive report with the filters applied.
How can I do this?
I'm thinking I could create a column in my SQL query that counts the number of rows that would satisfy my condition, but I'm not quite sure how to incorporate that into my current SQL query.
Any help would be appreciated. Thank you!
Answers
-
You need to create the link at query time like
select '<span class="navigation">' || CASE WHEN ID = 'STEM' THEN '<a class="navigation" title="Edit" href="' || APEX_UTIL.PREPARE_URL('f?p=&APP_ID.:11:&SESSION.::NO:RP,11:P11_ID:' || CONTAINEDIN) || '" style= "padding: 5px;"><img src="#IMAGE_PREFIX#app_ui/img/icons/apex-edit-pencil-alt.png" class="apex-edit-pencil-alt" alt=""></a>' ELSE '<a class="navigation" title="View" href="' || APEX_UTIL.PREPARE_URL('f?p=&APP_ID.:11:&SESSION.::NO:RP,11:P11_ID:' || STEM_ID) || '" style= "padding: 5px;"><img src="#IMAGE_PREFIX#app_ui/img/icons/apex-edit-view.png" class="apex-edit-view" alt=""></a>' END AS Action_links , MOCKSTEMS.WORD_ID, MOCKSTEMS.STEM_ID, MOCKSTEMS.LABSTEM, MOCKSTEMS.LABSTEMCATEGORY, MOCKLEMMAS.LEMMAFORM, MOCKSTEMS.LEMMA_ID, MOCKWORDS.ORIGINALWORD, MOCKSTEMS.CONTAINEDIN from MOCKSTEMS inner join MOCKWORDS on MOCKSTEMS.WORD_ID = MOCKWORDS.WORD_ID inner join MOCKLEMMAS on MOCKSTEMS.LEMMA_ID = MOCKLEMMAS.LEMMA_ID
change escape special char property of action_links to no to get the html translated. Hope this will help you
-
Yes, but don't use &SESSION. in your SQL, use the bind variable instead. Otherwise you blow away your SQL shared pool.
-
What is the "bind variable"?
-
@pkpanda Thank you for your answer. I tried using that query, but I get an error:
Do you have any ideas why?
-
when ID = 'STEM'
what data type is the ID column?
Bind variable:
:APP_SESSION
Discussion as to why
-
@Scott Wesley There is no column named 'ID' in the MOCKSTEMS table. The MOCKSTEMS table looks like this:
'STEM' is the static ID of the interactive report:
-
What is the field that identify the two different report put the column in the case statement or if you can create the app in apex.oracle.com then we can find the issue.