Null printing - 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

General SQL Server Forums

New to SQL Server Programming

Null printing

Author

Topic

goligol
Posting Yak Master

128 Posts

Posted - 2011-10-28 : 15:34:19

I have the following query, I know there are places that
LEFT (t1.naics,6) = t2.naics97 that it can copy the t2.NAICS02 for naics8_02 but it is returning NULL for all naics8_02

select t1.hs
,t1.naics
,t2.NAICS97
,t2.NAICS02
,naics8_02 = case
when LEFT (t1.naics,6) = t2.naics97 then t2.NAICS02
else null
end

FROM [trade].[dev].[hs2naics8_import_2007] t1
,[trade].[dev].[Concordance_97_to_2002] t2

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-28 : 15:57:06

what are their datatypes?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

goligol
Posting Yak Master

128 Posts

Posted - 2011-10-28 : 16:05:15

naics , NAICS97 and NAICS02 are float,

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-28 : 16:18:27

LEFT and numbers doesn't make to much sense

DECLARE @x float; SET @x = 123456789
SELECT LEFT(@x,6)

Maybe you need to cast it as varchar first

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-28 : 16:19:06

And why are you using something as imprecise as float?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

goligol
Posting Yak Master

128 Posts

Posted - 2011-10-28 : 16:43:34

how can I convert all those columns first to character or string?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-03 : 06:37:39

You need to convert to decimal and then to varchar

DECLARE @x float; SET @x = 123456789
SELECT left(cast(cast(@x as decimal(38,2)) as varchar(100)),6)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources