SQL SERVER 2000 Cursur

You would have to insert the returned record into a temp table or table variable inside the cursor

It sounds like you should be able to combine this into a single query.  If you provide your queries I'll see if you can do this without a CURSOR which would save a lot of processing time...CURSORs are evil.

I agree with that...just posted some cursor code because that is what was asked for.  Can probably get away from using one at all right here, unless a proc is to be called from the values.

Here are the two queries. I am simplifying there syntax to make this post shorter: I am very interested tosee if this can be done without a curser.  Thanks for your help so far.

query1:  This returns a varchar field and a date field. About 6000 or so unique records.            
select site,date
from a bunch of joined tables      
where some criteria
order by some critera.

Query2:  Takes site and date from query 1 and returns one record per iteration.                                          
                                            
select distinct site
from a bunch of joins
where site =  varchar value from site in query 1
and date = date value from date in query 1                                                                       
                  

--no problem.

select distinct site
from a bunch of joins

JOIN (
select site,date
from a bunch of joined tables      
where some criteria
) a
on  site =  a.site
and date = a.date    

Why can't you just add another join on the site and date in the first query?

it cant be that simple....I am going to rdp into my sytem and run this proposed solution. I will be back shortly.  Thanks:)

ok..thats not working...IM going to going to post each querie in a separate post.  Post 1 will be the querie  that pulls the master table...and the 2nd post will be the querie that needs the data queried from the 1st.

This elements of the result set of this querie that matter are two fields.  s.site_id and cc.close_date.
This result set is 6000+ records. Each record will need to be used in the where of query 2 unless you can make it work some other cool way.


select s.site_id as [Site ID], s.name as [Site Name],
cc.close_date as [Close Date], g3.title as title,  
g3.x_group as CH1group, g1.title as Status                                                      
   FROM
          clarify..table_case c
               inner join clarify..table_close_case cc  
                   on c.objid = cc.last_close2case                                
               inner join clarify..table_site s  
                   on c.case_reporter2site = s.objid
               inner join clarify..table_gbst_elm g1
                   on c.casests2gbst_elm = g1.objid
                inner join clarify..table_gbst_elm g2
                   on cc.close_rsolut2gbst_elm = g2.objid
                 inner join clarify..table_gbst_elm g3      
                    on c.calltype2gbst_elm = g3.objid
  WHERE    
     c.x_network='ch1'
      AND cc.close_date > '9/1/2007'                                              
     and g1.title='closed'                       
    and g3.title = 'PILOTINST'

ok here is querie 2...which after running against querie 1 6000+ times should return the results I need.
I have used this syntax to indicate where the values from query one are being used-----------value--------

select distinct s.site_id as [Site ID]                                                                
       FROM
              clarify..table_case c
                 inner join clarify..table_site s  
                            on c.case_reporter2site = s.objid
           inner join clarify..table_gbst_elm g1
            on c.casests2gbst_elm = g1.objid
          inner join clarify..table_gbst_elm g3      
            on c.calltype2gbst_elm = g3.objid
         WHERE    
      c.x_network='ch1'
      and s.site_id = -----------------------value from querie 1------------------------      
       and g3.x_group IN ('1') and  
      c.x_dispatch_no>0 and
      g3.title <> 'eqpaudit'
       and c.creation_time > -------------------value from querie 1---------------------------

s.site_id = the site_id from the other querie
c.creation_time = close_date from the other querie

SEe if this works for you....

select distinct s.site_id as [Site ID]                                                                
FROM 
      clarify..table_case c 
         inner join clarify..table_site s  
                    on c.case_reporter2site = s.objid
   inner join clarify..table_gbst_elm g1 
    on c.casests2gbst_elm = g1.objid 
  inner join clarify..table_gbst_elm g3      
    on c.calltype2gbst_elm = g3.objid 
INNER JOIN 
(
	select s.site_id as [Site ID], s.name as [Site Name],
	cc.close_date as [Close Date], g3.title as title,  
	g3.x_group as CH1group, g1.title as Status                                                      
	   FROM 
			  clarify..table_case c 
				   inner join clarify..table_close_case cc  
					   on c.objid = cc.last_close2case                                
				   inner join clarify..table_site s  
					   on c.case_reporter2site = s.objid
				   inner join clarify..table_gbst_elm g1 
					   on c.casests2gbst_elm = g1.objid 
					inner join clarify..table_gbst_elm g2
					   on cc.close_rsolut2gbst_elm = g2.objid 
					 inner join clarify..table_gbst_elm g3      
						on c.calltype2gbst_elm = g3.objid 
	  WHERE    
		 c.x_network='ch1' 
		  AND cc.close_date > '9/1/2007'                                              
		 and g1.title='closed'                       
		and g3.title = 'PILOTINST'
) z ON 
 WHERE    
c.x_network='ch1' 
and s.site_id = z.site_id    
and g3.x_group IN ('1') and  
c.x_dispatch_no>0 and 
g3.title <> 'eqpaudit'
and c.creation_time > z.close_date

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:

slight correction  (accidently put the word ON in there)

select distinct s.site_id as [Site ID]                                                                
FROM 
      clarify..table_case c 
         inner join clarify..table_site s  
                    on c.case_reporter2site = s.objid
   inner join clarify..table_gbst_elm g1 
    on c.casests2gbst_elm = g1.objid 
  inner join clarify..table_gbst_elm g3      
    on c.calltype2gbst_elm = g3.objid 
INNER JOIN 
(
	select s.site_id as [Site ID], s.name as [Site Name],
	cc.close_date as [Close Date], g3.title as title,  
	g3.x_group as CH1group, g1.title as Status                                                      
	   FROM 
			  clarify..table_case c 
				   inner join clarify..table_close_case cc  
					   on c.objid = cc.last_close2case                                
				   inner join clarify..table_site s  
					   on c.case_reporter2site = s.objid
				   inner join clarify..table_gbst_elm g1 
					   on c.casests2gbst_elm = g1.objid 
					inner join clarify..table_gbst_elm g2
					   on cc.close_rsolut2gbst_elm = g2.objid 
					 inner join clarify..table_gbst_elm g3      
						on c.calltype2gbst_elm = g3.objid 
	  WHERE    
		 c.x_network='ch1' 
		  AND cc.close_date > '9/1/2007'                                              
		 and g1.title='closed'                       
		and g3.title = 'PILOTINST'
) z  
 WHERE    
c.x_network='ch1' 
and s.site_id = z.site_id    
and g3.x_group IN ('1') and  
c.x_dispatch_no>0 and 
g3.title <> 'eqpaudit'
and c.creation_time > z.close_date

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:

the last where gives the following error

Server: Msg 156, Level 15, State 1, Line 33
Incorrect syntax near the keyword 'where'.