0

By referring this i have created a udf to apply date filter ,

Convert TimeScript to Date in Azure CosmosDB SQL Query.

SELECT udf.convertTime(c.DatetimeEpoch) as CommandDate FROM c where c.columnH='acb' and c.CommandDate = '09/21/2019',

But this query doesnot work when i gave the udf out put in where clause.

jubi
  • 569
  • 1
  • 10
  • 34
  • It wont, because your "where" clause is based on data in the table, not the subset of data selected by your query. You would need to use something like a subquery, or a cte, or a temp table or a table variable to hold the results, and then query those separately. – Unicorno Marley Sep 22 '19 at 03:28
  • After adding UDF to where clause it worked fine. As i am new to cosmos db not sure about the performnace. Which is the best practice out of 1) Use C# logic for filtering 2) UDF as i have written alreasy – jubi Sep 23 '19 at 04:27

2 Answers2

0

I think @Unicorno Marley has explained clearly why your sql didn't work.

You can modify your sql as below:

SELECT udf.convertTime(c.DatetimeEpoch) as CommandDate FROM c where c.columnH='acb' and udf.convertTime(c.DatetimeEpoch) = '09/21/2019'

EDIT: You can now use sub-query to avoid repeat execution of UDFs

SELECT s.CommandDate 
FROM (
    SELECT udf.convertTime(c.DatetimeEpoch) AS CommandDate 
    FROM c 
    where c.columnH = 'acb'
) s
WHERE s.CommandDate = '09/21/2019'

Ideally though you should perform this mapping client side instead of server-side UDFs, because UDF filters in the WHERE clause would be scans.

Aravind Krishna R.
  • 7,885
  • 27
  • 37
Tony Ju
  • 14,891
  • 3
  • 17
  • 31
  • @Unicorno Marley Which is the best option UDF or filtering through C# code? – jubi Sep 23 '19 at 04:11
  • @jubi No documentation indicates that this will affect performance. Here is an document about performance tips for .net sdk. Hope it helps. – Tony Ju Sep 23 '19 at 06:00
0

In CosmosDb, you can't use the alias CommandDate in the where clause, unlike in MSSQL. You have to repeat the udf again in the where clause as @Tony Ju answered.

subseven
  • 89
  • 1
  • 8