Urgent:: how to use substr() in x++ query directly

[Preeti Sonambekar]

Hi All,

I am using business connector to run x++ queries. I want to do something
like this:

update_recordset tempaddress
setting zip = substr(custtable.ZipCode,0,4),
join custtable where custtable.accountnum == tempaddress.id;

It gives me error: "illegal use of setting operator".

How can I achieve this? Basically custtable.zipcode can have 67220-1234
(plus 4) and I need to break it to 67220 and 1234 seperatly using x++ query
which can be consumed using .net business connector. Please remember that I
can't set variables using business connector, the way we can do in x++ editor.

Please advice.

Thanks,
Preeti

[SysProg]

This may not be the answer but why do you have a comma after you close substr
function:

update_recordset tempaddress
setting zip = substr(custtable.ZipCode,0,4),<-Here

join custtable where custtable.accountnum == tempaddress.id;

should be:

update_recordset tempaddress
setting zip = substr(custtable.ZipCode,0,4)

join custtable where custtable.accountnum == tempaddress.id;

Do you still get an error?

[SysProg]

I threw the code in a job and its not the comma. I'm not sure how to do this
without using variables. Apologizes, hopefully someone else has some insight.

[Luegisdorf]

Hi Preeti

AX does not support calculated SQL-Expressions such substr(), even T-SQL
does. Therefore this is not possible what you try to do. You have to proceed
the records with 'while select forupdate tempaddress' and an second select on
custtable with the like (*) operator. If a custtable is found, you execute
tempaddress.update(), otherwise not.

I know this is time consuming, but I that's the only way so far.

Best regards
Patrick