| Problem on search by character . [message #455306] |
Tue, 11 May 2010 01:38  |
limit
Messages: 16
Registered: December 2006
Location: Dhaka, Bangladesh.
|
Junior Member |
|
|
I have two table:
Table 1:
CREATE TABLE Test1
(
FI_ID INTEGER NOT NULL,
FI_CLASS_ID INTEGER NOT NULL,
)
ALTER TABLE Test1 ADD (
CONSTRAINT PK_Test1 PRIMARY KEY (FI_ID, FI_CLASS_ID)
ALTER TABLE Test1 ADD (CONSTRAINT FK_Test1_FI FOREIGN KEY FI_ID)
REFERENCES Test2 (FI_ID))
Table 2:
CREATE TABLE Test2
(
FI_ID INTEGER NOT NULL,
FI_NM VARCHAR2(60 BYTE) NOT NULL,
FI_ALIAS
)
ALTER TABLE Test2 (
CONSTRAINT PK_Test2
PRIMARY KEY
(FI_ID)
I am working on the table Test1. And also display data of Test1 in Form. I already have displayed 'FI_NM' against 'FI-ID' using Form Trigger Post-Query in the Form.
And also I have a text field and a Button named 'Search'. When I input any character in a text field and press Button then display error: "Query caused no records to be retrieved." But I input numeric value the display data in the form block.
For that I am using the following codes in
When-button-pressed:
:GLOBAL.TXT_ITEM :='%' || UPPER(:BLK_TEMP.TXT_FI_ID_NAME) || '%';
BEGIN
SET_BLOCK_PROPERTY('Test1', ONETIME_WHERE, 'FI_ID LIKE :GLOBAL.TXT_ITEM');
go_block('Test1');
EXECUTE_QUERY(NO_VALIDATE);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
Please help and also tell how to solve that problem.
Regards,
Noor
|
|
|
|
|
|
| Re: Problem on search by character . [message #455313 is a reply to message #455310] |
Tue, 11 May 2010 02:32  |
limit
Messages: 16
Registered: December 2006
Location: Dhaka, Bangladesh.
|
Junior Member |
|
|
When I use:
SET_BLOCK_PROPERTY('Test1', ONETIME_WHERE, 'FI_ID LIKE :GLOBAL.TXT_ITEM');
and also I input no numeric value or character, display all records. and
I input only numeric(1 or 11) value then display result, if match with FI_ID. And I input only character then display an error: "Query caused no records to be retrieved.".
When I use:
SET_BLOCK_PROPERTY('Test1', ONETIME_WHERE, 'FI_ID LIKE ''||:GLOBAL.TXT_ITEM||''');
Always display an error: "Query caused no records to be retrieved."
I want to display result by searching character value not numeric value.
Please give solution asap.
Please see the attached JPG file for quick understand what I need.
-
Attachment: Test1.JPG
(Size: 49.59KB, Downloaded 633 times)
[Updated on: Tue, 11 May 2010 02:46] Report message to a moderator |
|
|
|
|
|
| Re: Problem on search by character . [message #455321 is a reply to message #455319] |
Tue, 11 May 2010 02:54 |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
I strongly recommend that you do not use 'ASAP' again. This is a voluntary activity for us all and we will provide a solution or guidance as and when possible.
David
PS I am now going home so someone else will have to provide answers to you for the next 20 hours.
[Updated on: Wed, 12 May 2010 01:22] Report message to a moderator |
|
|
|
|
|
| Re: Problem on search by character . [message #455326 is a reply to message #455322] |
Tue, 11 May 2010 03:21 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Unless I'm wrong, you'll NEVER find anything with this approach.
TEST1.FI_ID is INTEGER. How do you expect it to work if you enter a "character" into the search item and, later, use (one of the following variations):
SET_BLOCK_PROPERTY('Test1', ONETIME_WHERE, 'FI_ID LIKE :GLOBAL.TXT_ITEM');
As FI_ID is a number, you can't even store character values in there so ... the whole idea is wrong.
What do you, actually, want to do? Search what? FI_IDs? Names? If the latter, no way as it doesn't belong to a table data block is based on. You'll either have to create a view or, possibly, search via List of values (where you'd see "name" along with its ID).
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Problem on search by character . [message #455340 is a reply to message #455338] |
Tue, 11 May 2010 04:32 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
limit wrote on Tue, 11 May 2010 10:23
SET_BLOCK_PROPERTY('Test1', ONETIME_WHERE, 'FI_ID IN (SELECT FI_NM FROM Test2 WHERE FI_NM LIKE ''||:GLOBAL.TXT_ITEM||'')');
is perfect. But FI_ID is numeric value.
How is that perfect exactly? You're checking if a numeric is in a character.
limit wrote on Tue, 11 May 2010 10:23
This way, how can I assign FI_NM into :STG_A_FI_X_FI_CLASS.TXT_FI_NAME?
With a select statement? In the post-query trigger?
|
|
|
|
|
|
|
|
|
|
| Re: Problem on search by character . [message #455362 is a reply to message #455348] |
Tue, 11 May 2010 06:15 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
It's a function.
Create a new item in your datablock, put it on the canvas, make sure it's set to be a non-database item.
Set it's datatype to character and it's length to 4000.
Add a button. In the WHEN-BUTTON-PRESSED put:
:STG_A_FI_X_FI_CLASS.<whatever your item is called> := GET_BLOCK_PROPERTY('STG_A_FI_X_FI_CLASS', last_query);
Press the button after running the query and post the result here.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Problem on search by character . [message #455571 is a reply to message #455550] |
Wed, 12 May 2010 03:46 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
The code I gave you originally should work.
Since it didn't I gave you additional code to run to enable us to debug the problem.
Since that didn't appear to work either Littlefoot asked you to post your current code so that we can see exactly what you have done.
Without that we can not help you further.
|
|
|
|
|
|
| Re: Problem on search by character . [message #455599 is a reply to message #455591] |
Wed, 12 May 2010 04:33 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That doesn't look anything like the code I told you use.
Use my code.
And don't ever code:
EXCEPTION
WHEN OTHERS THEN
NULL;
We want to see errors. it's the only way we'll find out what the problem is.
|
|
|
|
|
|
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
