Still having the problem, - 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

Still having the problem,

Author

Topic

goligol
Posting Yak Master

128 Posts

Posted - 2011-10-31 : 12:58:27

I have the following table where the type of the variables in two tables are float (as I imported them from Excel)
Eventhough I tried to convert them to varchar but still in the following query I dont get through result:

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

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

a part of results is presented below which is totally wrong (shouldnt show the NAICS97 where the first 6 digit is not equal to naics:

hs naics NAICS97 NAICS97 naics8_02
0810400030 11133403 111110 111110 NULL
0810400040 11133406 111110 111110 NULL
0810500000 11133909 111110 111110 NULL
0810600000 11133913 111110 111110 NULL
0810902530 11133913 111110 111110 NULL
0810902590 11133406 111110 111110 NULL
0810904000 11133913 111110 111110 NULL
0810904500 11133913 111110 111110 NULL
0811100020 311411 111110 111110 NULL
0811100050 311411 111110 111110 NULL
0811100060 311411 111110 111110 NULL
0811100070 311411 111110 111110 NULL
0811202020 311411 111110 111110 NULL
0811202040 311411 111110 111110 NULL
0812200000 311421 111110 111110 NULL
0812901000 311421 111110 111110 NULL
0813202000 311423 111110 111110 NULL
0813300000 311423 111110 111110 NULL
0813401000 311423 111110 111110 NULL
0813500040 11133505 111110 111110 NULL
0813500060 311423 111110 111110 NULL

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-31 : 13:07:30

You are displaying NAICS97 twice followed by your derived column named naics8_02
- doesn't match the query but is correctly showing naics8_02 as null where they don't match (all of these)
Also - do you need a join condition for the two tables.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

goligol
Posting Yak Master

128 Posts

Posted - 2011-10-31 : 13:16:45

sorry one of the columns is NAICS97 and the other one is NAICS02, But I dont know since it is not matching the query why it is printing them where not the same as first 6 digits of naics.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-31 : 13:46:44

the output looks fine. all naics8_02 are having NULL values which is as per case statement you've written (none of them has first 6 chars same and hence NULL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-31 : 13:49:15

It's producing null where they are not the same - I don't see what the problem is.

if left(naics,6) <> naics97 then naics8_02 = null]
That seems to be what you have - none of the test columns match so all of the naics8_02 are null.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

goligol
Posting Yak Master

128 Posts

Posted - 2011-10-31 : 14:16:14

the size of t2 table is way larger that t1. there are so many rows where if left(naics,6) == naics97, which still returns NULL:((((((

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-01 : 04:21:36

quote:
Originally posted by goligol

the size of t2 table is way larger that t1. there are so many rows where if left(naics,6) == naics97, which still returns NULL:((((((

show one such data

also are you sure you dont any invisible characters also present in it like ' ' or char(13) or char(10) etc

Is the collation used by column case sensitive?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-01 : 05:55:27

Can you post some examples - best is to populate a table variables to show the issue.
I suspect it is because you have a cartesian product and so are getting multiple rows for each in either table. Some will have the result you are looking for.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-01 : 06:01:42

declare @hs2naics8_import_2007 table (hs int, naics int)
declare @Concordance_97_to_2002 table (NAICS97 int, NAICS02 int)

insert @hs2naics8_import_2007 select 0810400030, 11133403
insert @hs2naics8_import_2007 select 0810400030, 11111003
insert @Concordance_97_to_2002 select 111110, 1234
insert @Concordance_97_to_2002 select 111334, 5678

select t1.hs
,t1.naics
,t2.NAICS97
,t2.NAICS02
,naics8_02 = case
when LEFT ((cast (t1.naics as varchar)),6) = (cast (t2.naics97 as varchar)) then (cast (t2.NAICS02 as varchar))
else null
end
FROM @hs2naics8_import_2007 t1, @Concordance_97_to_2002 t2

result
hs naics NAICS97 NAICS02 naics8_02
----------- ----------- ----------- ----------- ------------------------------
810400030 11133403 111110 1234 NULL
810400030 11111003 111110 1234 1234
810400030 11133403 111334 5678 5678
810400030 11111003 111334 5678 NULL

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

goligol
Posting Yak Master

128 Posts

Posted - 2011-11-01 : 11:38:48

I am running the following query and it is empty:

declare @hs2naics8_import_2007 table (hs int, naics int)
declare @Concordance_97_to_2002 table (NAICS97 int, NAICS02 int)

select t1.hs
,t1.naics
,t2.NAICS97
,t2.NAICS02
,naics8_02 = case
when LEFT ((cast (t1.naics as varchar)),6) = (cast (t2.naics97 as varchar)) then (cast (t2.NAICS02 as varchar))
else null
end
FROM @hs2naics8_import_2007 t1, @Concordance_97_to_2002 t2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-01 : 11:47:02

quote:
Originally posted by goligol

I am running the following query and it is empty:

declare @hs2naics8_import_2007 table (hs int, naics int)
declare @Concordance_97_to_2002 table (NAICS97 int, NAICS02 int)

select t1.hs
,t1.naics
,t2.NAICS97
,t2.NAICS02
,naics8_02 = case
when LEFT ((cast (t1.naics as varchar)),6) = (cast (t2.naics97 as varchar)) then (cast (t2.NAICS02 as varchar))
else null
end
FROM @hs2naics8_import_2007 t1, @Concordance_97_to_2002 t2

you're just creating two tables and querying without adding any data. thats why you get empty resultset. populate the tables with your required data and then try

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources