{"id":10641,"date":"2017-07-29T08:36:42","date_gmt":"2017-07-29T08:36:42","guid":{"rendered":"http:\/\/www.greytrix.com\/blogs\/sagex3\/?p=10641"},"modified":"2022-11-01T05:12:28","modified_gmt":"2022-11-01T05:12:28","slug":"how-to-define-filter-criterion-on-database-query-in-sage-x3","status":"publish","type":"post","link":"https:\/\/www.greytrix.com\/blogs\/sagex3\/2017\/07\/29\/how-to-define-filter-criterion-on-database-query-in-sage-x3\/","title":{"rendered":"How to define filter criterion on database query in Sage X3"},"content":{"rendered":"<p style=\"text-align: justify;\">In <strong>Sage X3<\/strong> latest versions, we are provided with new feature to define filter criterion on the database.\u00a0This function defines a filtering condition on a database query. The condition is expressed in Sdata where syntax.<\/p>\n<p style=\"text-align: justify;\">This function is only valid inside a Where clause of a database instruction (For, Filter, File, Link).<\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<p>Where evaluesdata(EXPRESSION, VARIABLES, COLUMNS)<\/p>\n<ul>\n<li>EXPRESSION is a filter expressed in SData where syntax.<\/li>\n<li>VARIABLES is an array containing the variable names used in the filter.<\/li>\n<li>COLUMNS is an array containing the database columns referenced by the variable names.<\/li>\n<\/ul>\n<p><strong>Example:<\/strong><\/p>\n<p>Local File MYTABLE [MYT]\nLocal Char VARS(20)(1..2), COLS(20)(1..2)<br \/>\nVARS(1)=&#8221;city&#8221; : VARS(2)=&#8221;total&#8221;<br \/>\nCOLS(1)=&#8221;[MYT]CITYNAME&#8221; : COLS(2)=&#8221;[MYT]AMOUNT&#8221;<\/p>\n<p># The condition<br \/>\nFilter [MYT] Where evalueSData(<br \/>\n&amp; &#8220;(city in &#8216;London&#8217;,&#8217;Paris&#8217;) and (total between 1 and 10) &#8221;<br \/>\n&amp; , VARS, COLS)<br \/>\n# is equivalent to<br \/>\nFilter [MYT] Where find(CITYNAME,&#8217;London&#8217;,&#8217;Paris&#8217;) and (AMOUNT&gt;=1 and AMOUNT&lt;=100)<\/p>\n<p style=\"text-align: justify;\">Here, the output data will contain city(either London or paris) and total amount between 1 to 100.<\/p>\n<p><strong>Comments:<\/strong><\/p>\n<p>This function only supports the following subset of the SData Query language:<\/p>\n<ul>\n<li style=\"text-align: justify;\">String, numeric, date and datetime literals (for example, &#8216;Hello &#8220;World&#8221;&#8216;,&#8221;I&#8217;m tired&#8221;, 3.14, @1959-05-29@, @2008-05-19T16:41:00Z@).<\/li>\n<li style=\"text-align: justify;\">Parentheses<\/li>\n<li style=\"text-align: justify;\">The following operators and functions:<\/li>\n<\/ul>\n<table style=\"font-family: calibri,sans-serif; font-size: 12pt; text-align: left; border: 1px solid #CCCCCC;\">\n<tbody>\n<tr>\n<td style=\"border: 1px solid #cccccc; text-align: center;\" width=\"100\"><strong>Keyword<\/strong><\/td>\n<td style=\"border: 1px solid #cccccc; text-align: center;\" width=\"300\"><strong>Description<\/strong><\/td>\n<td style=\"border: 1px solid #cccccc; text-align: center;\" width=\"170\"><strong>Example<\/strong><\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"100\">lt<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">less than<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"170\">amount lt 5000<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"100\">gt<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">greather than<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"170\">amount gt 3000<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"100\">le<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">less or equal<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"170\">amount le 4500<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"100\">ge<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">greater or equal<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"170\">amount ge 7800<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"100\">eq<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">equal<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"170\">amount eq 3000<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"100\">ne<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">not equal<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"170\">amount ne 3000<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"100\">between &#8230; and&#8230;<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">between two values<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"170\">amount between 20 and 30<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"100\">in<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">contained in a list of values<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"170\">amount in (3,14,15,9,26)<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"100\">like<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">matches a string pattern<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"170\">name like &#8216;%PONT%&#8217;<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"100\">and<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">logical and<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"170\">amount ne 300 and amount ne 500<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"100\">or<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">logical or<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"170\">amount eq 300 or amount eq 500<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"100\">abs<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">absolute value<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"170\">abs(amount) eq 6<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"100\">ascii<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">ASCII code of first character<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"170\">ascii(countrycode) eq 32<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"100\">char<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">single char from ASCII code<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"170\">countrycode eq char(32)<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"100\">div<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">division<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"170\">amount div 2 eq 3<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"100\">left(str,len)<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">leftmost `len` characters from str; `str` if less than `len` characters<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"170\">left(name,3)=&#8221;Doe&#8221;<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"100\">lower(str)<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">converts str to lower case<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"170\">lower(name)=&#8221;doe&#8221;<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"100\">upper(str)<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">converts str to upper case<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"170\">lower(name)=&#8221;DOE&#8221;<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"100\">mod<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">modulus<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"170\">number mod 2 eq 0<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"100\">mul<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">multiplication<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"170\">amount mul 3 eq 15<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"100\">not<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">negation<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"170\">not (amount eq 3)<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"120\">pow<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">power<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"190\">amount pow 3 eq 125<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"100\">right(str,len)<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">rightmost `len` characters from `str`; `str` if less than `len` characters<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"170\">right(name,2)=&#8217;oe&#8217;<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"100\">substring(str,index,len)<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"350\">substring starting at `start` and containing `len` characters; start is 1-based<\/td>\n<td style=\"text-align: left; border: 1px solid #CCCCCC;\" width=\"170\">substring(name,4,5)=&#8217;ti&#8217;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><span style=\"font-size: Medium;\"><strong>About Us<\/strong><\/span><br \/>\n<a href=\"https:\/\/www.greytrix.com\/\">Greytrix<\/a> \u2013 a globally recognized Premier Sage Gold Development Partner is a one-stop solution provider for Sage ERP and Sage CRM needs. Being recognized and rewarded for multi-man years of experience, we bring complete end-to-end assistance for your technical consultations, product customizations, data migration, system integrations, third-party add-on development and implementation expertise.<\/p>\n<p><a href=\"https:\/\/www.greytrix.com\/\">Greytrix<\/a>\u00a0caters to a wide range of Sage Enterprise Management (Sage X3) offerings \u2013 a Sage Business Cloud Solution. Our\u00a0unique GUMU&#x2122; integrations include <a href=\"https:\/\/www.greytrix.com\/product\/sage-crm\/gumu-sage-x3-integration\">Sage Enterprise Management (Sage X3) for Sage CRM<\/a>,\u00a0<a href=\"https:\/\/www.greytrix.com\/product\/sage-x3\/gumu-x3-salesforce-integration\">Salesforce.com<\/a> and\u00a0<a href=\"https:\/\/www.greytrix.com\/product\/magento-sage-integrations\/sage-x3-magento\">Magento eCommerce<\/a>\u00a0along with Implementation and Technical Support worldwide for <a href=\"https:\/\/www.greytrix.com\/africa\/product\/consulting\/implementation-planning\">Sage Enterprise Management (Sage X3)<\/a>. Currently we are <a href=\"https:\/\/www.greytrix.com\/africa\">Sage Enterprise Management Implementation Partner<\/a> in East Africa, Middles East, Australia, Asia.\u00a0We also offer best-in-class\u00a0<a href=\"https:\/\/www.greytrix.com\/product\/erp-development\/sage-x3\">Sage X3 customization and development services<\/a>,\u00a0integrated applications such as <a href=\"https:\/\/www.greytrix.com\/product\/sage-enterprise-management-integration-services\/pos-system\">POS<\/a> | <a href=\"https:\/\/www.greytrix.com\/product\/sage-enterprise-management-integration-services\/warehouse-management-system\">WMS<\/a> | <a href=\"https:\/\/www.greytrix.com\/product\/sage-enterprise-management-integration-services\/payment-gateway\">Payment Gateway<\/a> | <a href=\"https:\/\/www.greytrix.com\/product\/sage-enterprise-management-integration-services\/shipping-system\">Shipping System<\/a> | <a href=\"https:\/\/www.greytrix.com\/product\/sage-enterprise-management-integration-services\/business-intelligence\">Business Intelligence<\/a> | <a href=\"https:\/\/www.greytrix.com\/product\/sage-x3\/ecommerce-magento-integration\">eCommerce<\/a> and have developed add-ons such as <a href=\"https:\/\/www.greytrix.com\/product\/sage-enterprise-management-add-ons\/catch-weight\">Catch \u2013 Weight <\/a>\u00a0and <a href=\"https:\/\/www.greytrix.com\/product\/sage-enterprise-management-add-ons\/letter-of-credit\">Letter of Credit<\/a> for Sage Enterprise Management to Sage business partners, end users and Sage PSG worldwide.<\/p>\n<p>Greytrix is a recognized <a href=\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2017\/07\/26\/greytrix-on-boards-sage-global-rockstar-isv-program-with-gumu-for-sage-crm-sage-x3-solution\/\">Sage Rockstar ISV Partner<\/a> for GUMU&#x2122; Sage Enterprise Management \u2013 Sage CRM integration also listed on <a href=\"https:\/\/www.sage.com\/marketplace\/asb_ListingDetail?listingId=a1h24000007PC3aAAG\" target=\"_blank\" rel=\"noopener\">Sage Marketplace<\/a>; GUMU&#x2122; integration for Sage Enterprise Management \u2013 Salesforce is a 5-star app listed on <a href=\"https:\/\/appexchange.salesforce.com\/listingDetail?listingId=a0N30000000psM5EAI\" target=\"_blank\" rel=\"noopener\">Salesforce AppExchange<\/a>.<\/p>\n<p>For more information on Sage X3 Integration and Services, please contact us at\u00a0<a href=\"mailto:x3@greytrix.com\">x3@greytrix.com<\/a>. We will be glad to assist you.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In Sage X3 latest versions, we are provided with new feature to define filter criterion on the database.\u00a0This function defines a filtering condition on a database query. The condition is expressed in Sdata where syntax. This function is only valid inside a Where clause of a database instruction (For, Filter, File, Link). Syntax: Where evaluesdata(EXPRESSION,\u2026 <span class=\"read-more\"><a href=\"https:\/\/www.greytrix.com\/blogs\/sagex3\/2017\/07\/29\/how-to-define-filter-criterion-on-database-query-in-sage-x3\/\">Read More &raquo;<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-10641","post","type-post","status-publish","format-standard","hentry","category-features"],"_links":{"self":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-json\/wp\/v2\/posts\/10641","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-json\/wp\/v2\/comments?post=10641"}],"version-history":[{"count":3,"href":"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-json\/wp\/v2\/posts\/10641\/revisions"}],"predecessor-version":[{"id":25354,"href":"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-json\/wp\/v2\/posts\/10641\/revisions\/25354"}],"wp:attachment":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-json\/wp\/v2\/media?parent=10641"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-json\/wp\/v2\/categories?post=10641"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-json\/wp\/v2\/tags?post=10641"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}