Skip to Content

Query to bring back description on UDF instead of Value

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Apr 06, 2016 at 12:25 AM

    Hi Marli,

    You may check these:

    Use the description of an UDF in a query | SCN

    Thanks,

    Joseph

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 07, 2016 at 08:11 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

      error.png (705.4 kB)