examples/flight_demo_r.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "9dd1ba48-afd3-4fc6-9586-8806c028aed8",
   "metadata": {},
   "source": [
    "# Query SAP table and join with local data\n",
    "\n",
    "A first example demonstrates how to join two SAP tables with an external table. We’ll be using the [ABAP Flight Reference Scenario](https://help.sap.com/docs/ABAP_PLATFORM_NEW/fc4c71aa50014fd1b43721701471913d/a9d7c7c140a0408dbc5966c52d156b49.html), specifically joining the `SFLIGHT` and `SPFLI` tables which contain flight and flight schedule details respectively, with an external table `WEATHER` that holds weather information. We will extract flight information and associated temperatures at departure and arrival cities."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "72ef9aaf-4171-4cd1-b643-35ebd9a0b4e8",
   "metadata": {},
   "source": [
    "## Import DuckDB & load **ERPL** extension\n",
    "In the next cells we import duckdb. Then we install the ERPL extension and load it into the current DB-session. Via multiple SET-commands we configure the connection to our SAP development system. In our case we use the docker based [ABAP Platform Trial](https://hub.docker.com/r/sapse/abap-platform-trial). The credentials are set by default, details can be found in the documentation of the docker image. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c08c7d27-00f3-45e9-9c83-f0ea0882c71b",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "library(\"DBI\")\n",
    "con = dbConnect(duckdb::duckdb(dbdir = \":memory:\", config = list(allow_unsigned_extensions=\"true\")))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "6695d1fb-9af8-481f-8826-6bf750b0b740",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "'\\nINSTALL \\'./erpl.duckdb_extension\\';\\nLOAD \\'erpl\\';\\n\\nSET sap_ashost = \\'localhost\\';\\nSET sap_sysnr = \\'00\\';\\nSET sap_user = \\'DEVELOPER\\';\\nSET sap_password = \\'Htods70334\\';\\nSET sap_client = \\'001\\';\\nSET sap_lang = \\'EN\\';\\n'"
      ],
      "text/latex": [
       "'\\textbackslash{}nINSTALL \\textbackslash{}'./erpl.duckdb\\_extension\\textbackslash{}';\\textbackslash{}nLOAD \\textbackslash{}'erpl\\textbackslash{}';\\textbackslash{}n\\textbackslash{}nSET sap\\_ashost = \\textbackslash{}'localhost\\textbackslash{}';\\textbackslash{}nSET sap\\_sysnr = \\textbackslash{}'00\\textbackslash{}';\\textbackslash{}nSET sap\\_user = \\textbackslash{}'DEVELOPER\\textbackslash{}';\\textbackslash{}nSET sap\\_password = \\textbackslash{}'Htods70334\\textbackslash{}';\\textbackslash{}nSET sap\\_client = \\textbackslash{}'001\\textbackslash{}';\\textbackslash{}nSET sap\\_lang = \\textbackslash{}'EN\\textbackslash{}';\\textbackslash{}n'"
      ],
      "text/markdown": [
       "'\\nINSTALL \\'./erpl.duckdb_extension\\';\\nLOAD \\'erpl\\';\\n\\nSET sap_ashost = \\'localhost\\';\\nSET sap_sysnr = \\'00\\';\\nSET sap_user = \\'DEVELOPER\\';\\nSET sap_password = \\'Htods70334\\';\\nSET sap_client = \\'001\\';\\nSET sap_lang = \\'EN\\';\\n'"
      ],
      "text/plain": [
       "[1] \"\\nINSTALL './erpl.duckdb_extension';\\nLOAD 'erpl';\\n\\nSET sap_ashost = 'localhost';\\nSET sap_sysnr = '00';\\nSET sap_user = 'DEVELOPER';\\nSET sap_password = 'Htods70334';\\nSET sap_client = '001';\\nSET sap_lang = 'EN';\\n\""
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "ename": "ERROR",
     "evalue": "Error: rapi_prepare: Failed to execute statement \nINSTALL './erpl.duckdb_extension';\nLOAD 'erpl';\n\nSET sap_ashost = 'localhost';\nSET sap_sysnr = '00';\nSET sap_user = 'DEVELOPER';\nSET sap_password = 'Htods70334';\nSET sap_client = '001';\nSET sap_lang = 'EN';\n\nError: Invalid Input Error: Initialization function \"erpl_init\" from file \"/home/jr/.local/share/R/duckdb/.duckdb/extensions/v0.9.2/linux_amd64/erpl.duckdb_extension\" threw an exception: \"Failed to load ERPL extension: IO Error: Extension \"/home/jr/.local/share/R/duckdb/.duckdb/extensions/v0.9.2/linux_amd64/erpl_impl.duckdb_extension\" could not be loaded: libsapnwrfc.so: cannot open shared object file: No such file or directory\"\n",
     "output_type": "error",
     "traceback": [
      "Error: rapi_prepare: Failed to execute statement \nINSTALL './erpl.duckdb_extension';\nLOAD 'erpl';\n\nSET sap_ashost = 'localhost';\nSET sap_sysnr = '00';\nSET sap_user = 'DEVELOPER';\nSET sap_password = 'Htods70334';\nSET sap_client = '001';\nSET sap_lang = 'EN';\n\nError: Invalid Input Error: Initialization function \"erpl_init\" from file \"/home/jr/.local/share/R/duckdb/.duckdb/extensions/v0.9.2/linux_amd64/erpl.duckdb_extension\" threw an exception: \"Failed to load ERPL extension: IO Error: Extension \"/home/jr/.local/share/R/duckdb/.duckdb/extensions/v0.9.2/linux_amd64/erpl_impl.duckdb_extension\" could not be loaded: libsapnwrfc.so: cannot open shared object file: No such file or directory\"\nTraceback:\n",
      "1. dbExecute(con, sql)",
      "2. dbExecute(con, sql)",
      "3. dbSendStatement(conn, statement, ...)",
      "4. dbSendStatement(conn, statement, ...)",
      "5. dbSendQuery(conn, statement, ...)",
      "6. dbSendQuery(conn, statement, ...)",
      "7. .local(conn, statement, ...)",
      "8. rapi_prepare(conn@conn_ref, statement)"
     ]
    }
   ],
   "source": [
    "sql = \"\n",
    "INSTALL './erpl.duckdb_extension';\n",
    "LOAD 'erpl';\n",
    "\n",
    "SET sap_ashost = 'localhost';\n",
    "SET sap_sysnr = '00';\n",
    "SET sap_user = 'DEVELOPER';\n",
    "SET sap_password = 'ABAPtr1909';\n",
    "SET sap_client = '001';\n",
    "SET sap_lang = 'EN';\n",
    "\"\n",
    "sql\n",
    "\n",
    "dbExecute(con, sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70ce4b81-a2ac-464a-95eb-305d337e2133",
   "metadata": {},
   "source": [
    "If the loading of the extension was successful, we can find the exportet functions in the list of `duckdb_functions()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "7a35ffdb-163f-463b-9312-024b32eff791",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A data.frame: 14 × 14</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>database_name</th><th scope=col>schema_name</th><th scope=col>function_name</th><th scope=col>function_type</th><th scope=col>description</th><th scope=col>return_type</th><th scope=col>parameters</th><th scope=col>parameter_types</th><th scope=col>varargs</th><th scope=col>macro_definition</th><th scope=col>has_side_effects</th><th scope=col>internal</th><th scope=col>function_oid</th><th scope=col>example</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;list&gt;</th><th scope=col>&lt;list&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;lgl&gt;</th><th scope=col>&lt;lgl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>system</td><td>main</td><td>sap_rfc_search_group                  </td><td>table </td><td>NA</td><td>NA</td><td>LANGUAGE , GROUPNAME</td><td>VARCHAR, VARCHAR</td><td>NA </td><td>NA</td><td>NA</td><td>TRUE</td><td>1021</td><td>NA</td></tr>\n",
       "\t<tr><td>system</td><td>main</td><td>sap_rfc_invoke                        </td><td>table </td><td>NA</td><td>NA</td><td>col0, path</td><td>VARCHAR, VARCHAR</td><td>ANY</td><td>NA</td><td>NA</td><td>TRUE</td><td>1019</td><td>NA</td></tr>\n",
       "\t<tr><td>system</td><td>main</td><td>sap_read_table                        </td><td>table </td><td>NA</td><td>NA</td><td>col0    , MAX_ROWS, FILTER  , COLUMNS , THREADS </td><td>VARCHAR  , UINTEGER , VARCHAR  , VARCHAR[], UINTEGER </td><td>NA </td><td>NA</td><td>NA</td><td>TRUE</td><td>1029</td><td>NA</td></tr>\n",
       "\t<tr><td>system</td><td>main</td><td>sap_describe_fields                   </td><td>table </td><td>NA</td><td>NA</td><td>col0</td><td>VARCHAR</td><td>NA </td><td>NA</td><td>NA</td><td>TRUE</td><td>1027</td><td>NA</td></tr>\n",
       "\t<tr><td>system</td><td>main</td><td>sap_show_tables                       </td><td>table </td><td>NA</td><td>NA</td><td>THREADS  , TEXT     , TABLENAME</td><td>UINTEGER, VARCHAR , VARCHAR </td><td>NA </td><td>NA</td><td>NA</td><td>TRUE</td><td>1025</td><td>NA</td></tr>\n",
       "\t<tr><td>system</td><td>main</td><td>sap_rfc_search_function               </td><td>table </td><td>NA</td><td>NA</td><td>LANGUAGE , GROUPNAME, FUNCNAME </td><td>VARCHAR, VARCHAR, VARCHAR</td><td>NA </td><td>NA</td><td>NA</td><td>TRUE</td><td>1023</td><td>NA</td></tr>\n",
       "\t<tr><td>system</td><td>main</td><td>sap_rfc_ping                          </td><td>pragma</td><td>NA</td><td>NA</td><td></td><td></td><td>NA </td><td>NA</td><td>NA</td><td>TRUE</td><td>1017</td><td>NA</td></tr>\n",
       "\t<tr><td>system</td><td>main</td><td>sap_rfc_function_desc                 </td><td>pragma</td><td>NA</td><td>NA</td><td>col0</td><td>VARCHAR</td><td>NA </td><td>NA</td><td>NA</td><td>TRUE</td><td>1031</td><td>NA</td></tr>\n",
       "\t<tr><td>system</td><td>main</td><td>sap_rfc_set_trace_level               </td><td>pragma</td><td>NA</td><td>NA</td><td>col0</td><td>INTEGER</td><td>NA </td><td>NA</td><td>NA</td><td>TRUE</td><td>1033</td><td>NA</td></tr>\n",
       "\t<tr><td>system</td><td>main</td><td>sap_rfc_set_trace_dir                 </td><td>pragma</td><td>NA</td><td>NA</td><td>col0</td><td>VARCHAR</td><td>NA </td><td>NA</td><td>NA</td><td>TRUE</td><td>1035</td><td>NA</td></tr>\n",
       "\t<tr><td>system</td><td>main</td><td>sap_rfc_set_maximum_trace_file_size   </td><td>pragma</td><td>NA</td><td>NA</td><td>col0, col1</td><td>INTEGER       , ENUM('M', 'G')</td><td>NA </td><td>NA</td><td>NA</td><td>TRUE</td><td>1037</td><td>NA</td></tr>\n",
       "\t<tr><td>system</td><td>main</td><td>sap_rfc_set_maximum_stored_trace_files</td><td>pragma</td><td>NA</td><td>NA</td><td>col0</td><td>INTEGER</td><td>NA </td><td>NA</td><td>NA</td><td>TRUE</td><td>1039</td><td>NA</td></tr>\n",
       "\t<tr><td>system</td><td>main</td><td>sap_rfc_set_ini_path                  </td><td>pragma</td><td>NA</td><td>NA</td><td>col0</td><td>VARCHAR</td><td>NA </td><td>NA</td><td>NA</td><td>TRUE</td><td>1041</td><td>NA</td></tr>\n",
       "\t<tr><td>system</td><td>main</td><td>sap_rfc_reload_ini_file               </td><td>pragma</td><td>NA</td><td>NA</td><td></td><td></td><td>NA </td><td>NA</td><td>NA</td><td>TRUE</td><td>1043</td><td>NA</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 14 × 14\n",
       "\\begin{tabular}{llllllllllllll}\n",
       " database\\_name & schema\\_name & function\\_name & function\\_type & description & return\\_type & parameters & parameter\\_types & varargs & macro\\_definition & has\\_side\\_effects & internal & function\\_oid & example\\\\\n",
       " <chr> & <chr> & <chr> & <chr> & <chr> & <chr> & <list> & <list> & <chr> & <chr> & <lgl> & <lgl> & <dbl> & <chr>\\\\\n",
       "\\hline\n",
       "\t system & main & sap\\_rfc\\_search\\_group                   & table  & NA & NA & LANGUAGE , GROUPNAME & VARCHAR, VARCHAR & NA  & NA & NA & TRUE & 1021 & NA\\\\\n",
       "\t system & main & sap\\_rfc\\_invoke                         & table  & NA & NA & col0, path & VARCHAR, VARCHAR & ANY & NA & NA & TRUE & 1019 & NA\\\\\n",
       "\t system & main & sap\\_read\\_table                         & table  & NA & NA & col0    , MAX\\_ROWS, FILTER  , COLUMNS , THREADS  & VARCHAR  , UINTEGER , VARCHAR  , VARCHAR{[}{]}, UINTEGER  & NA  & NA & NA & TRUE & 1029 & NA\\\\\n",
       "\t system & main & sap\\_describe\\_fields                    & table  & NA & NA & col0 & VARCHAR & NA  & NA & NA & TRUE & 1027 & NA\\\\\n",
       "\t system & main & sap\\_show\\_tables                        & table  & NA & NA & THREADS  , TEXT     , TABLENAME & UINTEGER, VARCHAR , VARCHAR  & NA  & NA & NA & TRUE & 1025 & NA\\\\\n",
       "\t system & main & sap\\_rfc\\_search\\_function                & table  & NA & NA & LANGUAGE , GROUPNAME, FUNCNAME  & VARCHAR, VARCHAR, VARCHAR & NA  & NA & NA & TRUE & 1023 & NA\\\\\n",
       "\t system & main & sap\\_rfc\\_ping                           & pragma & NA & NA &  &  & NA  & NA & NA & TRUE & 1017 & NA\\\\\n",
       "\t system & main & sap\\_rfc\\_function\\_desc                  & pragma & NA & NA & col0 & VARCHAR & NA  & NA & NA & TRUE & 1031 & NA\\\\\n",
       "\t system & main & sap\\_rfc\\_set\\_trace\\_level                & pragma & NA & NA & col0 & INTEGER & NA  & NA & NA & TRUE & 1033 & NA\\\\\n",
       "\t system & main & sap\\_rfc\\_set\\_trace\\_dir                  & pragma & NA & NA & col0 & VARCHAR & NA  & NA & NA & TRUE & 1035 & NA\\\\\n",
       "\t system & main & sap\\_rfc\\_set\\_maximum\\_trace\\_file\\_size    & pragma & NA & NA & col0, col1 & INTEGER       , ENUM('M', 'G') & NA  & NA & NA & TRUE & 1037 & NA\\\\\n",
       "\t system & main & sap\\_rfc\\_set\\_maximum\\_stored\\_trace\\_files & pragma & NA & NA & col0 & INTEGER & NA  & NA & NA & TRUE & 1039 & NA\\\\\n",
       "\t system & main & sap\\_rfc\\_set\\_ini\\_path                   & pragma & NA & NA & col0 & VARCHAR & NA  & NA & NA & TRUE & 1041 & NA\\\\\n",
       "\t system & main & sap\\_rfc\\_reload\\_ini\\_file                & pragma & NA & NA &  &  & NA  & NA & NA & TRUE & 1043 & NA\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 14 × 14\n",
       "\n",
       "| database_name &lt;chr&gt; | schema_name &lt;chr&gt; | function_name &lt;chr&gt; | function_type &lt;chr&gt; | description &lt;chr&gt; | return_type &lt;chr&gt; | parameters &lt;list&gt; | parameter_types &lt;list&gt; | varargs &lt;chr&gt; | macro_definition &lt;chr&gt; | has_side_effects &lt;lgl&gt; | internal &lt;lgl&gt; | function_oid &lt;dbl&gt; | example &lt;chr&gt; |\n",
       "|---|---|---|---|---|---|---|---|---|---|---|---|---|---|\n",
       "| system | main | sap_rfc_search_group                   | table  | NA | NA | LANGUAGE , GROUPNAME | VARCHAR, VARCHAR | NA  | NA | NA | TRUE | 1021 | NA |\n",
       "| system | main | sap_rfc_invoke                         | table  | NA | NA | col0, path | VARCHAR, VARCHAR | ANY | NA | NA | TRUE | 1019 | NA |\n",
       "| system | main | sap_read_table                         | table  | NA | NA | col0    , MAX_ROWS, FILTER  , COLUMNS , THREADS  | VARCHAR  , UINTEGER , VARCHAR  , VARCHAR[], UINTEGER  | NA  | NA | NA | TRUE | 1029 | NA |\n",
       "| system | main | sap_describe_fields                    | table  | NA | NA | col0 | VARCHAR | NA  | NA | NA | TRUE | 1027 | NA |\n",
       "| system | main | sap_show_tables                        | table  | NA | NA | THREADS  , TEXT     , TABLENAME | UINTEGER, VARCHAR , VARCHAR  | NA  | NA | NA | TRUE | 1025 | NA |\n",
       "| system | main | sap_rfc_search_function                | table  | NA | NA | LANGUAGE , GROUPNAME, FUNCNAME  | VARCHAR, VARCHAR, VARCHAR | NA  | NA | NA | TRUE | 1023 | NA |\n",
       "| system | main | sap_rfc_ping                           | pragma | NA | NA | <!----> | <!----> | NA  | NA | NA | TRUE | 1017 | NA |\n",
       "| system | main | sap_rfc_function_desc                  | pragma | NA | NA | col0 | VARCHAR | NA  | NA | NA | TRUE | 1031 | NA |\n",
       "| system | main | sap_rfc_set_trace_level                | pragma | NA | NA | col0 | INTEGER | NA  | NA | NA | TRUE | 1033 | NA |\n",
       "| system | main | sap_rfc_set_trace_dir                  | pragma | NA | NA | col0 | VARCHAR | NA  | NA | NA | TRUE | 1035 | NA |\n",
       "| system | main | sap_rfc_set_maximum_trace_file_size    | pragma | NA | NA | col0, col1 | INTEGER       , ENUM('M', 'G') | NA  | NA | NA | TRUE | 1037 | NA |\n",
       "| system | main | sap_rfc_set_maximum_stored_trace_files | pragma | NA | NA | col0 | INTEGER | NA  | NA | NA | TRUE | 1039 | NA |\n",
       "| system | main | sap_rfc_set_ini_path                   | pragma | NA | NA | col0 | VARCHAR | NA  | NA | NA | TRUE | 1041 | NA |\n",
       "| system | main | sap_rfc_reload_ini_file                | pragma | NA | NA | <!----> | <!----> | NA  | NA | NA | TRUE | 1043 | NA |\n",
       "\n"
      ],
      "text/plain": [
       "   database_name schema_name function_name                         \n",
       "1  system        main        sap_rfc_search_group                  \n",
       "2  system        main        sap_rfc_invoke                        \n",
       "3  system        main        sap_read_table                        \n",
       "4  system        main        sap_describe_fields                   \n",
       "5  system        main        sap_show_tables                       \n",
       "6  system        main        sap_rfc_search_function               \n",
       "7  system        main        sap_rfc_ping                          \n",
       "8  system        main        sap_rfc_function_desc                 \n",
       "9  system        main        sap_rfc_set_trace_level               \n",
       "10 system        main        sap_rfc_set_trace_dir                 \n",
       "11 system        main        sap_rfc_set_maximum_trace_file_size   \n",
       "12 system        main        sap_rfc_set_maximum_stored_trace_files\n",
       "13 system        main        sap_rfc_set_ini_path                  \n",
       "14 system        main        sap_rfc_reload_ini_file               \n",
       "   function_type description return_type\n",
       "1  table         NA          NA         \n",
       "2  table         NA          NA         \n",
       "3  table         NA          NA         \n",
       "4  table         NA          NA         \n",
       "5  table         NA          NA         \n",
       "6  table         NA          NA         \n",
       "7  pragma        NA          NA         \n",
       "8  pragma        NA          NA         \n",
       "9  pragma        NA          NA         \n",
       "10 pragma        NA          NA         \n",
       "11 pragma        NA          NA         \n",
       "12 pragma        NA          NA         \n",
       "13 pragma        NA          NA         \n",
       "14 pragma        NA          NA         \n",
       "   parameters                                      \n",
       "1  LANGUAGE , GROUPNAME                            \n",
       "2  col0, path                                      \n",
       "3  col0    , MAX_ROWS, FILTER  , COLUMNS , THREADS \n",
       "4  col0                                            \n",
       "5  THREADS  , TEXT     , TABLENAME                 \n",
       "6  LANGUAGE , GROUPNAME, FUNCNAME                  \n",
       "7                                                  \n",
       "8  col0                                            \n",
       "9  col0                                            \n",
       "10 col0                                            \n",
       "11 col0, col1                                      \n",
       "12 col0                                            \n",
       "13 col0                                            \n",
       "14                                                 \n",
       "   parameter_types                                       varargs\n",
       "1  VARCHAR, VARCHAR                                      NA     \n",
       "2  VARCHAR, VARCHAR                                      ANY    \n",
       "3  VARCHAR  , UINTEGER , VARCHAR  , VARCHAR[], UINTEGER  NA     \n",
       "4  VARCHAR                                               NA     \n",
       "5  UINTEGER, VARCHAR , VARCHAR                           NA     \n",
       "6  VARCHAR, VARCHAR, VARCHAR                             NA     \n",
       "7                                                        NA     \n",
       "8  VARCHAR                                               NA     \n",
       "9  INTEGER                                               NA     \n",
       "10 VARCHAR                                               NA     \n",
       "11 INTEGER       , ENUM('M', 'G')                        NA     \n",
       "12 INTEGER                                               NA     \n",
       "13 VARCHAR                                               NA     \n",
       "14                                                       NA     \n",
       "   macro_definition has_side_effects internal function_oid example\n",
       "1  NA               NA               TRUE     1021         NA     \n",
       "2  NA               NA               TRUE     1019         NA     \n",
       "3  NA               NA               TRUE     1029         NA     \n",
       "4  NA               NA               TRUE     1027         NA     \n",
       "5  NA               NA               TRUE     1025         NA     \n",
       "6  NA               NA               TRUE     1023         NA     \n",
       "7  NA               NA               TRUE     1017         NA     \n",
       "8  NA               NA               TRUE     1031         NA     \n",
       "9  NA               NA               TRUE     1033         NA     \n",
       "10 NA               NA               TRUE     1035         NA     \n",
       "11 NA               NA               TRUE     1037         NA     \n",
       "12 NA               NA               TRUE     1039         NA     \n",
       "13 NA               NA               TRUE     1041         NA     \n",
       "14 NA               NA               TRUE     1043         NA     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "dbGetQuery(con, \"SELECT * FROM duckdb_functions() WHERE function_name LIKE '%sap%';\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cab12d7d-45aa-40fe-b020-3dd8d3f19ee4",
   "metadata": {},
   "source": [
    "## Explore the schema of the relevant tables\n",
    "\n",
    "The ERPL extension provides the method `sap_describe_fields` to explore the data dictionary schema of the respective table. For exploring local data we also can use the `DESCRIBE` command to get the fields of e.g. a CSV-file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4f2b43b1-449e-466a-9bdb-61434694ec93",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A data.frame: 14 × 11</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>pos</th><th scope=col>is_key</th><th scope=col>field</th><th scope=col>text</th><th scope=col>sap_type</th><th scope=col>length</th><th scope=col>decimals</th><th scope=col>check_table</th><th scope=col>ref_table</th><th scope=col>ref_field</th><th scope=col>language</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>0001</td><td>X</td><td>MANDT     </td><td>Client                            </td><td>CLNT</td><td>000003</td><td>000000</td><td>T000   </td><td>       </td><td>        </td><td>E</td></tr>\n",
       "\t<tr><td>0002</td><td>X</td><td>CARRID    </td><td>Airline Code                      </td><td>CHAR</td><td>000003</td><td>000000</td><td>SCARR  </td><td>       </td><td>        </td><td>E</td></tr>\n",
       "\t<tr><td>0003</td><td>X</td><td>CONNID    </td><td>Flight Connection Number          </td><td>NUMC</td><td>000004</td><td>000000</td><td>SPFLI  </td><td>       </td><td>        </td><td>E</td></tr>\n",
       "\t<tr><td>0004</td><td>X</td><td>FLDATE    </td><td>Flight date                       </td><td>DATS</td><td>000008</td><td>000000</td><td>       </td><td>       </td><td>        </td><td>E</td></tr>\n",
       "\t<tr><td>0005</td><td> </td><td>PRICE     </td><td>Airfare                           </td><td>CURR</td><td>000015</td><td>000002</td><td>       </td><td>SFLIGHT</td><td>CURRENCY</td><td>E</td></tr>\n",
       "\t<tr><td>0006</td><td> </td><td>CURRENCY  </td><td>Local currency of airline         </td><td>CUKY</td><td>000005</td><td>000000</td><td>SCURX  </td><td>       </td><td>        </td><td>E</td></tr>\n",
       "\t<tr><td>0007</td><td> </td><td>PLANETYPE </td><td>Aircraft Type                     </td><td>CHAR</td><td>000010</td><td>000000</td><td>SAPLANE</td><td>       </td><td>        </td><td>E</td></tr>\n",
       "\t<tr><td>0008</td><td> </td><td>SEATSMAX  </td><td>Maximum Capacity in Economy Class </td><td>INT4</td><td>000010</td><td>000000</td><td>       </td><td>       </td><td>        </td><td>E</td></tr>\n",
       "\t<tr><td>0009</td><td> </td><td>SEATSOCC  </td><td>Occupied Seats in Economy Class   </td><td>INT4</td><td>000010</td><td>000000</td><td>       </td><td>       </td><td>        </td><td>E</td></tr>\n",
       "\t<tr><td>0010</td><td> </td><td>PAYMENTSUM</td><td>Total of current bookings         </td><td>CURR</td><td>000017</td><td>000002</td><td>       </td><td>SFLIGHT</td><td>CURRENCY</td><td>E</td></tr>\n",
       "\t<tr><td>0011</td><td> </td><td>SEATSMAX_B</td><td>Maximum Capacity in Business Class</td><td>INT4</td><td>000010</td><td>000000</td><td>       </td><td>       </td><td>        </td><td>E</td></tr>\n",
       "\t<tr><td>0012</td><td> </td><td>SEATSOCC_B</td><td>Occupied Seats in Business Class  </td><td>INT4</td><td>000010</td><td>000000</td><td>       </td><td>       </td><td>        </td><td>E</td></tr>\n",
       "\t<tr><td>0013</td><td> </td><td>SEATSMAX_F</td><td>Maximum Capacity in First Class   </td><td>INT4</td><td>000010</td><td>000000</td><td>       </td><td>       </td><td>        </td><td>E</td></tr>\n",
       "\t<tr><td>0014</td><td> </td><td>SEATSOCC_F</td><td>Occupied Seats in First Class     </td><td>INT4</td><td>000010</td><td>000000</td><td>       </td><td>       </td><td>        </td><td>E</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 14 × 11\n",
       "\\begin{tabular}{lllllllllll}\n",
       " pos & is\\_key & field & text & sap\\_type & length & decimals & check\\_table & ref\\_table & ref\\_field & language\\\\\n",
       " <chr> & <chr> & <chr> & <chr> & <chr> & <chr> & <chr> & <chr> & <chr> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t 0001 & X & MANDT      & Client                             & CLNT & 000003 & 000000 & T000    &         &          & E\\\\\n",
       "\t 0002 & X & CARRID     & Airline Code                       & CHAR & 000003 & 000000 & SCARR   &         &          & E\\\\\n",
       "\t 0003 & X & CONNID     & Flight Connection Number           & NUMC & 000004 & 000000 & SPFLI   &         &          & E\\\\\n",
       "\t 0004 & X & FLDATE     & Flight date                        & DATS & 000008 & 000000 &         &         &          & E\\\\\n",
       "\t 0005 &   & PRICE      & Airfare                            & CURR & 000015 & 000002 &         & SFLIGHT & CURRENCY & E\\\\\n",
       "\t 0006 &   & CURRENCY   & Local currency of airline          & CUKY & 000005 & 000000 & SCURX   &         &          & E\\\\\n",
       "\t 0007 &   & PLANETYPE  & Aircraft Type                      & CHAR & 000010 & 000000 & SAPLANE &         &          & E\\\\\n",
       "\t 0008 &   & SEATSMAX   & Maximum Capacity in Economy Class  & INT4 & 000010 & 000000 &         &         &          & E\\\\\n",
       "\t 0009 &   & SEATSOCC   & Occupied Seats in Economy Class    & INT4 & 000010 & 000000 &         &         &          & E\\\\\n",
       "\t 0010 &   & PAYMENTSUM & Total of current bookings          & CURR & 000017 & 000002 &         & SFLIGHT & CURRENCY & E\\\\\n",
       "\t 0011 &   & SEATSMAX\\_B & Maximum Capacity in Business Class & INT4 & 000010 & 000000 &         &         &          & E\\\\\n",
       "\t 0012 &   & SEATSOCC\\_B & Occupied Seats in Business Class   & INT4 & 000010 & 000000 &         &         &          & E\\\\\n",
       "\t 0013 &   & SEATSMAX\\_F & Maximum Capacity in First Class    & INT4 & 000010 & 000000 &         &         &          & E\\\\\n",
       "\t 0014 &   & SEATSOCC\\_F & Occupied Seats in First Class      & INT4 & 000010 & 000000 &         &         &          & E\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 14 × 11\n",
       "\n",
       "| pos &lt;chr&gt; | is_key &lt;chr&gt; | field &lt;chr&gt; | text &lt;chr&gt; | sap_type &lt;chr&gt; | length &lt;chr&gt; | decimals &lt;chr&gt; | check_table &lt;chr&gt; | ref_table &lt;chr&gt; | ref_field &lt;chr&gt; | language &lt;chr&gt; |\n",
       "|---|---|---|---|---|---|---|---|---|---|---|\n",
       "| 0001 | X | MANDT      | Client                             | CLNT | 000003 | 000000 | T000    | <!----> | <!----> | E |\n",
       "| 0002 | X | CARRID     | Airline Code                       | CHAR | 000003 | 000000 | SCARR   | <!----> | <!----> | E |\n",
       "| 0003 | X | CONNID     | Flight Connection Number           | NUMC | 000004 | 000000 | SPFLI   | <!----> | <!----> | E |\n",
       "| 0004 | X | FLDATE     | Flight date                        | DATS | 000008 | 000000 | <!----> | <!----> | <!----> | E |\n",
       "| 0005 | <!----> | PRICE      | Airfare                            | CURR | 000015 | 000002 | <!----> | SFLIGHT | CURRENCY | E |\n",
       "| 0006 | <!----> | CURRENCY   | Local currency of airline          | CUKY | 000005 | 000000 | SCURX   | <!----> | <!----> | E |\n",
       "| 0007 | <!----> | PLANETYPE  | Aircraft Type                      | CHAR | 000010 | 000000 | SAPLANE | <!----> | <!----> | E |\n",
       "| 0008 | <!----> | SEATSMAX   | Maximum Capacity in Economy Class  | INT4 | 000010 | 000000 | <!----> | <!----> | <!----> | E |\n",
       "| 0009 | <!----> | SEATSOCC   | Occupied Seats in Economy Class    | INT4 | 000010 | 000000 | <!----> | <!----> | <!----> | E |\n",
       "| 0010 | <!----> | PAYMENTSUM | Total of current bookings          | CURR | 000017 | 000002 | <!----> | SFLIGHT | CURRENCY | E |\n",
       "| 0011 | <!----> | SEATSMAX_B | Maximum Capacity in Business Class | INT4 | 000010 | 000000 | <!----> | <!----> | <!----> | E |\n",
       "| 0012 | <!----> | SEATSOCC_B | Occupied Seats in Business Class   | INT4 | 000010 | 000000 | <!----> | <!----> | <!----> | E |\n",
       "| 0013 | <!----> | SEATSMAX_F | Maximum Capacity in First Class    | INT4 | 000010 | 000000 | <!----> | <!----> | <!----> | E |\n",
       "| 0014 | <!----> | SEATSOCC_F | Occupied Seats in First Class      | INT4 | 000010 | 000000 | <!----> | <!----> | <!----> | E |\n",
       "\n"
      ],
      "text/plain": [
       "   pos  is_key field      text                               sap_type length\n",
       "1  0001 X      MANDT      Client                             CLNT     000003\n",
       "2  0002 X      CARRID     Airline Code                       CHAR     000003\n",
       "3  0003 X      CONNID     Flight Connection Number           NUMC     000004\n",
       "4  0004 X      FLDATE     Flight date                        DATS     000008\n",
       "5  0005        PRICE      Airfare                            CURR     000015\n",
       "6  0006        CURRENCY   Local currency of airline          CUKY     000005\n",
       "7  0007        PLANETYPE  Aircraft Type                      CHAR     000010\n",
       "8  0008        SEATSMAX   Maximum Capacity in Economy Class  INT4     000010\n",
       "9  0009        SEATSOCC   Occupied Seats in Economy Class    INT4     000010\n",
       "10 0010        PAYMENTSUM Total of current bookings          CURR     000017\n",
       "11 0011        SEATSMAX_B Maximum Capacity in Business Class INT4     000010\n",
       "12 0012        SEATSOCC_B Occupied Seats in Business Class   INT4     000010\n",
       "13 0013        SEATSMAX_F Maximum Capacity in First Class    INT4     000010\n",
       "14 0014        SEATSOCC_F Occupied Seats in First Class      INT4     000010\n",
       "   decimals check_table ref_table ref_field language\n",
       "1  000000   T000                            E       \n",
       "2  000000   SCARR                           E       \n",
       "3  000000   SPFLI                           E       \n",
       "4  000000                                   E       \n",
       "5  000002               SFLIGHT   CURRENCY  E       \n",
       "6  000000   SCURX                           E       \n",
       "7  000000   SAPLANE                         E       \n",
       "8  000000                                   E       \n",
       "9  000000                                   E       \n",
       "10 000002               SFLIGHT   CURRENCY  E       \n",
       "11 000000                                   E       \n",
       "12 000000                                   E       \n",
       "13 000000                                   E       \n",
       "14 000000                                   E       "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "dbGetQuery(con, \"SELECT * FROM sap_describe_fields('SFLIGHT');\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "949086e0",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A data.frame: 16 × 11</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>pos</th><th scope=col>is_key</th><th scope=col>field</th><th scope=col>text</th><th scope=col>sap_type</th><th scope=col>length</th><th scope=col>decimals</th><th scope=col>check_table</th><th scope=col>ref_table</th><th scope=col>ref_field</th><th scope=col>language</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>0001</td><td>X</td><td>MANDT    </td><td>Client                            </td><td>CLNT</td><td>000003</td><td>000000</td><td>T000    </td><td>     </td><td>      </td><td>E</td></tr>\n",
       "\t<tr><td>0002</td><td>X</td><td>CARRID   </td><td>Airline Code                      </td><td>CHAR</td><td>000003</td><td>000000</td><td>SCARR   </td><td>     </td><td>      </td><td>E</td></tr>\n",
       "\t<tr><td>0003</td><td>X</td><td>CONNID   </td><td>Flight Connection Number          </td><td>NUMC</td><td>000004</td><td>000000</td><td>        </td><td>     </td><td>      </td><td>E</td></tr>\n",
       "\t<tr><td>0004</td><td> </td><td>COUNTRYFR</td><td>Country Key                       </td><td>CHAR</td><td>000003</td><td>000000</td><td>SGEOCITY</td><td>     </td><td>      </td><td>E</td></tr>\n",
       "\t<tr><td>0005</td><td> </td><td>CITYFROM </td><td>Departure city                    </td><td>CHAR</td><td>000020</td><td>000000</td><td>SGEOCITY</td><td>     </td><td>      </td><td>E</td></tr>\n",
       "\t<tr><td>0006</td><td> </td><td>AIRPFROM </td><td>Departure airport                 </td><td>CHAR</td><td>000003</td><td>000000</td><td>SAIRPORT</td><td>     </td><td>      </td><td>E</td></tr>\n",
       "\t<tr><td>0007</td><td> </td><td>COUNTRYTO</td><td>Country Key                       </td><td>CHAR</td><td>000003</td><td>000000</td><td>SGEOCITY</td><td>     </td><td>      </td><td>E</td></tr>\n",
       "\t<tr><td>0008</td><td> </td><td>CITYTO   </td><td>Arrival city                      </td><td>CHAR</td><td>000020</td><td>000000</td><td>SGEOCITY</td><td>     </td><td>      </td><td>E</td></tr>\n",
       "\t<tr><td>0009</td><td> </td><td>AIRPTO   </td><td>Destination airport               </td><td>CHAR</td><td>000003</td><td>000000</td><td>SAIRPORT</td><td>     </td><td>      </td><td>E</td></tr>\n",
       "\t<tr><td>0010</td><td> </td><td>FLTIME   </td><td>Flight time                       </td><td>INT4</td><td>000010</td><td>000000</td><td>        </td><td>     </td><td>      </td><td>E</td></tr>\n",
       "\t<tr><td>0011</td><td> </td><td>DEPTIME  </td><td>Departure time                    </td><td>TIMS</td><td>000006</td><td>000000</td><td>        </td><td>     </td><td>      </td><td>E</td></tr>\n",
       "\t<tr><td>0012</td><td> </td><td>ARRTIME  </td><td>Arrival time                      </td><td>TIMS</td><td>000006</td><td>000000</td><td>        </td><td>     </td><td>      </td><td>E</td></tr>\n",
       "\t<tr><td>0013</td><td> </td><td>DISTANCE </td><td>Distance                          </td><td>QUAN</td><td>000009</td><td>000004</td><td>        </td><td>SPFLI</td><td>DISTID</td><td>E</td></tr>\n",
       "\t<tr><td>0014</td><td> </td><td>DISTID   </td><td>Mass unit of distance (kms, miles)</td><td>UNIT</td><td>000003</td><td>000000</td><td>        </td><td>     </td><td>      </td><td>E</td></tr>\n",
       "\t<tr><td>0015</td><td> </td><td>FLTYPE   </td><td>Flight type                       </td><td>CHAR</td><td>000001</td><td>000000</td><td>        </td><td>     </td><td>      </td><td>E</td></tr>\n",
       "\t<tr><td>0016</td><td> </td><td>PERIOD   </td><td>Arrival n day(s) later            </td><td>INT1</td><td>000003</td><td>000000</td><td>        </td><td>     </td><td>      </td><td>E</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 16 × 11\n",
       "\\begin{tabular}{lllllllllll}\n",
       " pos & is\\_key & field & text & sap\\_type & length & decimals & check\\_table & ref\\_table & ref\\_field & language\\\\\n",
       " <chr> & <chr> & <chr> & <chr> & <chr> & <chr> & <chr> & <chr> & <chr> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t 0001 & X & MANDT     & Client                             & CLNT & 000003 & 000000 & T000     &       &        & E\\\\\n",
       "\t 0002 & X & CARRID    & Airline Code                       & CHAR & 000003 & 000000 & SCARR    &       &        & E\\\\\n",
       "\t 0003 & X & CONNID    & Flight Connection Number           & NUMC & 000004 & 000000 &          &       &        & E\\\\\n",
       "\t 0004 &   & COUNTRYFR & Country Key                        & CHAR & 000003 & 000000 & SGEOCITY &       &        & E\\\\\n",
       "\t 0005 &   & CITYFROM  & Departure city                     & CHAR & 000020 & 000000 & SGEOCITY &       &        & E\\\\\n",
       "\t 0006 &   & AIRPFROM  & Departure airport                  & CHAR & 000003 & 000000 & SAIRPORT &       &        & E\\\\\n",
       "\t 0007 &   & COUNTRYTO & Country Key                        & CHAR & 000003 & 000000 & SGEOCITY &       &        & E\\\\\n",
       "\t 0008 &   & CITYTO    & Arrival city                       & CHAR & 000020 & 000000 & SGEOCITY &       &        & E\\\\\n",
       "\t 0009 &   & AIRPTO    & Destination airport                & CHAR & 000003 & 000000 & SAIRPORT &       &        & E\\\\\n",
       "\t 0010 &   & FLTIME    & Flight time                        & INT4 & 000010 & 000000 &          &       &        & E\\\\\n",
       "\t 0011 &   & DEPTIME   & Departure time                     & TIMS & 000006 & 000000 &          &       &        & E\\\\\n",
       "\t 0012 &   & ARRTIME   & Arrival time                       & TIMS & 000006 & 000000 &          &       &        & E\\\\\n",
       "\t 0013 &   & DISTANCE  & Distance                           & QUAN & 000009 & 000004 &          & SPFLI & DISTID & E\\\\\n",
       "\t 0014 &   & DISTID    & Mass unit of distance (kms, miles) & UNIT & 000003 & 000000 &          &       &        & E\\\\\n",
       "\t 0015 &   & FLTYPE    & Flight type                        & CHAR & 000001 & 000000 &          &       &        & E\\\\\n",
       "\t 0016 &   & PERIOD    & Arrival n day(s) later             & INT1 & 000003 & 000000 &          &       &        & E\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 16 × 11\n",
       "\n",
       "| pos &lt;chr&gt; | is_key &lt;chr&gt; | field &lt;chr&gt; | text &lt;chr&gt; | sap_type &lt;chr&gt; | length &lt;chr&gt; | decimals &lt;chr&gt; | check_table &lt;chr&gt; | ref_table &lt;chr&gt; | ref_field &lt;chr&gt; | language &lt;chr&gt; |\n",
       "|---|---|---|---|---|---|---|---|---|---|---|\n",
       "| 0001 | X | MANDT     | Client                             | CLNT | 000003 | 000000 | T000     | <!----> | <!----> | E |\n",
       "| 0002 | X | CARRID    | Airline Code                       | CHAR | 000003 | 000000 | SCARR    | <!----> | <!----> | E |\n",
       "| 0003 | X | CONNID    | Flight Connection Number           | NUMC | 000004 | 000000 | <!----> | <!----> | <!----> | E |\n",
       "| 0004 | <!----> | COUNTRYFR | Country Key                        | CHAR | 000003 | 000000 | SGEOCITY | <!----> | <!----> | E |\n",
       "| 0005 | <!----> | CITYFROM  | Departure city                     | CHAR | 000020 | 000000 | SGEOCITY | <!----> | <!----> | E |\n",
       "| 0006 | <!----> | AIRPFROM  | Departure airport                  | CHAR | 000003 | 000000 | SAIRPORT | <!----> | <!----> | E |\n",
       "| 0007 | <!----> | COUNTRYTO | Country Key                        | CHAR | 000003 | 000000 | SGEOCITY | <!----> | <!----> | E |\n",
       "| 0008 | <!----> | CITYTO    | Arrival city                       | CHAR | 000020 | 000000 | SGEOCITY | <!----> | <!----> | E |\n",
       "| 0009 | <!----> | AIRPTO    | Destination airport                | CHAR | 000003 | 000000 | SAIRPORT | <!----> | <!----> | E |\n",
       "| 0010 | <!----> | FLTIME    | Flight time                        | INT4 | 000010 | 000000 | <!----> | <!----> | <!----> | E |\n",
       "| 0011 | <!----> | DEPTIME   | Departure time                     | TIMS | 000006 | 000000 | <!----> | <!----> | <!----> | E |\n",
       "| 0012 | <!----> | ARRTIME   | Arrival time                       | TIMS | 000006 | 000000 | <!----> | <!----> | <!----> | E |\n",
       "| 0013 | <!----> | DISTANCE  | Distance                           | QUAN | 000009 | 000004 | <!----> | SPFLI | DISTID | E |\n",
       "| 0014 | <!----> | DISTID    | Mass unit of distance (kms, miles) | UNIT | 000003 | 000000 | <!----> | <!----> | <!----> | E |\n",
       "| 0015 | <!----> | FLTYPE    | Flight type                        | CHAR | 000001 | 000000 | <!----> | <!----> | <!----> | E |\n",
       "| 0016 | <!----> | PERIOD    | Arrival n day(s) later             | INT1 | 000003 | 000000 | <!----> | <!----> | <!----> | E |\n",
       "\n"
      ],
      "text/plain": [
       "   pos  is_key field     text                               sap_type length\n",
       "1  0001 X      MANDT     Client                             CLNT     000003\n",
       "2  0002 X      CARRID    Airline Code                       CHAR     000003\n",
       "3  0003 X      CONNID    Flight Connection Number           NUMC     000004\n",
       "4  0004        COUNTRYFR Country Key                        CHAR     000003\n",
       "5  0005        CITYFROM  Departure city                     CHAR     000020\n",
       "6  0006        AIRPFROM  Departure airport                  CHAR     000003\n",
       "7  0007        COUNTRYTO Country Key                        CHAR     000003\n",
       "8  0008        CITYTO    Arrival city                       CHAR     000020\n",
       "9  0009        AIRPTO    Destination airport                CHAR     000003\n",
       "10 0010        FLTIME    Flight time                        INT4     000010\n",
       "11 0011        DEPTIME   Departure time                     TIMS     000006\n",
       "12 0012        ARRTIME   Arrival time                       TIMS     000006\n",
       "13 0013        DISTANCE  Distance                           QUAN     000009\n",
       "14 0014        DISTID    Mass unit of distance (kms, miles) UNIT     000003\n",
       "15 0015        FLTYPE    Flight type                        CHAR     000001\n",
       "16 0016        PERIOD    Arrival n day(s) later             INT1     000003\n",
       "   decimals check_table ref_table ref_field language\n",
       "1  000000   T000                            E       \n",
       "2  000000   SCARR                           E       \n",
       "3  000000                                   E       \n",
       "4  000000   SGEOCITY                        E       \n",
       "5  000000   SGEOCITY                        E       \n",
       "6  000000   SAIRPORT                        E       \n",
       "7  000000   SGEOCITY                        E       \n",
       "8  000000   SGEOCITY                        E       \n",
       "9  000000   SAIRPORT                        E       \n",
       "10 000000                                   E       \n",
       "11 000000                                   E       \n",
       "12 000000                                   E       \n",
       "13 000004               SPFLI     DISTID    E       \n",
       "14 000000                                   E       \n",
       "15 000000                                   E       \n",
       "16 000000                                   E       "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "dbGetQuery(con, \"SELECT * FROM sap_describe_fields('SPFLI');\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "99677f46-5650-46c5-98b5-7cf45b3cada6",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Warning message in duckdb_post_execute(res, out):\n",
      "“NAs introduced by coercion”\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A data.frame: 5 × 6</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>column_name</th><th scope=col>column_type</th><th scope=col>null</th><th scope=col>key</th><th scope=col>default</th><th scope=col>extra</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>FLDATE     </td><td>DATE   </td><td>YES</td><td>NA</td><td>NA</td><td>NA</td></tr>\n",
       "\t<tr><td>COUNTRY    </td><td>VARCHAR</td><td>YES</td><td>NA</td><td>NA</td><td>NA</td></tr>\n",
       "\t<tr><td>CITY       </td><td>VARCHAR</td><td>YES</td><td>NA</td><td>NA</td><td>NA</td></tr>\n",
       "\t<tr><td>TEMPERATURE</td><td>DOUBLE </td><td>YES</td><td>NA</td><td>NA</td><td>NA</td></tr>\n",
       "\t<tr><td>CONDITION  </td><td>VARCHAR</td><td>YES</td><td>NA</td><td>NA</td><td>NA</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 5 × 6\n",
       "\\begin{tabular}{llllll}\n",
       " column\\_name & column\\_type & null & key & default & extra\\\\\n",
       " <chr> & <chr> & <chr> & <chr> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t FLDATE      & DATE    & YES & NA & NA & NA\\\\\n",
       "\t COUNTRY     & VARCHAR & YES & NA & NA & NA\\\\\n",
       "\t CITY        & VARCHAR & YES & NA & NA & NA\\\\\n",
       "\t TEMPERATURE & DOUBLE  & YES & NA & NA & NA\\\\\n",
       "\t CONDITION   & VARCHAR & YES & NA & NA & NA\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 5 × 6\n",
       "\n",
       "| column_name &lt;chr&gt; | column_type &lt;chr&gt; | null &lt;chr&gt; | key &lt;chr&gt; | default &lt;chr&gt; | extra &lt;chr&gt; |\n",
       "|---|---|---|---|---|---|\n",
       "| FLDATE      | DATE    | YES | NA | NA | NA |\n",
       "| COUNTRY     | VARCHAR | YES | NA | NA | NA |\n",
       "| CITY        | VARCHAR | YES | NA | NA | NA |\n",
       "| TEMPERATURE | DOUBLE  | YES | NA | NA | NA |\n",
       "| CONDITION   | VARCHAR | YES | NA | NA | NA |\n",
       "\n"
      ],
      "text/plain": [
       "  column_name column_type null key default extra\n",
       "1 FLDATE      DATE        YES  NA  NA      NA   \n",
       "2 COUNTRY     VARCHAR     YES  NA  NA      NA   \n",
       "3 CITY        VARCHAR     YES  NA  NA      NA   \n",
       "4 TEMPERATURE DOUBLE      YES  NA  NA      NA   \n",
       "5 CONDITION   VARCHAR     YES  NA  NA      NA   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "dbGetQuery(con, \"DESCRIBE SELECT * FROM 'WEATHER.csv';\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60b55267-4339-4241-b357-1626e86fb79a",
   "metadata": {},
   "source": [
    "## Join tables query\n",
    "\n",
    "The actual SQL query joins the three tables and performs the following operations:\n",
    "- Retrieves flight details from `SFLIGHT` using ERPL's `sap_read_table`, aliasing it as `f`.\n",
    "- Again using ERPL's `sap_read_table` we join `SPFLI` (aliased as `s`) on `MANDT`, `CARRID`, and `CONNID` to get the flight's city of origin and destination.\n",
    "- Incorporates two instances of an external weather data CSV file, `w_from` and `w_to`, matching on flight date and respective cities' country and name for departure and arrival.\n",
    "- Rounds the temperature data to one decimal place for readability.\n",
    "- Orders the results by `CARRIER_ID`, `CONNECTION_ID`, and `FLIGHT_DATE`.\n",
    "- Limits the output to the first 25 rows for a concise view.\n",
    "\n",
    "The output of this query will provide a comprehensive view of the flights, including their departure and arrival cities, and the corresponding temperatures, thus offering valuable insights for flight operations analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "42646643-c993-43e4-b18e-d832df481746",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=white-space:pre-wrap>'\\nSELECT \\n  f.CARRID,\\n  f.CONNID,\\n  f.FLDATE,\\n  s.CITYFROM as CITY_FROM,\\n  ROUND(w_from.TEMPERATURE, 1) as TEMP_FROM,\\n  s.CITYTO as CITY_TO,\\n  ROUND(w_to.TEMPERATURE, 1) as TEMP_TO,\\n  FROM sap_read_table(\\'SFLIGHT\\') AS f\\n  JOIN sap_read_table(\\'SPFLI\\') AS s \\n      ON (f.MANDT = s.MANDT AND f.CARRID = s.CARRID AND f.CONNID = s.CONNID)\\n  JOIN \\'WEATHER.csv\\' AS w_from\\n      ON (f.FLDATE = w_from.FLDATE AND s.COUNTRYFR = w_from.COUNTRY AND s.CITYFROM = w_from.CITY)\\n  JOIN \\'WEATHER.csv\\' AS w_to\\n      ON (f.FLDATE = w_to.FLDATE AND s.COUNTRYTO = w_to.COUNTRY AND s.CITYTO = w_to.CITY)\\n  ORDER BY 1, 2, 3\\n  LIMIT 25\\n'</span>"
      ],
      "text/latex": [
       "'\\textbackslash{}nSELECT \\textbackslash{}n  f.CARRID,\\textbackslash{}n  f.CONNID,\\textbackslash{}n  f.FLDATE,\\textbackslash{}n  s.CITYFROM as CITY\\_FROM,\\textbackslash{}n  ROUND(w\\_from.TEMPERATURE, 1) as TEMP\\_FROM,\\textbackslash{}n  s.CITYTO as CITY\\_TO,\\textbackslash{}n  ROUND(w\\_to.TEMPERATURE, 1) as TEMP\\_TO,\\textbackslash{}n  FROM sap\\_read\\_table(\\textbackslash{}'SFLIGHT\\textbackslash{}') AS f\\textbackslash{}n  JOIN sap\\_read\\_table(\\textbackslash{}'SPFLI\\textbackslash{}') AS s \\textbackslash{}n      ON (f.MANDT = s.MANDT AND f.CARRID = s.CARRID AND f.CONNID = s.CONNID)\\textbackslash{}n  JOIN \\textbackslash{}'WEATHER.csv\\textbackslash{}' AS w\\_from\\textbackslash{}n      ON (f.FLDATE = w\\_from.FLDATE AND s.COUNTRYFR = w\\_from.COUNTRY AND s.CITYFROM = w\\_from.CITY)\\textbackslash{}n  JOIN \\textbackslash{}'WEATHER.csv\\textbackslash{}' AS w\\_to\\textbackslash{}n      ON (f.FLDATE = w\\_to.FLDATE AND s.COUNTRYTO = w\\_to.COUNTRY AND s.CITYTO = w\\_to.CITY)\\textbackslash{}n  ORDER BY 1, 2, 3\\textbackslash{}n  LIMIT 25\\textbackslash{}n'"
      ],
      "text/markdown": [
       "<span style=white-space:pre-wrap>'\\nSELECT \\n  f.CARRID,\\n  f.CONNID,\\n  f.FLDATE,\\n  s.CITYFROM as CITY_FROM,\\n  ROUND(w_from.TEMPERATURE, 1) as TEMP_FROM,\\n  s.CITYTO as CITY_TO,\\n  ROUND(w_to.TEMPERATURE, 1) as TEMP_TO,\\n  FROM sap_read_table(\\'SFLIGHT\\') AS f\\n  JOIN sap_read_table(\\'SPFLI\\') AS s \\n      ON (f.MANDT = s.MANDT AND f.CARRID = s.CARRID AND f.CONNID = s.CONNID)\\n  JOIN \\'WEATHER.csv\\' AS w_from\\n      ON (f.FLDATE = w_from.FLDATE AND s.COUNTRYFR = w_from.COUNTRY AND s.CITYFROM = w_from.CITY)\\n  JOIN \\'WEATHER.csv\\' AS w_to\\n      ON (f.FLDATE = w_to.FLDATE AND s.COUNTRYTO = w_to.COUNTRY AND s.CITYTO = w_to.CITY)\\n  ORDER BY 1, 2, 3\\n  LIMIT 25\\n'</span>"
      ],
      "text/plain": [
       "[1] \"\\nSELECT \\n  f.CARRID,\\n  f.CONNID,\\n  f.FLDATE,\\n  s.CITYFROM as CITY_FROM,\\n  ROUND(w_from.TEMPERATURE, 1) as TEMP_FROM,\\n  s.CITYTO as CITY_TO,\\n  ROUND(w_to.TEMPERATURE, 1) as TEMP_TO,\\n  FROM sap_read_table('SFLIGHT') AS f\\n  JOIN sap_read_table('SPFLI') AS s \\n      ON (f.MANDT = s.MANDT AND f.CARRID = s.CARRID AND f.CONNID = s.CONNID)\\n  JOIN 'WEATHER.csv' AS w_from\\n      ON (f.FLDATE = w_from.FLDATE AND s.COUNTRYFR = w_from.COUNTRY AND s.CITYFROM = w_from.CITY)\\n  JOIN 'WEATHER.csv' AS w_to\\n      ON (f.FLDATE = w_to.FLDATE AND s.COUNTRYTO = w_to.COUNTRY AND s.CITYTO = w_to.CITY)\\n  ORDER BY 1, 2, 3\\n  LIMIT 25\\n\""
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A data.frame: 25 × 7</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>CARRID</th><th scope=col>CONNID</th><th scope=col>FLDATE</th><th scope=col>CITY_FROM</th><th scope=col>TEMP_FROM</th><th scope=col>CITY_TO</th><th scope=col>TEMP_TO</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;date&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>AA</td><td>0017</td><td>2016-11-15</td><td>NEW YORK</td><td>28.3</td><td>SAN FRANCISCO</td><td>19.8</td></tr>\n",
       "\t<tr><td>AA</td><td>0017</td><td>2017-02-03</td><td>NEW YORK</td><td>18.9</td><td>SAN FRANCISCO</td><td>17.2</td></tr>\n",
       "\t<tr><td>AA</td><td>0017</td><td>2017-04-24</td><td>NEW YORK</td><td>14.7</td><td>SAN FRANCISCO</td><td>16.2</td></tr>\n",
       "\t<tr><td>AA</td><td>0017</td><td>2017-07-13</td><td>NEW YORK</td><td>16.8</td><td>SAN FRANCISCO</td><td>22.5</td></tr>\n",
       "\t<tr><td>AA</td><td>0017</td><td>2017-10-01</td><td>NEW YORK</td><td>14.6</td><td>SAN FRANCISCO</td><td>28.3</td></tr>\n",
       "\t<tr><td>AA</td><td>0017</td><td>2017-12-20</td><td>NEW YORK</td><td>13.0</td><td>SAN FRANCISCO</td><td>21.4</td></tr>\n",
       "\t<tr><td>AZ</td><td>0555</td><td>2016-11-15</td><td>ROME    </td><td>20.6</td><td>FRANKFURT    </td><td>24.2</td></tr>\n",
       "\t<tr><td>AZ</td><td>0555</td><td>2017-02-03</td><td>ROME    </td><td>13.1</td><td>FRANKFURT    </td><td>24.1</td></tr>\n",
       "\t<tr><td>AZ</td><td>0555</td><td>2017-04-24</td><td>ROME    </td><td>20.7</td><td>FRANKFURT    </td><td>24.6</td></tr>\n",
       "\t<tr><td>AZ</td><td>0555</td><td>2017-07-13</td><td>ROME    </td><td>14.0</td><td>FRANKFURT    </td><td>16.3</td></tr>\n",
       "\t<tr><td>AZ</td><td>0555</td><td>2017-10-01</td><td>ROME    </td><td>20.9</td><td>FRANKFURT    </td><td>15.3</td></tr>\n",
       "\t<tr><td>AZ</td><td>0555</td><td>2017-12-20</td><td>ROME    </td><td>23.8</td><td>FRANKFURT    </td><td>24.7</td></tr>\n",
       "\t<tr><td>AZ</td><td>0789</td><td>2016-11-15</td><td>TOKYO   </td><td>29.7</td><td>ROME         </td><td>20.6</td></tr>\n",
       "\t<tr><td>AZ</td><td>0789</td><td>2017-02-03</td><td>TOKYO   </td><td>23.4</td><td>ROME         </td><td>13.1</td></tr>\n",
       "\t<tr><td>AZ</td><td>0789</td><td>2017-04-24</td><td>TOKYO   </td><td>28.5</td><td>ROME         </td><td>20.7</td></tr>\n",
       "\t<tr><td>AZ</td><td>0789</td><td>2017-07-13</td><td>TOKYO   </td><td>19.4</td><td>ROME         </td><td>14.0</td></tr>\n",
       "\t<tr><td>AZ</td><td>0789</td><td>2017-10-01</td><td>TOKYO   </td><td>19.8</td><td>ROME         </td><td>20.9</td></tr>\n",
       "\t<tr><td>AZ</td><td>0789</td><td>2017-12-20</td><td>TOKYO   </td><td>15.6</td><td>ROME         </td><td>23.8</td></tr>\n",
       "\t<tr><td>DL</td><td>0106</td><td>2016-11-13</td><td>NEW YORK</td><td> 9.1</td><td>FRANKFURT    </td><td>13.8</td></tr>\n",
       "\t<tr><td>DL</td><td>0106</td><td>2017-02-01</td><td>NEW YORK</td><td>24.3</td><td>FRANKFURT    </td><td>23.9</td></tr>\n",
       "\t<tr><td>DL</td><td>0106</td><td>2017-04-22</td><td>NEW YORK</td><td>15.8</td><td>FRANKFURT    </td><td>23.5</td></tr>\n",
       "\t<tr><td>DL</td><td>0106</td><td>2017-07-11</td><td>NEW YORK</td><td>20.7</td><td>FRANKFURT    </td><td>30.6</td></tr>\n",
       "\t<tr><td>DL</td><td>0106</td><td>2017-09-29</td><td>NEW YORK</td><td>16.3</td><td>FRANKFURT    </td><td>19.1</td></tr>\n",
       "\t<tr><td>DL</td><td>0106</td><td>2017-12-18</td><td>NEW YORK</td><td>14.4</td><td>FRANKFURT    </td><td>21.8</td></tr>\n",
       "\t<tr><td>JL</td><td>0407</td><td>2016-11-17</td><td>TOKYO   </td><td>20.3</td><td>FRANKFURT    </td><td>20.4</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 25 × 7\n",
       "\\begin{tabular}{lllllll}\n",
       " CARRID & CONNID & FLDATE & CITY\\_FROM & TEMP\\_FROM & CITY\\_TO & TEMP\\_TO\\\\\n",
       " <chr> & <chr> & <date> & <chr> & <dbl> & <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t AA & 0017 & 2016-11-15 & NEW YORK & 28.3 & SAN FRANCISCO & 19.8\\\\\n",
       "\t AA & 0017 & 2017-02-03 & NEW YORK & 18.9 & SAN FRANCISCO & 17.2\\\\\n",
       "\t AA & 0017 & 2017-04-24 & NEW YORK & 14.7 & SAN FRANCISCO & 16.2\\\\\n",
       "\t AA & 0017 & 2017-07-13 & NEW YORK & 16.8 & SAN FRANCISCO & 22.5\\\\\n",
       "\t AA & 0017 & 2017-10-01 & NEW YORK & 14.6 & SAN FRANCISCO & 28.3\\\\\n",
       "\t AA & 0017 & 2017-12-20 & NEW YORK & 13.0 & SAN FRANCISCO & 21.4\\\\\n",
       "\t AZ & 0555 & 2016-11-15 & ROME     & 20.6 & FRANKFURT     & 24.2\\\\\n",
       "\t AZ & 0555 & 2017-02-03 & ROME     & 13.1 & FRANKFURT     & 24.1\\\\\n",
       "\t AZ & 0555 & 2017-04-24 & ROME     & 20.7 & FRANKFURT     & 24.6\\\\\n",
       "\t AZ & 0555 & 2017-07-13 & ROME     & 14.0 & FRANKFURT     & 16.3\\\\\n",
       "\t AZ & 0555 & 2017-10-01 & ROME     & 20.9 & FRANKFURT     & 15.3\\\\\n",
       "\t AZ & 0555 & 2017-12-20 & ROME     & 23.8 & FRANKFURT     & 24.7\\\\\n",
       "\t AZ & 0789 & 2016-11-15 & TOKYO    & 29.7 & ROME          & 20.6\\\\\n",
       "\t AZ & 0789 & 2017-02-03 & TOKYO    & 23.4 & ROME          & 13.1\\\\\n",
       "\t AZ & 0789 & 2017-04-24 & TOKYO    & 28.5 & ROME          & 20.7\\\\\n",
       "\t AZ & 0789 & 2017-07-13 & TOKYO    & 19.4 & ROME          & 14.0\\\\\n",
       "\t AZ & 0789 & 2017-10-01 & TOKYO    & 19.8 & ROME          & 20.9\\\\\n",
       "\t AZ & 0789 & 2017-12-20 & TOKYO    & 15.6 & ROME          & 23.8\\\\\n",
       "\t DL & 0106 & 2016-11-13 & NEW YORK &  9.1 & FRANKFURT     & 13.8\\\\\n",
       "\t DL & 0106 & 2017-02-01 & NEW YORK & 24.3 & FRANKFURT     & 23.9\\\\\n",
       "\t DL & 0106 & 2017-04-22 & NEW YORK & 15.8 & FRANKFURT     & 23.5\\\\\n",
       "\t DL & 0106 & 2017-07-11 & NEW YORK & 20.7 & FRANKFURT     & 30.6\\\\\n",
       "\t DL & 0106 & 2017-09-29 & NEW YORK & 16.3 & FRANKFURT     & 19.1\\\\\n",
       "\t DL & 0106 & 2017-12-18 & NEW YORK & 14.4 & FRANKFURT     & 21.8\\\\\n",
       "\t JL & 0407 & 2016-11-17 & TOKYO    & 20.3 & FRANKFURT     & 20.4\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 25 × 7\n",
       "\n",
       "| CARRID &lt;chr&gt; | CONNID &lt;chr&gt; | FLDATE &lt;date&gt; | CITY_FROM &lt;chr&gt; | TEMP_FROM &lt;dbl&gt; | CITY_TO &lt;chr&gt; | TEMP_TO &lt;dbl&gt; |\n",
       "|---|---|---|---|---|---|---|\n",
       "| AA | 0017 | 2016-11-15 | NEW YORK | 28.3 | SAN FRANCISCO | 19.8 |\n",
       "| AA | 0017 | 2017-02-03 | NEW YORK | 18.9 | SAN FRANCISCO | 17.2 |\n",
       "| AA | 0017 | 2017-04-24 | NEW YORK | 14.7 | SAN FRANCISCO | 16.2 |\n",
       "| AA | 0017 | 2017-07-13 | NEW YORK | 16.8 | SAN FRANCISCO | 22.5 |\n",
       "| AA | 0017 | 2017-10-01 | NEW YORK | 14.6 | SAN FRANCISCO | 28.3 |\n",
       "| AA | 0017 | 2017-12-20 | NEW YORK | 13.0 | SAN FRANCISCO | 21.4 |\n",
       "| AZ | 0555 | 2016-11-15 | ROME     | 20.6 | FRANKFURT     | 24.2 |\n",
       "| AZ | 0555 | 2017-02-03 | ROME     | 13.1 | FRANKFURT     | 24.1 |\n",
       "| AZ | 0555 | 2017-04-24 | ROME     | 20.7 | FRANKFURT     | 24.6 |\n",
       "| AZ | 0555 | 2017-07-13 | ROME     | 14.0 | FRANKFURT     | 16.3 |\n",
       "| AZ | 0555 | 2017-10-01 | ROME     | 20.9 | FRANKFURT     | 15.3 |\n",
       "| AZ | 0555 | 2017-12-20 | ROME     | 23.8 | FRANKFURT     | 24.7 |\n",
       "| AZ | 0789 | 2016-11-15 | TOKYO    | 29.7 | ROME          | 20.6 |\n",
       "| AZ | 0789 | 2017-02-03 | TOKYO    | 23.4 | ROME          | 13.1 |\n",
       "| AZ | 0789 | 2017-04-24 | TOKYO    | 28.5 | ROME          | 20.7 |\n",
       "| AZ | 0789 | 2017-07-13 | TOKYO    | 19.4 | ROME          | 14.0 |\n",
       "| AZ | 0789 | 2017-10-01 | TOKYO    | 19.8 | ROME          | 20.9 |\n",
       "| AZ | 0789 | 2017-12-20 | TOKYO    | 15.6 | ROME          | 23.8 |\n",
       "| DL | 0106 | 2016-11-13 | NEW YORK |  9.1 | FRANKFURT     | 13.8 |\n",
       "| DL | 0106 | 2017-02-01 | NEW YORK | 24.3 | FRANKFURT     | 23.9 |\n",
       "| DL | 0106 | 2017-04-22 | NEW YORK | 15.8 | FRANKFURT     | 23.5 |\n",
       "| DL | 0106 | 2017-07-11 | NEW YORK | 20.7 | FRANKFURT     | 30.6 |\n",
       "| DL | 0106 | 2017-09-29 | NEW YORK | 16.3 | FRANKFURT     | 19.1 |\n",
       "| DL | 0106 | 2017-12-18 | NEW YORK | 14.4 | FRANKFURT     | 21.8 |\n",
       "| JL | 0407 | 2016-11-17 | TOKYO    | 20.3 | FRANKFURT     | 20.4 |\n",
       "\n"
      ],
      "text/plain": [
       "   CARRID CONNID FLDATE     CITY_FROM TEMP_FROM CITY_TO       TEMP_TO\n",
       "1  AA     0017   2016-11-15 NEW YORK  28.3      SAN FRANCISCO 19.8   \n",
       "2  AA     0017   2017-02-03 NEW YORK  18.9      SAN FRANCISCO 17.2   \n",
       "3  AA     0017   2017-04-24 NEW YORK  14.7      SAN FRANCISCO 16.2   \n",
       "4  AA     0017   2017-07-13 NEW YORK  16.8      SAN FRANCISCO 22.5   \n",
       "5  AA     0017   2017-10-01 NEW YORK  14.6      SAN FRANCISCO 28.3   \n",
       "6  AA     0017   2017-12-20 NEW YORK  13.0      SAN FRANCISCO 21.4   \n",
       "7  AZ     0555   2016-11-15 ROME      20.6      FRANKFURT     24.2   \n",
       "8  AZ     0555   2017-02-03 ROME      13.1      FRANKFURT     24.1   \n",
       "9  AZ     0555   2017-04-24 ROME      20.7      FRANKFURT     24.6   \n",
       "10 AZ     0555   2017-07-13 ROME      14.0      FRANKFURT     16.3   \n",
       "11 AZ     0555   2017-10-01 ROME      20.9      FRANKFURT     15.3   \n",
       "12 AZ     0555   2017-12-20 ROME      23.8      FRANKFURT     24.7   \n",
       "13 AZ     0789   2016-11-15 TOKYO     29.7      ROME          20.6   \n",
       "14 AZ     0789   2017-02-03 TOKYO     23.4      ROME          13.1   \n",
       "15 AZ     0789   2017-04-24 TOKYO     28.5      ROME          20.7   \n",
       "16 AZ     0789   2017-07-13 TOKYO     19.4      ROME          14.0   \n",
       "17 AZ     0789   2017-10-01 TOKYO     19.8      ROME          20.9   \n",
       "18 AZ     0789   2017-12-20 TOKYO     15.6      ROME          23.8   \n",
       "19 DL     0106   2016-11-13 NEW YORK   9.1      FRANKFURT     13.8   \n",
       "20 DL     0106   2017-02-01 NEW YORK  24.3      FRANKFURT     23.9   \n",
       "21 DL     0106   2017-04-22 NEW YORK  15.8      FRANKFURT     23.5   \n",
       "22 DL     0106   2017-07-11 NEW YORK  20.7      FRANKFURT     30.6   \n",
       "23 DL     0106   2017-09-29 NEW YORK  16.3      FRANKFURT     19.1   \n",
       "24 DL     0106   2017-12-18 NEW YORK  14.4      FRANKFURT     21.8   \n",
       "25 JL     0407   2016-11-17 TOKYO     20.3      FRANKFURT     20.4   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql = \"\n",
    "SELECT \n",
    "  f.CARRID,\n",
    "  f.CONNID,\n",
    "  f.FLDATE,\n",
    "  s.CITYFROM as CITY_FROM,\n",
    "  ROUND(w_from.TEMPERATURE, 1) as TEMP_FROM,\n",
    "  s.CITYTO as CITY_TO,\n",
    "  ROUND(w_to.TEMPERATURE, 1) as TEMP_TO,\n",
    "  FROM sap_read_table('SFLIGHT') AS f\n",
    "  JOIN sap_read_table('SPFLI') AS s \n",
    "      ON (f.MANDT = s.MANDT AND f.CARRID = s.CARRID AND f.CONNID = s.CONNID)\n",
    "  JOIN 'WEATHER.csv' AS w_from\n",
    "      ON (f.FLDATE = w_from.FLDATE AND s.COUNTRYFR = w_from.COUNTRY AND s.CITYFROM = w_from.CITY)\n",
    "  JOIN 'WEATHER.csv' AS w_to\n",
    "      ON (f.FLDATE = w_to.FLDATE AND s.COUNTRYTO = w_to.COUNTRY AND s.CITYTO = w_to.CITY)\n",
    "  ORDER BY 1, 2, 3\n",
    "  LIMIT 25\n",
    "\"\n",
    "sql\n",
    "\n",
    "dbGetQuery(con, sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b2227ca-c3d4-410a-b678-5d59a532b5c4",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "4.3.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}