Query to bring back description on UDF instead of Value

[former_member268870]

Experts,

I need to create a query to show the Description instead of the Value on a UDF on a Service Call.

Please see attached UDF field data and that it is setup that the Value is 1,2,3,4 and the Description is 303952,5836072-4 ect.

It is this 303952 that the query needs to return.

Select T0.[CallID], T0.[U_NBS_S_05]

I tried to get the UDF1 table, but could not link this in the query generator.

Any help would be greatly appreciated.

Regards,

Marli

[Former Member · Accepted solution]

Hi Marli,

You may check these:

Use the description of an UDF in a query | SCN

Thanks,

Joseph

[former_member227598]

Hi Marli,

Please try below query format & change as per requirements.

Select T0.[CallID], T0.[U_NBS_S_05] ,(select Descr from  ufd1 where TableID = 'OSCL' AND FieldID = '20' AND FLDVALU= T0.[U_NBS_S_05]) FROM OITM T0

from OSCL T0

Rgds,

Kamlesh Naware

[former_member268870]

Joseph and Kamlesh,

Thanks for this info, it was really helpful.

I included in my query:

SELECT DISTINCT T0.[callID] AS 'Lab. Analysis Call ID', T8.[Name] AS 'Bill Program', T0.[CreateDate], T0.[CustmrName], T0.[U_NBS_S_29], T0.[internalSN] as 'Eng S/N', T0.[NumatCard] AS 'AC SN', T0.[ManufSN] AS 'Tail', T7.[U_NBS_SC_09], T7.[U_NBS_SC_03], T7.[U_RL_SC_01],

T0.[U_NBS_S_05], (Select Descr from ufd1 where TableID = 'OSCL' AND FieldID = '20' AND FLDVALU = T0.[U_NBS_05])

FROM OSCL T0 

INNER JOIN OITM T4 ON T0.[itemCode] = T4.[ItemCode] 
INNER JOIN OCTR T7 ON T0.[contractID] = T7.[ContractID]
INNER JOIN OSCP T8 ON T0.[problemTyp] = T8.[prblmTypID]
WHERE T0.[CreateDate] >= [%0] AND T0.[CreateDate] <= [%1] AND T8.[Name] = [%2] AND T0.[U_NBS_S_05] = [%3]

I get a error that seems totally unrelated to a UDF that is not mentioned in this query. (Invalid Column name 'U_NB_S_03') I believe that I deleted that UDF a while back.

Your help would be much appreciated.

Marli