Hey all,
I'm trying to use list selection/combo box in the Service Order Transactions grid using a derived property. I would like the warranty code box to only allow selection of a warranty code that is currently active for the selected unit. However, the unit may have multiple active warranties. When I try to use:(SELECTwar.warr_codeFROM dbo.fs_unit_warr warINNER JOIN dbo.fs_unit unitON war.comp_id = unit.comp_idWHERE unit.ser_num = ue_SerNumAND war.end_date > GETDATE())
It gives me a Database Error 512 because it's returning multiple results. Is there any way to have a derived property to return multiple results without error or is there another workaround to use the combo box in a grid in this manner?
try adding 'distinct' after 'select'
As I suspected, that won't work. The issue is that the query is returning multiple results into a result set that doesn't allow it. The unit in question might have multiple warranties, so even using distinct would give multiple results and I can't use TOP 1, MIN, or MAX because the warranty used might be different than the result supplied.
Is there a way to allow the grid to return multiple results as like an inline list? This works for using the built in warranty code functions.
Sorry, missed that this was a 'sub query', and thus does not accept 'distinct'.
Your issue is the below since it can return Multiple records:AND war.end_date > GETDATE()Maybe something like the below instead.
... ** Main SQL **...WHERE EXISTS (SELECTwar.warr_codeFROM dbo.fs_unit_warr warINNER JOIN dbo.fs_unit unitON war.comp_id = unit.comp_idWHERE unit.ser_num = ue_SerNumAND war.end_date > GETDATE())
If you want comma separated list of warr_codes then use SQL STRING_AGG (a new SQL Function). Someone told me about it in another question a few hours ago.
https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16