CODEWITHSUNDEEP
dynamics-ax-2009axapta
piku
piku

Reputation: 613

function in where clause+ axapta2009

I am having Illegal use of WHERE expression for the following statement

select dateField from tableName
where dayname(dayofwk(tableName.dateField)) like 'sunday';

Pls help

Upvotes: 0

Views: 930

Answers (3)

Gyan
Gyan

Reputation: 1

you can't use any function on any field of the same table for which you are using the query

Upvotes: 0

user89974
user89974

Reputation:

Anthony is right, of course. However, there are still at leat 2 options to acheive the same result.

  • Create a new integer field in your table. This field should store the return value of the dayOfWk() function. Later you can easily query this table.

  • Second option - create a View inside AX and use a computed column feature toghether with the datePart() SQL Server function. Something like datepart(dw, tableName.DateField) should do it.

The first option will probably result in better performance.

Upvotes: 3

AnthonyBlake
AnthonyBlake

Reputation: 2354

You cannot use a function in a where clause, or in any select statement

Upvotes: 0

Related Questions