| Parameter using in LOV [message #87472] |
Thu, 06 January 2005 17:51  |
rupa
Messages: 41
Registered: August 2002
|
Member |
|
|
Hi Friends!
I have a trigger and it has a Loop. I am getteing some values for an item (say country) and I am assigning it to a Parameter within that Loop. I am using this Parameter values for some other purpose. Now I need to use the same Parameter values in my LOV 'SQL query'.
For Eg: I am getting a country value in a loop. Each time the coutry value is adding to Parameter as like this :Parameter.Ctry = US,UK,AUS,GER etc
Now I have a query in my LOV and want to use this parameter values. I tried using my sql query like this
'Select item1 from table1 where country in (:parameter.ctry)'. But this is not working in run time. Can anybody help me how to use this values.
|
|
|
|
| Re: Parameter using in LOV [message #87474 is a reply to message #87472] |
Thu, 06 January 2005 20:12  |
Sam
Messages: 255
Registered: April 2000
|
Senior Member |
|
|
hi rupa,
your lov is not populating because oracle reads all of the values ie. US,UK,IN as a single string....
you nead to break this into individual varchar2 strings...like 'US','UK','IN'.... then only your query will work....
assign the country values in parameter like this ....
if :parameter.ctry is null then
:parameter.city := chr(39)||US||chr(39);
else
:parameter.city := :parameter.ctry||','||chr(39)||US||chr(39);
end if;
after that run your query , it is going to work.....
regards,
Sam.........
|
|
|
|
| Re: Parameter using in LOV [message #87476 is a reply to message #87474] |
Thu, 06 January 2005 20:25 |
Sam
Messages: 255
Registered: April 2000
|
Senior Member |
|
|
seems like it was printed wrong........
it is actually
if :parameter.ctry is null then
:parameter.city := chr(39)||US||chr(39);
else
:parameter.city := :parameter.ctry||','||chr(39)||US||chr(39);
end if;
ignore all the ; characters that appear except at the ned of the statement....
it was printed by default.............
cheers....
sam
|
|
|
|
| Re: Parameter using in LOV [message #87477 is a reply to message #87474] |
Thu, 06 January 2005 21:02 |
rupa
Messages: 41
Registered: August 2002
|
Member |
|
|
Hi Sam!
Thanks for early reply. I already assigned the parameter like below. It is the same as what you said but little different(The result is same with your code and my code). My code is as follows:
IF :PARAMETER.P_CTRY IS NULL THEN
:PARAMETER.P_CTRY := ''''||CTRY||'''';
ELSE
:PARAMETER.P_CTRY := :PARAMETER.P_CTRY||','||''''||
CTRY||'''';
END IF;
**('CTRY' is a temporary item which gets the country code from a select statement inside a loop)
Currently I am getting the values like this if I use the code as above: 'US','UK','GER','FRA',etc.
But I can't get these values in my LOV as I am getting these in a LOOP.
Now my query is how I have to use these values (I may have 50+ countries) in my LOV's 'SQL Query' without hardcoding.
|
|
|
|
| Re: Parameter using in LOV [message #87478 is a reply to message #87477] |
Thu, 06 January 2005 22:12 |
Sam
Messages: 255
Registered: April 2000
|
Senior Member |
|
|
hi rupa...
dynamically create the lov in key-list-val trigger of the item where you require the LOV....this will give you the option of manipulating it in anyway in future....
here is a sample code of key-list-val to dynamically create lov and populate it.............
****************************************************
DECLARE
X BOOLEAN;
rg_name VARCHAR2(40) := 'RG_CNTRY';
rg_id RecordGroup;
lov_id LOV;
qry varchar2(4000);
BEGIN
---BEFORE THIS ASSIGN ALL THE VALUES IN LOOP IN :PARAMETER.CNTRY
rg_id := Find_Group( rg_name );
IF :PARAMETER.CNTRY IS NULL THEN
--THIS IS FOR SELECTING ALL VALUES---------------------
qry := 'SELECT A FROM B';
ELSIF :PARAMETER.CNTRY IS NOT NULL THEN
qry := 'SELECT A FROM B
WHERE A.COUNTRY NOT IN ('||:PARAMETER.CNTRY||')';
END IF;
IF NOT Id_Null(rg_id) THEN
Delete_Group( rg_id );
END IF;
rg_id := Create_Group_From_Query( rg_name,qry);
/*** Populate the record group */
errcode := Populate_Group( rg_id );
lov_id := Find_LOV('LV_COUNTRY');
Set_LOV_Property(lov_id,GROUP_NAME,'RG_CNTRY');
X := SHOW_LOV('LV_COUNTRY',110,25);
END; -- END OF CODE
try it and tell me whether it works ....
i am sure it will.,....
best of luck............
regards,
sam...
|
|
|
|
| Re: Parameter using in LOV [message #87504 is a reply to message #87478] |
Sun, 09 January 2005 16:58 |
rupa
Messages: 41
Registered: August 2002
|
Member |
|
|
Hi Sam!
Thanks for your suggestion but it still not working.
The values are coming into :Parameter.P_CNTRY but it's not showing any values. If I hardcode the values it's working. Can you suggest anything else????
|
|
|
|
| Re: Parameter using in LOV [message #87514 is a reply to message #87504] |
Mon, 10 January 2005 17:31 |
Sam
Messages: 255
Registered: April 2000
|
Senior Member |
|
|
send me the code of how you are assigning the values.....and the query you have written to popluate the values.......
regards ,
sam....
|
|
|
|
| Re: Parameter using in LOV [message #87520 is a reply to message #87514] |
Tue, 11 January 2005 00:49 |
rupa
Messages: 41
Registered: August 2002
|
Member |
|
|
Hi Sam!
Sorry to trouble you.
I am getting some problem using a LOV & a parameter.
I tried using my parameter value in Lov's query but couldn't work out. So what I did is I created dynamically the Record group and tried to get the values but still it's not giving the result.
I tried using the below code at the
when-button-pressed trigger for an item. The value of
:PARAMETER.P_CTRY is coming correctly only at this
trigger as it's showing in the message. But when I set
the LOV property, the GROUP_NAME is not changing. I
think the problem is here. Can you check this code
whether I am doing any mistake.
DECLARE
A_VALUE_CHOSEN BOOLEAN;
RG_NAME VARCHAR2(40):= 'RG_CNTRY';
ERRCODE NUMBER;
RG_ID RECORDGROUP;
P_QUERY VARCHAR2(2000);
LOV_ID LOV;
RG_NM VARCHAR2(40);
BEGIN
RG_ID := FIND_GROUP(RG_NAME);
P_QUERY := 'SELECT VALUE,VALUE_DESC FROM '||
DW_GARS_SCREEN_MAINT_R '||
'WHERE FIELD_NAME = '||
'GARS Country'||' AND VALUE IN(
'||:PARAMETER.P_CTRY ||
') ORDER BY 1,2';
MESSAGE('COUNTRY IS : '||:PARAMETER.P_CTRY);
/*
IF I SEE THE VALUE OF :PARAMETER.P_CTRY HERE BY GIVING
A MESSAGE, THEN THE :PARAMETER.P_CTRY VALUE IT'S
COMING AS 'US','UK','AUS',ETC. THATMEANS THIS VALUE IS
CORRECT. SO NO PROBLEM WITH :PARAMETER.P_CTRY
*/
IF ID_NULL(RG_ID) THEN
DELETE_GROUP(RG_ID);
END IF;
RG_ID := CREATE_GROUP_FROM_QUERY(RG_NAME,P_QUERY);
ERRCODE := POPULATE_GROUP(RG_ID);
LOV_ID := FIND_LOV('G_COUNTRY');
RG_NM := GET_LOV_PROPERTY(LOV_ID,GROUP_NAME);
MESSAGE('GROUP NAME(1ST TIME) : '||RG_NM);
IF RG_NM = 'GR_COUNTRY' THEN
SET_LOV_PROPERTY(LOV_ID,GROUP_NAME,RG_NAME);
SET_LOV_PROPERTY
(LOV_ID,AUTO_REFRESH,PROPERTY_FALSE);
MESSAGE('GROUP (2ND TIME) IS : '
||GET_LOV_PROPERTY(LOV_ID,GROUP_NAME));
/* HERE THE SET_LOV_PROPERTY IS NOT WORKING As THE
GROUP NAME IS STILL SHOWING AS 'GR_COUNTRY' ONLY.
WHY???????????????????? I ALSO DON'T KNOW. THIS IS
CAUSING THE ACTUAL PROBLEM. THE LOV IS NOT CHANGING */
END IF;
A_VALUE_CHOSEN := SHOW_LOV('G_COUNTRY');
IF NOT A_VALUE_CHOSEN THEN
MESSAGE('You have not selected a value.');
BELL;
RAISE FORM_TRIGGER_FAILURE;
END IF;
END;
Note:-- 'G_COUNTRY' is a 'LOV' and is attached to the
item. It's Record Group is 'GR_COUNTRY'.
The SQL query in Record Group 'GR_COUNTRY' is just a
select statement only. i.e.,
'SELECT VALUE,VALUE_DESC FROM DW_GARS_SCREEN_MAINT_R'.
I am getting the Parameter(:PARAMETER.P_CTRY) value
from the trigger NEW-FORM-INSTANCE using below code.
DECLARE
CURSOR CUR_ROLE IS SELECT GRANTED_ROLE
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = :GLOBAL.USER_NAME;
CTRY VARCHAR2(30);
BEGIN
:GLOBAL.USER_NAME :=
GET_APPLICATION_PROPERTY(USERNAME);
FOR C1 IN CUR_ROLE
LOOP
BEGIN
SELECT SUBSTR(C1.GRANTED_ROLE,
INSTR(C1.GRANTED_ROLE,'_' ) + 1,
LENGTH(C1.GRANTED_ROLE))
INTO CTRY FROM DUAL;
IF :PARAMETER.P_CTRY IS NULL THEN
:PARAMETER.P_CTRY := ''''||CTRY||'''';
ELSE
:PARAMETER.P_CTRY := :PARAMETER.P_CTRY||','
||''''||CTRY||'''';
END IF;
--<<<SOME TRANSACTIONS HERE USING PARAMETER VALUE>>>--
END LOOP;
END;
END;
Note:-- GRANTED_ROLE values are :
GARS_GER,GARS_US,GARS_UK,GARS_AUS,etc.
I get the country name using GRANTED_ROLE (its
suffix).
Thanks
|
|
|
|
| Re: Parameter using in LOV [message #87526 is a reply to message #87520] |
Tue, 11 January 2005 02:35 |
Sam
Messages: 255
Registered: April 2000
|
Senior Member |
|
|
in the place,
IF RG_NM = 'GR_COUNTRY' THEN
SET_LOV_PROPERTY(LOV_ID,GROUP_NAME,RG_NAME);
SET_LOV_PROPERTY
(LOV_ID,AUTO_REFRESH,PROPERTY_FALSE);
MESSAGE('GROUP (2ND TIME) IS : '
||GET_LOV_PROPERTY(LOV_ID,GROUP_NAME));
/* HERE THE SET_LOV_PROPERTY IS NOT WORKING As THE
GROUP NAME IS STILL SHOWING AS 'GR_COUNTRY' ONLY.
WHY???????????????????? I ALSO DON'T KNOW. THIS IS
CAUSING THE ACTUAL PROBLEM. THE LOV IS NOT CHANGING */
END IF;
directly assign ...
SET_LOV_PROPERTY(LOV_ID,GROUP_NAME,'RG_CNTRY');
then
write
x := show_lov('G_COUNTRY');
and write this code in key-list-val trigger of the item....
best of luck.....
|
|
|
|
| Re: Parameter using in LOV [message #87529 is a reply to message #87526] |
Tue, 11 January 2005 16:04 |
rupa
Messages: 41
Registered: August 2002
|
Member |
|
|
Hi Sam!
Thank you very much. Solved the problem and is working well now. I made a little mistake in my code. When I am assigning P_QUERY with a select statement one single quote was missing. I corrected it. And I also changed the code which you adviced. It worked out. Thankyou once again for your help.
WISH YOU A HAPPY & PROSPEROUS NEW YEAR.
Bye...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
