Home » Developer & Programmer » Reports & Discoverer » list box for passing parameters
list box for passing parameters [message #259639] Thu, 16 August 2007 02:49 Go to next message
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 #259647 is a reply to message #259639] Thu, 16 August 2007 03:08 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What does this mean?
Quote:
LOV is dynamic and is got from backend.

Are there two queries - one used in List of Values of report's parameter and another ... somewhere else (a form?); what is a "text box" you mention?

If you are talking about comparing List of values query and any other query, I'm afraid it is not possible. At least, I don't know the way to do that. However, if you use the same data set in several products, could you create a VIEW and base all those queries on it? Doing so, you would be sure that data set is always the same (and you, probably, wouldn't get the INVALID IDENTIFIER error); restrict it using the additional parameter in List of values' WHERE condition.
icon7.gif  Re: list box for passing parameters [message #259659 is a reply to message #259647] Thu, 16 August 2007 03:57 Go to previous messageGo to next message
brintha
Messages: 28
Registered: August 2007
Junior Member
hi,
Im not comparing List of values query and any other query. if i select one value out of list and run the reoprt, I get an error "invalid identifier". Im not sure if the code i had written in after parameter form for list box is correct. I just followed the same code that is used for a text box. I need the correct code for it. sorry if im not clear in the previous mail.

thanks and regards,
brintha.r



Re: list box for passing parameters [message #259683 is a reply to message #259659] Thu, 16 August 2007 04:33 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ah, I see!

So, why wouldn't you post both queries so that we could see them and, possibly, give our opinion about the issue?
Re: list box for passing parameters [message #259693 is a reply to message #259683] Thu, 16 August 2007 04:55 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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
It is a string (here) so compiler doesn't complain during trigger compilation time, but - what does it do at runtime? Is there a table whose alias is 'SH'? If not, this might result with an error.
Re: list box for passing parameters [message #259724 is a reply to message #259706] Thu, 16 August 2007 06:00 Go to previous messageGo to next message
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 #259730 is a reply to message #259724] Thu, 16 August 2007 06:12 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
brintha
a value which is selected in the list box is not correctly converted to string in after parameter form

What does it mean? Would TO_CHAR help? Or is it a CHAR so you'd need to TRIM it?
Re: list box for passing parameters [message #259737 is a reply to message #259730] Thu, 16 August 2007 06:42 Go to previous message
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
Previous Topic: Date function to be used in reports..QRY
Next Topic: Displaying column dynamically in report using parameters
Goto Forum:
  


Current Time: Fri Dec 09 01:55:35 CST 2016

Total time taken to generate the page: 0.11292 seconds