Skip to Content
0

Adding UDF To Query

Sep 19, 2017 at 08:52 AM

32

avatar image
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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

avatar image
Former Member Sep 19, 2017 at 11:33 AM
-1

Thank you Johan,

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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

0
Johan Hakkesteegt Sep 19, 2017 at 10:44 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded