Home » SQL & PL/SQL » SQL & PL/SQL » I need Ideas
I need Ideas [message #191343] Tue, 05 September 2006 16:07 Go to next message
Jolanda
Messages: 68
Registered: September 2004
Member
Hello you all,

I have two tables and I need to get a date field out of one of the tables.

I have a table_a and table_b.

First I need to check in table_b if there is a filled date field. If there is...it should check this with table_a, and the function should return the date from table_a, which is newer than the newest in table_b. So, in this case it should return 13 march according to table_b's content.
However, If table_b is empty, The function should return the lowest date from table_a and not raise an NO_DATA_FOUND exception if table_b is empty.



table_a table_b

16 march 10 march
18 march 11 march
15 march
13 march


any ideas?


Thanks in advance


Re: I need Ideas [message #191347 is a reply to message #191343] Tue, 05 September 2006 16:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>any ideas?
Sounds like a typical homework problem.
Post what you've tried & explain why it is not the correct solution.
Re: I need Ideas [message #191348 is a reply to message #191347] Tue, 05 September 2006 16:35 Go to previous messageGo to next message
Jolanda
Messages: 68
Registered: September 2004
Member
Hi Anacedent,

Thanks for your reply. Actually I use this in dynamic sql, becauase I need to do this for a lot of tables.




v_select := ' SELECT MAX (' ||
                determine_column_name(p_tabelnaam,'_date') || ')' ||' FROM ' || p_table_a || ' WHERE ' ||
                here comes the where clause.....
  
    EXECUTE IMMEDIATE v_select
      INTO v_dateA;
  
    IF v_dateA IS NULL
    THEN
    
      v_select := ' SELECT MIN (' ||

here I do the select statement from the other table.
              

    
      EXECUTE IMMEDIATE v_select
        INTO v_dateB;
-- to_char is done because I need this as a string for another dynamic sql function...so the function returns a varchar2.
v_Date := TO_CHAR(v_dateB, 'dd-mm-yyyy HH:MI:SS');
	     RETURN v_date;
      

 END IF;
   END IF;
 
    EXCEPTION
    when NO_DATA_FOUND then
    NULL;
    
  END ;



When I can't find a date field in the first INTO v_dateA statement, It raises an exception...NO_DATA_FOUND.
And what I also don't have is the select statement between the tables to determine the date I need.


bye for now and thanks for your reply
Re: I need Ideas [message #191350 is a reply to message #191348] Tue, 05 September 2006 16:55 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
You can do this in one select if you use an outer join on table B.
Re: I need Ideas [message #191511 is a reply to message #191350] Wed, 06 September 2006 16:56 Go to previous message
Jolanda
Messages: 68
Registered: September 2004
Member
Hi Frank,

Is it possible that you send an example?


Thanks
Previous Topic: error while using sqlldr
Next Topic: diff rowid & rownum
Goto Forum:
  


Current Time: Sun Dec 08 18:51:39 CST 2024