PL/SQL :: Oracle 10g - Write Select Statement With XML
Sep 26, 2012
I have Oracle 10g. In a table with just one record there is a clob with the following xml. In the following clob, there could be any number of Emp elements.
<?xml version="1.0"?>
<ROWSET>
<Emp>
<ENAME>SMITH</ENAME>
<EMPNO>7369</EMPNO>
<SAL>800</SAL>
</Emp>
<Emp>
<ENAME>ALLEN</ENAME>
<EMPNO>7499</EMPNO>
[code]...
I want to write a select statement which gives me result as follows (all ENAME and EMPNO from the xml)
ENAME EMPNO
SMITH 7369
ALLEN 7499
WARD 7521
View 4 Replies
ADVERTISEMENT
Feb 16, 2012
I am student of Oracle programming(SQL).Has a question on MIN and MAX functions:
- "Write a SELECT statement that returns one row for each customer with four columns--the customer's first and last name, the date of the customer's most recent order, and the date of the customer's oldest order and the difference between the two dates. Sort the result set so the customers who have been ordering with the company the longest appear first. Use most recent order date as a tie-breaker for the sort."
where orders and order_details,customers are 3 different.this is my query ...but its not working.
SELECT DISTINCT customers.customer_first_name||' '||customers.customer_last_name AS Customer_name,MAX(order_date) AS "Most recent order", MIN(order_date) AS "Oldest order"
FROM customers
INNER JOIN orders
ON customers.customer_id=orders.customer_id
LEFT JOIN order_details
ON orders.order_id=order_details.order_id
LEFT JOIN items
ON order_details.item_id=items.item_id
ORDER BY Customer_name
View 6 Replies
View Related
Feb 25, 2011
SQL> desc res;
Name Null? Type
----------------------------------------- -------- ----------------------------
RESULT PUBLIC.XMLTYPE
SQL> select * from res;
RESULT
--------------------------------------------------------------------------------
<fine No="2"><stdNo>2</stdNo><value>300</value><reason>breaks keyboard</reason><
date>2011-10-03</date></fine>
how can me write results of select statement into xml file instead of show them on screen?
View 20 Replies
View Related
Jul 27, 2010
i want display a string like this using a select statement in oracle 10g.i have tried but not yet done.
example:
-----------
from 'ABCDEFGH' to 'ACEG'
removing 'BDFH' from the source string 'ABCDEFGH'
i giving here the example you can take any valid string i want the result like the above example and also in a dynamic manner means we can give string to a select statement in run time.can it is possible in a select statement only.
View 14 Replies
View Related
Aug 27, 2009
My company use a sybase database that runs business jobs. Currently we run SQL queries from Perl to gather time information on the jobs. Now we have an application that is using Oracle. The server it is on, doesn't have perl, so I am using a shell script to login to sqlplus and run a query for a job and it's end time. I have accomplished this. However, here is the 2 problems I am having.
1. The query reults are returned in Scientific time, I'm able to convert that to EPOCH time in the SQL syntax, however, it comes back with a 13 digit time, instead of 10. The last 3 digits are zero. How can you remove the last 3 digits in the query or convert the 13 digits to Human Time. Right now when you see the select statement, I am doing a to_char to get it to EPOCH time.
2. How to only show the latest time in the query and not show ALL job end times from it's past runs.
Here is my shell script, and I do realize this maybe a select statement syntax solution to one or both, but the UNIX time stamp is puzzling.
#!/usr/bin/sh
sqlplus -S username/password@JAWSPROD <<eof> myfile
set heading off feedback off verify off
select JAWS_APP.JAWSJOB.JOBNAME, to_char(JAWS_APP.JOBRUN.ENDTIME) from JAWS_APP.JAWSJOB, JAWS_APP.JOBRUN where JAWS_APP.JAWSJOB.JOBID = JAWS_APP.JOBRUN.JOBID and JAWS_APP.JAWSJOB.JOBNAME in ('pa_box_settle');
exit
View 1 Replies
View Related
Jul 3, 2012
How to call a function with a row type return in an Oracle select statement.
For e.g. :
If I had this function with a rowtype return:
------------------------------
create function abc
return xyz%rowtype
is
rec xyz%rowtype;
begin
select * into rec from xyz where col1 = n;
return rec;
end;
--------------------------------
How could I use this in a select clause, as there is a multi column return by the function ?
View 5 Replies
View Related
Apr 3, 2013
Yesterday only I have Installed Oracle 11G & created DataBase - JafferDB And from Oracle SQL Developer, I have created a connection called - JafferCon and SID also given..and the role is - SYSDBA And I excute the below statement
insert into MyTable1 Values ('AAA1', 'BBB1', 'CCC1')
insert into MyTable1 Values ('AAA2', 'BBB2', 'CCC2')
Then I checked by Select statement, it has shown the values....No Problem.... But, as a test, I deleted the connection and created a new connection tio the same DataBase with different name and when I checked by Select Statement....., it has not shown the values....?
View 2 Replies
View Related
Jan 17, 2013
Imagine a table below and I want to write a statement that shows time,name,ip,received and sent (SELECT *). (time, city, ip are primary key lets say).However there is the condition that I want:
It should be grouped for each CITY and I want to get max(RECEIVED) by descending order for each days. How can I do that?
select *
from table
group by city
having max(received)
Is it possible to get output without giving condition to other elements (IP, SENT... etc)
TIME- NAME- IP- RECEIVED- SENT-
10.01.2013,LON,10,2342 ,326
10.01.2013,PAR,10,563463 ,3
10.01.2013,LON,30,23412 ,574
11.01.2013,NY,20,36545 ,246357
11.01.2013,NY,40,23423 ,5
11.01.2013,TOK,40,45475 ,3453
11.01.2013,TOK,30,23423 ,574574
12.01.2013,LON,10,574 ,2342
[code]....
View 5 Replies
View Related
Nov 27, 2010
I was trying to write nested decode statement with multiple searches. For example
Case when seq = 1 and date1 > date2 then Record_Name End
How can i modify this to decode.
View 4 Replies
View Related
Sep 13, 2013
In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition
ON(source.DNO = target.DNO
AND source.BNO=target.BNO);
I thought that using UNIONALL for select statement of the schemas as below.
SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....
View 5 Replies
View Related
Jul 4, 2010
i want to select dynamic column names in my select statement in my function.
View 4 Replies
View Related
Jan 11, 2012
I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).
When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:
SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
2 || ' User '||s1.username || '@' || s1.machine
3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text
||' is blocking the SQL statement on '|| s2.username || '@'
4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> '
6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 ,
7 v$session s2,v$sql sqlt1, v$sql sqlt2
8 WHERE s1.sid =l1.sid
9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
[code]...
From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?
View 10 Replies
View Related
May 26, 2010
I want to write select query to get sum of quantity for each type of item ,in the table I have itemnum,itemtype and quantity ,type have value(A,B,C,....)
the result will be like that ex:
item.No A B C D ....
10 50 60 80 20
--- 50 will be sum(quantity) for item type A
how can I do that, I mean how can I retrieve many column from the same field?
View 17 Replies
View Related
Jan 24, 2011
is it possible to write select query o/p in html tag?
View 6 Replies
View Related
Dec 29, 2010
Why Blind select is better than Conditional select Statement?
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
Mar 22, 2013
simply select and works great:
select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity,gcrs.Area_name, gcrs.sector_name,
substr(gcrs.stream_name,1,case when instr(gcrs.stream_name,' (')=0 then 100 else instr(gcrs.stream_name,' (')-1 end) ISSUE_DIVISION,
case when gcrs.STREAM_NAME like 'NON-GT%' THEN 'NON-GT' ELSE gcrs.STREAM_NAME END as ISSUE_DIVISION_2
from table(f_carat_issues_as_of('31/MAR/2013')) i inner join v_gcrs_with_stream gcrs on i.segment_id = gcrs.segment_id
where UPPER(ISSUE_STATUS) like '%OPEN%'
Now I want to callte two columns:ISSUE_DIVISION and ISSUE_DIVISION_2
if they are equal in new columns should be value 1 if are not equal should be 0,how can I do it ?
View 4 Replies
View Related
May 5, 2010
I have a question about select statements, as I am new to them and don't know how to work all the commands yet.
I'm making a select statement that is about half right... it is shown below:
select t.warehouse_id,
t.quantity_on_hand,
c.product_name
from pahtest3.inventories t
join pahtest3.product_information c using (product_id)
WHERE warehouse_id in (7);
I need to take this select statement and make it so it shows all the products that don't have any quantities in the warehouse in addition to the ones that are already being shown in that select statement.
View 2 Replies
View Related
Jun 15, 2007
decode (a.cancel_time,'',sum ((to_date('23:59','hh24:mi') - a.alarm_time)*24*60), sum((a.cancel_time - a.alarm_time)*24*60)) Duration,
After executing this statement. I get negative values if cancel time is null
I want to subtract alarm_time by 24 hours, if cancel time is null. If not null then cancel-time - alarm_time
View 1 Replies
View Related
Aug 23, 2008
how to set the timing on for all SELECT statements in a procudeure...and i want that should come as a output for very SELECT statement slong with my original output.
View 6 Replies
View Related
Sep 1, 2011
I have a need to use the Alias name of a column within the same select statement( because I can't have another select statement using the first select as table - BO tool limitation).
Ex:
Select dept_id, agency, sum(quantity) as "sum_qty"
where sum_qty > 500;
Currently oracle won't allow using alias name Sum_qty in the same select statement. Is there a way to use alias within the same select statement?
View 12 Replies
View Related
Jul 5, 2011
I'm trying to write a simple query so I can do some testing on my application. I am trying to do something like this:
SELECT
Location,
LEVEL,
FROM
S_ORG_EXT
where
Location = 'North America' and LEVEL ='Software'
OR location = 'North America'
and Active = 'N'
in the where statement, I have put in the 'Active' that isn't a column. I want to be able to be able to change that in the select part. But I am not able to do so.
this is what I have tried:
SELECT
Location,
LEVEL,
Active = 'N' --I want to change this in the to N or Y so I can get different results.
FROM
S_ORG_EXT
where
Location = 'North America' and LEVEL ='Software'
OR location = 'North America'
and Active = 'N'
View 7 Replies
View Related
Feb 11, 2011
I have the following tables:
create table lookups (code varchar2(20), amount number);
insert into lookups values ('Rent' , 500);
insert into lookups values ('Breakpoint' , 10);
create table products (id number, cost number, year varchar2(4));
insert into products values (1, 1000, '2011');
insert into products values (1, 2000, '2011');
insert into products values (2, 100, '2011');
insert into products values (3, 50, '2011');
commit;
I want to write a query which lists the IDs and the sum(cost), and a Y/N indicator which is set to 'Y' IF sum(cost) > ( (lookups.rent value) * (100 - lookups.breakpoint value))/100
I have written this query:
SELECT id,
sum(cost)cost,
year,
CASE
WHEN cost >
((SELECT amount
[code]....... ORDER BY id;
This returns
ID COST YEAR YN
--------- ---------- ---- -
1 1000 2011 Y
1 2000 2011 Y
2 100 2011 N
3 50 2011 N
The YN is correct, but it needs to sum the amounts. So there should only be one row for id1 = 3000.e.g.
ID COST YEAR YN
--------- ---------- ---- -
1 3000 2011 Y
2 100 2011 N
3 50 2011 N
I am not sure how to do this. Or is there a better way of doing this than using CASE.
View 4 Replies
View Related
May 6, 2011
Below is the query that I have:
Quote:
select a.contact, b.db_name
from MOM.db_contacts@DB_LINK a, MOM.databases@DB_LINK b,
where a.DB_ID=b.DB_ID
and b.DB_name=(SELECT unique substr(upper(t.target_name), 1, instr(t.target_name,'_',1,1)-1)
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE t.target_type = 'oracle_database'
and UPPER(t.target_name) like '%11GDB%');
How can I add two more columns into the select statement and the two columns are in the tables that were in the sub query..I would like to have something like this:
Quote:
select a.contact, b.db_name, COLUM1, COLUMN2
from MOM.db_contacts@DB_LINK a, MOM.databases@DB_LINK b, mgmt_targets t, mgmt_current_severity s
where a.DB_ID=b.DB_ID
and b.DB_name=(SELECT unique substr(upper(t.target_name), 1, instr(t.target_name,'_',1,1)-1)
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE t.target_type = 'oracle_database'
and UPPER(t.target_name) like '%11GDB%');
View 5 Replies
View Related
Apr 28, 2010
I have a cursor in a procedure that selects columns from a table on a remote Oracle database using a database link. It all works fine when the table name and database link name is 'hard coded' into the procedure but what I would like to achieve is have the select statement lookup the table name and database link name from a column in another table. See example below
CURSOR c_total_bookings IS
SELECT personnel_id,
surname,
forenames,
birth_date,
[code]...
What I would like to do is replacecompany_bookings@remote.linkwith a variable that takes its value from another table.
View 1 Replies
View Related
Jun 24, 2011
select GAM.FORACID,
SOL.SOL_DESC,
GAM.ACCT_NAME,
LHT.LIM_SANCT_DATE,
LHT.SANCT_LIM,
to_char( GAM.CLR_BAL_AMT,'9,99,999.99'),
[code]...
i am getting error like this
ERROR at line 9:
ORA-00911: invalid character
View 4 Replies
View Related
May 3, 2013
Execute Immediate on SELECT Statement
declare l_stmt VARCHAR2(1000);
begin
l_stmt := 'Select * from mytable' ;
EXECUTE IMMEDIATE l_stmt;
--- dbms_output.put_line( l_stmt);
end;
it is not returning any output. I have hardcoded the table name here where as in real time Mytable name will differ.
View 14 Replies
View Related
Feb 19, 2013
I was given a SQL query to develop another query that will need to do something similar. The query I was given I believe is a stored procedure. It is PL/SQL and has a SELECT statement that looks like
SELECT :var1 AS var1
FROM t1
WHERE
:var1 = 1
In the past I have seen where variables can be used in the WHERE clause like
SELECT c1
FROM t1
WHERE
c1 = :my_column_valueand this makes sense to me.
Usually :my_column_value is declared in the DECLARE block of the PL/SQL. In the query I was given, :my_column_value is not declared in any DECLARE blocks (maybe I wasn't given the entire code??). But even if it was, that would be even more confusing because in the DECLARE block it could be assigned one value and then set to another value in the WHERE clause???
The first query doesn't make sense to me and when I run it as plain SQL I get an error like "SP2-0552: Bind variable "var1" not declared. I am guessing that such a syntax is not valid for regular SQL?
The only thing I can gather that the first query does is something along the lines of
SELECT 1 as var1
so it will output a 1 for every row. Not always that interesting of a query, but it is needed sometimes, I know. But I still don't understand this type of assignment.
So what's going on here? I'd be happy to look it up and read about it myself, but I don't know what to search for.
View 5 Replies
View Related
Nov 12, 2013
i want a select statement to retrieve the last name added into the "ename" column .i mean :if i insert "jack" into "ename" column , i want to write a select statement to retrieve that last name while i do not know it is jack or any other name ?
View 2 Replies
View Related
Sep 14, 2013
How to use a function with DML operations in it in a select statement.
View 11 Replies
View Related
