Adding UDF To Query

[Former Member]

Hi All,

I am wondering i how i can add the following UDF into my query. We use this free text field as the accurate delivery date, is there a way this can be converted and displayed as a column in the below query as the 'Next Delivery Date' this UDF is only used in Purchase Orders.

SELECT t0.Cardcode as "Customer#", t0.Cardname as "Customer Name", t0.DocNum as "SO #", t1.ItemCode, t0.DocDate, t0.DocDueDate, t1.OpenQty, t1.OpenQty*T1.price AS 'Open Value', t2.onhand FROM dbo.ORDR t0 INNER JOIN dbo.RDR1 t1 on t1.docentry = t0.docentry INNER JOIN OITM t2 on t1.itemcode=t2.itemcode WHERE t1.LineStatus = 'O' AND t1.OpenQty > 0

[Former Member]

Thank you Johan,

Unfortunately when i enter UDF - FreeTxt - i am getting an error.

[Johan_H]

Hi James,

Could you please elaborate a little bit?

What do you mean with "...enter UDF - FreeTxt..."?

Where do you get the error, and what is it?

Regards,

Johan

[Johan_H]

Hi James,

If this field is used for dates only, you should change the data type to date.

And/or if this field is used for other types of data (actual free text), you should create a new UDF of data type date, copy all dates into that field, and use that in your query.

As a field of data type free text can technically contain anything, that means that the risk of human error is too great to use this field in any kind of formula. Take the date in your screenshot for example: 2017.09.25. How easy is it to write that wrong, now that all is technically allowed: 17.09.25 or 2017,09,25 or 2017.90.25 or 2017. 09.25 or 201709.25 or 2017.9.25. And that is just a couple of the types of writing mistakes that I have personally come across in my career. You may notice that most of these mistakes do not even register as such at first glance, however in any type of formula that expects a date, these would cause an error.

If you just need to display this field in your query result, and nothing else, then you may simply add it to the query:

SELECT t0.Cardcode as "Customer#"
     , t0.Cardname as "Customer Name"
     , t0.DocNum as "SO #"
     , t1.ItemCode
     , t0.DocDate
     , t0.DocDueDate
     , t1.OpenQty
     , t1.OpenQty*T1.price AS 'Open Value'
     , t2.onhand
     , ISNULL((select max(ISNULL(U_Your_UDF_I_Dont_Know_The_Name, DocDueDate)) 
               from OPOR
                    inner join POR1 on OPOR.DocEntry = POR1.DocEntry
                                   and POR1.ItemCode = t1.ItemCode)
             , DATEADD(YEAR, 99, GETDATE())) AS [Next Delivery Date]
FROM dbo.ORDR t0
     INNER JOIN dbo.RDR1 t1 on t1.docentry = t0.docentry
     INNER JOIN OITM t2 on t1.itemcode=t2.itemcode
WHERE t1.LineStatus = 'O' AND t1.OpenQty > 0

Please note that this solution makes some assumptions on how the UDF is used and its content is maintained.

Regards,

Johan