Views - Error Message 248

Question

Post reply

Views - Error Message 248

valepuffo

SSC Enthusiast

Points: 136
More actions
August 28, 2008 at 8:18 am

#70743

Good morning!
This is my first time in the forum, so I'm not sure I'm doing the things in the correct way. I hope so.
My problem is connected with a complex view in the db.
The view alone works well. When I add a where condition, it works well. When I add a second condition joined with 'and', it works well. But when I change 'and' with 'or', I receive the following error:
Message 248, level 16... The conversion of the '2222222222222'. ls' overflowed an int column. Maximum integer value exceeded.
I've looked in the db for this '2222222222222' value, but I' ve found nothing. The problem is connected with a char(2) column not null.
I can't understand what the problem could be.
Is there anyone who has an idea to solve it?
Thank you very much.
(Sorry if my English is not always correct)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Joel Ewald SSCertifiable Points: 6120 More actions · Answer 1

it sounds like the second condition you added is an integer and the data is char/varchar

IE if you are doing something like

DECLARE @Parm int

SET @Parm = 22

SELECT * FROM view where Col = @val

OR Col2 = @Parm --second condition

change the parm to char / varchar.

This would be easier to answer if we knew the data structure and where clause.

It could also be a join within the view

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

morning!
This is my first time in the forum, so I'm not sure I'm doing the things in the correct way. I hope so.

Valepuffo read the article given in my signature block and then following it ask your question again and then many people can and will try to assist you in solving the problem

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

valepuffo SSC Enthusiast Points: 136 More actions · Answer 3

This is the query that produced the error:

SELECT DISTINCT * FROM dbo. vBOOKW_SEARCH WHERE

(TRANSP_MODE_LANG_ID = 'en' OR TRANSP_MODE_LANG_ID IS NULL)

This column TRANSP_MODE_LANG_ID is the column LANG_ID of the following table:

CREATE TABLE [dbo].[CAT_TRANSPMODE_NLS]

(

[TRANSPORTMODE_KY] [int] NOT NULL,

[LANG_ID] [char](2) COLLATE Latin1_General_CS_AS NOT NULL,

[TRANSPORTMODE_DESC] [varchar](50) COLLATE Latin1_General_CS_AS NULL,

PRIMARY KEY CLUSTERED

([TRANSPORTMODE_KY] ASC,[LANG_ID] ASC)

WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

)

ON [PRIMARY]

Text data:

INSERT INTO PTTR.CAT_TRANSPMODE_NLS (TRANSPORTMODE_KY, LANG_ID, TRANSPORTMODE_DESC)

SELECT '1','en','Port/Door' UNION ALL

SELECT '1','it','Port/Door' UNION ALL

SELECT '2','en','Door/Door' UNION ALL

SELECT '2','it','Door/Door' UNION ALL

SELECT '3','en','Door/Port' UNION ALL

SELECT '3','it','Door/Port' UNION ALL

SELECT '4','en','Port/Port' UNION ALL

SELECT '4','it','Port/Port'

My problem now is that the view vBOOKW_SEARCH is very complex: it's made by about 30 'left outer join' between tables and other views. It would be simplier if I colud give you the whole db, but it is part of a project at work, so I can't.

Thanks for your help!