How can I improve this convert(int,myColumn) query?

Question

SQL Server 2000

Background

I've got a table that stores miscellaneous meta data about a specific course in my course table. The table is defined:

create table course_prefs {
id int identity not null,
crs_nbr int references course (crs_nbr) not null,
fiscal_yr int not null,
group_name varchar(50) not null,
item_name varchar(50) null,
value varchar(100) not null)

and there are some values like so:

ID    Crs_Nbr  Fiscal_Yr Group_Name        Item_Name     Value
1     5327     2007     StuAchievement     Qualifier     alg
2     5329     2007     StuAchievement     Qualifier     alg
153   2000     2003     LocUCInfo          543           F,0,0
154   2000     2003     LocUCInfo          542           F,0,0
6149  15746    2009     summerAttn         HS            coreClass
6150  12367    2009     summerAttn         HS            coreClass

...and I've begun making views from this prefs table to suit the specific needs. However, when I join to the following view:

CREATE    view loc_uc_info as

select cp.crs_nbr, c.abbr, cp.fiscal_yr, convert(int,cp.item_name) as loc_id
, substring(cp.value,1,1) as subject_area
, substring(cp.value,3,1) as honors
, substring(cp.value,5,1) as can_be_elective
from course_prefs cp join course c on cp.crs_nbr = c.crs_nbr
where cp.group_name = 'LocUCInfo'

The Problem
I get the following error message:

Syntax error converting the varchar value 'HS' to a column of data type smallint.

What I Want

I need to write a query that joins to this view on the loc_id column. This means that both the parent table and the view are joined on columns typed as integers. BUT - the view has both integer and char values in the item_name column thus, I get the syntax error. What can I do to get around this?

Things I've Tried:

1. Using a derived query in place of the view and I get the same error.
2. Creating another view based solely on the uc_loc_info view. Got same error.
3. Using the isnumeric(cp.item_name) = 1 where clause in my loc_uc_info view to restrict the results.

Answer 1

Just from the top of my head: What about creating two views?

One that does the join without converting and another one that just does the conversion on the first view.

Since the first view should only contain numbers in the Item_Name (namely 543 and 542) you will not have the conversion error.

Answer 2

Not really sure what you want the outcome to be but what about using:

case when isnumeric(cp.item_name) = 1 then convert(int,cp.item_name) else null end

instead of just your

convert(int,cp.item_name)

Answer 3

Note: Final working code added below first message.

Can you explain more what you're trying to accomplish with this line in your view?

convert(int, cp.item_name) as loc_id,

Penfold's suggestion seems like a good one.

Here is working code. (Yes, it uses 2005 "sys." tables. Convert those to run on 2000.) It replaces your "loc_id" column with Penfold's suggestion.

Code

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Course')
    DROP TABLE dbo.Course
GO
CREATE TABLE dbo.Course (
    ID          int             not null,   -- identity
    Abbr        varchar(5)      not null,
    Crs_Nbr     int             not null    --references course (crs_nbr)
)
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Course_Prefs')
    DROP TABLE dbo.Course_Prefs
GO
CREATE TABLE dbo.Course_Prefs (
    ID          int             not null,   -- identity
    Crs_Nbr     int             not null,   --references course (crs_nbr)
    Fiscal_Yr   int             not null,
    Group_Name  varchar(50)     not null,
    Item_Name   varchar(50)     null,
    Value       varchar(100)    not null
)
GO
INSERT INTO dbo.Course VALUES (1, 'Crs1', 5327)
INSERT INTO dbo.Course VALUES (2, 'Crs2', 5329)
INSERT INTO dbo.Course VALUES (3, 'Crs3', 2000)
INSERT INTO dbo.Course VALUES (4, 'Crs4', 15746)
INSERT INTO dbo.Course VALUES (5, 'Crs5', 12367)
GO

INSERT INTO dbo.Course_Prefs VALUES (1, 5327, 2007, 'StuAchievement', 'Qualifier', 'alg')
INSERT INTO dbo.Course_Prefs VALUES (2, 5329, 2007, 'StuAchievement', 'Qualifier', 'alg')
INSERT INTO dbo.Course_Prefs VALUES (153, 2000, 2003, 'LocUCInfo', '543', 'F,0,0')
INSERT INTO dbo.Course_Prefs VALUES (154, 2000, 2003, 'LocUCInfo', '542', 'F,0,0')
INSERT INTO dbo.Course_Prefs VALUES (6149, 15746, 2009, 'summerAttn', 'HS', 'coreClass')
INSERT INTO dbo.Course_Prefs VALUES (6150, 12367, 2009, 'summerAttn', 'HS', 'coreClass')
GO

SELECT * FROM dbo.Course
SELECT * FROM dbo.Course_Prefs
GO

IF EXISTS (SELECT * FROM sys.views WHERE name = 'Loc_uc_Info')
    DROP VIEW dbo.Loc_uc_Info
GO
CREATE VIEW dbo.Loc_uc_Info AS
SELECT
    cp.crs_nbr,
    c.abbr,
    cp.fiscal_yr,
    case when isnumeric(cp.item_name) = 1 then convert(int,cp.item_name) else null end  AS loc_id,
    --convert(int, cp.item_name) as loc_id,
    substring(cp.value, 1, 1) as subject_area,
    substring(cp.value, 3, 1) as honors,
    substring(cp.value, 5, 1) as can_be_elective
