Help with view using CASE - 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 2005 Forums

Transact-SQL (2005)

Help with view using CASE

Author

Topic

chapo
Starting Member

39 Posts

Posted - 2008-04-11 : 08:09:55

I have a view where I'm trying to populate a field base on the values on the USEJSLOAD field. The USEJSLOAD it's a (bit,null) field and there are only two types of values on it they are 'True' or 'False' so if it's true grab the data from one field else the other field. When I try to run the view I get the following error, 'Conversion failed when converting the varchar value '1-1S ' to data type int. Any help would be greatly be appreciated.

(CASE WHEN dbo.ftlibr_base.USEJSLOAD = 1 THEN dbo.trn_transport.TRN_LOADNODERIVED ELSE trn_transport.TRN_DISPATCHNO END)

Sample data:
ftlibr_base
Work_Order | USEJSLOAD
1 | True
2 | False
3 | True

dbo.trn_transport
Work_Order | TRN_LOADNODERIVED | TRN_DISPATCHNO
1 | 1-1S |1
2 | 4-2S |10
3 | 1-30S |2

I would like:

Work_Order | Load #
1 | 1-1S
2 | 10
3 | 3-20S

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-11 : 08:28:03

All the resulting expressions in CASE must be of same data type.

(CASE WHEN dbo.ftlibr_base.USEJSLOAD = 1 THEN dbo.trn_transport.TRN_LOADNODERIVED ELSE cast(trn_transport.TRN_DISPATCHNO as Varchar(10)) END)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

chapo
Starting Member

39 Posts

Posted - 2008-04-11 : 09:53:19

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources