Home » Developer & Programmer » Forms » Querying an particular Field issue ..
Querying an particular Field issue .. [message #348199] Tue, 16 September 2008 00:18 Go to next message
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 #348207 is a reply to message #348199] Tue, 16 September 2008 00:46 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I believe I understood the first part of the message. However, I'd rewrite POST-QUERY trigger as
SELECT Item_name 
INTO :block_name.item_name
FROM xyz
WHERE item_id = :xx.item_id;

So far, so good (as you've said).

Could you, please, explain once again what these two sentences mean:
Quote:
1) But when i query for a particular Item_name it is not looking into it and retrieving all the records.

2) I need to have both to query all the records and a particular Item_name.

1) Looking into what?
2) ???
Re: Querying an particular Field issue .. [message #348222 is a reply to message #348199] Tue, 16 September 2008 01:57 Go to previous messageGo to next message
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,

  1. 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.
  2. 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 Go to previous messageGo to next message
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.



Re: Querying an particular Field issue .. [message #348774 is a reply to message #348460] Wed, 17 September 2008 23:06 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Format your code!!!

David
Previous Topic: forms 10g application error
Next Topic: moves between two blocks
Goto Forum:
  


Current Time: Wed Dec 07 16:50:22 CST 2016

Total time taken to generate the page: 0.09750 seconds