FROM dbo.Course_Prefs   AS cp
JOIN dbo.Course         AS c ON cp.crs_nbr = c.crs_nbr
--WHERE cp.group_name = 'LocUCInfo'
GO
SELECT * FROM dbo.Loc_uc_Info
GO

Results

         ID Abbr      Crs_Nbr
----------- ----- -----------
          1 Crs1         5327
          2 Crs2         5329
          3 Crs3         2000
          4 Crs4        15746
          5 Crs5        12367

         ID     Crs_Nbr   Fiscal_Yr Group_Name                                         Item_Name                                          Value
----------- ----------- ----------- -------------------------------------------------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------
          1        5327        2007 StuAchievement                                     Qualifier                                          alg
          2        5329        2007 StuAchievement                                     Qualifier                                          alg
        153        2000        2003 LocUCInfo                                          543                                                F,0,0
        154        2000        2003 LocUCInfo                                          542                                                F,0,0
       6149       15746        2009 summerAttn                                         HS                                                 coreClass
       6150       12367        2009 summerAttn                                         HS                                                 coreClass

    crs_nbr abbr    fiscal_yr      loc_id subject_area honors can_be_elective
----------- ----- ----------- ----------- ------------ ------ ---------------
       5327 Crs1         2007        NULL a            g      
       5329 Crs2         2007        NULL a            g      
       2000 Crs3         2003         543 F            0      0
       2000 Crs3         2003         542 F            0      0
      15746 Crs4         2009        NULL c            r      C
      12367 Crs5         2009        NULL c            r      C

Edit: Forgot to include Penfold's code.

---

Final Working Code Based on HLGEM's Suggestion

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Course')
    DROP TABLE dbo.Course
GO
CREATE TABLE dbo.Course (
    ID          int             not null,   -- identity
    Abbr        varchar(5)      not null,
    Crs_Nbr     int             not null    --references course (crs_nbr)
)
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Course_Prefs')
    DROP TABLE dbo.Course_Prefs
GO
CREATE TABLE dbo.Course_Prefs (
    ID          int             not null,   -- identity
    Crs_Nbr     int             not null,   --references course (crs_nbr)
    Fiscal_Yr   int             not null,
    Group_Name  varchar(50)     not null,
    Item_Name   varchar(50)     null,
    Value       varchar(100)    not null
)
GO
INSERT INTO dbo.Course VALUES (1, 'Crs1', 5327)
INSERT INTO dbo.Course VALUES (2, 'Crs2', 5329)
INSERT INTO dbo.Course VALUES (3, 'Crs3', 2000)
INSERT INTO dbo.Course VALUES (4, 'Crs4', 15746)
INSERT INTO dbo.Course VALUES (5, 'Crs5', 12367)
GO

INSERT INTO dbo.Course_Prefs VALUES (1,     5327, 2007, 'StuAchievement', 'Qualifier', 'alg')
INSERT INTO dbo.Course_Prefs VALUES (2,     5329, 2007, 'StuAchievement', 'Qualifier', 'alg')
INSERT INTO dbo.Course_Prefs VALUES (153,   2000, 2003, 'LocUCInfo',      '543',       'F,0,0')
INSERT INTO dbo.Course_Prefs VALUES (154,   2000, 2003, 'LocUCInfo',      '542',       'F,0,0')
INSERT INTO dbo.Course_Prefs VALUES (6149, 15746, 2009, 'summerAttn',     'HS',        'coreClass')
INSERT INTO dbo.Course_Prefs VALUES (6150, 12367, 2009, 'summerAttn',     'HS',        'coreClass')
GO

SELECT * FROM dbo.Course
SELECT * FROM dbo.Course_Prefs
GO

IF EXISTS (SELECT * FROM sys.views WHERE name = 'Loc_uc_Info')
    DROP VIEW dbo.Loc_uc_Info
GO
CREATE VIEW dbo.Loc_uc_Info AS
SELECT
    cp.crs_nbr,
    c.abbr,
    cp.fiscal_yr,
    convert(int,
        case
            when isnumeric(cp.item_name) = 1 then cp.item_name
            else 0
        end
    ) as loc_id,
    substring(cp.value, 1, 1)   as subject_area,
    substring(cp.value, 3, 1)   as honors,
    substring(cp.value, 5, 1)   as can_be_elective
FROM dbo.Course_Prefs   AS cp
JOIN dbo.Course         AS c ON cp.crs_nbr = c.crs_nbr
WHERE cp.group_name = 'LocUCInfo'
GO
SELECT * FROM dbo.Loc_uc_Info
GO

Answer 4

try this

convert(int,case when isnumeric(cp.item_name)= 1 then cp.item_name else null end as loc_id

if that doesn't work try this

convert(int,case when isnumeric(cp.item_name)= 1 then cp.item_name else 0 end as loc_id

Personally I believe somethign is very flawed about your basic design, you shouldn't have numerics and character data in the same column like that. Nor should you have comma delimted values.

And I'm not a fan of views, especially views that get put on top of views as they can kill performance when they can't be properly indexed.