join.. - 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

join..

Author

Topic

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-07-30 : 06:57:54

Hi
I am trying to join 2 tables,
one has code as its primary key (int)
i am comparing it to part of a field
of another table that will have a code
as part of the string..

so ex
table A
CODE (int)
NAME (varchar)

Table B
names (varchar) example 'dan12jui' - the 4th and 5th chars are ALWAYS numbers..

join is
[CODE]
A.CODE = substring(B.names,4,2)
[/CODE]

and i've tried

[CODE]
A.CODE = CAST(substring(B.names,4,2) as int)
[/CODE]

and

[CODE]
A.CODE = CONVERT(int,substring(B.names,4,2))
[/CODE]

But i still get an error :

Server: Msg 245, Level 16, State 1, Line 2
Syntax error converting the varchar value 'y ' to a column of data type int.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-30 : 07:04:39

You have some invalid data. Can you post some more sample data?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 07:22:16

The error clearly suggests that part you extracted contains character y which it cant convert to int. so i dont think your assumption that 4th & 5th character will be a number is correct.

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-07-30 : 08:15:58

Well i did a distinct select of substring(B.names,4,2) and it is always numeric ..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-30 : 08:24:15

Then where do you think the error message originates from?

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-30 : 08:24:56

quote:
Originally posted by pazzy11

Well i did a distinct select of substring(B.names,4,2) and it is always numeric ..

Can you post the full code you used?

Madhivanan

Failing to plan is Planning to fail

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-07-31 : 09:02:05

FULL CODE of QRY not working

[CODE]
select

s.name as category,
si.site as site,
--substring(eu.usr_name,4,2) as site,
eu.usr_name as agent,
avg(a.duration) as AHT_TOTAL
from action a, interaction i, easyBO..skill s,
easyBO..e_user eu,sites si
where
-- joins --
a.interaction_id = i.code and
i.skill_id = s.code and
a.agent_id = eu.code and
a.agent_id != 698 and -- not itcorp
-- filters --
i.skill_id is not null and
a.type_desc_id in (51,52) and
a.duration is not null and
a.agent_id != 698 and -- not itcorporate..
i.code in
(
select code from interaction where status in(9,20) and
dateadd(second, duration, start_moment) between '2008-06-01' and '2008-06-02'
)
--and i.code not in
--(select * from #T4) -- we want one shots...

and
si.code = cast(substring(eu.usr_name,4,2) as int)
group by s.name,si.site,eu.usr_name
[/CODE]
************************************************************
************************************************************
************************************************************
************************************************************
This qry below is the same except it's using site code taken
from the agent name, (no join with site)
and it works
[CODE]

select

s.name as category,
substring(eu.usr_name,4,2) as site,
eu.usr_name as agent,
avg(a.duration) as AHT_TOTAL
from action a, interaction i, easyBO..skill s,
easyBO..e_user eu
where
-- joins --
a.interaction_id = i.code and
i.skill_id = s.code and
a.agent_id = eu.code and
a.agent_id != 698 and -- not itcorp
-- filters --
i.skill_id is not null and
a.type_desc_id in (51,52) and
a.duration is not null and
a.agent_id != 698 and -- not itcorporate..
i.code in
(
select code from interaction where status in(9,20) and
dateadd(second, duration, start_moment) between '2008-06-01' and '2008-06-02'
)
--and i.code not in
--(select * from #T4) -- we want one shots...

GROUP BY s.name,substring(eu.usr_name,4,2),eu.usr_name
[/CODE]

I would like to post some sample data ..
i cant attach a file ..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-31 : 09:23:04

See what you get

Select usr_name,substring(usr_name,4,2) from easyBO..e_user
where substring(usr_name,4,2) like '%[^0-9]%'

Madhivanan

Failing to plan is Planning to fail

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-07-31 : 09:44:46

same error ..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-31 : 09:51:36

quote:
Originally posted by pazzy11

same error ..

Did you execute this?

Select usr_name,substring(usr_name,4,2) from easyBO..e_user
where substring(usr_name,4,2) like '%[^0-9]%'

Madhivanan

Failing to plan is Planning to fail

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-07-31 : 10:04:41

same error as it needs to be numeric in the 4th AND 5th ..
so something like
where (
substring(usr_name,4,1) like '%[^0-9]%' AND
substring(usr_name,4,2) like '%[^0-9]%'
)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-31 : 10:07:56

quote:
Originally posted by pazzy11

same error as it needs to be numeric in the 4th AND 5th ..
so something like
where (
substring(usr_name,4,1) like '%[^0-9]%' AND
substring(usr_name,4,2) like '%[^0-9]%'
)

I asked you to execute just this query

Select usr_name,substring(usr_name,4,2) from easyBO..e_user
where substring(usr_name,4,2) like '%[^0-9]%'

and see what you get

Madhivanan

Failing to plan is Planning to fail

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-07-31 : 10:10:56

ah yes !!

this seems to give me the problematic cases ...
does the ^ symbol mean "not like" =?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-31 : 10:16:51

quote:
Originally posted by pazzy11

ah yes !!

this seems to give me the problematic cases ...
does the ^ symbol mean "not like" =?

Yes

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources