| list box for passing parameters [message #259639] |
Thu, 16 August 2007 02:49  |
brintha
Messages: 28
Registered: August 2007
|
Junior Member |
|
|
Hi all,
I need to select a value from list for passing parameters to report in 10g. The LOV is dynamic and is got from backend. I need to know if the after parameter form will have the same coding as text box or it has a different code. If i select a value and run the report, It shows an error as "Invalid identifier" and the base query that i wrote is displayed in error message.
Help me with the code in afterparameter form for listbox if it is different.
thanks and regards,
brintha.r
|
|
|
|
|
|
|
|
|
|
| Re: list box for passing parameters [message #259693 is a reply to message #259683] |
Thu, 16 August 2007 04:55  |
brintha
Messages: 28
Registered: August 2007
|
Junior Member |
|
|
hi,
im giving the base query and the afterparameter form query.
Base query:
SELECT DISTINCT (SH.SHIPMENT_GID),
SH.SHIPMENT_NAME,
SH.TARIFF_NAME,
SH.TARIFF_ORGANIZATION_NUMBER,
SH.TARIFF_ORGANIZATION_NAME,
SH.TOTAL_NET_WEIGHT,
SH.TOTAL_NET_VOLUME,
SH.TOTAL_WEIGHT,
SH.TOTAL_VOLUME,
SH.IS_MEMO_BL,IN_TRAILER_BUILD,
SH.START_TIME,
SH.END_TIME,
SH.TERM_LOCATION_TEXT,
SH.CM_NAME,
SH.INSURANCE_POLICY_NUMBER
FROM
SHIPMENT SH,
SHIPMENT_REFNUM S,
SHIPMENT_REFNUM_QUAL SRQ
WHERE
SH.DOMAIN_NAME='RAJ01EPUDO' AND
S.SHIPMENT_GID= SH.SHIPMENT_GID AND
S.SHIPMENT_REFNUM_QUAL_GID=SRQ.SHIPMENT_REFNUM_QUAL_GID AND &P_SHIP
After parameter form query:
function AfterPForm return boolean is
v_string varchar2(32750);
ws_date varchar2(30);
v_boolean BOOLEAN;
startDate Date;
endDate Date;
NOOFMONTHS number :=0;
REFERENCE_VALUE Varchar2(100) :='1=1';
ACCOUNT_NUMBER Varchar2(100) :='1=1';
PLS_ORIG_P1 VARCHAR2(32766) ;
StartDateUTC Varchar2(50) :='';
EndDateUTC Varchar2(50) :='';
begin
IF :p_ship IS NOT NULL AND :p_ship != '1=1' THEN
PLS_ORIG_P1 := :p_ship ;
:p_ship:= 'RPT_GENERAL.F_REMOVE_DOMAIN(SH.INSURANCE_POLICY_NUMBER)' ||GET_FILTER_CONDITION(RPT_GENERAL.F_REMOVE_DOMAIN(:p_ship));
ELSE
:p_ship :=' 1=1 ';
END IF;
return (TRUE);
end;
thanks and regards,
brintha.r
[Updated on: Thu, 16 August 2007 04:59] Report message to a moderator |
|
|
|
| Re: list box for passing parameters [message #259706 is a reply to message #259693] |
Thu, 16 August 2007 05:17 |
 |
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Base query works correctly, right?
I guess that error appears somewhere in RPT_GENERAL.F_REMOVE_DOMAIN or GET_FILTER_CONDITION. If so, you'll have to check what is happening with all the parameters you pass to it, because it seems that one/or more of them is evaluated differently than you expect, thus resulting with an error.
| Oracle | ORA-00904: string: invalid identifier
Cause: The column name entered is either missing or invalid.
Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.
|
Perhaps it is not wrong, but - what does 'SH.' represent here::p_ship:= 'RPT_GENERAL.F_REMOVE_DOMAIN(SH.INSURANCE_POLICY_NUMBER |
|
|
|
| Re: list box for passing parameters [message #259724 is a reply to message #259706] |
Thu, 16 August 2007 06:00 |
brintha
Messages: 28
Registered: August 2007
|
Junior Member |
|
|
hi,
thanks for ur reply. yes. there is no problem with the base query. but if a value which is selected in the list box is not correctly converted to string in after parameter form. i think that is the problem. SH. is alias table.
thanks and regards,
brintha.r
|
|
|
|
|
|
| Re: list box for passing parameters [message #259737 is a reply to message #259730] |
Thu, 16 August 2007 06:42 |
brintha
Messages: 28
Registered: August 2007
|
Junior Member |
|
|
hi,
yes. I dont know how to convert the value selected from list. Im also not aware of how it is passed to main query once it is selected and of the value(format) that is passed to after pform.
this is the code in after pform that works correctly fot a text box.
but i dont know if i can use the same for list.
IF :p_ship IS NOT NULL AND :p_ship != '1=1' THEN
PLS_ORIG_P1 := :p_ship ;
:p_ship:= 'RPT_GENERAL.F_REMOVE_DOMAIN(SH.INSURANCE_POLICY_NUMBER)' ||GET_FILTER_CONDITION(RPT_GENERAL.F_REMOVE_DOMAIN(:p_ship));
ELSE
:p_ship :=' 1=1 ';
END IF;
regards,
brintha.r
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
