Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    9

    Unanswered: select based on group criterion

    Hi

    I am completely new to Oracle PL/SQL. I have a simple question, probably someone here can answer.

    I need to execute two SQL queries which look like :

    SELECT userID FROM USER where name like '%John%';

    SELECT birthdate from USERDETAILS where userID IN (a,b,c,d...);

    The values a,b,c,d etc should be got from the first query. I do not want to do a join on the two tables and the process should be done in two seperate queries(strictly).. So, basically, I need to execute the first one, store the results in an array in PL/SQL and then use the array to run the second query. Searched on the internet for examples, but cudn't find any. Can anyone please give me an outline? First of all, Can an array be used as condition in a SQL statement? If so, How?

    Thanks in advance
    -Chinna

  2. #2
    Join Date
    Oct 2003
    Posts
    16

    Do you need 2 seperate queries really ?

    Oracle supports the neeto subquery syntax so you can combine the queries, with no joins, like this

    SELECT birthdate from USERDETAILS
    where userID IN (SELECT userID FROM USER where name like '%John%');

    The other option is to use a temporary table, and do a join on that table. I'm not sure I understand why you don't want to use a join. Otherwise, you'll want to create a stored procedure... this would look something like this :

    CREATE OR REPLACE
    PROCEDURE get_birthdate (name_match IN VARCHAR(255)) IS
    Emp_id NUMBER;
    birthdate VARCHAR(255);
    CURSOR c1 (match VARCHAR(255)) IS
    SELECT id FROM users
    WHERE name like match;
    CURSOR c2 (birthdate VARCHAR(255)) IS
    SELECT birthdate FROM userDetails
    WHERE id=Emp_id;

    BEGIN
    OPEN c1(name_match);
    LOOP
    FETCH c1 INTO Emp_id;
    OPEN c2(Emp_id);
    LOOP
    FETCH c2 INTO birthdate;
    DBMS_OUTPUT.PUT_LINE(birthdate);
    EXIT WHEN C2%NOTFOUND
    END LOOP
    EXIT WHEN C1%NOTFOUND;
    END LOOP;
    CLOSE c1;
    END;

    Then you would make a call like select get_birthdate('%John%');

    Hope that helps

    Chris
    Come see the BiteSize SQL Tutorial
    http://www.bitesizeinc.net

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •