Link to home
Create AccountLog in
Avatar of Robb Hill
Robb HillFlag for United States of America

asked on

SQL SERVER 2000 Cursur

I am using sql 2000 and have two queries.  The first query will return aproximately 6000 rows.  Each row contains a varchar field and a  date field.

For each row in this query I need to take those two values and use them in the where clause of
another query.

Each time the second query is called from the first query one row will be returned.
I need the resultset to return all of the rows from the second query.  I am assumign they will be stored in a temp table or something as the cursur performs its iteration.  

Please help!!!!
Avatar of chapmandew
chapmandew
Flag of United States of America image

cursor should look something like this:

DECLARE @ID INT

DECLARE  CursorTemplate CURSOR
FAST_FORWARD FOR       
      SELECT ID FROM Table1

OPEN CursorTemplate

SET @InCursor = 1

FETCH NEXT FROM CursorTemplate
INTO      @ID

WHILE (@@FETCH_STATUS = 0)
BEGIN

      SELECT * FROM Table2
      WHERE Field = @ID
      FETCH NEXT FROM CursorTemplate
      INTO      @ID

END

CLOSE CursorTemplate
DEALLOCATE CursorTemplate
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.
Avatar of Robb Hill

ASKER

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

Open in new window

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

Open in new window

the last where gives the following error

Server: Msg 156, Level 15, State 1, Line 33
Incorrect syntax near the keyword 'where'.
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account