user defined function - error converting data type when used in a view

user defined function - error converting data type when used in a view

Post by Robert Pasca » Mon, 17 Sep 2001 23:11:04



I have a user defined function which is passed a numeric and then
returns another numeric (a range lookup).

When used in a view, this returns everything as expected.

I then try to limit the view by the returned value (select * from
myview where maxrange >= 5), but I get an error 'Converting nvarchar
to numeric'.

Problem is, that I know it is numeric.

My question is - can I do select from a view on the result of a
function (I think YES).... my second problem is that if I do something
like (select * from myview where somecolumn=1234567 and myvalue >= 5)
this will work.

I can run this sucessfully on my development SQL Server 2000, but the
production database (which I created my scripts from the development
database) do not work. I am confused at the differences as the
databases should be identical.

Anyone got any ideas?

 
 
 

user defined function - error converting data type when used in a view

Post by BP Margoli » Mon, 17 Sep 2001 23:23:15


Robert,

Do you have **any** of the data stored as nvarchar? Even if you are
confident that the actual data is numeric, perhaps a non-numeric slipped in
accidentally. This is the single most likely possibility. Perhaps you can
run through nvarchar columns checking them with the IsNumeric function to
identify any accidentally non-numeric values.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> I have a user defined function which is passed a numeric and then
> returns another numeric (a range lookup).

> When used in a view, this returns everything as expected.

> I then try to limit the view by the returned value (select * from
> myview where maxrange >= 5), but I get an error 'Converting nvarchar
> to numeric'.

> Problem is, that I know it is numeric.

> My question is - can I do select from a view on the result of a
> function (I think YES).... my second problem is that if I do something
> like (select * from myview where somecolumn=1234567 and myvalue >= 5)
> this will work.

> I can run this sucessfully on my development SQL Server 2000, but the
> production database (which I created my scripts from the development
> database) do not work. I am confused at the differences as the
> databases should be identical.

> Anyone got any ideas?


 
 
 

user defined function - error converting data type when used in a view

Post by Robert Pasca » Tue, 18 Sep 2001 07:25:31


Thanks,

I trecked back multiple levels behind the view, and systematically
checked each column, and indeed, one of the columns was an nvarchar,
and it had a non-numeric in there (old data conversion).

I've since cleaned the data, and it's great.
Thanks for your pointer!

Rob


>Robert,

>Do you have **any** of the data stored as nvarchar? Even if you are
>confident that the actual data is numeric, perhaps a non-numeric slipped in
>accidentally. This is the single most likely possibility. Perhaps you can
>run through nvarchar columns checking them with the IsNumeric function to
>identify any accidentally non-numeric values.

 
 
 

1. Compile error: User-defined data type not defined!

Hi all,

I hope that someone can help me. Every time I compile my code I get an error on the line where it says:

    dim dbs as database

The error message is "Compile error: User defined type not defined". Ihave no problem in Access 95, but in Access 2000 this happens. Please help!

Thanks a million in advance!

f

Here's the code:

Private Sub FillOptions()
' Fill in the options for this switchboard page.

    ' The number of buttons on the form.
    Const conNumButtons = 8

    Dim dbs As database
    Dim rst As Recordset
    Dim strSQL As String
    Dim intOption As Integer

    ' Set the focus to the first button on the form,
    ' and then hide all of the buttons on the form
    ' but the first.  You can't hide the field with
    ' the focus.
    Me![Option1].SetFocus
    For intOption = 2 To conNumButtons
        Me("Option" & intOption).Visible = False
        Me("OptionLabel" & intOption).Visible = False
    Next intOption

    ' Open the table of Switchboard Items, and find
    ' the first item for this Switchboard Page.
    Set dbs = CurrentDb()
    strSQL = "SELECT * FROM [Switchboard Items]"
    strSQL = strSQL & " WHERE [ItemNumber] > 0 AND
    [SwitchboardID]=" & Me![SwitchboardID]
    strSQL = strSQL & " ORDER BY [ItemNumber];"
    Set rst = dbs.OpenRecordset(strSQL)

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.166 / Virus Database: 79 - Release Date: 20/06/00

2. DB2 UDB PE for Linux 7.2 install problems (segfaults)

3. call a user defined function from another user-defined function

4. trunc

5. Convert to user-defined data types

6. Dynamic SQL

7. ORA-3113 using views and user-defined types

8. Why can't I use "localhost

9. Difference between view and subquery using user-defined types

10. Advice on Views?: dynamic SQL, using cursors, inline user-defined functions

11. User-defined data type not defined

12. Help!!-NEWBIE - Application Wizard -(WROX BOOK) Error- User defined type not defined

13. Error: User-defined type not defined