Home » Developer & Programmer » Forms » Records from date to sysdate (merged)
Records from date to sysdate (merged) [message #138850] Sat, 24 September 2005 10:04 Go to next message
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
Records from date to sysdate [message #138851 is a reply to message #138850] Sat, 24 September 2005 10:08 Go to previous messageGo to next message
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
Records from date to sysdate [message #138852 is a reply to message #138850] Sat, 24 September 2005 10:10 Go to previous messageGo to next message
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 #138859 is a reply to message #138852] Sat, 24 September 2005 13:08 Go to previous messageGo to next message
saadatahmad
Messages: 448
Registered: March 2005
Location: Pakistan/Kuwait
Senior Member

hi,
Remove your code from the trigger and write this code.

GO_BLOCK('EMPLOYEES');
SET_BLOCK_PROPERTY('EMPLOYEES', DEFAULT_WHERE, 'hire_date BETWEEN '||''''||:CONTROL.criteria||''''||' AND sysdate');
EXECUTE_QUERY;


I considered that your criteria item is placed on the control block.

Note: Always remember to prefix the block name with the item name specified in the code.

Regards.
Re: Records from date to sysdate [message #138861 is a reply to message #138859] Sat, 24 September 2005 13:12 Go to previous messageGo to next message
saadatahmad
Messages: 448
Registered: March 2005
Location: Pakistan/Kuwait
Senior Member

hey,
Don't post the duplicate posts. One post is enough to get the attention of the members.

Regards.
Re: Records from date to sysdate [message #138907 is a reply to message #138861] Sun, 25 September 2005 08:17 Go to previous messageGo to next message
sharma.radha
Messages: 59
Registered: August 2005
Location: mumbai
Member

Hi Saadatahmad
First i would like to thank you for giving time to my question.
But Ahmad, still i am not getting perfect solution.

When i set the datatype of 'criteria' to date then it only gives the values for
that dates which have '00' in its last two digits like '11-jan-1900'.
But changing the datatype to char Then it works..

So, can you explain a bit on this behaviour of the module.
Please clear this 'DEFAULT_WHERE' clause too.


/*And the replication of messages is due to the slow speed of internet on my computer,
i did post same message 2 times because the status bar not showing the complete status mark.
And please do not think that i did it to get the attention.*/


Thanks
Radha
Re: Records from date to sysdate [message #138911 is a reply to message #138907] Sun, 25 September 2005 09:31 Go to previous messageGo to next message
saadatahmad
Messages: 448
Registered: March 2005
Location: Pakistan/Kuwait
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 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT 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 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Posting this message twice could be considered an accident, but THREE TIMES!!! DO NOT DO THIS AGAIN!!! Mad

@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 #139084 is a reply to message #138949] Mon, 26 September 2005 09:34 Go to previous messageGo to next message
sharma.radha
Messages: 59
Registered: August 2005
Location: mumbai
Member

Thanks Saadatahmad.
Great! I got the concept. You cleared my concepts.
Thank you very-2 much again n again.

Take care
Bye.
Radha Sharma.
Re: very-2 easy qusestion.... [message #139085 is a reply to message #138946] Mon, 26 September 2005 09:35 Go to previous messageGo to next message
sharma.radha
Messages: 59
Registered: August 2005
Location: mumbai
Member

Thanks David.

Bye.

Radha.
Re: very-2 easy qusestion.... [message #139086 is a reply to message #138949] Mon, 26 September 2005 09:42 Go to previous messageGo to next message
sharma.radha
Messages: 59
Registered: August 2005
Location: mumbai
Member


Hi David,
Sometimes accidents are also countered accidently, so please DAVID do not forget this. And if you still think that i did not make it accidently then .. I AM SORRY AND I WONT DO THIS MISTAKE AGAIN IN FUTURE.

Bye and have a very nice day.

Radha Sharma.
Re: very-2 easy qusestion.... [message #139158 is a reply to message #139084] Mon, 26 September 2005 12:51 Go to previous messageGo to next message
sharma.radha
Messages: 59
Registered: August 2005
Location: mumbai
Member

Hi Saadatahmad,

I create i non-database block
'BLOCKING' now i want to populate it, i wrote this query in button_pressed_trigger

GO_BLOCK('BLOCKING');
SELECT EMPLOYEES.EMPLOYEE_ID,EMPLOYEES.FIRST_NAME,EMPLOYEES.HIRE_DATE INTO :BLOCKING.EMPLOYEE_ID,:BLOCKING.FIRST_NAME,
:BLOCKING.HIRE_DATE
FROM EMPLOYEES;
exception
when TOO_MANY_ROWS then
message('abc');

But it gives error because it returns more then one record, so how do i prevent from this problem.
should i make cursor for it?

waiting ....

Radha.
Re: Records from date to sysdate (merged) [message #139190 is a reply to message #138850] Mon, 26 September 2005 15:08 Go to previous messageGo to next message
kiran
Messages: 503
Registered: July 2000
Senior Member
Yes one option to use the Cursor or else try to keep where cluase if you have any defined condition.

--Kiran.
Re: very-2 easy qusestion.... [message #139216 is a reply to message #139158] Mon, 26 September 2005 19:06 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have a look at http://www.orafaq.com/forum/m/138793/67467/#msg_138793.

What are you trying to do? Is this a detail table? If so, then build a 'Relations' between the two tables.

Do you want some other fields of information for each record? Then use Post-Query trigger and place data into non-database fields in the EXISTING block.

David
Re: very-2 easy qusestion.... [message #139266 is a reply to message #139216] Tue, 27 September 2005 02:57 Go to previous messageGo to next message
saadatahmad
Messages: 448
Registered: March 2005
Location: Pakistan/Kuwait
Senior Member

hi,
Please check this and read it completely. There is another thread inside this. Go to that one as well and download the ENTER.FMB form and check that as well. Come back if you still have the problem.
http://www.orafaq.com/forum/m/134047/67693/#msg_134047

Regards
Re: very-2 easy qusestion.... [message #139331 is a reply to message #139266] Tue, 27 September 2005 09:16 Go to previous messageGo to next message
sharma.radha
Messages: 59
Registered: August 2005
Location: mumbai
Member

Hi Sadatahmad,
Sadat here my problem is differnt(i went through that link it also help me in future),
i want to know the records like
If i clik a button i get the records of that employees which are hired todays date i mean dd/mon. AND

----------------------------------------------------------------
FOR THIS I WROTE THIS QUERY FOR NON-DATABASE BLOCK IN BUTTON PRESSED TRIGGER-

/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');
----------------------------------------------------------------
This works BUT if there are more then one record then it works or not ihave doubt in this...
----------------------------------------------------------------
The second one is to get the records between dates
FROM - TO to DATE.

Present now i achieved to get the records between FROM to SYSDATE.(here i have to :write only one field ie from date).

But not the records which we will get by entering
from and to both dates manually.
I tried a lot but not get the proper logic.

So please sort out this problem.

Waiting for your reply again.. AND sorry for bothering you again n again.

Radha.






Re: very-2 easy qusestion.... [message #139339 is a reply to message #139331] Tue, 27 September 2005 09:50 Go to previous messageGo to next message
sharma.radha
Messages: 59
Registered: August 2005
Location: mumbai
Member

Hi Sadatahmad,

My second problem is perfectly solved, thanks to you for this to guide me a lot.

Now i want to know the records like-

If i clik a button i get the records of that employees which are hired todays date i mean dd/mon. AND
in this i use data block and one item from Non data base Block.

Please read my previous reply which i sent before a few minutes.

Waiting...Waiting

Radha.
Re: very-2 easy qusestion.... [message #139344 is a reply to message #139339] Tue, 27 September 2005 10:16 Go to previous messageGo to next message
saadatahmad
Messages: 448
Registered: March 2005
Location: Pakistan/Kuwait
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 #139785 is a reply to message #139344] Thu, 29 September 2005 10:02 Go to previous messageGo to next message
sharma.radha
Messages: 59
Registered: August 2005
Location: mumbai
Member

Hi sadatahmad,

First of all thanks to you for tolerating me upto now.
Yes i did the mistake to write EXECUTE_QUERY actually i confused too at that point.. But now with your help my confusions are gone away.


Thanks sadatahmad thanks

Radha Sharma.
Re: very-2 easy qusestion.... [message #140106 is a reply to message #139344] Fri, 30 September 2005 23:29 Go to previous messageGo to next message
sharma.radha
Messages: 59
Registered: August 2005
Location: mumbai
Member

Hi Sadatahmad,

I want to pass the values from data entry(based on data block) FORM in REPORT.

Suppose we take
four fields- empid,hiredate,deptid, amount on an FORM
After entering and saving that values, we take reports
of that particular entry by clicking a button which is on a form.

So what i do to make report that takes values from the form.

If it is possible for you then please attach an example...as same
as you done in your early replies. Actually from the example it is
very easy to understand the concepts.


I know it is time taking task but please..


Thanks

Radha.
Re: very-2 easy qusestion.... [message #140122 is a reply to message #140106] Sat, 01 October 2005 03:00 Go to previous messageGo to next message
saadatahmad
Messages: 448
Registered: March 2005
Location: Pakistan/Kuwait
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 444 times)
Re: very-2 easy qusestion.... [message #140200 is a reply to message #140122] Sun, 02 October 2005 09:51 Go to previous messageGo to next message
sharma.radha
Messages: 59
Registered: August 2005
Location: mumbai
Member

Hi sadatahmad,

This is my Report file and in next message i am sending Form file.



  • Attachment: TESTS.RDF
    (Size: 60.00KB, Downloaded 528 times)
Re: very-2 easy qusestion.... [message #140201 is a reply to message #140200] Sun, 02 October 2005 10:00 Go to previous messageGo to next message
sharma.radha
Messages: 59
Registered: August 2005
Location: mumbai
Member

(The Schema is of "HR" instead of "Scott" )


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.
Just
Like when we call FORM from FORM.


Waiting Sadatahmad.. for reply.

Radha.
  • Attachment: LIST_LIST.fmb
    (Size: 56.00KB, Downloaded 376 times)
Re: very-2 easy qusestion.... [message #140202 is a reply to message #140200] Sun, 02 October 2005 10:27 Go to previous messageGo to next message
saadatahmad
Messages: 448
Registered: March 2005
Location: Pakistan/Kuwait
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 Go to previous messageGo to next message
saadatahmad
Messages: 448
Registered: March 2005
Location: Pakistan/Kuwait
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 Laughing
RAISE FORM_TRIGGER_FAILURE;

Regards.

[Updated on: Sun, 02 October 2005 10:58]

Report message to a moderator

Re: Records from date to sysdate (merged) [message #140209 is a reply to message #140204] Sun, 02 October 2005 12:36 Go to previous messageGo to next message
sharma.radha
Messages: 59
Registered: August 2005
Location: mumbai
Member

Thanks Sadatahmad..

Now it's working.

And that interesting Message was :bind to my naughty brother.
He is very-2 naughty.

Thanksss
Radha.
Re: very-2 easy qusestion.... [message #140354 is a reply to message #138949] Mon, 03 October 2005 19:50 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT 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
Previous Topic: ........ Menu bar ..... How to cal........
Next Topic: Dynamic creation of tab pages
Goto Forum:
  


Current Time: Wed Nov 26 15:44:41 CST 2014

Total time taken to generate the page: 0.08907 seconds