Error with UDF in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Error with UDF in SQL

Author

Topic

fishbait01
Starting Member

2 Posts

Posted - 2006-01-04 : 15:07:53

Environment: SQL SERVER 2000

Hi,

I have a DW application that requires generating different columns based on logic using the product number and a couple of other parameters. Here is an example:

Input
Product Number: ABC1
Product Type: 1234
Division Name: ZZA

Output
Family: Hard Shell
Series: A series
Range: +/- 5.0
Category: Retail
Sales Type: Resale Sales

The output values are determined by hard coded logic. The logic does not contain any table reads. This data must be loaded into a 3rd party OLAP Cube using a simple SQL statement using a GROUP BY.

I created a UDF receiving 4 parms and returning a nvarchar. Here is an example of the SELECT statement using this UDF:

SELECT
…
ProdNo
ProdType,
Division,
ProdSect_fns(Division, ProdNo, ProdType , 'FAMILY') as ProdFamily
ProdSect_fns(Division, ProdNo, ProdType , 'SERIES') as ProdSeries
ProdSect_fns(Division, ProdNo, ProdType , 'RANGE') as ProdRange
ProdSect_fns(Division, ProdNo, ProdType , 'CATEGORY') as ProdCategory
ProdSect_fns(Division, ProdNo, ProdType , 'GROUPING') as ProdGrouping
FROM InvoiceTable

Problem:

If I use a simple SQL Statement (without GROUP BY) this works fine but as soon as I have a GROUP BY, the function ProdSect_fns returns the same value for all 5 columns.

Second Problem:

I tried testing a workaround by adding theses 5 columns to the DW InvoiceTable and use the DW Loading Stored Procedure to populate these colums but when I added the function in the INSERT INTO or UPDATE, it returns an error: Arithmetic overflow error converting expression to data type nvarchar?

Any ideas what is causing my problem?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-04 : 15:31:42

Problem1 :

Try
Select * from
(SELECT
…
ProdNo
ProdType,
Division,
ProdSect_fns(Division, ProdNo, ProdType , 'FAMILY') as ProdFamily
ProdSect_fns(Division, ProdNo, ProdType , 'SERIES') as ProdSeries
ProdSect_fns(Division, ProdNo, ProdType , 'RANGE') as ProdRange
ProdSect_fns(Division, ProdNo, ProdType , 'CATEGORY') as ProdCategory
ProdSect_fns(Division, ProdNo, ProdType , 'GROUPING') as ProdGrouping
FROM InvoiceTable ) as Qry1
group by ProdNo, ProdType, Division, ProdFamily, ProdSeries, ProdRange, .....

fishbait01
Starting Member

2 Posts

Posted - 2006-01-04 : 15:45:00

Tried your suggestion but I'm still get the same problem (same value for all 5 columns calling the UDF)

There are many solutions to a problem. Your imagination is your limit!

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-04 : 20:27:27

Can u please give the function ProdSect_fns() along with some sample data in the table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources