{"id":10501,"date":"2020-10-15T04:52:46","date_gmt":"2020-10-15T10:52:46","guid":{"rendered":"https:\/\/traust.com\/?p=10501"},"modified":"2020-10-15T08:25:14","modified_gmt":"2020-10-15T14:25:14","slug":"dynamic-lov-with-a-subquery-in-oracle-apex","status":"publish","type":"post","link":"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/","title":{"rendered":"Dynamic LOV with a Subquery in Oracle APEX"},"content":{"rendered":"<p>A dynamic List of Values (LOV) in <a href=\"https:\/\/traust.com\/enterprise-web-apps\/\">Oracle APEX<\/a> is used to fetch data that will be in a constant state of change.\u00a0 For this blog, we will be examining a real-world example that uses a dynamic LOV with a subquery to help book clients to rooms.<\/p>\n[su_spacer size=&#8221;30&#8243;]\n<h2>The Situation<\/h2>\n<p>A few weeks ago, I built a room reservation page in an APEX application for a <a href=\"https:\/\/www.houseofcharity.org\/\">local non-profit<\/a>. I was testing the functionality when a common problem emerged.<\/p>\n<p>The problem was that by default, a user was able to double-book clients and rooms. This would lead to LOTS of problems because:<\/p>\n<ul>\n<li>Each client should only be able to have one current reservation<\/li>\n<li>Each room should only ever be booked to a single client<\/li>\n<\/ul>\n[su_spacer size=&#8221;30&#8243;]\n<h2>Limiting the Results<\/h2>\n<p>The reservation creation form has two LOVs (clients and rooms).\u00a0 We want to limit the options in the LOVs so that it only shows clients and rooms that are available.<\/p>\n<p>The table structure for this example looks like:<\/p>\n<ul>\n<li>Client table (Client ID, Name)<\/li>\n<li>Room table (Room ID, Room Number)<\/li>\n<li>Reservation table (Client ID, Room ID, Check In Date, and Check Out Date)<\/li>\n<\/ul>\n<p>The two queries below return the client\u2019s name and the room numbers as display values, respectively, with IDs for the return values.<\/p>\n<pre><code class=\"language-sql\">\r\nSELECT CLIENT.NAME display_value, CLIENT.ID return_value\r\nFROM CLIENT\r\n<\/code><\/pre>\n<pre><code class=\"language-sql\">\r\nSELECT ROOM.ROOM_NUMBER display_value, ROOM.ID return_value\r\nFROM ROOM\r\n<\/code><\/pre>\n[su_spacer size=&#8221;45&#8243;]\n<h2>Subquery Time<\/h2>\n<p>Now we can add a subquery in the WHERE clause to narrow the results of clients and rooms. The subqueries below will return a list of clients and rooms that currently have active reservations for use in the larger query. It is important to note that an active reservation will have a null check out date.<\/p>\n[su_spacer size=&#8221;30&#8243;]\n<pre><code class=\"language-sql\">\r\nSELECT CLIENT.NAME display_value, CLIENT.ID return_value\r\nFROM CLIENT\r\nWHERE ID NOT IN (\r\nSELECT CLIENT_ID\r\nFROM RESERVATION\r\nWHERE CHECKOUT_DATE IS NULL)\r\n<\/code><\/pre>\n<pre><code class=\"language-sql\">\r\nSELECT ROOM.ROOM_NUMBER display_value, ROOM.ID return_value\r\nFROM ROOM\r\nWHERE ROOM.ID NOT IN (\r\nSELECT ROOM_ID\r\nFROM RESERVATION\r\nWHERE CHECKOUT_DATE IS NULL)<\/code><code class=\"language-sql\"><\/code><\/pre>\n[su_spacer size=&#8221;45&#8243;]\n<h2>Conclusion<\/h2>\n<p>In summary, the WHERE clauses in these larger queries contain subqueries that check for room and client IDs attached to open reservations. When the WHERE clause is found, the client ID or room ID is kicked out of the result pool and prevents that object from being double-booked!<\/p>\n[su_spacer size=&#8221;70&#8243;]\n<h2>Learn More<\/h2>\n<p>You can learn more helpful tips like the ones above by visiting the <a href=\"https:\/\/traust.com\/blog\/\">Traust Consulting Blog<\/a>.<\/p>\n[su_spacer size=&#8221;30&#8243;]\n","protected":false},"excerpt":{"rendered":"<p>A dynamic List of Values (LOV) in Oracle APEX is used to fetch data that will be in a constant state of change.\u00a0 For this blog, we will be examining&#8230;<\/p>\n","protected":false},"author":20,"featured_media":10515,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[50,35,52],"tags":[],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Dynamic LOV with a Subquery in Oracle APEX - Traust<\/title>\n<meta name=\"description\" content=\"A real-world example and a walkthrough about how to use a subquery to create a dynamic List of Values (LOV) in Oracle APEX.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Dynamic LOV with a Subquery in Oracle APEX - Traust\" \/>\n<meta property=\"og:description\" content=\"A real-world example and a walkthrough about how to use a subquery to create a dynamic List of Values (LOV) in Oracle APEX.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/\" \/>\n<meta property=\"og:site_name\" content=\"Traust\" \/>\n<meta property=\"article:published_time\" content=\"2020-10-15T10:52:46+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-10-15T14:25:14+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/traust.com\/wp-content\/uploads\/2020\/10\/SQL-Icon.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"626\" \/>\n\t<meta property=\"og:image:height\" content=\"626\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Luke Rohde\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@TraustConsult\" \/>\n<meta name=\"twitter:site\" content=\"@TraustConsult\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Luke Rohde\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/\"},\"author\":{\"name\":\"Luke Rohde\",\"@id\":\"https:\/\/traust.com\/#\/schema\/person\/2af8547f31bebf73e4d13692df03f83e\"},\"headline\":\"Dynamic LOV with a Subquery in Oracle APEX\",\"datePublished\":\"2020-10-15T10:52:46+00:00\",\"dateModified\":\"2020-10-15T14:25:14+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/\"},\"wordCount\":364,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/traust.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/traust.com\/wp-content\/uploads\/2020\/10\/SQL-Icon.jpg\",\"articleSection\":[\"APEX\",\"IT\",\"Technical\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/\",\"url\":\"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/\",\"name\":\"Dynamic LOV with a Subquery in Oracle APEX - Traust\",\"isPartOf\":{\"@id\":\"https:\/\/traust.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/traust.com\/wp-content\/uploads\/2020\/10\/SQL-Icon.jpg\",\"datePublished\":\"2020-10-15T10:52:46+00:00\",\"dateModified\":\"2020-10-15T14:25:14+00:00\",\"description\":\"A real-world example and a walkthrough about how to use a subquery to create a dynamic List of Values (LOV) in Oracle APEX.\",\"breadcrumb\":{\"@id\":\"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/#primaryimage\",\"url\":\"https:\/\/traust.com\/wp-content\/uploads\/2020\/10\/SQL-Icon.jpg\",\"contentUrl\":\"https:\/\/traust.com\/wp-content\/uploads\/2020\/10\/SQL-Icon.jpg\",\"width\":626,\"height\":626},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/traust.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Dynamic LOV with a Subquery in Oracle APEX\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/traust.com\/#website\",\"url\":\"https:\/\/traust.com\/\",\"name\":\"Traust Consulting\",\"description\":\"Enterprise Solutions. Tailored to Work.\",\"publisher\":{\"@id\":\"https:\/\/traust.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/traust.com\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/traust.com\/#organization\",\"name\":\"Traust\",\"url\":\"https:\/\/traust.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/traust.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/traust.com\/wp-content\/uploads\/2020\/05\/Traust-Logo-2.png\",\"contentUrl\":\"https:\/\/traust.com\/wp-content\/uploads\/2020\/05\/Traust-Logo-2.png\",\"width\":200,\"height\":200,\"caption\":\"Traust\"},\"image\":{\"@id\":\"https:\/\/traust.com\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/x.com\/TraustConsult\",\"https:\/\/www.linkedin.com\/company\/traust-consulting\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/traust.com\/#\/schema\/person\/2af8547f31bebf73e4d13692df03f83e\",\"name\":\"Luke Rohde\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/traust.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/4e13d600233be2c482a193461da44343?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/4e13d600233be2c482a193461da44343?s=96&d=mm&r=g\",\"caption\":\"Luke Rohde\"},\"description\":\"Luke is a hard working, trustworthy, honest, dependable, genuine consultant, and he cares about doing good work that he can be proud of. In 2019, he left his position at Surly Brewing Company to pursue an education opportunity at Prime Digital Academy in Minneapolis, Minnesota. After completing that course in March 2020, he began work at Traust as a web developer and an Oracle APEX engineer.\",\"url\":\"https:\/\/traust.com\/blog\/author\/lrohde\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Dynamic LOV with a Subquery in Oracle APEX - Traust","description":"A real-world example and a walkthrough about how to use a subquery to create a dynamic List of Values (LOV) in Oracle APEX.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/","og_locale":"en_US","og_type":"article","og_title":"Dynamic LOV with a Subquery in Oracle APEX - Traust","og_description":"A real-world example and a walkthrough about how to use a subquery to create a dynamic List of Values (LOV) in Oracle APEX.","og_url":"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/","og_site_name":"Traust","article_published_time":"2020-10-15T10:52:46+00:00","article_modified_time":"2020-10-15T14:25:14+00:00","og_image":[{"width":626,"height":626,"url":"https:\/\/traust.com\/wp-content\/uploads\/2020\/10\/SQL-Icon.jpg","type":"image\/jpeg"}],"author":"Luke Rohde","twitter_card":"summary_large_image","twitter_creator":"@TraustConsult","twitter_site":"@TraustConsult","twitter_misc":{"Written by":"Luke Rohde","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/#article","isPartOf":{"@id":"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/"},"author":{"name":"Luke Rohde","@id":"https:\/\/traust.com\/#\/schema\/person\/2af8547f31bebf73e4d13692df03f83e"},"headline":"Dynamic LOV with a Subquery in Oracle APEX","datePublished":"2020-10-15T10:52:46+00:00","dateModified":"2020-10-15T14:25:14+00:00","mainEntityOfPage":{"@id":"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/"},"wordCount":364,"commentCount":0,"publisher":{"@id":"https:\/\/traust.com\/#organization"},"image":{"@id":"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/#primaryimage"},"thumbnailUrl":"https:\/\/traust.com\/wp-content\/uploads\/2020\/10\/SQL-Icon.jpg","articleSection":["APEX","IT","Technical"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/","url":"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/","name":"Dynamic LOV with a Subquery in Oracle APEX - Traust","isPartOf":{"@id":"https:\/\/traust.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/#primaryimage"},"image":{"@id":"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/#primaryimage"},"thumbnailUrl":"https:\/\/traust.com\/wp-content\/uploads\/2020\/10\/SQL-Icon.jpg","datePublished":"2020-10-15T10:52:46+00:00","dateModified":"2020-10-15T14:25:14+00:00","description":"A real-world example and a walkthrough about how to use a subquery to create a dynamic List of Values (LOV) in Oracle APEX.","breadcrumb":{"@id":"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/#primaryimage","url":"https:\/\/traust.com\/wp-content\/uploads\/2020\/10\/SQL-Icon.jpg","contentUrl":"https:\/\/traust.com\/wp-content\/uploads\/2020\/10\/SQL-Icon.jpg","width":626,"height":626},{"@type":"BreadcrumbList","@id":"https:\/\/traust.com\/blog\/dynamic-lov-with-a-subquery-in-oracle-apex\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/traust.com\/"},{"@type":"ListItem","position":2,"name":"Dynamic LOV with a Subquery in Oracle APEX"}]},{"@type":"WebSite","@id":"https:\/\/traust.com\/#website","url":"https:\/\/traust.com\/","name":"Traust Consulting","description":"Enterprise Solutions. Tailored to Work.","publisher":{"@id":"https:\/\/traust.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/traust.com\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/traust.com\/#organization","name":"Traust","url":"https:\/\/traust.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/traust.com\/#\/schema\/logo\/image\/","url":"https:\/\/traust.com\/wp-content\/uploads\/2020\/05\/Traust-Logo-2.png","contentUrl":"https:\/\/traust.com\/wp-content\/uploads\/2020\/05\/Traust-Logo-2.png","width":200,"height":200,"caption":"Traust"},"image":{"@id":"https:\/\/traust.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/x.com\/TraustConsult","https:\/\/www.linkedin.com\/company\/traust-consulting\/"]},{"@type":"Person","@id":"https:\/\/traust.com\/#\/schema\/person\/2af8547f31bebf73e4d13692df03f83e","name":"Luke Rohde","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/traust.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/4e13d600233be2c482a193461da44343?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4e13d600233be2c482a193461da44343?s=96&d=mm&r=g","caption":"Luke Rohde"},"description":"Luke is a hard working, trustworthy, honest, dependable, genuine consultant, and he cares about doing good work that he can be proud of. In 2019, he left his position at Surly Brewing Company to pursue an education opportunity at Prime Digital Academy in Minneapolis, Minnesota. After completing that course in March 2020, he began work at Traust as a web developer and an Oracle APEX engineer.","url":"https:\/\/traust.com\/blog\/author\/lrohde\/"}]}},"_links":{"self":[{"href":"https:\/\/traust.com\/wp-json\/wp\/v2\/posts\/10501"}],"collection":[{"href":"https:\/\/traust.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/traust.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/traust.com\/wp-json\/wp\/v2\/users\/20"}],"replies":[{"embeddable":true,"href":"https:\/\/traust.com\/wp-json\/wp\/v2\/comments?post=10501"}],"version-history":[{"count":0,"href":"https:\/\/traust.com\/wp-json\/wp\/v2\/posts\/10501\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/traust.com\/wp-json\/wp\/v2\/media\/10515"}],"wp:attachment":[{"href":"https:\/\/traust.com\/wp-json\/wp\/v2\/media?parent=10501"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/traust.com\/wp-json\/wp\/v2\/categories?post=10501"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/traust.com\/wp-json\/wp\/v2\/tags?post=10501"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}