October 30, 2002 at 2:31 am
I would appreciate it if someone could tell me how to convert the following ASP (VBScript) to a Stored Procedure :-
sql = "SELECT part FROM temp_all_masters"
Set rs = db.Execute(sql)
if NOT(rs.EOF or rs.BOF) then
while NOT(rs.EOF or rs.BOF)
p = Trim(rs("part"))
px = p&"%"
sql = "SELECT part FROM temp_non_masters WHERE part LIKE '"&px&"'"
Set rsx = db.Execute(sql)
if NOT(rsx.EOF or rsx.BOF) then
sql = "INSERT INTO temp_possible_masters (part) VALUES('"&p&"')"
db.Execute(sql)
end if
Set rsx = Nothing
rs.MoveNext
wend
end if
October 30, 2002 at 3:07 am
Can you post the details of the tables and the purpose of the code, so that we can better help you.
October 30, 2002 at 3:16 am
The temp_all_masters tables contains a list of master part codes. eg ABCDEF
The temp_non_masters tables contains a list of part codes which are made up of master-part-code + minor-part-code. eg ABCDEFabcde
(not all master/minors will be in this table).
I want to create a new table temp_possible_masters which contains all the part codes from temp_all_masters where there are corresponding master/minor codes in temp_non_masters.
Edited by - jgroom on 10/30/2002 03:22:02 AM
October 30, 2002 at 3:28 am
Hi , if part has always 6 positions you could try something like this.
insert into temp_possible_masters (part)
select part
from temp_non_masters NM
inner join temp_all_masters AM on
ltrim(rtrim(AM.part)) = left(NM.part,6)
If the number of characters is unknown then you could try something like this:
insert into temp_possible_masters (part)
select part
from temp_non_masters NM
inner join temp_all_masters AM
on nm.col like convert(varchar(50),ltrim(rtrim(AM.col))+ '%')
Edited by - klaas-jan on 10/30/2002 03:40:40 AM
October 30, 2002 at 3:31 am
Unfortunately it doesn't.
October 30, 2002 at 3:41 am
Hi see my last edit
October 30, 2002 at 3:54 am
Thank you <b>VERY</b> much Klaas-Jan - that seems to have worked.
Now I can use that technique for my really big query.
Enjoy your day - Regards John Groom
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply