Not sure if i am using lexical parameter properly [message #336485] |
Sun, 27 July 2008 15:15 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ora1980
Messages: 251 Registered: May 2008
|
Senior Member |
|
|
Hi all
I am trying to incorporate a lexixal parameter in my report
so i changed my query to
SELECT OH.ORDER_NUMBER,OH.ORDERED_DATE,OL.LINE_NUMBER,OH.FLOW_STATUS_CODE,OL.SCHEDULE_ARRIVAL_DATE,OL.ORDERED_ITEM,MSI.DESCRIPTION
FROM OE_ORDER_HEADERS_ALL OH,
OE_ORDER_LINES_ALL OL,MTL_SYSTEM_ITEMS MSI
WHERE OH.HEADER_ID=OL.HEADER_ID AND MSI.INVENTORY_ITEM_ID=OL.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID=OL.ORG_ID ®no
which orignally was
SELECT OH.ORDER_NUMBER,OH.ORDERED_DATE,OL.LINE_NUMBER,OH.FLOW_STATUS_CODE,OL.SCHEDULE_ARRIVAL_DATE,OL.ORDERED_ITEM,MSI.DESCRIPTION
FROM OE_ORDER_HEADERS_ALL OH,
OE_ORDER_LINES_ALL OL,MTL_SYSTEM_ITEMS MSI
WHERE OH.HEADER_ID=OL.HEADER_ID AND MSI.INVENTORY_ITEM_ID=OL.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID=OL.ORG_ID and oh.header_id
between :reg1 and :reg2
so when i am running the report, i see the extra prompt for
a lexical parameter, and i dont enter in that text box..
the report runs fine, but my doubt is,
by simply changing the query like this, shall i conclude that i have used lexical parameter properly ? is that all ?
so how
|
|
|
Re: Not sure if i am using lexical parameter properly [message #336521 is a reply to message #336485] |
Mon, 28 July 2008 00:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Obviously, as the report runs without an error, you have used a lexical parameter properly.
However, as you left it empty, it does nothing (as if it doesn't exist). Therefore, you may omit it from a query (if you don't plan to use it).
P.S. Forgot to tell ... lexical parameter is usually initialized in the AFTER PARAMETER FORM trigger. You'd create a parameter (not a lexical one) which has a certain value (for example, two NUMBER parameters). Then you'd initialize lexical parameter as:lex_param := 'and header_id between ' || :par_id_1 || ' and ' || :par_id_2;
[Updated on: Mon, 28 July 2008 00:42] Report message to a moderator
|
|
|
|