| Querying an particular Field issue .. [message #348199] |
Tue, 16 September 2008 00:18  |
dev88
Messages: 15
Registered: September 2008
Location: SAN JOSE
|
Junior Member |
|
|
Hi Gurus,
Working on a form, and facing an issue while querying for any particular field.
Ex: I have a field called Item_name which is an non-database column displayed and Item_id DB item not displayed on the form and have LOV like (select item_id,item_name from xyz) attached to Item_name.
At Block level, in POST-QUERY have a query like below
BEGIN
SELECT Item_name INTO l_item_name
FROM xyz
WHERE item_id = :xx.item_id
AND 1=1;
:BLOCK_NAME.item_name := l_item_name ;
END;
Currently it is working fine when i query for all the records, But when i query for a particular Item_name it is not looking into it and retrieving all the records.
Please suggest me where i am wrong. I need to have both to query all the records and a particular Item_name.
Thanks in advance..
DD.
[Updated on: Tue, 16 September 2008 00:19] Report message to a moderator |
|
|
|
|
|
| Re: Querying an particular Field issue .. [message #348222 is a reply to message #348199] |
Tue, 16 September 2008 01:57  |
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
|
Senior Member |
|
|
As I understood, you want to retrieve all records if you don't choose a name in ENTER-QUERY mode, and retrieve only the record related to the chosen name in case you choose one.
Besides, where the ID is filled? Normally, it must be filled when choosing a name from the LOV related to the name. But is the name unique? Is just one ID is retrieved for every name in the LOV of the "name" field?
If this is the case, your query must be built as Littlefoot said:
SELECT Item_name
INTO :block_name.item_name
FROM xyz
WHERE item_id = :xx.item_id;
In Enter-QUERY mode,
- if you are retrieving all records (not choosing a name), the name should be retrieved depending on the ID "queried", since the POST-QUERY fires after querying each record.
- If you choose a name (from the LOV), the ID is then filled, and the query will get the name related to this ID even if the name field is already filled by the LOV.
Briefly, check if the ID (the database item) is filled correctly, and the name is retrieved depending on this ID.
|
|
|
|
| Re: Querying an particular Field issue .. [message #348460 is a reply to message #348199] |
Tue, 16 September 2008 16:51 |
dev88
Messages: 15
Registered: September 2008
Location: SAN JOSE
|
Junior Member |
|
|
Hi Gurus,
Thank you, for your replyes. My issue was when i query all the records it was retriview fine, but when i query for a partial item name (COMPUTE%) its was retriving.
I found the fix, its working fine now. Here below i did.
I have coded in PRE-QUERY like below
---------------------
IF :BLOCK_NAME.ITEM_NAME IS NOT NULL THEN
SET_BLOCK_PROPERTY('BLOCK_NAME,DEFAULT_WHERE,
'item_id IN( SELECT item_id from XYZ msib
WHERE ITEM_NAME LIKE :BLOCK_NAME.ITEM_NAME)' );
END IF;
------------------------
Thanks,
DD.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
