SQL & PL/SQL :: Combining 2 Select Statements In A View?
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
ADVERTISEMENT
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
Dec 11, 2012
Is there any way to combine an identifier and select statement in PL/SQL when using the insert into command?
e.g.
DECLARE
name := 'BOB';
BEGIN
insert into mytable(NAME, SLOWEST_LAP, FASTEST_LAP) name, (select min(time), max(time) from lLAP_TIMES);
END;
In the above statement I am trying to insert the identifier "name" (BOB) into MYTABLE.NAME, along with the result of the select query from the table LAP_TIMES ... min(time) into MYTABLE.SLOWEST_LAP, and, .... max(time) into MYTABLE.FASTEST_LAP.
If the above is possible in one statement how would I also combine an identifier, with two select statements into an "insert into" statement?
e.g.
name := 'BOB';
insert into mytable(NAME, SLOWEST_LAP, FASTEST_LAP, EVENT) name, (select min(time), max(time) from LAP_TIMES), (select race_event from MEET);
In the above example I am also trying to insert the result from MEET.RACE_EVENT into the column MYTABLE.EVENT
View 4 Replies
View Related
Oct 23, 2009
Been trying to concatenate the following two select statements (to then pass as a parameter in DML statement) without much luck.
Select lpad(ST_DAY,'2',0) || '/' || lpad(ST_MONTH,'2',0) from RET_FORMATS;
Select to_char(sysdate,'YYYY')"Year" FROM DUAL;
View 1 Replies
View Related
Oct 11, 2011
Is it possible to have $ substitutions in Select statements ?
For example
Select * from my_table where ID in ${ID_LIST} and DAY >= to_date(${SOME_DATE})
$ID_LIST = (100,200,300)
$SOME_DATE = 10-10-2011 12:12:00
For the ID_LIST is using Prepared statements with ?,?,? the way to go ? Or are there are any Define we can do in SQL plus for this substitions ?
View 2 Replies
View Related
Sep 3, 2013
I'm attempting to audit unsuccessful Select statements in order to trap a problem we're experiencing with our application. I have set the AUDIT_TRAIL initialization parameter to DB_EXTENDED, and bounced our database.
I've issued the AUDIT SELECT ANY TABLE WHENEVER NOT SUCCESSFUL command, and when I issue a SELECT statement as an application user, nothing appears in SYS.AUD$ even though the application has issued a select statement which returned no rows.
View 3 Replies
View Related
May 2, 2010
We are doing select statements. I have 3 tables that I need to get information out of and I believe I need to use a join but everything I put into oracle gives me an error.I'm doing the selects for a pharmacy and have a customer table, a drug table, and a prescriptions table.
I need to write a select statement that shows what customers are taking what drugs and how many mgs they take
customer_id,
customer_first_name,
customer_last_name,
drug_id,
drug_name,
prescription_unit
i think i need that information for the select but I cant seem to write a select statement that runs without errors.
View 6 Replies
View Related
Jul 7, 2010
how can i insert and update to a table in oracle database 10g through a select statement. not using merge.
View 2 Replies
View Related
Oct 9, 2013
if i have a function
function test_1
begin
if....then ...end
SELECT...
BULK COLLECT INTO..
FROM ....
[code]...
obviously this is not the whole code, but lets say the first if...then..end is true , does this mean it will exit the block? like it won't continue to the other stuff like select, if ...then statements?
View 15 Replies
View Related
Dec 10, 2010
ive got two select statements which fetches data from different tables. I need to join the two result set . is it possible to do it from sql. Heres the query.
1)
SELECT
COUNT(CASE WHEN (INTERACTION_TYPE= 'EmailED' AND CONTACT_PARTY=1) THEN 1 END)CUSTOMER_EMAIL,
COUNT(CASE WHEN INTERACTION_TYPE= 'EmailED' AND CONTACT_PARTY=2 THEN 1 END)OTHER_EMAIL,
[Code]....
2)
SELECT
SUM (CHEQUE_TOTAL) CHEQUE_TOTAL
FROM RI_CHEQUE_VOUCHER_REFUND refund
INNER JOIN CH_CASE case ON (case.id = refund.id)
INNER JOIN EVA_ENTITY_DEFINITION ed ON (ed.name= 'ChequeRefundCaseED')
WHERE
case.creation_time<= SYSDATE
AND
case.creation_time>= SYSDATE-7
Again I need to combine the resultset.So the result would look like
Customer_Email Other_Email Customer_Whitemail Other_whitemail Customer_telephone Other_Telephone Cheque_total
View 3 Replies
View Related
Feb 19, 2013
I used bulk collect and for all statements to select and insert the data in temp table.The select SQl is returning one row. But its not inserting this row into temp table.Its not throwing any exceptions. Used ref cursor because the select statement is going for every cursor.
here modified the code and provided only one cursor.
Create Or Replace Procedure Sales_Hist_Update_Bkp Is
Type Type_Name Is Record(
Sku_Item_Key Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Sku_Item_Key%Type,
Locationno Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Locationno%Type,
Bsns_Unit_Key Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Bsns_Unit_Key%Type,
Act_Item_Cost_Amt Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Item_Cost_Amt%Type,
Act_Rglr_Unit_Price_Amt Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Rglr_Unit_Price_Amt%Type,
[code]...
View 11 Replies
View Related
Jun 3, 2012
I'm trying to retrieve data with multiple select statements. The query works fine for 1 account (segment6) but fails for more than account. Below is the query.
select PERIOD_NAME "Month/Year",segment1 "Company" ,Segment6 "GL Account", currency_code "Currency",Mmt "Movement",
BEGIN_BALANCE+PERIOD_DR-PERIOD_CR "Balance At Date"
FROM
(select gb.period_name "PERIOD_NAME",decode (gcc.segment1, '20', 'Internode', '10', 'Agile') SEGMENT1,
gcc.segment6 "SEGMENT6", gb.currency_code "CURRENCY_CODE", gb.begin_balance_dr "BBDR", gb.begin_balance_cr "BBCR",
[code].....
View 3 Replies
View Related
Oct 13, 2012
I want to count the batch records using BATCH_ID with CASE statement ,for that i am using below query but its not working ,
SELECT COUNT(*) FROM <TABLENAME> WHERE VNBATCH_ID=CASE WHEN #SDC <10 AND #PERIOD >=10 THEN
0||#SDC||#PERIOD||#BATCH_ID
WHEN #SDC <10 AND #PERIOD <10 THEN
0||#SDC||0||#PERIOD||#BATCH_ID
WHEN #SDC >=10 AND #PERIOD <10 THEN
#SDC||0||#PERIOD||#BATCH_ID
ELSE
#SDC||#PERIOD||#BATCH_ID
END
View 11 Replies
View Related
Aug 11, 2013
I want to merge the following two sql statements into single output.
select id,count(*) from derailed where changed_on between to_date('26-july-13 18:30:00','DD-Mon-YY hh24:MI:SS') and to_date('01-August-13 18:29:00','DD-Mon-YY HH24:MI:SS') group by id;
select id,code from dbo;
View 11 Replies
View Related
Jun 7, 2011
I am using oracle 10g i have a table on my computer that i made for a friend when i load it on their computer the select statements say no data found if i use select * from table name all the data will show
if a column name select * from table name where duty_date = '05-JAN-11'no data found
View 1 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
Aug 22, 2011
I am importing some data from Oracle into another database on a regular basis. It works fine for most of the queries but couple of queries don't work sometimes (random). I don't get any errors or any data.
We switched on the Oracle auditing to find out the queries being sent to oracle db. We can see all the queries in the Audit log. Is it possible to configure Auditing to get the "Number of Rows" returned by Select statements so that we can be sure that some data was returned.
View 8 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 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
View Related
Jun 15, 2012
I had some query.
Actually had some table with following information
Here A,B,C are the vendors and Code is 21 and Amount is mentioned
Code Vendor Amount
21 A 56
21 A 62
21 B 31
21 C 100
21 C 100
Want to display top three vendor for that Code based on Vendor Amount
Code Vendor Amount
21 C 200(100+100)
21 A 118(56+62)
21 B 31
How should i do this?
View 10 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
Jan 17, 2011
I'm having some trouble combining some data. I've done a lot of research into joins and merges, nothing really seemed to do what I needed. I could be over thinking it.
Basically, I want the query to look at the "info" table from a specified time-span (1 to 7). I want it to look at the source of the data and if it is "one_better" always use that data for that specific time. Otherwise, return the data from "one". The results don't care about the source of the data.
The "info" table I'm working with looks like this:
TIMEDATASOURCE
1DATA1one
1DATA1betterone_better
2DATA2one
3DATA3betterone_better
4DATA4one
4DATA4betterone_better
5DATA5 one
7DATA7one
I would like the results to look like this:
TIMEDATA
1DATA1better
2DATA2
3DATA3better
4DATA4better
5DATA5
7DATA7
View 1 Replies
View Related
Aug 22, 2011
I have to implement a new logic such that , Old logic which pulls data will stay in place.now New logic pull data form SAP . So i have implemented the new logic in to the existing cursor as below.
i.e.
Cursor new_sap
old_query
Union
New_query;
I have 3 new cursor like dis. How to process these old and new cursors in FOR loop.. Ex:
For c1 in C2(order_number)
if order_source is sap then ( dis filters old data which is not in SAP)
new query in cursor should process
else
old query in cursor should process
end if;
How will i do this programatically.
View 1 Replies
View Related
Aug 15, 2012
I have a table with 4 records with start_date and end_date fields
start_date end_date
1/1/2003 1/4/2005
1/1/2004 5/3/2006
1/1/2007 1/4/2008
1/1/2008 6/3/2009
as you see records 1,2 and records 3,4 have overlapping dates. I need to combine them to get continuous intervals. The result should be:
start_date end_date
1/1/2003 5/3/2006
1/1/2007 6/3/2009
Is it possible to do by SQL query?
View 10 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
