SQL Query Issue...

[vb89]

I have the following code and im not sure what is missing or coded wrong here...

Using: Oracle 10g

  open x_cursor;
   loop
      fetch x_cursor bulk collect into x_tab;
      for ii in 1 .. x_tab.count loop
         personID := x_tab(ii).personID;
         if profile_tab.exists (ipersonID) and 
            color_tab.exists(ipersonID) then

utl_file.put_line(file_handle, x_tab(ii).first_name|| ',' ||
                                     x_tab(ii).last_name || ',' ||
                                     profile_tab(ipersonID).weight || ',' ||
                                     profile_tab(ipersonID).birth_year || ',' ||
                                     color_tab(personID).skin || ',' ||;
 end if;
 end loop;                                     

 elsif 
            
            open x_cursor;
            loop
            fetch x_cursor bulk collect into player_tab_pre;
            for ii in 1 .. x_tab.count loop
               ipersonID := player_tab_pre(ii).personID;
               if profile_tab.exists (ipersonID) and 
                  color_tab_pre.exists(ipersonID) then
 utl_file.put_line(file_handle, x_tab(ii).first_name|| ',' ||
                                     x_tab(ii).last_name || ',' ||
                                     profile_tab(ipersonID).weight || ',' ||
                                     profile_tab(ipersonID).birth_year || ',' ||
                                     color_tab_pre(personID).skin || ',' ||
 end if;
      end loop;
      exit when x_cursor%notfound;
                                     
   end loop; 
   close x_cursor;

The reason that i'm basically using the same exact set of code to output data because the color_cursor (color_tab) doesn't has some individuals that the color_cursor_pre (color_tab_pre) has so basically what i'm trying to do is if a individual isn't found in the color_tab to go down a search for them in the color_tab_pre...hope this explaniataion helps. Currently when i try to compile the code is throws an error at the second 'open x cursor'...so i'm assuming im missing some code there.

Edited by: user652714 on Dec 26, 2008 8:55 AM

 

[LKBrwnDBA]

This code seems to be wrong, you have an elseif AFTER the end if, you are opening the cursor twice and only have one 'close' statement. In fact the code you posted is incomplete and cannot be analyzed.

-- Etc --
end if;
 end loop;                                     

 elsif 
-- Etc --

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[ChrisHunt]

Well, that floating [tt]elsif[/tt] is what's going to throw up a syntax error, but it's not the only error in your code.

Why bulk collect the entire cursor on every iteration of the loop? Why do the whole this twice? Here's a better approach...

FOR x IN x_cursor LOOP
  IF profile_tab.exists (x.personID)
  AND color_tab.exists(x.personID) THEN

    utl_file.put_line(file_handle, x.first_name|| ',' ||
                                   x.last_name || ',' ||
                                   profile_tab(x.personID).weight || ',' ||
                                   profile_tab(x.personID).birth_year || ',' ||
                                   color_tab(x.personID).skin || ',' );

  ELSIF profile_tab.exists (x.personID)
  AND color_tab_pre.exists(ipersonID) THEN

    utl_file.put_line(file_handle, x.first_name|| ',' ||
                                   x.last_name || ',' ||
                                   profile_tab(x.personID).weight || ',' ||
                                   profile_tab(x.personID).birth_year || ',' ||
                                   color_tab_pre(x.personID).skin || ',' );

  END IF;
END LOOP;

A cursor for loop is a more straightforward way to loop through the rows of a cursor, and putting the if statement inside the loop means you've only got to go through once.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[SantaMufasa]

VB89,

You can avoid the "END (IF/LOOP/et cetera)..." matching issues/grief if you make a practice of always visually aligning your code constructs (as Chris) did, above) in this fashion:

BEGIN
    FOR...LOOP
        IF ...
            FOR...LOOP
                ...
            END LOOP;
        ELSIF ...
            IF ...
                FOR...LOOP
                    ...
                END LOOP;
            ELSE
                ...
            END IF;
        END IF;
    END LOOP;
END;

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”