Creating Functions in SQL server

[Former Member]

HI

i creating functions for specific purpose in the test database...

is it against SAP Support policy?

suggest me...

[Former Member]

Hi,

You can do so only in test database. However, in production db, you can not create such function or SP.

If the fields have the same type, you do not need to change anything. "," in the numeric field is only by a display format. It is not physically there.

Thanks,

Gordon

[Former Member]

HI Gordon & Manvendra

In database also,The udf is storing with ","...

is it possible to assign FMS to UDF which has Type "numeric"?

I tried but it not displaying FMS?

and also i am not able to convert UDF datatype "nvarchar" to "numeric or decimal" ...

I used "cast" function for the conversion which works at backend but at front end its showing the error as

[Microsoft][SQL Server Native Client 10.0][SQL Server]Error converting data type nvarchar to numeric. (CINF)

Please suggest me to solve that...

[Former Member]

Hello,

Make a new UDF with same name and make it Numeric and remove last one.

$[ORDR.DocTotal.Number]

if we write number in sentex then by default FMS pick only number.

Thanks

Manvendra Singh Niranjan

Edited by: Manvendra Singh Niranjan on Jun 29, 2011 7:32 AM

[Former Member]

HI Manvendra

I tried that but unfortunately the FMS is not working when i create in the UDF type "numeric"?

I am not able to see the thet FMS icon when i set automatic FMS...

Edited by: kambadasan on Jun 29, 2011 7:35 AM

[Former Member]

GOTO View >Picker Display select all option

And

GOTO View >Indicator Display

Thanks

Manvendra Singh NIranjan

[Former Member]

HI Manvendra

After defining the UDF as type "Numeric" and applied the below FMS

SELECT T0.[Price]   from itm1 T0  WHERE T0.[PriceList] =1 and  T0.[ItemCode] =$[POR1.ItemCode.0]

It showing the error as "Invalid Numeric value"

[Former Member]

Thanks

[Former Member]

HI

This function remove "," from the value...

i.e if the value stored as "1,000.00" then it will remove the comma and show it as "1000.00"..

It is an UDF value in POR1 table

please suggest...

[Former Member]

HI

this DDF have any calculation on line total or Doc Total and only reporting purpose ?

if only reporting purpose then you can update or having calculation then cant do same.

One thing more you should work on Test database and after update made same transaction means purchase cycle and sale cycle for testing purpose.

Thanks

Manvendra Singh Niranjan

[Former Member]

Hi

I have created one SP which will compare the UDF price(from ITM 1 table through FMS) with the 'price' field in POR1.

If its not equal then the SP will fire...

The SP is firing even when both values are equal...

This is because the "price" field doesnt contain "," but the UDF field contains "," so the SP firing...

Then i used many string functions,cast functions to make it work but it results negative...

so i thought of removing the "," using the user defined functions....

suggest me...

[Former Member]

if you are just going to start entries then you can .

But i have posted then Use replace function in SQL

-

---

SQL Syntax:

REPLACE( string1, string2, string3 )

Parameters:

string1

Any character string or binary expression that can contain the string from string2.

string2

Any character string or binary expression. This string is searching in string1.

string3

Any character string or binary expression. This string replace all occurrence of string2.

-

---

May it will work

Thanks

Manvendra Singh Niranjan

[Former Member]

Hi

But what string i use asl replace in place of ","....

[Former Member]

Hello,

I think you should change data type of your udf in numeric to nvarchar .

try this

[Former Member]

Hello,

what is the use of your function ?

If you are updating SAP database then it is against SAP Support policy.

but if want to get any result but no issue.

Thanks

Manvendra Singh Niranjan