{"id":47,"date":"2011-05-19T13:33:00","date_gmt":"2011-05-19T13:33:00","guid":{"rendered":"http:\/\/greytrix.com\/Blogs\/sageaccpacerp\/?p=16"},"modified":"2024-12-12T10:53:18","modified_gmt":"2024-12-12T10:53:18","slug":"using-sql-queries-through-accpac-view","status":"publish","type":"post","link":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/2011\/05\/19\/using-sql-queries-through-accpac-view\/","title":{"rendered":"Use SQL Queries through Accpac view"},"content":{"rendered":"

There are few situations that almost every Accpac Developers face while working with Accpac Views, like\u2026
\nPERFORMANCE<\/strong>, when you have to browse\/read a filtered piece of information from an Accpac table\/view that has a huge size of Data, yes! Accpac certainly allows us to filter the Data on the fields that are exposed by the view, but sometimes it is very slow specifically if the field that you are using in your filter is not indexed and the data is huge.
\nSQL QUERIES<\/strong>, often while writing business logic we wish if we could use a SQL Query and it would have been so simple and faster.
\nQUERYING MULTIPLE TABLES<\/strong>, with views if you have to deal with multiple tables (like if one has to list Orders of Customers belonging to specific Customer Type) we need to do a nested loop and all.
\nConsidering these concerns of Developer community Sage has introduced a View \u201cCS0120\u201d CSQRY since v5.5 onwards, which we can use to pass our SQL queries.
\nUsing this view you get results much faster, like we had a speed issue reported by a client where we were trying to list them all open orders of a customer in a List Control with checkbox, we had opened the view in reading mode and were fetching each row to add to this control it took approx 35 sec\u2026 for 6000 rows (why we did not use bound control and all is a different story), we changed the logic and pulled the result in \u201cCS0120\u201d view by passing appropriate query and it just took a couple of seconds for the same set of data to be populated in the List Control\u2026
\nHere is the sample code of how to use CSQRY view, just in case you are not aware of how to use it…<\/p>\n

Dim CSQry As AccpacCOMAPI.AccpacView
\nDim sSql As String<\/em><\/p>\n

‘Open view
\nmDBLinkCmpRW.OpenView “CS0120”, CSQry<\/p>\n

Below is the query that can be passed to the CSQRY
\nsSql = “”
\nsSql = “select ICITEM.ItemNo from ICITEM where ICITEM. ItemNo in (\u2018A0001\u2019, \u2018A0002\u2019, \u2018A0003\u2019)”<\/p>\n

CSQry.Cancel
\nCSQry.Browse sSql, True
\nCSQry.InternalSet (256)<\/p>\n

Do While CSQry.Fetch
\n\u2018< << Add your business logic here >>>
\nLoop
\nCSQry.Close<\/p>\n

About Us<\/strong><\/span>
\nGreytrix is a one-stop solution provider for Sage ERP and Sage CRM needs. We provide complete end-to-end assistance for your technical consultations, product customizations, data migration, system integrations, third-party add-on development and implementation expertise.
\nGreytrix<\/a>\u00a0has some unique solutions of\u00a0Sage 300 integration with Sage CRM,<\/a>\u00a0Salesforce.com\u00a0<\/a>and\u00a0Magento eCommerce<\/a> along with Sage 300 Migration from Sage 50 US, Sage 50 CA, Sage PRO, QuickBooks, Sage Business Vision and Sage Business Works. We also offer best-in-class\u00a0Sage 300 customization and development services<\/a>\u00a0to Sage business partners, end users, and Sage PSG worldwide.
\nFor more details on Sage 300 Services, please contact us at\u00a0accpac@greytrix.com<\/a>. We will be glad to assist you.<\/p>\n","protected":false},"excerpt":{"rendered":"

There are few situations that almost every Accpac Developers face while working with Accpac Views, like\u2026 PERFORMANCE, when you have to browse\/read a filtered piece of information from an Accpac table\/view that has a huge size of Data, yes! Accpac certainly allows us to filter the Data on the fields that are exposed by the\u2026 Read More »<\/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":[2],"tags":[93,2415,2416,2451,2730,2959],"class_list":["post-47","post","type-post","status-publish","format-standard","hentry","category-sage-accpac","tag-accpac-views","tag-sage-300-technical-assistance","tag-sage-300-technical-blogs","tag-sage-accpac-erp","tag-sql-queries-through-accpac-view","tag-using-sql-queries"],"yoast_head":"\nUse SQL Queries through Accpac view<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/2011\/05\/19\/using-sql-queries-through-accpac-view\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Use SQL Queries through Accpac view\" \/>\n<meta property=\"og:description\" content=\"There are few situations that almost every Accpac Developers face while working with Accpac Views, like\u2026 PERFORMANCE, when you have to browse\/read a filtered piece of information from an Accpac table\/view that has a huge size of Data, yes! Accpac certainly allows us to filter the Data on the fields that are exposed by the\u2026 Read More »\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/2011\/05\/19\/using-sql-queries-through-accpac-view\/\" \/>\n<meta property=\"og:site_name\" content=\"Sage 300 ERP \u2013 Tips, Tricks and Components\" \/>\n<meta property=\"article:published_time\" content=\"2011-05-19T13:33:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-12-12T10:53:18+00:00\" \/>\n<meta name=\"author\" content=\"sage300\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"sage300\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/2011\/05\/19\/using-sql-queries-through-accpac-view\/\",\"url\":\"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/2011\/05\/19\/using-sql-queries-through-accpac-view\/\",\"name\":\"Use SQL Queries through Accpac view\",\"isPartOf\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/#website\"},\"datePublished\":\"2011-05-19T13:33:00+00:00\",\"dateModified\":\"2024-12-12T10:53:18+00:00\",\"author\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/#\/schema\/person\/1aced4e22c30ccf606960de27c9542f5\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/2011\/05\/19\/using-sql-queries-through-accpac-view\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/2011\/05\/19\/using-sql-queries-through-accpac-view\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/2011\/05\/19\/using-sql-queries-through-accpac-view\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Use SQL Queries through Accpac view\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/#website\",\"url\":\"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/\",\"name\":\"Sage 300 ERP \u2013 Tips, Tricks and Components\",\"description\":\"Explore the world of Sage 300 with our insightful blogs, expert tips, and the latest updates. We\u2019ll empower you to leverage Sage 300 to its fullest potential. As your one-stop partner, Greytrix delivers exceptional solutions and integrations for Sage 300. Our blogs extend our support to businesses, covering the latest insights and trends. Dive in and transform your Sage 300 experience with us! \",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/#\/schema\/person\/1aced4e22c30ccf606960de27c9542f5\",\"name\":\"sage300\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g\",\"caption\":\"sage300\"},\"url\":\"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/author\/sageaccpac\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Use SQL Queries through Accpac view","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:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/2011\/05\/19\/using-sql-queries-through-accpac-view\/","og_locale":"en_US","og_type":"article","og_title":"Use SQL Queries through Accpac view","og_description":"There are few situations that almost every Accpac Developers face while working with Accpac Views, like\u2026 PERFORMANCE, when you have to browse\/read a filtered piece of information from an Accpac table\/view that has a huge size of Data, yes! Accpac certainly allows us to filter the Data on the fields that are exposed by the\u2026 Read More »","og_url":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/2011\/05\/19\/using-sql-queries-through-accpac-view\/","og_site_name":"Sage 300 ERP \u2013 Tips, Tricks and Components","article_published_time":"2011-05-19T13:33:00+00:00","article_modified_time":"2024-12-12T10:53:18+00:00","author":"sage300","twitter_card":"summary_large_image","twitter_misc":{"Written by":"sage300","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/2011\/05\/19\/using-sql-queries-through-accpac-view\/","url":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/2011\/05\/19\/using-sql-queries-through-accpac-view\/","name":"Use SQL Queries through Accpac view","isPartOf":{"@id":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/#website"},"datePublished":"2011-05-19T13:33:00+00:00","dateModified":"2024-12-12T10:53:18+00:00","author":{"@id":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/#\/schema\/person\/1aced4e22c30ccf606960de27c9542f5"},"breadcrumb":{"@id":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/2011\/05\/19\/using-sql-queries-through-accpac-view\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/2011\/05\/19\/using-sql-queries-through-accpac-view\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/2011\/05\/19\/using-sql-queries-through-accpac-view\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/"},{"@type":"ListItem","position":2,"name":"Use SQL Queries through Accpac view"}]},{"@type":"WebSite","@id":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/#website","url":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/","name":"Sage 300 ERP \u2013 Tips, Tricks and Components","description":"Explore the world of Sage 300 with our insightful blogs, expert tips, and the latest updates. We\u2019ll empower you to leverage Sage 300 to its fullest potential. As your one-stop partner, Greytrix delivers exceptional solutions and integrations for Sage 300. Our blogs extend our support to businesses, covering the latest insights and trends. Dive in and transform your Sage 300 experience with us! ","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/#\/schema\/person\/1aced4e22c30ccf606960de27c9542f5","name":"sage300","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g","caption":"sage300"},"url":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/author\/sageaccpac\/"}]}},"_links":{"self":[{"href":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/wp-json\/wp\/v2\/posts\/47","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/wp-json\/wp\/v2\/comments?post=47"}],"version-history":[{"count":1,"href":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/wp-json\/wp\/v2\/posts\/47\/revisions"}],"predecessor-version":[{"id":33644,"href":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/wp-json\/wp\/v2\/posts\/47\/revisions\/33644"}],"wp:attachment":[{"href":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/wp-json\/wp\/v2\/media?parent=47"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/wp-json\/wp\/v2\/categories?post=47"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sageaccpacerp\/wp-json\/wp\/v2\/tags?post=47"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}