How to create to do list form using Oracle Form 6i [message #604538] |
Tue, 31 December 2013 23:41 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/178896.jpg) |
dark_prince
Messages: 121 Registered: June 2013 Location: India
|
Senior 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..
![/forum/fa/11532/0/](/forum/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 #604704 is a reply to message #604681] |
Fri, 03 January 2014 02:13 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 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.
|
|
|
|
|
|
|
|
|