{"id":85814,"date":"2021-03-03T14:36:02","date_gmt":"2021-03-03T09:06:02","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=85814"},"modified":"2021-03-03T14:36:02","modified_gmt":"2021-03-03T09:06:02","slug":"open-sql-native-sql-in-sap-abap","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/open-sql-native-sql-in-sap-abap\/","title":{"rendered":"Open SQL &amp; Native SQL in SAP ABAP"},"content":{"rendered":"<p>In this tutorial, we\u2019ll be learning about the two types of SQL (Structured Query Language) used in SAP ABAP. Let us dive deep into the world of database enquiry!<\/p>\n<h3>What is SQL?<\/h3>\n<ul>\n<li>SQL stands for \u2018Structured Query Language\u2019.<\/li>\n<li>It is a language used to access, query and manipulate data in databases.<\/li>\n<li>You can create, view, delete, modify or update data, tables, views, etc. in a database using SQL.<\/li>\n<li>ANSI (American National Standards Institute) has declared SQL to be a standard language, especially for relational databases.<\/li>\n<\/ul>\n<h3>SQL Types in ABAP<\/h3>\n<p>SQL has many different types, and in ABAP we use two types of SQL:<\/p>\n<ol>\n<li>OpenSQL<\/li>\n<li>Native SQL<\/li>\n<\/ol>\n<p>Let us see both these types in detail.<\/p>\n<h3>OpenSQL in SAP ABAP<\/h3>\n<ul>\n<li>OpenSQL allows access to the ABAP Data Dictionary without checking which database platform the R3 system uses.<\/li>\n<li>It uses ABAP statements to operate upon the R3 system\u2019s central database.<\/li>\n<li>ABAP has a rule that says you cannot use more than one database system if you are using database-specific queries.<\/li>\n<li>Hence, if you want to use multiple database systems, OpenSQL is your best bet.<\/li>\n<li>Both the queries and results run independently of the database system in ABAP.<\/li>\n<li>However, OpenSQL only works with the database tables created by Data Dictionary.<\/li>\n<\/ul>\n<p>Here are some keywords in OpenSQL:<\/p>\n<table>\n<tbody>\n<tr>\n<td><b>STATEMENT<\/b><\/td>\n<td><b>MEANING<\/b><\/td>\n<\/tr>\n<tr>\n<td>SELECT<\/td>\n<td>this statement accesses, reads and displays data from the table<\/td>\n<\/tr>\n<tr>\n<td>INSERT<\/td>\n<td>this statement adds information into the tables (writes)<\/td>\n<\/tr>\n<tr>\n<td>UPDATE<\/td>\n<td>this statement modifies or changes the data within lines of tables<\/td>\n<\/tr>\n<tr>\n<td>MODIFY<\/td>\n<td>this statement either adds lines or changes line content of tables<\/td>\n<\/tr>\n<tr>\n<td>DELETE<\/td>\n<td>this statement deletes data from tables<\/td>\n<\/tr>\n<tr>\n<td>OPEN CURSOR, FETCH, CLOSE CURSOR<\/td>\n<td>this statement uses the cursor to read through the database<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>OpenSQL statements also have two additional instructions that they perform, called system fields &#8211;<\/p>\n<table>\n<tbody>\n<tr>\n<td><b>FIELD<\/b><\/td>\n<td><b>MEANING<\/b><\/td>\n<\/tr>\n<tr>\n<td>SY-SUBRC<\/td>\n<td>This system field returns 0 if the operation was successful, and a nonzero value if it was not, to indicate type of error code<\/td>\n<\/tr>\n<tr>\n<td>SY-DBCNT<\/td>\n<td>This system field contains the number of lines from the database that have been processed<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>OpenSQL Example in ABAP &#8211;<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">TABLES SDATAFLAIR.\r\n\r\nDATA C TYPE CURSOR,\r\nWA LIKE SDATAFLAIR.\r\n\r\nOPEN CURSOR C FOR SELECT * FROM SDATAFLAIR WHERE CARRID = 'LH '\r\nAND CONNID = '0400'\r\nAND DATE = '19950228'\r\nORDER BY PRIMARY KEY.\r\n\r\nDO.\r\nFETCH NEXT CURSOR C INTO WA.\r\nIF SY-SUBRC &lt;&gt; 0.\r\nCLOSE CURSOR C.\r\nEXIT.\r\nENDIF.\r\nWRITE: \/ WA-BOOKID, WA-CUSTOMID, WA-CUSTTYPE, WA-WUNIT,WA-INVOICE.\r\nENDDO.<\/pre>\n<p>The above code will return the list of all customers of ID = LH &amp; 0400, from date 1995-02-28<\/p>\n<h3>Native SQL in SAP ABAP<\/h3>\n<ul>\n<li>Native SQL uses ABAP-specific or database-specific queries in the code.<\/li>\n<li>This means that it can integrate all the data that is not part of the R3 system.<\/li>\n<li>So native SQL can manage data that is specific to a database i.e. the data that is not managed by the ABAP Data Dictionary.<\/li>\n<li>However, by association of this definition, if you want to use more than one platform, you will need to use openSQL instead.<\/li>\n<li>In native SQL, you need to begin the statements with \u2018EXEC SQL\u2019 and end it with \u2018ENDEXEC\u2019 statement.<\/li>\n<\/ul>\n<p>Native SQL Example in ABAP &#8211;<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">REPORT ZS_DATAFLAIR. \r\nDATA: BEGIN OF df,  \r\n      connid  TYPE DF1-connid,\r\n      cityfrom TYPE DF1-cityfrom,\r\n      cityto  TYPE DF1-cityto,  \r\n      END OF df. \r\n    \r\nDATA DF1 TYPE DF1-carrid VALUE 'LH'. \r\nEXEC SQL PERFORMING loop_output.\r\n   SELECT connid, cityfrom, cityto  \r\n   INTO : df \r\n   FROM DF1 \r\n   WHERE carrid = : DF1 \r\nENDEXEC. \r\n\r\nFORM loop_output.  \r\n   WRITE: \/ df-connid, df-cityfrom, df-cityto. \r\nENDFORM.<\/pre>\n<p>The above code will output all customers containing carrid = DF1.<\/p>\n<h3>OpenSQL vs Native SQL<\/h3>\n<table>\n<tbody>\n<tr>\n<td><b>OpenSQL in ABAP<\/b><\/td>\n<td><b>Native SQL in ABAP<\/b><\/td>\n<\/tr>\n<tr>\n<td>OpenSQL statements, much like regular ABAP statements, need a period (.) after the end of each statement.<\/td>\n<td>In native SQL, there is no need for a period after the statement. Additionally, it does not follow typical ABAP rules.<\/td>\n<\/tr>\n<tr>\n<td>OpenSQL allows data from multiple database platforms<\/td>\n<td>Native SQL does not allow data from multiple database platforms<\/td>\n<\/tr>\n<tr>\n<td>Tables that belong to other systems cannot be accessed<\/td>\n<td>Tables that belong to other systems can be accessed<\/td>\n<\/tr>\n<tr>\n<td>Syntax check is performed in between the EXEC and ENDEXEC statements<\/td>\n<td>No syntax check is performed<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h3>Summary<\/h3>\n<p>Thus, in this tutorial, we learnt about two types of SQL used in ABAP &#8211; OpenSQL and native SQL.<\/p>\n<p>We learnt why SQL is used and especially in ABAP. We learnt how to use both kinds of SQL and the difference between them.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this tutorial, we\u2019ll be learning about the two types of SQL (Structured Query Language) used in SAP ABAP. Let us dive deep into the world of database enquiry! What is SQL? SQL stands&#46;&#46;&#46;<\/p>\n","protected":false},"author":1,"featured_media":86439,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23786],"tags":[23820,23821,23819],"class_list":["post-85814","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sap-abap","tag-native-sql-in-sap-abap","tag-open-sql-vs-native-sql-in-sap-abap","tag-opensql-in-sap-abap"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.8 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Open SQL &amp; Native SQL in SAP ABAP - DataFlair<\/title>\n<meta name=\"description\" content=\"Learn what is OpenSQL and Native SQL in SAP ABAP with examples. Learn the difference between OpenSQL vs Native SQL in ABAP.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/open-sql-native-sql-in-sap-abap\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Open SQL &amp; Native SQL in SAP ABAP - DataFlair\" \/>\n<meta property=\"og:description\" content=\"Learn what is OpenSQL and Native SQL in SAP ABAP with examples. Learn the difference between OpenSQL vs Native SQL in ABAP.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/open-sql-native-sql-in-sap-abap\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2021-03-03T09:06:02+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/03\/Open-SQL-Native-SQL-in-SAP-ABAP.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Open SQL &amp; Native SQL in SAP ABAP - DataFlair","description":"Learn what is OpenSQL and Native SQL in SAP ABAP with examples. Learn the difference between OpenSQL vs Native SQL in ABAP.","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:\/\/data-flair.training\/blogs\/open-sql-native-sql-in-sap-abap\/","og_locale":"en_US","og_type":"article","og_title":"Open SQL &amp; Native SQL in SAP ABAP - DataFlair","og_description":"Learn what is OpenSQL and Native SQL in SAP ABAP with examples. Learn the difference between OpenSQL vs Native SQL in ABAP.","og_url":"https:\/\/data-flair.training\/blogs\/open-sql-native-sql-in-sap-abap\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2021-03-03T09:06:02+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/03\/Open-SQL-Native-SQL-in-SAP-ABAP.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/open-sql-native-sql-in-sap-abap\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/open-sql-native-sql-in-sap-abap\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/b49855299264df5e27e3ec6c2cd9fde9"},"headline":"Open SQL &amp; Native SQL in SAP ABAP","datePublished":"2021-03-03T09:06:02+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/open-sql-native-sql-in-sap-abap\/"},"wordCount":625,"commentCount":0,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/open-sql-native-sql-in-sap-abap\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/03\/Open-SQL-Native-SQL-in-SAP-ABAP.jpg","keywords":["Native SQL in SAP ABAP","Open SQL vs Native SQL in SAP ABAP","OpenSQL in SAP ABAP"],"articleSection":["SAP ABAP Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/open-sql-native-sql-in-sap-abap\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/open-sql-native-sql-in-sap-abap\/","url":"https:\/\/data-flair.training\/blogs\/open-sql-native-sql-in-sap-abap\/","name":"Open SQL &amp; Native SQL in SAP ABAP - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/open-sql-native-sql-in-sap-abap\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/open-sql-native-sql-in-sap-abap\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/03\/Open-SQL-Native-SQL-in-SAP-ABAP.jpg","datePublished":"2021-03-03T09:06:02+00:00","description":"Learn what is OpenSQL and Native SQL in SAP ABAP with examples. Learn the difference between OpenSQL vs Native SQL in ABAP.","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/open-sql-native-sql-in-sap-abap\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/open-sql-native-sql-in-sap-abap\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/open-sql-native-sql-in-sap-abap\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/03\/Open-SQL-Native-SQL-in-SAP-ABAP.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/03\/Open-SQL-Native-SQL-in-SAP-ABAP.jpg","width":1200,"height":628,"caption":"Open SQL & Native SQL in SAP ABAP"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/open-sql-native-sql-in-sap-abap\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"SAP ABAP Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/sap-abap\/"},{"@type":"ListItem","position":3,"name":"Open SQL &amp; Native SQL in SAP ABAP"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/b49855299264df5e27e3ec6c2cd9fde9","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/ef46b745ddad2fad690af626c6ef29b91809ad0a9f5ef398d07817d8cad042f5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/ef46b745ddad2fad690af626c6ef29b91809ad0a9f5ef398d07817d8cad042f5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ef46b745ddad2fad690af626c6ef29b91809ad0a9f5ef398d07817d8cad042f5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"DataFlair Team is a group of passionate educators and industry experts dedicated to providing high-quality online learning resources on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. With years of experience in the field, the team aims to simplify complex topics and help learners advance their careers. At DataFlair, we believe in empowering students and professionals with the knowledge and skills needed to thrive in today\u2019s fast-paced tech industry. Follow us for Free courses, expert insights, tutorials, and practical tips to boost your learning journey.","url":"https:\/\/data-flair.training\/blogs\/author\/datafbdad\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/85814","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=85814"}],"version-history":[{"count":5,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/85814\/revisions"}],"predecessor-version":[{"id":86440,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/85814\/revisions\/86440"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/86439"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=85814"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=85814"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=85814"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}