Home » Developer & Programmer » Forms » How to create to do list form using Oracle Form 6i (Windows XP, Oracle Form 6i)
How to create to do list form using Oracle Form 6i [message #604538] Tue, 31 December 2013 23:41 Go to next message
dark_prince
Messages: 98
Registered: June 2013
Location: India
Member
Hiii...Happy New Year Guys...
I have two table vehicle_details and financed_vehicle_details with following structures..

CREATE TABLE vehicle_details    ( vehicle_code          NUMBER
                                , vehicle_no            VARCHAR2(100)
                                , vehicle_type          VARCHAR2(100)
                                , fitness_cert_val      DATE
                                , road_tax_uptill       DATE
                                , puc                   DATE
                                , carriage_permit_exp   DATE
                                , engine_no             VARCHAR2(100)
                                , chasis_no             VARCHAR2(100)
                                , make_model            VARCHAR2(100)
                                , year_of_model         NUMBER(4)
                                , insurance_comp        VARCHAR2(200)
                                , next_prem_date        DATE
                                , CONSTRAINT vehicle_code_pk    PRIMARY KEY (vehicle_code)
                                , CONSTRAINT vehicle_no_uk      UNIQUE      (vehicle_no)
                                )

and
CREATE TABLE financed_vehicle_details   ( finance_code  NUMBER
                                        , finance_comp  VARCHAR2(200)
                                        , vehicle_type  VARCHAR2(100)
                                        , vehicle_code  NUMBER
                                        , vehicle_no    VARCHAR2(100)
                                        , emi_per_mon   NUMBER
                                        , due_date      VARCHAR2(30)
                                        , emi_start_dt  DATE
                                        , emi_end_dt    DATE
                                        , fin_loan_amt  NUMBER
                                        , fin_tenure    VARCHAR2(30)
                                        , rate_of_int   NUMBER(10,2)
                                        , CONSTRAINT fin_code_pk    PRIMARY KEY (finance_code)
                                        , CONSTRAINT fin_veh_fk     FOREIGN KEY (vehicle_code)  REFERENCES vehicle_details  (vehicle_code)  ON DELETE CASCADE
                                        )


These both two table contains 7 date columns. I want to retrieve data from both table if these 7 date columns match the condition kinda like following which is plsql code..

DECLARE
    v_count NUMBER;
    v_name  VARCHAR2(100);
    v_date  DATE;

    CURSOR next_cur
    IS
        SELECT  vehicle_no, next_prem_date, ABS(TO_DATE(next_prem_date) - TO_DATE(SYSDATE)) c_next
        FROM    vehicle_details
        ORDER   BY 3;
BEGIN
     FOR v_next IN next_cur
     LOOP
         IF v_next.c_next <= 30
         THEN
             IF v_next.next_prem_date >= TO_DATE(SYSDATE) - 7
             THEN
                 dbms_output.put_line('Vehicle No : '||v_next.vehicle_no||' Next Prem Date : '||v_next.next_prem_date||' Days Left : '||v_next.c_next);
             END IF;
         END IF;
     END LOOP;
END;



I only want to show data if the date <= 30 days and it must be >= week like above code..
But I don't know how to all the 7 column a once in the forms. I tried its not showing complete data..
I also uploaded screen shot of the form..

./fa/11532/0/

where title - vehicle no
description - date column name
date - date
days left - how many to days are left or passed
can anyone tell if there is something wrong in above code...and how to display at runtime...thnak u
Re: How to create to do list form using Oracle Form 6i [message #604551 is a reply to message #604538] Wed, 01 January 2014 01:54 Go to previous messageGo to next message
Littlefoot
Messages: 19303
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If I understood the question correctly, you should apply the following type of the WHERE clause to that data block (how? SET_BLOCK_PROPERTY and its DEFAULT_WHERE or ONETIME_WHERE property):
select <column list here>
from vehicle_details
where sysdate - fitness_cert_val between 7 and 30
   or sysdate - road_tax_uptill  between 7 and 30
   or sysdate - puc              between 7 and 30
   etc.
Re: How to create to do list form using Oracle Form 6i [message #604568 is a reply to message #604551] Wed, 01 January 2014 06:50 Go to previous messageGo to next message
dark_prince
Messages: 98
Registered: June 2013
Location: India
Member
Hii thansk for your reply...You understand my question partly...
I want to display data from more than one table. so how to use this...

And I want to display data based on following condition

1. If next_prem_date is 30-jan-2014 and today date is 01-jan-2014 so their difference is 29. If the difference is >30 days the query mus not return rows and display message "No record found". else if <=30 then return rows and display message "this this days are left"..In this I want to display rows based on future date.

2. If puc is 29-dec-2013 and today date is 1-jan-2014 so their difference is 3 days. If the difference is >= 7 days i.e. TO_DATE(SYSDATE)- 7 then query must return rows and it should display message "this this days ago". In this I want to display rows based on past date.

3. If the emp_dob is 29-dec-1991 and today date is 29-dec-2013 then query must return rows and it should display message "Today is your birthday".

4. And If emp_dob is 29-dec-1991 and today date is 1-jan-2014 then qury must return rows and it should displayed message "2 days ago".

5. And I want to display data from more than two tables.

Please tell me how to do this...
Re: How to create to do list form using Oracle Form 6i [message #604676 is a reply to message #604568] Thu, 02 January 2014 10:57 Go to previous messageGo to next message
Littlefoot
Messages: 19303
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

I want to display data from more than one table

The simplest option would be to create a view (as join of all those tables), and then base data block on that view.

For all those "conditions", create a POST-QUERY trigger which would check each of them and display appropriate message.
Re: How to create to do list form using Oracle Form 6i [message #604681 is a reply to message #604676] Thu, 02 January 2014 11:57 Go to previous messageGo to next message
dark_prince
Messages: 98
Registered: June 2013
Location: India
Member
Some table doesn't relate with each other so still I have to create single view or multiple...and about conditions I tried to run it down but its not working correctly can you help me with query kinda like example of it..

[Updated on: Thu, 02 January 2014 12:49]

Report message to a moderator

Re: How to create to do list form using Oracle Form 6i [message #604704 is a reply to message #604681] Fri, 03 January 2014 02:13 Go to previous messageGo to next message
Littlefoot
Messages: 19303
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If these tables aren't related (not directly, not via some other table(s)), then you have a problem. It means that the same data block has to accept values from different tables. Consider switching data source type from "table" to a "FROM clause query". You'd then set block's QUERY_DATA_SOURCE_NAME property with SET_BLOCK_PROPERTY, while property's value would be a full SELECT statement that selects data from different tables.

You should, of course, know which SELECT to use in every possible case. How will you do that? No idea; maybe with radio buttons (located in a new, control block) or something else.

Alternatively, if you think that PL/SQL procedure you posted in your first message does the job nicely, base data block on a stored procedure, no problem with that.
Re: How to create to do list form using Oracle Form 6i [message #604706 is a reply to message #604704] Fri, 03 January 2014 03:33 Go to previous messageGo to next message
dark_prince
Messages: 98
Registered: June 2013
Location: India
Member
Suppose "POP_UP" is my data block name which is of course manual one and under it list item named "CATEGORY" if created. And then based on the category data will be displayed, so in this scenario, it creating view will not necessary i think, right ?? And data will also be systematic....

So above what i said is it right or do I have to do something different...
thank you...
Re: How to create to do list form using Oracle Form 6i [message #604884 is a reply to message #604706] Sun, 05 January 2014 08:15 Go to previous messageGo to next message
dark_prince
Messages: 98
Registered: June 2013
Location: India
Member
Hey guys please help me...
Re: How to create to do list form using Oracle Form 6i [message #604887 is a reply to message #604884] Sun, 05 January 2014 10:30 Go to previous messageGo to next message
Littlefoot
Messages: 19303
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
in this scenario, it creating view will not necessary i think, right ??

I don't know whether you are right or not. As you have unrelated tables, how do you plan to fetch data from different tables?

Quote:
data will also be systematic

I don't understand what that means.

Quote:
or do I have to do something different

I said everything I meant to say in my previous message. Therefore, you'll have to wait for someone else's opinion.

Re: How to create to do list form using Oracle Form 6i [message #604889 is a reply to message #604887] Sun, 05 January 2014 10:45 Go to previous messageGo to next message
dark_prince
Messages: 98
Registered: June 2013
Location: India
Member
Thanks for your reply littlefoot, guess i'll have to wait then... But can you atleast help me with the condition which i explain earlier, can you atleast give me example of each condition with select statement it would be very big help...
Re: How to create to do list form using Oracle Form 6i [message #604890 is a reply to message #604889] Sun, 05 January 2014 10:47 Go to previous messageGo to next message
Littlefoot
Messages: 19303
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
An example? I already did, here.
Re: How to create to do list form using Oracle Form 6i [message #604891 is a reply to message #604890] Sun, 05 January 2014 10:49 Go to previous message
dark_prince
Messages: 98
Registered: June 2013
Location: India
Member
Ok thanks...
Previous Topic: query statistic from multi tables
Next Topic: ORA-01036: illegal variable name/number
Goto Forum:
  


Current Time: Wed Jul 23 15:29:25 CDT 2014

Total time taken to generate the page: 1.22968 seconds