| displaying data in a form called from another [message #448637] |
Wed, 24 March 2010 07:41  |
rosswelsh
Messages: 42
Registered: February 2010
Location: Sunderland
|
Member |
|
|
Hi all,
I have a form (SearchHeatNumber) with one number field on it (EnterHeatNumber) and when the users insert this number and press the 'OK' button I have, I want the database to return the data for this HeatNumber value, but in a different form (AddDataRequest). I have written the following code but it will not work, firstly it gives me the error ORA-01772 which says that it is trying to convert a string into a number and cannot do it. I have defined HeatNumber as both a number and a char and get the error on both occasions. Secondly, I am unsure about how to populate in a different form and im sure the code i've written wont do that anyway but im hoping its a start.
declare
v_heatnumber adddatarequest.heatnumber%type;
v_requestedby adddatarequest.requestedby%type;
v_description adddatarequest.description%type;
v_typeofquery adddatarequest.typeofquery%type;
v_querydescription adddatarequest.querydescription%type;
v_documenttitle adddatarequest.documenttitle%type;
v_datacrossref adddatarequest.datacrossref%type;
v_datadespatched adddatarequest.datadespatched%type;
v_userfollowup adddatarequest.userfollowup%type;
v_additionalinfo adddatarequest.additionalinfo%type;
v_attachedsql adddatarequest.attachedsql%type;
v_workcompby adddatarequest.workcompby%type;
v_datecomp adddatarequest.datecomp%type;
v_confirmedby adddatarequest.confirmedby%type;
v_confirmeddate adddatarequest.confirmeddate%type;
begin
select heatnumber, requestedby, description, typeofquery,querydescription,documenttitle,datacrossref,
datadespatched, userfollowup, additionalinfo, attachedsql, workcompby, datecomp, confirmedby, confirmeddate
into v_heatnumber, v_requestedby, v_description, v_typeofquery, v_querydescription, v_documenttitle,
v_datacrossref, v_datadespatched, v_userfollowup, v_additionalinfo, v_attachedsql, v_workcompby,
v_datecomp, v_confirmedby, v_confirmeddate
from adddatarequest
where heatnumber = ('<:block_3.enterheatnumber>');
exception when no_data_found
then dbms_output.put_line ('No records found for this number');
end;
I hope you can understand what im asking and can help!
Thanks!
|
|
|
|
|
|
| Re: displaying data in a form called from another [message #448652 is a reply to message #448637] |
Wed, 24 March 2010 08:44  |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
As far as the ORA-01772 goes this:
where heatnumber = ('<:block_3.enterheatnumber>');
should be:
where heatnumber = :block_3.enterheatnumber;
No quotes, brackets or anything else.
Anything in quotes is a string automatically, numbers do not require (and shouldn't have) quotes.
The datablock item should be set to number type. I'm assuming the heatnumber column is number type.
Although, as Littlefoot points out, the select statement is pointless anyway.
And why are you using dbms_output in forms code? Forms doesn't display dbms_output.
[Updated on: Wed, 24 March 2010 08:45] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: displaying data in a form called from another [message #448698 is a reply to message #448645] |
Wed, 24 March 2010 15:39 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
Littlefoot wrote on Wed, 24 March 2010 09:11
The second form should perform query based on the SHN value. In the PRE-QUERY trigger you'd then use:heatnumber := :global.shn;
I find that sometimes when I do it this way, you might get a popup asking you if you want to save your changes (although it's probably some other coding I do that causes it).
The way I do it is like this in WHEN-NEW-FORM-INSTANCE:
SET_BLOCK_PROPERTY('{BLOCK_NAME}',DEFAULT_WHERE, ':heatnumber := global.shn');
go_block('{BLOCK_NAME}');
execute_query;
And like littlefoot and cookiemonster have said, put in a
message('where... ' || DEFAULT_WHERE); |
|
|
|
|
|
| Re: displaying data in a form called from another [message #449378 is a reply to message #448637] |
Mon, 29 March 2010 05:40 |
rosswelsh
Messages: 42
Registered: February 2010
Location: Sunderland
|
Member |
|
|
Thanks for the advice on this subject, I still haven't been able to get it to work. At the moment I have this:
My 'Ok' button on SearchHeatNumber form has the code:
begin
:global.shn := :block3.enterheatnumber;
call_form('/home/ca5rwe/forms/HeatNumberResults');
end;
The Pre-Query trigger on HeatNumberResults form has the code:
begin
:heatnumber := :global.shn;
end;
and the When-New-Form-Instance trigger has the code:
begin
set_block_property('{HeatNumberResults}',onetime_where, ':heatnumber := global.shn');
go_block('{HeatNumberResults}');
execute_query;
message('where there are no records for this heat number'||onetime_where);
end;
The HeatNumberResults form loads but it doesn't display the data (which does exist). Any ideas?
Just seen something - does the pre-query trigger need to say :enterheatnumber as on the 1st form, or does it need to be :heatnumber which is the actual database field name?
[Updated on: Mon, 29 March 2010 05:43] Report message to a moderator |
|
|
|
|
|
|
|
| Re: displaying data in a form called from another [message #449413 is a reply to message #448637] |
Mon, 29 March 2010 07:52 |
rosswelsh
Messages: 42
Registered: February 2010
Location: Sunderland
|
Member |
|
|
In addition to this, would this work in the same way when searching for a non exact value, i.e. searching using the %like syntax?
Or, for example would it need to be something along the lines of, in the Pre-Query trigger:
:HeatNumber like :global_shn%
|
|
|
|
| Re: displaying data in a form called from another [message #449433 is a reply to message #448637] |
Mon, 29 March 2010 09:18 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Some notes one these things.
When you are in enter-query mode you can type values into the fields in the block and when you execute the query oracle will append these values to the where clause of the query it submits.
Normally it does this with simple AND conditions.
PRE-QUERY trigger:
When you assign values to fields in the pre-query trigger this mimics entering values in enter-query mode. Whatever values you assign are appended to the where clause. You can't use this to LIKE's or OR's because you are literally assigning values to datablock itmes which the default query process will pick up.
Default_where:
This sets the datablocks where clause property (in the property pallete). If you've got something set in the property pallete this will over-write it.
What ever is in here will be appended directly to the where clause of the query forms issues. Consquently it has to be SQL syntax not PL/SQL syntax. So this bit of code you tried earlier:
set_block_property('{HeatNumberResults}',onetime_where, ':heatnumber := global.shn');
Should have been:
set_block_property('{HeatNumberResults}',onetime_where, 'heatnumber = :global.shn');
Since the := operator isn't valid in SQL and you need to reference the column in the table not the data-block item. The global does need the : because it only exists in the form.
Onetime_where
Exactly the same as default_where but only applies the next execution of a query in that block.
Note that default/onetime where don't override anything in PRE-QUERY or the other way round.
So to answer your question the simplest method would be:
set_block_property('{HeatNumberResults}',onetime_where, 'heatnumber LIKE :global.shn||''%''');
[Updated on: Mon, 29 March 2010 09:19] Report message to a moderator |
|
|
|
| Re: displaying data in a form called from another [message #449461 is a reply to message #449433] |
Mon, 29 March 2010 12:42 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
cookiemonster wrote on Mon, 29 March 2010 10:18Consquently it has to be SQL syntax not PL/SQL syntax. So this bit of code you tried earlier:
set_block_property('{HeatNumberResults}',onetime_where, ':heatnumber := global.shn');
Should have been:
set_block_property('{HeatNumberResults}',onetime_where, 'heatnumber = :global.shn');
Since the := operator isn't valid in SQL and you need to reference the column in the table not the data-block item. The global does need the : because it only exists in the form.
Sorry, I probably steered him wrong based on my post. I gave an answer based on how I remember doing it. Colon, no colon, you're bound to get those mixed up from time to time.
I would be interested to know if you got my version to work if you left out the pre-query trigger.
Also, I am assuming you left out the curly brackets when you made your code.
|
|
|
|
| Re: displaying data in a form called from another [message #449514 is a reply to message #448637] |
Tue, 30 March 2010 04:02 |
rosswelsh
Messages: 42
Registered: February 2010
Location: Sunderland
|
Member |
|
|
Im not having much luck with this. I've done as Cookiemonster has suggested and used the following, having changed field/block names to match the field I am searching:
set_block_property('{QueryDescrResults}',onetime_where, 'querydescription LIKE :global.qdesc||''%''');
This compiles fine and runs, but when I search it brings back all rows in the table, regardless of whether it matches the search criteria or not, and gives the error FRM-41056 Cannot find Block: invalid ID.
Ive doubled checked and the blocks and global variable names are all correct and in the right triggers.
Any suggestions?
And joy_division, I did leave out the curly brackets, but did not try leaving out the pre-query trigger.
Thanks.
|
|
|
|
|
|
|
|
|
|
| Re: displaying data in a form called from another [message #449520 is a reply to message #448637] |
Tue, 30 March 2010 04:48 |
rosswelsh
Messages: 42
Registered: February 2010
Location: Sunderland
|
Member |
|
|
Nope, still getting the same results.
Ok, I have all this, see if you can spot if anything is wrong:
On the 'OK' button on form 'SearchQueryDescription'
begin
:global.qdesc := :querydescr.enterquerydescr;
call_form('/home/ca5rwe/forms/QueryDescrResults');
end;
In the Pre-Query trigger on form 'QueryDescrResults'
begin
--set_block_property('QueryDescrResults', onetime_where,'querydescription LIKE :global.qdesc||''%''');
set_block_property('QueryDescrResults',onetime_where,'querydescription LIKE ' || :global.qdesc || '%');
end;
I left both versions in so I could swap between if necessary. Both versions have the same result.
Finally in the WNFI trigger on form 'QueryDescrResults'
begin
set_window_property('Window1',window_state,maximize);
execute_query;
declare
bk_id Block;
begin
bk_id := Find_Block('QueryDescrResults');
IF (NOT id_null(bk_id)) THEN
message('block exists', ACKNOWLEDGE);
else
message('block does not exist', ACKNOWLEDGE);
end if;
end;
end; |
|
|
|
|
|
|
|
| Re: displaying data in a form called from another [message #449526 is a reply to message #448637] |
Tue, 30 March 2010 05:10 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Are you sure you commented out the execute_query?
Are you sure it's the WNFI trigger that's giving that error and not some other trigger?
I ask because find_block should never give that error.
If the answer to both of those is yes then try doing a compile all (control + shift + k) and re-run.
If you still get the error then attach the form to this thread and we'll see if we can see what you are doing wrong.
|
|
|
|
| Re: displaying data in a form called from another [message #449528 is a reply to message #448637] |
Tue, 30 March 2010 05:31 |
rosswelsh
Messages: 42
Registered: February 2010
Location: Sunderland
|
Member |
|
|
It now appears to be working. I did all 3 things you suggested and I still got the error.
So I ran the QueryDescrResults form on its own, rather than performing a search go get it to load and the find_block said the block existed, so I commented that out and put the execute_query back in and it has ran fine. Maybe it needed to re-generate the .fmx file perhaps?
|
|
|
|
|
|
|
|
|
|
| Re: displaying data in a form called from another [message #449563 is a reply to message #449531] |
Tue, 30 March 2010 08:54 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
rosswelsh wrote on Tue, 30 March 2010 11:50
Thanks very much for your help, I know it must get quite tedious going over simple stuff!
It wasn't that bad. Tedious is where you have to ask 10 questions repeatedly just to find out what the person is trying to achieve (or better yet what error they are getting). You at least managed to describe what you were doing fairly concisely.
|
|
|
|
| Re: displaying data in a form called from another [message #449693 is a reply to message #448637] |
Wed, 31 March 2010 08:23 |
rosswelsh
Messages: 42
Registered: February 2010
Location: Sunderland
|
Member |
|
|
Just got one last question, thought id put it in here rather than create a new topic. I am now creating a search between two dates and the code I have used is:
set_block_property('datecomp',onetime_where,'DateComp BETWEEN ||:global.DateFrom|| and ||:global.DateTo||');
This compiles and runs fine, but when I input my two values, the results form loads but says it is unable to execute the query and when I go to the errors, it gives me ORA-00936: missing expression. I've tried moving the || round and putting in more apostrophes (at least where I think they should be) but I still the same error.
Any suggestions?
Thanks
|
|
|
|
|
|
|
|
|
|
|
|
| Re: displaying data in a form called from another [message #449700 is a reply to message #448637] |
Wed, 31 March 2010 08:58 |
rosswelsh
Messages: 42
Registered: February 2010
Location: Sunderland
|
Member |
|
|
SELECT ROWID,HEATNUMBER,REQUESTEDBY,DESCRIPTION,TYPEOFQUERY,
QUERYDESCRIPTION,DOCUMENTTITLE,DATACROSSREF,DATADESPATCHED,
USERFOLLOWUP,ADDITIONALINFO,ATTACHEDSQL,WORKCOMPBY,
DATECOMP,CONFIRMEDBY,CONFIRMEDDATE
FROM ADDDATAREQUEST
WHERE DateComp BETWEEN to_date(29-MAR-10) and to_date(30-MAR-10)
I tried altering the year format to YY, I had it set to YYYY before, just to see what it did, but it made no difference.
CM: multi-lined the select.
[Updated on: Wed, 31 March 2010 09:14] by Moderator Report message to a moderator |
|
|
|
| Re: displaying data in a form called from another [message #449706 is a reply to message #448637] |
Wed, 31 March 2010 09:13 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
The problem isn't date format. The problem is the date isn't wrapped in quotes so it thinks it's an object name. That's why you are getting invalid identifier rather than invalid month or some other date error.
'DateComp BETWEEN to_date('''||:global.DateFrom||''') and to_date('''||:global.DateTo||''')'
is what you need. To get a single ' to display in a string you have to put two ' together in the string.
|
|
|
|
| Re: displaying data in a form called from another [message #449708 is a reply to message #448637] |
Wed, 31 March 2010 09:21 |
rosswelsh
Messages: 42
Registered: February 2010
Location: Sunderland
|
Member |
|
|
That seems to do the trick although it said there was no data to retrieve, which there is.
Just to check, is this code on the OK button correct?
:global.datefrom := :DateCompleted.DateFrom;
:global.dateto := :DateCompleted.DateTo;
call_form('/home/ca5rwe/forms/DateCompResults'); |
|
|
|
| Re: displaying data in a form called from another [message #449716 is a reply to message #448637] |
Wed, 31 March 2010 09:48 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Looks ok.
At this point I'd pull out one of my favourite forms debugging tricks:
get_block_property(<block_name>, last_query);
That returns the last query issued to the db by the block.
Create a button in your search form. Add code to the WHEN-BUTTON-PRESSED to call that and display the results in a message - or better yet, add a item to the datablock and stick the output in that so you can copy it.
Run the form, execute the query, press the button.
Copy and paste the select statement into sqlplus and see what happens.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
any suggestions?
