blog.atwork.at

news and know-how about microsoft, technology, cloud and more.

Use the OData Filter for Azure SQL Databases

A very quick tip today: When using an Azure Logic App in combination with an Azure SQL Database, filtering rows can be done like here.

As example: A T-SQL query as this one...

SELECT * FROM [dbo].[Customers] WHERE [StorageDataSentDate] IS NULL ORDER BY [Id]

...will be formulated as filter expression with an OData query as here.

image

The definition for the Get_rows_(V2) action looks similar as here:

... "Get_rows_(V2)": {
"inputs": {
"host": {"connection": {"name": "@parameters('$connections')['sql']['connectionId']"} },
"method": "get",
"path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('[someserver].database.windows.net'))},
  @{encodeURIComponent(encodeURIComponent('governancetoolkit365'))}/tables/
  @{encodeURIComponent(encodeURIComponent('[dbo].[Customers]'))}/items",
  "queries": {"$filter": "(StorageDataSentDate eq null)", $orderby": "Id"}
},
"runAfter": {"Initialize_variable": ["Succeeded"]},
"runtimeConfiguration": {
  "paginationPolicy": {"minimumItemCount": 5}
},
"type": "ApiConnection"
}, ...

Depending on the data size, remember to configure pagination.

The output can be processed in an ForEach action as needed.

..."For_each": {
"actions": {
"Append_to_string_variable": {
"inputs": {
   "name": "Helper",
   "value": "@{items('For_each')?['StorageDataSentDate']}, @{items('For_each')?['Domain']}"
},
"runAfter": {},
"type": "AppendToStringVariable"
}
},
"foreach": "@body('Get_rows_(V2)')?['value']",
"runAfter": { "Get_rows_(V2)": ["Succeeded"] },
"type": "Foreach"
},...

Happy developing Azure Logic Apps!

Comments (1) -

  • tkm

    3/25/2020 8:03:28 AM |

    Hi,

    Thanks for the great article.

    I have question: How do I get ODATA from stored procedure results, just the way G?

  • megan collins

    11/28/2020 6:27:15 PM |

    I simply wanted to write down a quick word to say thanks to you for those wonderful tips and hints you are showing on this site. As a result of checking through the net and meeting techniques that were not productive, Same as your blog I found another one   <a href="smconsultant.com/.../"; rel="dofollow">Oracle Fusion HCM  </a>.Actually I was looking for the same information on internet for <a href="smconsultant.com/oracle-hcm-cloud-tutorial/"; rel="dofollow">Oracle HCM Cloud </a> and came across your blog.  I am impressed by the information that you have on this blog.  Thanks once more for all the details.

Loading