I have this query, giving me the names of gynaecologists in a hospital plus the number of natural births (i.e. no c-section) they were involved in:
- Code: Select all
SELECT
caregiver.Name AS Caregiver,
(SELECT
COUNT(DISTINCT qnnaire1.PregnancyId)
FROM BmaCare.Questionnaire qnnaire1
LEFT JOIN BmaCare.Questionnaire qnnaire2 ON qnnaire1.PregnancyId = qnnaire2.PregnancyId
CROSS APPLY OPENJSON(qnnaire1.Data, '$.actions')
WITH (
entries nvarchar(max) '$.entries' AS JSON
) json1
CROSS APPLY OPENJSON(json1.entries)
WITH (
[key] nvarchar(100) '$.key',
[value] nvarchar(100) '$.value'
) birthtimedata
CROSS APPLY OPENJSON(json1.entries)
WITH (
[key] nvarchar(100) '$.key',
[value] nvarchar(100) '$.value'
) birthtypedata
CROSS APPLY OPENJSON(qnnaire2.Data, '$.actions')
WITH (
entries nvarchar(max) '$.entries' AS JSON
) json2
CROSS APPLY OPENJSON(json2.entries)
WITH (
[key] nvarchar(100) '$.key',
[value] nvarchar(100) '$.value'
) gynedata
WHERE
birthtimedata.[key] = 'Child.BirthTime' AND
birthtimedata.[value] is not null AND
birthtypedata.[key] = 'Child.BirthType' AND
birthtypedata.[value] = 'Natural' AND
qnnaire2.Name = 'Intake' AND
gynedata.[key] = 'DeliveryCaregivers.Gynaecologist' AND
gynedata.[value] = caregiver.Id
) AS [Total natural births]
FROM BmaCare.Caregiver careGiver
WHERE careGiver.Type = 'Gynaecologist'
When I execute this query in SQL Server Management Studio 2017, I get the desired result. However, I need to do something with this result in Servoy, and I use databaseManager.getDataSetByQuery() to retrieve the same records. Which I do not get: Servoy gives me a "Bad SQL syntax" error instead. Is there anything else I need to take into consideration here so that the query executes fine? And if not: are there any coding alternatives to databaseManager.getDataSetByQuery() that do come up with the correct records here?
Thanks!