I'm trying to create a view that includes both custom and delivered tables. The SQL code produces the desired results in SQL Developer. However, when I create and test the view using that code, I get very different and incorrect results. The SQL part of the view is below:
SELECT j.emplid, pt.deptid, pt.y_policy_id, pt.effdt, pt.title, pt.comments, pt.y_policy_covg, pt.doc_url
FROM psoprdefn a
, ps_job j
, ps_y_policy_tbl pt
WHERE substr(a.oprid,1,1) = '0'
AND a.emplid = j.emplid
AND j.job_indicator = 'P'
AND j.effdt = (SELECT MAX(effdt) FROM ps_job
WHERE emplid = j.emplid AND empl_rcd = j.empl_rcd AND effdt <= sysdate)
[code]....
I have a SQL query with a construction like: select a, b from tablea ,select * from (select a as a from tableb) where ....
The issue is that in sqldeveloper I don't get any errors and the select works fine.I use the same select in myEclipse report builder and I get an table or view doesn't exist.
We wrote one data load process to load GZ files into Database.during process we will change client facing view definitions to backup table so that we can work on base tables.
This view definition changes are related to FROM and WHERE clause (not columns/type). during load process, client/user may connect to current server and accessing these views. My question is what will be the reflection of changing view definition while user is accessing view?
I created a scenario- STEP1: Created a view- create or replace view view_01 as select object_name from dba_objects union all select object_name from dba_objects union all select object_name from dba_objects union all [code]....
View definition is replaced by new definition while select is executing on that view. select returned number of records as per view definition one.
I am using some UPS shipping software that imports shipping information based on an order number that the user enters and we are having performance problems. In the software's configuration you choose one database object (namely a table or view) to get the information from. I have created myView that selects from multiple tables, lookup tables, etc. I must use a view because the software only has the ability to add a " and <importKeyThatIHaveDefined> = <valueEnteredByUser>". For instance " and order_number = 123". It cannot join multiple tables, etc. The address_field1 info is the same for each record with the same order_number so I can just get the first row (or any row with order_number = <enteredOrderNumber> for that matter).
When I first looked at this it was created as something like this:
select order_number, line_number, order_number || '' || line_number as ol, address_field1 from order;Keep in mind that there are more than just this table and more fields selected. They are all joined correctly (for lookup values etc.).
The concatenation was determined to be the bottleneck in performance. ol was defined as the import key to be used and the users were just entering the order number plus a 1 to get ONLY the first line number. Each order (usually) has more than one line and even the line number is not necessarily unique for each order. For instance, order 123 may have line 1 twice and line 2 once.
select order_number, line_number, address_field1 from order where rownum = 1;
The software appended the " and order_number = 123" as expected but because rownum = 1 had already been executed (before the and order_number = 123" the view only returned one row even WITHOUT the order_number = 123.
I need to return only ONE row for a given order number EVEN IF there are multiple records with the same order_number and line_number. Using "where order_number = <enteredOrderNumber> and line_number = 1" is not necessarily unique so that won't work.If I can't make it work this way and can't use the rownum = 1 what options do I have?
View created properly, But when i select from this view this giving message missing right parenthesis. For information the select statement of the view gives multiple rows. Where is the problem.
I have been out of work for 2+ years. Am about to start a job next week doing Oracle back end, Forms, and Reports development among other things. I was asked if I could take a look at 3 report requirements and give an estimate on how long it would take to correct errors in these reports. All I have is a user requirement document stating what the report is currently doing and what it should be doing, a partial screen print of an Oracle Form showing correct data, and a sample report page showing incorrect data.
I am finding it rather difficult to give an estimate without seeing tables, relations, code, etc. Is it me or does this seem nearly impossible?I do not have access to their system yet so cannot view the database or run select statements, run the report, etc. All I have are the documents I listed above.
1. I Wnat to analyze the buffer cache hit ratio. This is what i did.
DECLARE bufcac NUMBER(10, 2); BEGIN
[Code]....
2. I would like to analyze the PGA and determine what percentage out of the maximum allocated PGA is being used. I tried the code below but can't find the percentage. Sad
I have a view, below, which does few left outer joins to the same V_MARKET view to get data i need. When I run SQL by itself, ut runs pretty fast, 2-5 seconds. But when I do "select * from V_DEPT_DATA where busines_date = '01-APR-10'", it takes more than 10 minutes to run. I added all needed indexes and still have problems with it .
CREATE OR REPLACE VIEW V_DEPT_DATA AS SELECT v1.business_date , v1.division , v1.department , v1.account , en.trader , [code]........
I created a user and granted connect, resource to the user. but trying to select columns from the view USER_HISTORY$,it's showing no value, i tried to login the user several times, but it's displaying no value, any particular reason or any minor mistake?
currently i m going through some dumps for my OCA-11g prep.I came across one sentence :A view cannot have an ORDER BY clause in the SELECT statement.well this statement is false and the explanation given was :
Query operations containing ORDER BY clause are also permitted, so long as the ORDER BY clause appears outside the parentheses.
The following is an example of what I mean: CREATE VIEW my_view AS (SELECT*FROM emp) ORDER BYempno.
but when i tried running the query like this :CREATE VIEW my_view AS SELECT*FROM emp ORDER BYempno ,it worked w/o giving parentheses.
Need a trigger in view with select statement that means
CREATE OR REPLACE VIEW TEST_VIEW AS SELECT * FROM TEST_TABLE; CREATE OR REPLACE TRIGGER TEST_VIEW_TRG1 INSTEAD OF DELETE ON TEST_VIEW DECLARE BEGIN Dbms_Output.Put_Line('STATEMENT TRIGGER.'); END;
i wanted to use select statement instead of delete.How can i get that
I need to view the rows of the result of a select query in table format in GUI application.I have used XMLELEMENT to somewhat match the scenario to display as ','(comma) separate values as b belwo
I have a quite complex view that selects from approx 10 long tables (approx 4M records each) and build one "customer sentence" pre customer id. I will be always getting just one row from this view, eg. select * from my_view where party_id = XYZ. I'll NEVER EVER select the whole view.
The problem is that running a query: select * from my_view where party_id = XYZ takes really long time, while putting the party_id = XYZ condition directly into the view executes in 0.0 seconds.
After putting a ORDERED FIRST_ROWS(1) hint into a view the execution plans seems to be the same (or very similar) for both queries. Unfortunately, I can not transfer anything but screenshot from the environmnet - therefore I paste the exec plans as screenshots only - pls follow the link: [URL]...
View DDL: create or replace view my_view as select /*+ ORDERED FIRST_ROWS(1) */ pt.party_id pt.party_id as id_klienta_mdm, pt.master_reference_no as id_klienta_ref_mdm,
[CODE] DECLARE D1 DATE:='&D1'; D2 DATE; BEGIN WHILE D1<=D2 LOOP DBMS_OUTPUT.PUT_LINE(TO_CHAR(D1,'DAY DD-MON-YYYY')); D1:=D1+1; END LOOP; END;
Here I want to display all the seven days. One more question is here I'm d1<=d2 but d2 is null. So how does <= works here a null. A null is always null. What will be there in d2 how does the loop works with this comparision.
I have the query below that works absolutely fine in Oracle 10g but is not running in Oracle 11g. Couple of things I notices about this query.
1. If I remove the alias 'FACILITY_W_CODE' in the ORDER BY clause, query works fine in 11g. If I replace 'FACILITY_W_CODE' with the actual fields - 'F.SERVICE_AREA || F.DISTRICT_OFFICE || F.DO_GROUP || F.DO_SSC' even then it works fine.
2. If I remove the GROUP BY clause in the inside query but keep the alias 'FACILITY_W_CODE' in the outside ORDER BY clause, the query works fine.
I am not sure where the issue is, GROUP BY or the ALIAS. Also, the alias issue seems to be only where the alias is used for multiple fields.
SELECT F.SERVICE_AREA || F.DISTRICT_OFFICE || F.DO_GROUP || F.DO_SSC FACILITY_W_CODE, PF.FAC_TYPE FACILITY_TYPE, PF.FAC_IDENT LOCATION_IDENTIFIER, P.DISTRICT_CODE USER_W_CODE, P.EMAIL_ADDRESS, PF.CERTIFICATION_AUTHORITY, PF.CALLBACK_TECHNICIAN, PF.CALLBACK_ENVIRONMENTAL FROM PEOPLE P JOIN ( SELECT I_PF.ID, I_PF.FAC_TYPE, I_PF.FAC_IDENT,
I am fighting with some code to work in IE7. It is currently working in Firefox.
A little bit more on the context of the situation is that I am entering the code as a personalization on an Oracle Self Service Page. I am entering in as a raw text personalization.
My teacher taught the lesson of DML statemnts, he told us how does merge works , but he did not give us any query for that,provide query for Merge and if possible then explain it too , I am using Oracle 10g Sql Plus.
I want to know how Remap_datafile paramater in IMPDP works in Oracle 10g. give me the whole scenario for two databases. One in Linux Platform and other in Windows platform. I want to import the tablesapce data from Linux to Windows Server database.
this is my coding on save button. this is working fine but only once. when i enter the data second time and try to save, it didn't do anything. what should i do?
I have a two node RAC on 11.2.0.3. We are using SCAN Listener here. On my node 1, i can connect to the database using tns alias: The alias looks something like:
sqlplus system/actdbasystem@cusms ----- works sucesfully on the prod server. Even the same prod alias copied to second tnsnames.ora in the cluster and run, it works sucesful too. But the issue is, when we copy the same alias onto any other servers and try to connect give's the following error message:
