beginner person but tough SQL problem

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

SQL Server Development (2000)

beginner person but tough SQL problem

Author

Topic

jjasper
Starting Member

25 Posts

Posted - 2008-04-03 : 09:46:46

Two tables

One that has a field called "labelname" (about 2 million records)
asprin 50 tab
aspirin liquid
aspirin 30 tab
...

One table has a “like” field and a quantity field (about 200 records)

checkme quantity

asp%30% 7
lotion% 20
...

I need to loop through each labelname and if it matches the “like” field, do a calculation on the quantity field

I can do it with ASP, but need to see if it can be done in one query statement - is it possible ?

Thank you

John

jjasper
Starting Member

25 Posts

Posted - 2008-04-03 : 10:05:29

Sorry - I posted to wrong forum - I am re-posting to T-SQL 2000 forum

John

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-03 : 10:09:18

Not sure this works but you can try it:-

SELECT t1.labelname,SUM(t2.quantity)
FROM FirstTable t1
CROSS JOIN SecondTable t2
WHERE PATINDEX('%'+ t2.checkme + '%',t1.labelname)>0
GROUP BY t1.labelname

jjasper
Starting Member

25 Posts

Posted - 2008-04-03 : 10:15:50

Thank you for responding I am working on your answer, my first result was the following:

The sum or average aggregate operation cannot take a char data type as an argument.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-03 : 10:18:06

why do you have quantity as char datatype?

jjasper
Starting Member

25 Posts

Posted - 2008-04-03 : 10:24:01

Well, basically because it was a mass import from a CDROM and I am not smart enough to have imported it as a number...

I saw that it was a char so I (cast as decimal) - and it is running now - so I am hopeful I get something good back - I wasn't totally clear in my explanation and am amazed you so clearly understood what I wanted...

This seems to be taking quite a bit of time but probably so because of the table size...

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-04-03 : 11:13:30

...This seems to be taking quite a bit of time but probably so because of the table size...
or it could be becauuse you don't have proper (any) indices on your 2 tables....what indices are in place?

jjasper
Starting Member

25 Posts

Posted - 2008-04-03 : 11:34:53

ummmm.... none - I actully stopped the (still running) query after 50 minutes.

Ok so I should build and index before trying again.

Can you tell me best way to build an index on the table? - sorry if very basic question -just want to learn correct way....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources