From: "Carl Heaton" Subject: Re: dynamic sql or not? that is the question Date: 2000/08/08 Message-ID: <8mpcg1$th$1@taliesin2.netcom.net.uk>#1/1 References: X-Priority: 3 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600 X-Complaints-To: abuse@corp.netcom.net.uk X-Trace: taliesin2.netcom.net.uk 965752129 945 212.2.6.140 (8 Aug 2000 16:28:49 GMT) Organization: (Posted via) GTS Netcom. X-MSMail-Priority: Normal NNTP-Posting-Date: 8 Aug 2000 16:28:49 GMT Newsgroups: comp.databases.oracle.tools John, Maybe you could you do a (C) loop in Pro*C and repeat the following for each keyword in the list (pass each one in (e.g. from a C array) as "bind_var_keyword"):- select id, count(id) into :bind_var_id, :bind_var_count from keywords where keyword like :bind_var_keyword||'%'; The results of each select (or better still cursor open/fetch/close) could then be stored back in 2 C arrays : Not beyond the wit of man to then sort the results if that's what you need. But then again, you could use dynamic SQL if you want to do it all in one database 'hit' and let Oracle do the sorting for you. Depends if you're more comfortable with C or Dynamic SQL (I'm guessing the former). Having said that, dynamic SQL isn't that hard : it's mostly about composing a string which contains the SQL statement (plus some stuff to do with bind variables). I did it a while ago in Pro*Fortran which I presume is similar (in principle) to Pro*C. Carl. "John T" wrote in message news:vLTj5.13972$c8.3949530@typhoon-news1.southeast.rr.com... > I am not a PROC or SQL wizard but I believe that the only way to accomplish > what I want is dynamic sql. I am developing a PROC application with 8.0.5. > My program is being passed a list of keywords to search for. I am trying to > look up titles of documents based on the number of hits on the keywords. If > I was writing the sql myself it would look something like: > > select id, count(id) from keywords > where keyword like 'keyword1%' or keyword like 'keyword2%' ...or keword like > 'keywordn%' > group by id > order by count(id) desc; > > From my understanding of PROC, I need to use method 4 to generate the > dynamic where clause. Am I nuts and can someone give me an example? > > Also, I will then need a dynamic sql statement that looks like: > > Select * from titles where id in (list from above query). > > Thanks in advance for your help. > > --John > jlt1@mediaone.net > > > > >