Home » Developer & Programmer » Forms » Records from date to sysdate (merged)
| Records from date to sysdate (merged) [message #138850] |
Sat, 24 September 2005 10:04  |
sharma.radha
Messages: 59
Registered: August 2005
Location: mumbai
|
Member |
|
|
Hi to everyones.
I am new to forms..
The form which i am going to make has 2 blocks
one is based on table employees.
Other has one text item "CRITERIA" DATATYPE date.
BUT NOT THE DATABASE ITEM.
NOW I want to search the records which lies between criteria and sysdate
ex.
begin
SELECT FIRST_NAME,EMPLOYEE_ID,HIRE_DATE INTO :FIRST_NAME,:EMPLOYEE_ID,:Hire_DATE FROM EMPLOYEES
WHERE HIRE_DATE BETWEEN :CRITERIA AND SYSDATE;
end
I wrote this query in when_button_pressed trigger, but it didnt work.
So please give some time to this query.
waiting for your quick responses......
Thanks in advance.
Radha Sharma
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Records from date to sysdate [message #138911 is a reply to message #138907] |
Sun, 25 September 2005 09:31  |
 |
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
|
Senior Member |
|
|
hi,
one reason may be you'r setting the format mask for date items.
Anyway, as you asked to explain the WHERE clause, I'll explain you this WHERE clause in detail and I think after that you'll be able to solve your problem yourself.
I'll take the emp table in scott's schema for this explanation.
Let's start:
Open form builder and create a new form for emp table. Choose all the default settings.
Now the form is created. Open the properties of EMP data block and in Where clause property write this - deptno=10
Run the form and Query. You'll see that only the records for deptno=10 has been queried. It means we have set the WHERE clause at Design time.
Now close the forms run-time and go to form builder. Remove the WHERE clause from the properties of the EMP data block.
Now create a new block manually named CONTROL (non-database block). This is not a data block. Create a new text item named CRITERIA for this block and display it on your canvas. Make the item's database item property to NUMBER.
Create a button on the layout. Write the the code for When-Button-Pressed Trigger:
GO_BLOCK('EMP');
SET_BLOCK_PROPERTY('EMP', DEFAULT_WHERE, 'WHERE deptno = '||:CONTROL.criteria);
EXECUTE_QUERY;
Now run the form. Write any deptno in the deptno field and click the button. The query will be performed for that deptno only.
What you'r doing here? You'r setting the where clause dynamically at run-time.
Now close the form and go to form builder again. Change the datatype of the CRITERIA to DATE.
Open the trigger code and change the where clause to
SET_BLOCK_PROPERTY('EMP', DEFAULT_WHERE, 'WHERE hiredate BETWEEN '||''''||:CONTROL.criteria||''''||' AND sysdate');
Run the form and write in the criteria field 01-JAN-1983, push the button and only the records which are between 01-JAN-1983 and sysdate will be shown.
Now here, we dynamically set the WHERE clause of the EMP block.
'''' these four quote means we want to write a single quote inside two quotes. AT Run-Time, this where clause is read as
'WHERE hiredare BETWEEN '01-JAN-1983' AND sysdate'.
Notice that there are two quotes around 01-JAN-1983 and these are inside the two quotes which surround the whole WHERE clause. for each single quote inside a quote, we need four quotes like ''''.
Now whatever date you'll specify in the CRITERIA item at run-time, it will be the part of your WHERE clause.
On the contrary, you'r trying to write the code like SELECT INTO which is not required here because you'r querying the database block so no need for this SELECT INTO clause.
You can see that I didn't change any format mask for the date items. If you want to change it then you'll have to be carefull about it.
I think this is enough to make you understand how the WHERE clase works at run-time and you can apply this technique to solve your problem.
Note: To read more about the WHERE clause open the documentation of forms and read it from there.
Regards.
[Updated on: Sun, 25 September 2005 09:33] Report message to a moderator |
|
|
|
| Re: very-2 easy qusestion.... [message #138946 is a reply to message #138850] |
Sun, 25 September 2005 23:18 |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
Put the ':criteria' on a control block (non-database) as ':ctrl.criteria' (always use the ':block.item' naming convention) and put the "HIRE_DATE BETWEEN :CRITERIA AND SYSDATE" in the where clause of your block. Now, of course, it will not give you your expected results if the criteria is NULL so either research the 'set_block_property' 'default_where' or put a meaningful NVL in your where clause. For example, "HIRE_DATE BETWEEN nvl(:CRITERIA,to_date('01-JAN-1900','DD-MON-YYYY)) AND SYSDATE".
David
[Updated on: Mon, 26 September 2005 18:59] Report message to a moderator |
|
|
|
| Re: very-2 easy qusestion.... [message #138949 is a reply to message #138946] |
Sun, 25 September 2005 23:36 |
|
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
Posting this message twice could be considered an accident, but THREE TIMES!!! DO NOT DO THIS AGAIN!!! 
@other contributors, please report multiple postings, I get really uptight when I answer a question and find that you worthy people have already answered it.
David
@Radha Sharma - My apologies for yelling at you. I had exactly the same problem with one of my threads being posted six times!! Fortunately, as a moderator I could delete the extra threads. In future I will just delete them without going 'over the top'.
[Updated on: Mon, 03 October 2005 19:49] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: very-2 easy qusestion.... [message #139344 is a reply to message #139339] |
Tue, 27 September 2005 10:16 |
|
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
|
Senior Member |
|
|
Hi,
In your previous post, it seems that employees1 is the control block in which you are trying to get the employees information from your employee table which are hired today and in your last post you mentioned that it is the database block.
I'm confused here....sorry for that.
However, I'll suggest you as per your previous post.
your query:
| Quote: |
/GO_BLOCK(EMPLOYEES1);
select employee_id,first_name,hire_date into :employees1.employee_id,:employees1.first_name,:employees1.hire_date FROM employees
where substr(hire_date,1.6)=substr(sysdate,1.6);
excute_query;
when no_data_found the
message('no body hired today');
|
If you have more than one employee hired today this code will not work. Moreover, if this is a non-database block why are you writing EXECUTE_QUERY?
ok....see the table and the data in the table and I'll fetch the records from this table in forms in a non-database block.
saadat@anba.world> CREATE TABLE employees
2 (employee_id NUMBER,
3 first_name VARCHAR2(32),
4 hire_date DATE);
Table created.
Elapsed: 00:00:00.00
saadat@anba.world> INSERT INTO employees
2 VALUES(1, 'MARTIN', '15-JAN-2005');
1 row created.
Elapsed: 00:00:00.00
saadat@anba.world> INSERT INTO employees
2 VALUES(2, 'JAMES', '20-MAR-2005');
1 row created.
Elapsed: 00:00:00.00
saadat@anba.world> INSERT INTO employees
2 VALUES(3, 'CLARK', '23-AUG-2005');
1 row created.
Elapsed: 00:00:00.00
saadat@anba.world> INSERT INTO employees
2 VALUES(4, 'NEIL', '15-SEP-2005');
1 row created.
Elapsed: 00:00:00.00
saadat@anba.world> INSERT INTO employees
2 VALUES(5, 'SMITH', '27-SEP-2005');
1 row created.
Elapsed: 00:00:00.00
saadat@anba.world> INSERT INTO employees
2 VALUES(6, 'DUNKEN', '27-SEP-2005');
1 row created.
Elapsed: 00:00:00.00
saadat@anba.world> COMMIT;
Commit complete.
Elapsed: 00:00:00.00
saadat@anba.world> SELECT * FROM employees;
EMPLOYEE_ID FIRST_NAME HIRE_DATE
----------- -------------------------------- ---------
1 MARTIN 15-JAN-05
2 JAMES 20-MAR-05
3 CLARK 23-AUG-05
4 NEIL 15-SEP-05
5 SMITH 27-SEP-05
6 DUNKEN 27-SEP-05
6 rows selected.
Elapsed: 00:00:00.00
Now you can see that here, I have two records which are hired today.
Ok....now We'll create a form. I'm attaching the form and you just download it and see the code in the When-Button-Pressed trigger.
Just create the employees table in your database and then insert the values and then run this form. Two records will be fetched from database.
I hope this is what you're requiring. If something else then please write the script here for your table and data and attach your form with the post. I'll download your form and see what you want to achieve.
Regards.
|
|
|
|
|
|
|
|
| Re: very-2 easy qusestion.... [message #140122 is a reply to message #140106] |
Sat, 01 October 2005 03:00 |
|
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
|
Senior Member |
|
|
hi,
It doesn't matter whether it takes time to create a form as an example but I don't think you can learn better this way.
You should try yourself first and then if not succeeded, post the code here and then some members here can try to solve your problem. This is there in the sticky as well.
Anyway, next time please try it yourself and then you can share your problems with us.
For your problem, we just need to pass a parameter from a form to the report.
First create a simple Report by this query:
SELECT empno, ename, hiredate, deptno
FROM emp
WHERE deptno = :P_DEPTNO
This report query will create a parameter named P_DEPTNO in your report. Save the report by the name TEST.
Now, here's the code to pass the parameter from form to the code on a When-Button-Pressed trigger:
DECLARE
pl_id PARAMLIST;
BEGIN
pl_id := Get_Parameter_List('tmpdata');
IF NOT Id_Null(pl_id) THEN
Destroy_Parameter_List(pl_id);
END IF;
pl_id := Create_Parameter_List('tmpdata');
Add_Parameter(pl_id,'P_DEPTNO',TEXT_PARAMETER,NAME_IN('EMP.deptno'));
Run_Product(REPORTS, 'test', SYNCHRONOUS, RUNTIME,FILESYSTEM, pl_id, NULL);
END;
You can chnange this code according to your requirements.
Note if you want to add another parameter here. you just need to add another parameter in the same way as added by Add_Parameter built-in.
I'm attaching the form. After creating the report, Just run this for, execute query and Click the button to run the report.
Note: Read the help from forms about passing parameters.
Regards.
-
Attachment: test.fmb
(Size: 48.00KB, Downloaded 1585 times)
|
|
|
|
|
|
|
|
| Re: very-2 easy qusestion.... [message #140202 is a reply to message #140200] |
Sun, 02 October 2005 10:27 |
|
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
|
Senior Member |
|
|
Hi,
You mean if you pass the parameters to report, you don't want to show the parameter form. You just want to view the report depending on the parameters passed to the report?
Right now, you don't have any parameter defined in your report. If we define the parameters in report and pass the values from form to report then it works( according to you).
But you don't want the parameter form to be displayed when the report is called. Is it rihgt?
Please clerify !!!
Regards.
[Updated on: Sun, 02 October 2005 10:30] Report message to a moderator |
|
|
|
| Re: Records from date to sysdate (merged) [message #140204 is a reply to message #138850] |
Sun, 02 October 2005 10:37 |
|
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
|
Senior Member |
|
|
You quoted:| Quote: |
So the problem is when i declare user parameter in report then it works. And takes values manually as parameter value.
But i want that after pressing button it creates report with out taking values maually.
|
This means that this is working when you specify the parameters. The only thing is that you want to suppress the parameter form in th ereport if i'm right.
For this purpose, you just need to add one more line in your button's code in the form.
add_parameter(pl_id,'p_employee_id',TEXT_PARAMETER,NAME_IN('EMPLOYEES.EMPLOYEE_ID'));
add_parameter(pl_id,'FIRST_NAME',TEXT_PARAMETER,NAME_IN('EMPLOYEES.FIRST_NAME'));
add_parameter(pl_id,'SALARY',TEXT_PARAMETER,NAME_IN('EMPLOYEES.SALARY'));
Add_Parameter(pl_id, 'PARAMFORM', TEXT_PARAMETER, 'NO'); --Just add this line in your code.
Just add the line I mentioned here and you'll not see the parameter form.
I hope this is what you want.
BTW:
EXCEPTION
WHEN NO_DATA_FOUND THEN
MESSAGE('ABAI GADHE WHT R U DOING'); --Interesting Message 
RAISE FORM_TRIGGER_FAILURE;
Regards.
[Updated on: Sun, 02 October 2005 10:58] Report message to a moderator |
|
|
|
|
|
| Re: very-2 easy qusestion.... [message #140354 is a reply to message #138949] |
Mon, 03 October 2005 19:50 |
|
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
Radha Sharma - My apologies for yelling at you. I had exactly the same problem with one of my threads being posted six times!! Fortunately, as a moderator I could delete the extra threads. In future I will just delete them without going 'over the top'.
David
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 06:23:28 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
