Home » SQL & PL/SQL » SQL & PL/SQL » Using Variable inside a view
Using Variable inside a view [message #580402] Sun, 24 March 2013 03:39 Go to next message
emadnabil
Messages: 155
Registered: August 2007
Senior Member
Dear all,

i want to put a variable inside a view
here is my view
create or replace view loading as 
SELECT H.ORGANIZATION_ID ORG_ID,H.HEADER_ID,H.REQUEST_NUMBER, H.DATE_REQUIRED ISSUE_DATE,H.ATTRIBUTE1,
H.ORGANIZATION_ID,L.INVENTORY_ITEM_ID,SS.SALES_ID,S.SECONDARY_INVENTORY_NAME SUB_INV_CODE,S.DESCRIPTION SALESPERSON,I.SEGMENT1 ITEM_CODE,
I.DESCRIPTION ITEM_DESC,sum(DECODE(L.UOM_CODE,'EAC',nvl(l.primary_quantity,0),nvl(l.primary_quantity,0))) QTY
FROM MTL_TXN_REQUEST_HEADERS H ,MTL_TXN_REQUEST_LINES L,
MTL_SECONDARY_INVENTORIES_FK_V S, MTL_SYSTEM_ITEMS_B I,SFIS_SALESMEN SS
WHERE H.HEADER_ID = L.HEADER_ID
AND S.SECONDARY_INVENTORY_NAME = H.TO_SUBINVENTORY_CODE
AND SS.SUBUINVENTORY = S.SECONDARY_INVENTORY_NAME
AND S.ORGANIZATION_ID = H.ORGANIZATION_ID
AND I.INVENTORY_ITEM_ID = L.INVENTORY_ITEM_ID
AND I.ORGANIZATION_ID = H.ORGANIZATION_ID
AND H.FROM_SUBINVENTORY_CODE = 'TOBACCO'
and to_date(H.DATE_REQUIRED,'dd-mm-rrrr') = to_date('14-mar-2013','dd-mm-rrrr')
AND H.HEADER_STATUS <>'6'
AND L.LINE_STATUS ='5'
GROUP BY H.HEADER_ID,H.REQUEST_NUMBER, H.DATE_REQUIRED,H.ATTRIBUTE1,
H.ORGANIZATION_ID,L.INVENTORY_ITEM_ID, S.DESCRIPTION,I.DESCRIPTION,I.SEGMENT1,S.SECONDARY_INVENTORY_NAME,SS.SALES_ID;




you can see this line inside my view
and to_date(H.DATE_REQUIRED,'dd-mm-rrrr') = to_date('14-mar-2013','dd-mm-rrrr')


i want this condition to be like
and to_date(H.DATE_REQUIRED,'dd-mm-rrrr') = to_date('Vdate','dd-mm-rrrr')



i want Vdate as a variable parameter

Thanks

Re: Using Variable inside a view [message #580403 is a reply to message #580402] Sun, 24 March 2013 04:00 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Hello there,


One possible solution could be something similar to the following (an example based on the employee table)
CREATE OR REPLACE VIEW myEmpView
AS
    SELECT employee_id, salary
    FROM employees
    WHERE department_id = To_NUMBER(USERENV('client_info'));
/

Then every time before running the query based on your view, you update your parameter. In this example we define as parameter, the department parameter = 100
EXECUTE DBMS_APPLICATION_INFO.SET_CLIENT_INFO('100');

And finally query the view
SELECT *
FROM myEmpView;

Which gives you the result based on the supplied parameter
EMPLOYEE_ID	SALARY
----------- ----------
	108	 12008
	109	  9000
	110	  8200
	111	  7700
	112	  7800
	113	  6900

6 rows selected.

SQL> 


Regards,
Dariyoosh
Re: Using Variable inside a view [message #580404 is a reply to message #580402] Sun, 24 March 2013 04:06 Go to previous message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please (once more) format your query.
If you don't know how to do it, learn it using SQL Formatter.

Regards
Michel
Previous Topic: column with primary key is shown as nullable=Y in user_tab_columns after online redefinition
Next Topic: PL/SQL (INSERT/UPDATE) unique constraint violated error In trigger due to sequencel.nextval
Goto Forum:
  


Current Time: Wed Oct 22 04:33:17 CDT 2014

Total time taken to generate the page: 0.15486 seconds