{"id":20447,"date":"2024-02-19T12:30:09","date_gmt":"2024-02-19T11:30:09","guid":{"rendered":"https:\/\/help.openbee.com\/open-bee-portal\/administrators-guide\/sql-connectivity-module\/sql-queries\/"},"modified":"2024-06-07T10:52:31","modified_gmt":"2024-06-07T08:52:31","slug":"sql-queries","status":"publish","type":"page","link":"https:\/\/help.openbee.com\/en\/open-bee-portal\/administrators-guide\/sql-connectivity-module\/sql-queries\/","title":{"rendered":"SQL queries"},"content":{"rendered":"
\n

The “SQL Queries” screen allows you to create and configure queries that will retrieve data from your existing database to populate Open Bee\u2122 Portal metadata.<\/p>\n

To add a query, click “+”.<\/strong><\/p>\n

<\/p>\n

Name<\/strong> : the name of The SQL query name,<\/p>\n

Ranking rule :<\/strong> The ranking rule or form that will be used to trigger the query.<\/p>\n

SQL Connection: The SQL<\/strong> connection defined in the first tab to use.<\/p>\n

SQL Query<\/strong><\/p>\n

In this block, you can enter your SELECT query.
\nIn the WHERE clause of your query, you can call a single metadata<\/strong> by putting it between < ><\/strong>.<\/p>\n

Example:<\/p>\n

\n
\n
SELECT nom, prenom, email \nFROM ma_table \nWHERE ref_contrat = <Num\u00e9ro de contrat><\/pre>\n<\/div>\n<\/div>\n
In this example, when the user enters the value of the metadata “Contract number” the request will be executed and 3 pieces of information will be retrieved, the last name, first name and email.<\/p>\n
Example of a request to an xls file:<\/p>\n
\n
\n
SELECT [Ent\u00eate_de_ma_colonne_r\u00e9sultat] \nFROM [Nom_de_la_feuille$] \nWHERE [Ent\u00eate_de_ma_colonne_source] = '<M\u00e9tadonn\u00e9e_de_d\u00e9clenchement>'<\/pre>\n<\/div>\n<\/div>\n
In an xls file ,  please put a  $  after the  sheet name  .<\/strong><\/p>\n
Another example of a request to a csv file:<\/p>\n
\n
\n
SELECT [COL1] FROM fichier.csv WHERE [COL2] LIKE '<Nom_index>'<\/pre>\n<\/div>\n<\/div>\n
\n
\n
When configuring the query, you must choose whether the query will return one or more rows of results.
\nThis choice is important because it conditions the storage of the data we are trying to recover.<\/p>\n<\/div>\n<\/div>\n
Here, we can assume that “This query returns a single result row<\/strong>“. Indeed, by entering a contract number, a unique reference, one expects to receive a unique record.<\/p>\n
Consider the following example:<\/p>\n
\n
\n
SELECT nom, prenom, email FROM ma_table WHERE client = <Nom du client><\/pre>\n<\/div>\n<\/div>\n
Let’s say we want to retrieve a customer’s sales contacts, so we can expect to receive multiple rows of results,
\nIn this case, it is important to choose “This query returns several rows of results<\/strong>” because the storage will have to be done in a metadata of type Tableau.<\/p>\n
Once the query has been entered, click on the “Perform association <\/strong>” button to test the query and verify that the columns mentioned in the SELECT query are correctly returned.<\/strong><\/p>\n
<\/p>\n
In this example, the columns of the SELECT query are displayed after clicking “Perform Association<\/strong>“.
\nThen, for each column, we choose a metadata of the classification rule or the form that will receive the data.
\nYou can’t leave a column unassociated.<\/p>\n
\n
\n
If your query returns multiple rows of results, for consistency you won’t be able to choose just any type of metadata.
\nThis is because you will need to use a metadata of type Table, List, or Multiple Field.
\nIn addition, some types of metadata cannot be used in the association, such as File, Image, Annotation, or Signature because they are incompatible.
\nBut also the Users and Groups type, Lists with the “Multiple” option and Sublists.<\/p>\n<\/div>\n<\/div>\n

\nExecution mode<\/strong> : Choose when The SQL query name will run either when you add a document, or change a metadata, or both.
\nPossible values are: Add a document, Edit a document, Add or edit a document.<\/p>\n
Once the request is configured, save by clicking on “Validate<\/strong>“.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
The “SQL Queries” screen allows you to create and configure queries that will retrieve data from your existing database to populate Open Bee\u2122 Portal metadata. To add a query, click “+”. Name : the name of The SQL query name, Ranking rule : The ranking rule or form that will be used to trigger the […]<\/p>\n","protected":false},"author":4,"featured_media":0,"parent":20409,"menu_order":1,"comment_status":"closed","ping_status":"closed","template":"templates\/ob-help-products.php","meta":{"footnotes":""},"class_list":["post-20447","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/pages\/20447","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/comments?post=20447"}],"version-history":[{"count":4,"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/pages\/20447\/revisions"}],"predecessor-version":[{"id":22478,"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/pages\/20447\/revisions\/22478"}],"up":[{"embeddable":true,"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/pages\/20409"}],"wp:attachment":[{"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/media?parent=20447"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}