Show UDT description in query
I have seen a couple of other post like this but none that quite answer my question. We populate a UDF (County) from a set of valid values which create a drop down list. The UDF correctly has the description and not the code. However a simple query (below) returns the value number and not the description
SELECT T1.CardName , T0.U_city , T0.U_County
FROM OCPR T0 INNER JOIN OCRD T1 ON T1.CardCode = T0.CardCode
Peter Juby replied
Try the following:
SELECT T1.CardName , T0.U_city , T0.U_County, T2.Descr
FROM OCPR T0 INNER JOIN OCRD T1 ON T1.CardCode = T0.CardCode, UFD1 T2, CUFD T3
WHERE T0.U_City = T2.FldValue and T2.TableID = 'OCPR' and T2.FieldID = 2 and T3.TableID = 'OCPR' and T3.FieldID = 2
Replace FieldID 2 with the ID of the T0.U_city field. Look in CUFD to get the internal ID for the field.
The only problem is you have 2 UDF's, so you will have to find a way of bringing both Description Fields back, possibly using a UNION clause or something along those lines. Test with just one first and see if it returns the correct values.