hi all. i am facing an problem in a query i have to retreive data and my the table that i have are as bellow.
job

JOB_C PICKUP_DATE PICKUP_ADDRESS DROP_DATE DROP_ADDRESS FARE METER_READ TAXI_NO BATCH CUST_ MILEAGE
----- --------------------------------------------------------------------------- -------------------- --------------------------------------------------------------------------- -------------------- ---------- ---------- ---------- ----- ----- ----------
j001 20-DEC-04 04.15.12 24 julia court 20-DEC-04 04.15.12 17gary road 21 21212122 RTY09218 f001 cn001 14
j002 18-NOV-04 01.15.12 bedford road 01-NOV-04 04.12.30 17gary road 21 21212122 RTY09218 f001 cn001 10
j003 04-NOV-04 21.30.12 king street 12-FEB-04 04.12.30 17gary road 21 21212122 RTY09218 f002 cn002 18
j004 01-DEC-04 09.15.32 kinne road 01-DEC-04 04.12.30 17gary road 21 21212122 RTY09218 f003 cn003 8
j005 02-DEC-04 09.15.32 kinne road 02-DEC-04 04.12.30 17gary road 21 21212122 ASDD19018 f004 cn003 13
j006 03-DEC-04 09.15.32 asswale road 04-DEC-04 04.12.30 89 market street 21 45422121 DESR10928 f005 cn004 15
j007 04-DEC-04 09.15.32 13 asswale road 04-DEC-04 04.12.30 58 george street 21 16353121 QWEQ1232 f006 cn005 16
j008 05-DEC-04 09.15.32 13 asswale road 05-DEC-04 04.12.30 24stafford street 21 14353121 AWEQ1232 f007 cn006 17

and the other table is customer
CUST_FIRSTNAME CUST_LASTNAME CUST_ CUST_TEL CHARGES
-------------------- -------------------- ----- ---------- ----------
peter serry cn002 122383747 12.34
harry potter cn001 19282837 4.34
jenny lewis cn003 928237837 3.35
david waugh cn004 12273636 12.35
tom alter cn005 198297837 5.45
rob littlejon cn006 1829832773 7.87
johm abraham cn007 9763297338 21.72
linda kennie cn008 1827373738 2.5
kennie denis cn009 778263636 6.65
mark gouhter cn010 9283837744 3.78
peter serry cn011 122383747 10.34
the query that i have is

The names of clients who hired a taxi on a given day

my code is
DECLARE
     CURSOR c1 IS
          SELECT * FROM customer c
        WHERE c.cust_no IN
                ( SELECT j.cust_no FROM job j WHERE
                   to_char(Pickup_date,'dd-mon-yy')='&given_date');
    client_record c1%ROWTYPE;
BEGIN
     OPEN c1;
        LOOP
         FETCH c1
            INTO client_record;
            Exit when c1%NOTFOUND;
             dbms_output.put_line (client_record.cust_firstname);
          END LOOP;
    CLOSE c1;
END;

i dont know but it is not working. please try to help me. when output screen comes i am not able to type anything as well. thank you


happysingh

Try this,

my code is
DECLARE
get_date varchar2(30) := '&given_date';
     CURSOR c1 IS
          SELECT * FROM customer c
        WHERE c.cust_no IN
                ( SELECT j.cust_no FROM job j WHERE
                   to_char(Pickup_date,'dd-mon-yy') = to_char(to_date(get_date), ,'dd-mon-yy');
    client_record c1%ROWTYPE;
BEGIN
     OPEN c1;
        LOOP
         FETCH c1
            INTO client_record;
            Exit when c1%NOTFOUND;
             dbms_output.put_line (client_record.cust_firstname);
          END LOOP;
    CLOSE c1;
END;

If it still does not work, post the error here.

Sujit