SQL & PL/SQL :: SELECT Works But View Doesn't
Jun 11, 2012
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]....
View 8 Replies
ADVERTISEMENT
Jun 22, 2012
I have a website I am working on a database for, and to insert new data into the table is not a problem, even to output the table isn't an issue.
But a delete query won't work and it doesn't return any errors or echo commands that I put in the steps of the logic.
View 11 Replies
View Related
Aug 13, 2010
when i am trying to excute below query.. it says ORA-00942: table or view does not exist.
create or replace force view v2 as select * from employee;
View 11 Replies
View Related
Jul 26, 2012
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.
View 2 Replies
View Related
Apr 25, 2012
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.
View 6 Replies
View Related
Dec 7, 2006
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 7 Replies
View Related
Mar 1, 2012
I am using Oracle Database 9i. I have created a view as follows:
CREATE OR REPLACE FORCE VIEW ipl.abc_test AS
SELECT '10' dsgn, TRIM (b.oc1) code, b.grp, b.POLICY, b.premcode,
b.receipt, b.recptdt, b.PLAN, b.term, b.paymode, b.sumass, b.datcom,b.duedt, b.instl, b.lprem, b.rprem, b.sprem, b.extprem, b.totprem,b.oc10, b.oc9, b.oc8, b.oc7, b.oc6, b.oc5, b.oc4, b.oc3, b.oc2,b.oc1, b.bill_oc1 billno, a.fprdate riskdt,
NVL (a.bmon, TO_CHAR (a.fprdate, 'YYYYMM')) bmon, a.entryopt,
a.extprem3 suplext
[Code]...
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.
View 17 Replies
View Related
Jun 7, 2011
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.
View 3 Replies
View Related
Apr 8, 2012
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
SELECT NAME,
To_char(Decode(unit, 'bytes', VALUE / ( 1024 * 1024 ),
VALUE), '999,999,999.9') VALUE,
[Code]....
Apart from the little problem I have with number 2, not figuring out how to get the precentage seeing that all the data is store in the field VALUE.
I would also like to create a view that has both 1 and 2 in it, but whenever I try creating it, I got and error stating and INTO clause is expected.
View 10 Replies
View Related
Jan 12, 2011
If i inserted the values in table it gets inserting very few rows only.I dont know y it is?
View 15 Replies
View Related
Dec 23, 2010
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]........
View 7 Replies
View Related
Aug 16, 2013
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?
View 1 Replies
View Related
Oct 27, 2011
My need is to grant priveleges for select on view without granting on nested tables.
Actual problem is that I can grant privileges for particular user, but can't grant privileges for user group.
View 4 Replies
View Related
Jun 13, 2010
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.
View 5 Replies
View Related
Aug 31, 2012
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
View 1 Replies
View Related
May 28, 2012
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
SELECT RTRIM (XMLAGG (XMLELEMENT (e, EMPNO || ',')).EXTRACT ('//text()'),
',')
AS empid,
RTRIM (XMLAGG (XMLELEMENT (e, ENAME || ',')).EXTRACT ('//text()'),
[code]...
But the case is I need to display the value in table format Horizontally as below
EMPIDemployee nameDEPID
778
278CLARK10
397MILLER
934KING
[code]...
View 14 Replies
View Related
Jun 20, 2011
I have a materialized view (smpl_mview) if i want to see the SELECT statement written for the materialized view, how can i see/retrive select stmt?
View 1 Replies
View Related
Jul 24, 2011
I'm using Oracle 9i Enterprise edition, Is there a select statement to view transaction log for specific date?
View 1 Replies
View Related
Aug 20, 2012
[URL]...
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]...
View 6 Replies
View Related
Jun 27, 2011
[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.
View 9 Replies
View Related
Apr 29, 2012
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,
[code]....
View 5 Replies
View Related
Jun 1, 2013
How oracle retrieves data from a table.like let say a table product as
create table product(id number,name varchar2(10))
table product dont have any index on it
now if this table conatain 500 rows and we have two query
1. select * from product where id=26
2. select * from product
does both query take same time or first query will execute in less time since it has where condition
View 2 Replies
View Related
Jan 29, 2008
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.
[Code]
<html>
<head>
<style>
</style>
</head>
<body lang=EN-US style='tab-interval:.5in'>
[code].....
View 3 Replies
View Related
Aug 1, 2011
I tested the SQL in 11G and get the ORA-00979 (not group by error) , the same SQL is working in 10G.
553322.1 - ORA-00979 IN 11.1.0.6 BUT NOT IN 9I OR 10G
814423.1 - ORA-00979 AFTER UPGRADE TO 11G
Do we have simple soluation for it if I don't have Oracle support account?
View 1 Replies
View Related
Jul 23, 2013
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.
View 3 Replies
View Related
Oct 15, 2010
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.
View 5 Replies
View Related
Jul 12, 2011
how oracle background process works and in what sequence when we file an insert or update command?
View 4 Replies
View Related
Jun 26, 2012
declare
blk_name varchar2(60);
form_name varchar2(60);
[Code].....
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?
View 5 Replies
View Related
Jul 27, 2012
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:
Prod =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = x1200.prod.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cusms)
(UR = A)
)
)
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:
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 27 12:37:55 2012
Copyright © 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12519: TNS:no appropriate service handler found
[code]...
why is it my connection using sqlplus works locally and not from other servers?
View 2 Replies
View Related
Mar 6, 2011
How can I do this ?
is there an rpad that works for multiple chars?
select RPAD('test',10, 'X') from dual;
I want to be able to do something like
select RPAD('test',6, '%20') from dual;
which should return test%20%20
View 7 Replies
View Related
