Sql views¶
La ressource Vues SQL ou « SQL views » vous permet de créer et de récupérer le jeu de résultats des vues SQL. Les « SQL views » peuvent être exécutées directement sur la base de données et afficher le jeu de résultats via la ressource API Web.
status |
Time |
Size |
200 OK |
2.06 s |
3.66 KB |
{
"sqlViews": [
{
"id": "xxxxxxxxxxx",
"displayName": "abandoned_dashboards_S"
},
{
"id": "xxxxxxxxxxx",
"displayName": "aggregate_des_count_D"
},
{
"id": "xxxxxxxxxxx",
"displayName": "aggregate_des_no_orgunits_S"
},
{
"id": "xxxxxxxxxxx",
"displayName": "datasets_abandoned_S"
}
]
}
status |
Time |
Size |
200 OK |
1748 ms |
2.13 KB |
{
"created": "2022-03-24T15:17:04.088",
"lastUpdated": "2022-03-24T15:17:04.088",
"name": "cocs_wrong_cardinality_D",
"href": "https://ministere-sante.mg/api/sqlViews/xxxxxxxxxxx",
"id": "xxxxxxxxxxx",
"sqlQuery": "WITH baz as ( SELECT foo.categorycomboid,foo.categoryoptioncomboid,foo.actual_cardnality,bar.theoretical_cardnality FROM ( SELECT b.categorycomboid,a.categoryoptioncomboid, COUNT(*) as actual_cardnality FROM categoryoptioncombos_categoryoptions a INNER JOIN categorycombos_optioncombos b on a.categoryoptioncomboid = b.categoryoptioncomboid GROUP BY b.categorycomboid,a.categoryoptioncomboid ) as foo INNER JOIN (SELECT categorycomboid,COUNT(*) as theoretical_cardnality FROM categorycombos_categories\n GROUP BY categorycomboid) bar on foo.categorycomboid = bar.categorycomboid\nWHERE foo.actual_cardnality != bar.theoretical_cardnality ) SELECT x.uid as catcombo_uid,y.uid as coc_uid,x.name as catcombo_name,y.name as coc_name, baz.actual_cardnality,baz.theoretical_cardnality FROM baz INNER JOIN categorycombo x on baz.categorycomboid = x.categorycomboid INNER JOIN categoryoptioncombo y on baz.categoryoptioncomboid = y.categoryoptioncomboid;",
"displayName": "cocs_wrong_cardinality_D",
"publicAccess": "rw------",
"description": "Category option combinations with incorrect cardinality.",
"type": "QUERY",
"externalAccess": false,
"cacheStrategy": "NO_CACHE",
"favorite": false,
"lastUpdatedBy": {
"displayName": "hisp1 hisp1",
"id": "xxxxxxxxxxx",
"username": "hisp1"
},
"access": {
"read": true,
"update": false,
"externalize": false,
"delete": false,
"write": false,
"manage": false
},
"user": {
"displayName": "hisp1 hisp1",
"id": "xxxxxxxxxxx",
"username": "hisp1"
},
"favorites": [],
"userGroupAccesses": [],
"attributeValues": [],
"translations": [],
"userAccesses": []
}
SQL views sont utiles pour créer des vues de données qui peuvent être plus facilement construites avec SQL par rapport à la combinaison des multiples objets de l’API Web. Par exemple :
| WITH baz as ( SELECT foo.categorycomboid,foo.categoryoptioncomboid,foo.actual_cardnality,bar.theoretical_cardnality | FROM ( SELECT b.categorycomboid,a.categoryoptioncomboid, COUNT(*) as actual_cardnality FROM categoryoptioncombos_categoryoptions a | INNER JOIN categorycombos_optioncombos b on a.categoryoptioncomboid = b.categoryoptioncomboid GROUP BY b.categorycomboid,a.categoryoptioncomboid ) | as foo INNER JOIN (SELECT categorycomboid,COUNT(*) as theoretical_cardnality FROM categorycombos_categories\n GROUP BY categorycomboid) bar | ON foo.categorycomboid = bar.categorycomboid\nWHERE foo.actual_cardnality != bar.theoretical_cardnality ) SELECT x.uid as catcombo_uid,y.uid as coc_uid, | x.name as catcombo_name,y.name as coc_name, baz.actual_cardnality,baz.theoretical_cardnality FROM baz INNER JOIN categorycombo x on baz.categorycomboid = x.categorycomboid | INNER JOIN categoryoptioncombo y on baz.categoryoptioncomboid = y.categoryoptioncomboid;
status |
Time |
Size |
200 OK |
1748 ms |
2.13 KB |
{
"listGrid": {
"metaData": {},
"headerWidth": 6,
"subtitle": "Category option combinations with incorrect cardinality.",
"width": 6,
"title": "cocs_wrong_cardinality_D",
"height": 50,
"headers": [
{
"hidden": false,
"meta": false,
"name": "catcombo_uid",
"column": "catcombo_uid",
"type": "java.lang.String"
},
{
"hidden": false,
"meta": false,
"name": "coc_uid",
"column": "coc_uid",
"type": "java.lang.String"
},
{
"hidden": false,
"meta": false,
"name": "catcombo_name",
"column": "catcombo_name",
"type": "java.lang.String"
},
{
"hidden": false,
"meta": false,
"name": "coc_name",
"column": "coc_name",
"type": "java.lang.String"
},
{
"hidden": false,
"meta": false,
"name": "actual_cardnality",
"column": "actual_cardnality",
"type": "java.lang.String"
},
{
"hidden": false,
"meta": false,
"name": "theoretical_cardnality",
"column": "theoretical_cardnality",
"type": "java.lang.String"
}
],
"rows": [
[
"xxxxxxxxxxx",
"xxxxxxxxxxx",
"Age - Genre TRAUMATISME T6",
"15 – 17 ans, Feminin",
1,
2
],
[
"xxxxxxxxxxx",
"xxxxxxxxxxx",
"DP_Partenaires",
"OMS, USAID_Mikolo/AIM",
2,
1
],
[
"xxxxxxxxxxx",
"xxxxxxxxxxx",
"DP_Partenaires",
"OMS, USAID Marie Stop Mada",
2,
1
],
[
"xxxxxxxxxxx",
"xxxxxxxxxxx",
"Age - Genre TRAUMATISME T6",
"25 – 59 ans, Feminin",
1,
2
]
]
}
}
