CODEWITHSUNDEEP
c#odatasubquery
Matt Brandon
Matt Brandon

Reputation: 331

Sub Select in Odata

I am looking for a way to create a subselect/subquery using Odata. The query I want to create should look like the following:

SELECT * 
FROM Treatment
WHERE status = 'Pos'
and ID IN ( 
    SELECT FIRST(ID)
    FROM Treatment
    WHERE TreatmentTypeSK = 9
    GROUP BY Person, TreatmentTypeSK
    ORDER BY Date DESC
)

As an example of what I'm trying to accomplish, please look at the following data.

ID    Person    TreatmentTypeSK    Status    Date
----------------------------------------------------
1     Bob       9                  Pos       1/5/15
2     Bob       9                  Neg       2/10/15
3     Jane      9                  Pos       1/20/15
4     Jane      9                  Neg       1/1/15
5     Jane      8                  Pos       3/2/15

With the query above (which I hope to produce using odata), the only result that would get returned would be ID 3 because it is Jane's latest Treatment record with a TreatmentTypeSK of 9 and the status is 'Pos'. Note that ID 2 is not returned because even though Bob has a couple records of TreatmentTypeSK 9, the latest one does not have a 'Pos' status.

Upvotes: 4

Views: 3228

Answers (1)

TomDoesCode
TomDoesCode

Reputation: 3681

Sub queries like this aren't with Odata, you would have to do 2 queries, but I think that you have even bigger problems as I don't think that the sub query itself is going to be possible.

I think that while it may be technically possible to achieve this in within the OData spec using group by mentioned in this link: http://docs.oasis-open.org/odata/odata-data-aggregation-ext/v4.0/cs01/odata-data-aggregation-ext-v4.0-cs01.html#_Toc378326290

I think that this isn't supported by most of the libraries so you may struggle to actually use it. I tried it on TripPin and it didn't work...

I would probably expose this functionality as an odata function or alternatively (and a bit messily), change the query to not have the group by part - this would eliminate the need for the sub query as well. The trouble would be that you could then get multiple rows per person but you could post process this on the client.

Upvotes: 1

Related Questions