Advertisement

07.23.2008 at 12:40PM PDT, ID: 23589869 | Points: 200
[x]
Attachment Details

how to fix " bind variables not allowed for data definition operations"

Asked by wasabi3689 in PL / SQL, Oracle 8.x

When I just run select... without creating view, it works fine

When I run my query ( create view...) I have the following error
ORA-01027: bind variables not allowed for data definition operations

How to fix it?

Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:

DROP VIEW xxcdb.xx_assets_depreciation_v;
 
CREATE OR REPLACE VIEW xxcdb.xx_assets_depreciation_v
 
(
 
          asset_id, asset_number, serial_number,  tag_number, po_number, model_number,
          description, attribute_category_code, segment2, manufacturer_name,
          date_placed_in_service, original_cost, current_cost, 
          adjusted_cost, deprn_amount, acmulated_deprn, net_book_value, deprn_run_date, 
          CALENDAR_PERIOD_OPEN_DATE, CALENDAR_PERIOD_CLOSE_DATE,
          DATE_RETIRED,
          full_name, EMPLOYEE_NUMBER,
          units_assigned, state, location, retirement_pending_flag, 
          book_type_code
 
)
 
As  
 
 
 
 
(
 
SELECT    distinct
          fav.asset_id, 
          fav.asset_number, fav.serial_number,  fav.tag_number, 
          fiv.po_number, fav.model_number,
          fav.description, fav.attribute_category_code, fc.segment2, fav.manufacturer_name,
          fb.date_placed_in_service, fb.original_cost, fb.cost current_cost, 
          fb.adjusted_cost, x.deprn_amount, x.acmulated_deprn, 
          fb.original_cost-x.acmulated_deprn net_book_value, x.deprn_run_date,
          x.CALENDAR_PERIOD_OPEN_DATE, x.CALENDAR_PERIOD_CLOSE_DATE,
          ret.DATE_RETIRED,
          ppx.full_name, ppx.EMPLOYEE_NUMBER,
          fdh.units_assigned, fl.segment2 state,
          fl.segment1|| '.' ||fl.segment2|| '.' ||fl.segment3 || '.' ||fl.segment4 location, 
          fb.retirement_pending_flag,
          x.book_type_code
     FROM apps.fa_categories fc,
          apps.fa_invoice_details_v fiv,
          apps.per_people_x ppx,
          apps.fa_distribution_history fdh,
          apps.fa_books_v fb,
          apps.fa_additions_v fav,
          apps.fa_retirements ret,
          apps.fa_locations fl,
         
          (
           
           select  fd.JE_HEADER_ID,
                   fd.COST, fd.deprn_amount, 
                    deprn_reserve acmulated_deprn,
                    fd.deprn_run_date, 
                    fdp.CALENDAR_PERIOD_OPEN_DATE, fdp.CALENDAR_PERIOD_CLOSE_DATE,
                   fd.asset_id, fd.book_type_code
                   from apps.fa_deprn_detail fd 
                   ,fa.fa_deprn_periods fdp
                   where fd.deprn_amount<>0 
                   and fdp.period_counter = fd.PERIOD_COUNTER
                   and fdp.BOOK_TYPE_CODE=fd.BOOK_TYPE_CODE
                   order by fd.deprn_run_date desc
           
           ) x  
           
          
 
    WHERE fav.asset_category_id = fc.category_id
      AND fb.asset_id(+) = fav.asset_id
      AND fdh.asset_id(+) = fav.asset_id
      AND TRUNC (SYSDATE) >= TRUNC (fdh.date_effective(+))
      AND fdh.date_ineffective IS NULL
      AND fdh.assigned_to = ppx.person_id(+)
      AND fiv.asset_id(+) = fav.asset_id
      and fb.book_type_code = 'WRI CORPORATE' 
      and fav.asset_id = x.asset_id 
      and fl.location_id = fdh.location_id
      --and fb.retirement_pending_flag='NO'
      and ret.asset_id (+) =fav.asset_id
      and x.CALENDAR_PERIOD_CLOSE_DATE between :begin_date and :end_date
      
      --and fb.asset_id ='1025'
      -- and fav.ASSET_NUMBER ='1025'
      --and fb.asset_id between '2600' and '8500'
      --and fb.date_placed_in_service between to_date('1/1/2007', 'mm/dd/yyyy') and to_date('12/31/2007', 'mm/dd/yyyy')
      --and x.deprn_run_date between to_date('1/1/2007', 'mm/dd/yyyy') and to_date('5/31/2007', 'mm/dd/yyyy')
     
 union
 
 SELECT    distinct
          fav.asset_id, 
          fav.asset_number, fav.serial_number,  fav.tag_number, 
          fiv.po_number, fav.model_number,
          fav.description, fav.attribute_category_code, fc.segment2, fav.manufacturer_name,
          fb.date_placed_in_service, fb.original_cost, fb.cost current_cost, 
          fb.adjusted_cost, x.deprn_amount, x.acmulated_deprn, 
          fb.original_cost-x.acmulated_deprn net_book_value, x.deprn_run_date,
          x.CALENDAR_PERIOD_OPEN_DATE, x.CALENDAR_PERIOD_CLOSE_DATE,
          ret.DATE_RETIRED,
          ppx.full_name, ppx.EMPLOYEE_NUMBER,
          fdh.units_assigned, fl.segment2 state,
          fl.segment1|| '.' ||fl.segment2|| '.' ||fl.segment3 || '.' ||fl.segment4 location, 
          fb.retirement_pending_flag,
          x.book_type_code
     FROM apps.fa_categories fc,
          apps.fa_invoice_details_v fiv,
          apps.per_people_x ppx,
          apps.fa_distribution_history fdh,
          apps.fa_books_v fb,
          apps.fa_additions_v fav,
          apps.fa_retirements ret,
          apps.fa_locations fl,
         
          (
           
           select  fd.JE_HEADER_ID,
                   fd.COST, fd.deprn_amount, 
                    deprn_reserve acmulated_deprn,
                    fd.deprn_run_date, 
                    fdp.CALENDAR_PERIOD_OPEN_DATE, fdp.CALENDAR_PERIOD_CLOSE_DATE,
                   fd.asset_id, fd.book_type_code
                   from apps.fa_deprn_detail fd 
                   ,fa.fa_deprn_periods fdp
                   where fd.deprn_amount<>0 
                   and fdp.period_counter = fd.PERIOD_COUNTER
                   and fdp.BOOK_TYPE_CODE=fd.BOOK_TYPE_CODE
                   order by fd.deprn_run_date desc
           
           ) x  
           
          
 
    WHERE fav.asset_category_id = fc.category_id
      AND fb.asset_id(+) = fav.asset_id
      AND fdh.asset_id(+) = fav.asset_id
      AND TRUNC (SYSDATE) >= TRUNC (fdh.date_effective(+))
      AND fdh.date_ineffective IS NULL
      AND fdh.assigned_to = ppx.person_id(+)
      AND fiv.asset_id(+) = fav.asset_id
      and fb.book_type_code = 'WRI CORPORATE' 
      and fav.asset_id = x.asset_id 
      and fl.location_id = fdh.location_id
      --and fb.retirement_pending_flag='NO'
      and ret.asset_id (+) =fav.asset_id
      and ret.DATE_RETIRED is null
      and fb.original_cost-x.acmulated_deprn=0
      and x.CALENDAR_PERIOD_CLOSE_DATE <= :begin_date
      
     )
[+][-]07.23.2008 at 12:54PM PDT, ID: 22073160

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.23.2008 at 12:58PM PDT, ID: 22073201

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.23.2008 at 01:31PM PDT, ID: 22073528

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.23.2008 at 02:11PM PDT, ID: 22073904

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.23.2008 at 02:47PM PDT, ID: 22074252

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.23.2008 at 03:03PM PDT, ID: 22074365

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.23.2008 at 03:13PM PDT, ID: 22074428

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628