Help wanted for a simple subquery ....? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 wanted for a simple subquery ....?

Author

Topic

somee
Starting Member

5 Posts

Posted - 2010-01-13 : 09:42:21

Dear , when executing following subquery statement alone , I get a list of worker numbers.

SELECT distinct convert(int,substring(primarykey,1,11))
from tb_history where sp1_date > '20090101'
and id = 'D'
and tablename in ('tb_a' , 'tb_b' , 'tb_c', 'tb_d')

in tables a,b,c,d the field primarykey (varchar 255) is a concatenation of a workernumber (limited to 11 characters) and a start date.
The primarykey field can also contain values such as 'COMPANY 12 ' for other tables however.

tb_history is a table used for logging.

So far so good.

However when I perform following query:

select employee_number from tb_employees where
employee_number in
(
SELECT distinct convert(int,substring(primarykey,1,11))
from tb_name where sp1_date > '20090101'
and id = 'D'
and sptablename in ('tb_a' , 'tb_b' , 'tb_c', 'tb_d')
)

The query returns an error: explicit conversion of VARCHAR value 'COMPANY' to a INT field.

Does anyone know how this can be solved??

Kind Regards

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-13 : 09:46:59

Is workernumber the same as employee_number? Is employee_number an integer field or a Varchar field?

somee
Starting Member

5 Posts

Posted - 2010-01-13 : 09:49:58

Yes , employee_number is an int.

workernumber is part of the primary key concatenation (the first 11 characters), and is converted to an int as well

convert(int,substring(primarykey,1,11))

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-13 : 09:53:45

You need to tell it to ignore when the first character is not a number, COMPANY is not convertable...

SELECT distinct convert(int,substring(primarykey,1,11))
from tb_name where sp1_date > '20090101'
and primarykey like '[0-9]%'
and id = 'D'
and sptablename in ('tb_a' , 'tb_b' , 'tb_c', 'tb_d')

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-13 : 09:55:57

Try this:

from tb_name where sp1_date > '20090101'
and id = 'D'
and sptablename in ('tb_a' , 'tb_b' , 'tb_c', 'tb_d')
and substring(primarykey,1,11) like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-13 : 09:56:41

No, you're never too old to Yak'n'Roll if you're too young to die.

somee
Starting Member

5 Posts

Posted - 2010-01-13 : 10:02:47

@webfred and @DP978

I tried both the queries separately , and they show me the right result set.

However this results in the same problem as I had before:

When I use this query in a subquery: it still gives me the error :
"explicit conversion of VARCHAR value 'COMPANY' to a INT field."

It seems to ignore the where clause with includes the tablename in the subquery...

somee
Starting Member

5 Posts

Posted - 2010-01-13 : 10:22:23

anyone?

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-13 : 10:25:59

Try deriving the table first then...

select a.employee_number from tb_employees a
Inner join (SELECT distinct convert(int,substring(primarykey,1,11)) as emp_num
from tb_name where sp1_date > '20090101'
and primarykey like '[0-9]%'
and id = 'D'
and sptablename in ('tb_a' , 'tb_b' , 'tb_c', 'tb_d')) b
on a.employee_number = b.emp_num

See if this works.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-13 : 10:27:06

If your substring(primarykey,1,11) has always leading zeroes then try this:

select employee_number from tb_employees where
right('00000000000'+convert(varchar(11),employee_number),11) in
(
SELECT distinct substring(primarykey,1,11)
from tb_name where sp1_date > '20090101'
and id = 'D'
and sptablename in ('tb_a' , 'tb_b' , 'tb_c', 'tb_d')
)

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-13 : 10:29:45

Oh, if the solution from DP978 works then it is better than mine...because it is faster to join numeric values.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 10:29:55

what about this?


select employee_number from tb_employees where
employee_number in 
( 
SELECT distinct convert(int,substring(primarykey,1,11))
from tb_name 
where sp1_date > '20090101' 
and id = 'D' 
and sptablename in ('tb_a' , 'tb_b' , 'tb_c', 'tb_d')
and isnumeric(substring(primarykey,1,11)) = 1
)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-13 : 10:34:38

quote:

Originally posted by visakh16

what about this?


select employee_number from tb_employees where
employee_number in 
( 
SELECT distinct convert(int,substring(primarykey,1,11))
from tb_name 
where sp1_date > '20090101' 
and id = 'D' 
and sptablename in ('tb_a' , 'tb_b' , 'tb_c', 'tb_d')
and isnumeric(substring(primarykey,1,11)) = 1
)

That should work equal to:
substring(primarykey,1,11) like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

No, you're never too old to Yak'n'Roll if you're too young to die.

somee
Starting Member

5 Posts

Posted - 2010-01-13 : 10:43:40

@visakh16

isnumeric is not a built in function in Sybase....?

Anything Else

@webfred

indeed, joining on numbers is far better....

@DP978
I keep getting syntax errors...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 10:47:11

quote:
Originally posted by somee

@visakh16

isnumeric is not a built in function in Sybase....?

Anything Else

@webfred

indeed, joining on numbers is far better....

@DP978
I keep getting syntax errors...

ah...so you're using sybase?
then try in some sybase forums
this is ms sql server forum.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources