Datatype Conflict - SQL SELECT & COUNT

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 2008 Forums

Transact-SQL (2008)

Datatype Conflict - SQL SELECT & COUNT

Author

Topic

Tfontaine
Starting Member

3 Posts

Posted - 2012-01-26 : 05:01:58

Hello all,

would be glad if anyone could give me a hint / solution for this.

Situation (simplyfied)
Table t1 material with t1.id, t1.shortcode, t1.desc
Table t2 items with t2.id, t2.longcode, t2.desc

Tables are linked by t2.longcode of an item (eg. "A44D06") always starts with a t1.shortcode (z.B. "A44") of a material.

Resultset should be 3 columns with t1.shortcode, t1.desc and the number of items from t2 that consist of the t1 material:


|shortcode...........|desc......................|item_count........|
|A44.................|tin.......................|14................|
|A45.................|copper....................|7.................|
|A46.................|steel.....................|35................|
...

In Access 2010 SQL I get a datatype conflict when executing this query:


SELECT t1.shortcode, t1.desc, COUNT(t2.id) as item_count 
FROM t1 INNER JOIN t2 ON t1.shortcode = LEFT(t2.longcode, LEN(t1.shortcode)) 
GROUP BY t1.shortcode,t1.desc 
ORDER BY t1.shortcode

Although both t1.shortcode and t2.longcode have the same datatype in the tables (Character/Text), the JOIN condition obviously can't get along with that modified string "LEFT(t2.longcode, LEN(t1.shortcode))". Same error for doing without JOIN and using a simple SELECT from both tables WHERE t1.shortcode = LEFT(t2.longcode, LEN(t1.shortcode)) instead.

Does anyone know a solution for the datatype conflict or even a better approach to get the result?

THANK YOU!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-26 : 05:14:13

Are we talking about Access or T-SQL 2008 now?

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

Tfontaine
Starting Member

3 Posts

Posted - 2012-01-26 : 05:32:24

It's rather executing the above T-SQL statement within Access2010. Sorry if my post is misplaced here.

Kristen
Test

22859 Posts

Posted - 2012-01-26 : 05:46:23

This maybe?

INNER JOIN t2 ON t2.longcode LIKE t1.shortcode + '%'

Tfontaine
Starting Member

3 Posts

Posted - 2012-01-26 : 10:34:33

Thanks to Kristen, you gave me the proper idea. Using a subquery with LIKE it now delivers the desired results.
[CODE]
SELECT t1.shortcode, t1.desc,
(SELECT COUNT(*) FROM t2
WHERE t2.longcode LIKE t1.shortcode + '%') as item_count
FROM t1 ORDER BY t1.shortcode
[/CODE]

Thank you again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources