SQL & PL/SQL :: Write A SELECT Statement That Returns One Row For Each Customer With Four Columns

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


ADVERTISEMENT

SQL & PL/SQL :: Select Statement That Returns Field Size?

May 17, 2010

I am using 10g.

I want to do a select statement that will give me the name of the fields in a table. And I also want to know the field type and field size.

Can this be done?

View 1 Replies View Related

SQL & PL/SQL :: Select Statement - Inner Query Returns Data

Aug 23, 2012

I am new to oracle. I am creating a view, where in the inner query returns below kind data.

column1 column2
-------------------
a y
a y
b y
b n
c y
d n

I want to extract all column1 values which always has "y" in column2.

In this case, output has to be

output
------
a
c

View 10 Replies View Related

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 View Related

JDeveloper, Java & XML :: How To Write Results Of Select Statement Into File

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

PL/SQL :: Write In Sql Command Line Display CUSTOMER Number?

Sep 13, 2013

how to write this in sql command lineDisplay CUSTOMER number, name, rep, balance, and credit limit for all CUSTOMERs of Rep 20. 

In addition, display this information for CUSTOMERs of rep 65 who have a $10,000 credit limit.  Arrange the output by credit limit descending within rep ascending. no more than two decimal digits

View 3 Replies View Related

SQL & PL/SQL :: How To Add Two More Columns Into Select Statement

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

SQL & PL/SQL :: Select Statement That Will Split Rows Into Columns?

Aug 15, 2013

I have a table which stores Employees and their Phone numbers. Each employee can have multiple numbers e.g.

Employee, Number
Adam, 123
Adam, 456
John, 123
John, 456

I am trying to write a select statement that will split the rows into columns and group by each employee e.g.

Employee, Number1, Number 2
Adam, 123, 456
John, 123, 456

View 19 Replies View Related

SQL & PL/SQL :: Write Single Row Sub-query Which Returns ID_2 Value Of Row With Del_flag As N

Feb 13, 2012

In my main query, There is a single row sub query which returns row with del_flag as N. Now in my table, there are 2 rows getting added from the incoming feed with del_flag as Y & N.

Here is my Table:

ID_1 DEL_F ID_2
-------------------------------
16643162 Y 49696
16643162 N 16643162
16612344 Y 98888

So how can i write a single row sub-query which returns ID_2 value of a row with del_flag as N. If there is no row with DEL_F as N, it should return ID_2 value of row with DEL_F as Y.i have tried below query, but it showing the error.

select
(case
when min(del_f)='N' then to_number(ID_2)
when min(del_f)='Y' then to_number(ID_2)
end ) ID_2 from table where ID_1=?

Here, im passing ID_1 value from main query

View 8 Replies View Related

SQL & PL/SQL :: Procedure That Displays Customer ID / Customer Name / Product Name

Dec 7, 2011

I'm trying to write a procedure that displays customerID, customer name, product name, and the total quantity of products the customer purchased, and the total amount the customer paid.Here's the relevant Schema tables:

CREATE TABLE Product (
Product_ID NUMBER(6) NOT NULL,
Description VARCHAR2(30),
Product_Code CHAR(4));
[code]....

Now I'm trying to wrap the above query in procedure code. I believe that I need a cursor, but I don't know what kind of cursor variable to store the result of the SELECT statement in because the query selects columns from several different tables, and I'm not sure how to terminate the FOR loop (but I think probably I can use the EXIT WHEN cursor%NOTFOUND;Here's the procedure code I have written thus far:

CREATE OR REPLACE PROCEDURE find_customer_statistics IS
DECLARE
TYPE cust_stats IS REF CURSOR; weak ref cursor declaration

SELECT sales_order.customer_id, customer.name, product.description, SUM(line_item.quantity),
SUM(line_item.subtotal)
FROM sales_order, customer, product, line_item
WHERE customer.customer_id = sales_order.customer_id
AND line_item.order_id = sales_order.order_id
[code]....

View 7 Replies View Related

Statement Separate In SQLPlus / It Successfully Returns Schema

Mar 28, 2008

I am running this statement in a Java DAO and it results in an SQLException. I'm trying to get the DB schema. If I run this statement separate in SQLPlus, it successfully returns the schema. Why this is failing in my Java program?

ResultSet r = s.executeQuery("SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM S_DUAL;");

View 5 Replies View Related

SQL & PL/SQL :: Select From - Single Row Subquery Returns More Than One Row

May 17, 2010

select * from course_section
where term_id = (select term_id from term where term_desc in ('Spring 2006','Spring 2007'));

and it is showing:

ERROR at line 2:
ORA-01427: single-row subquery returns more than one row

View 5 Replies View Related

SQL & PL/SQL :: Creating Sequence - Select Returns Integer

Oct 18, 2010

im creating sequences for an already created db and need to set MINVALUE to sequences.

create sequence tb_1
minvalue X
start with X;

so, X == select Max(id_tb1) from tb_1.

I need that select returns an integer.

How can I do this??

View 11 Replies View Related

SQL & PL/SQL :: Write A Statement That Shows Time / Name / IP / Received And Sent

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

PL/SQL :: Write Dynamic SQL To Query Unknown Number Of Columns

Jun 6, 2012

I need to write a dynamic SQL in PL SQL to query an unknown number of columns. Let me take a simple example query here:

SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS

If I have known the number of columns, e.g. querying two columns: "FIRST_NAME" and "LAST_NAME", I can write a DYNAMIC SQL based on the template in table 8-2 of URL....

DECLARE
stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
FIRST_NAME varchar2(200);
LAST_NAME varchar2(200);
BEGIN
cur_hdl := dbms_sql.open_cursor; -- open cursor
stmt_str := 'SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS';
[code]....

However, if I wish to write a dynamical sql to query these two columns for a more general purpose (which should meet the requirement to query different number of columns, e.g. three columns, FIRST_NAME, LAST_NAME, BIRTHDAY instead of two columns FIRST_NAME and LAST_NAME). To do this I first try to query the same two columns but using a different method, following URL.....My code for the same query has error, and I cannot solve it.

DECLARE
stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
l_columns dbms_sql.desc_tab;
l_dummy NUMBER;
l_value NUMBER;
[code]....

View 7 Replies View Related

SQL & PL/SQL :: Write Nested Decode Statement With Multiple Searches

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

SQL & PL/SQL :: How To Select All Columns From Table Except Those Columns Which Type In Query

Jan 21, 2011

I have a two question.

Question 1:How to select all columns from table except those columns which i type in query

Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data

View 5 Replies View Related

SQL & PL/SQL :: Select Statement From Schemas In MERGE Statement In USING Clause

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

SQL & PL/SQL :: Write Query To Delete Similar Records In Particular Fields (columns) In Different Tables

Jul 17, 2012

write a query to delete similar records in particular fields(columns) in different tables.

View 2 Replies View Related

Performance Tuning :: Servers Will Be Running SELECT Which Returns Zero Rows All Time

Feb 11, 2011

Our application servers will be running a SELECT which returns zero rows all the time.This SELECT is put into a package and this package will be called by application servers very frequently which is causing unnecessary CPU.

Original query and plan

SQL> SELECT SEGMENT_JOB_ID, SEGMENT_SET_JOB_ID, SEGMENT_ID, TARGET_VERSION
FROM AIMUSER.SEGMENT_JOBS
WHERE SEGMENT_JOB_ID NOT IN
(SELECT SEGMENT_JOB_ID
FROM AIMUSER.SEGMENT_JOBS) 2 3 4 5 ;
[code]....

Which option will be better or do we have other options?They need to pass the column's with zero rows to a ref cursor.

View 6 Replies View Related

SQL & PL/SQL :: Select First 40 Columns Without Giving All Column Names In Select Clause?

Mar 3, 2011

I have a table with around 80 columns. All i need is to select first 40 columns.

Is there any way to select first 40 columns without giving all the 40 Column Names in select clause.

View 2 Replies View Related

SQL & PL/SQL :: Select Dynamic Column Names In Select Statement In Function?

Jul 4, 2010

i want to select dynamic column names in my select statement in my function.

View 4 Replies View Related

SQL & PL/SQL :: Conditional Select - Query To Returns Results Based On Both City And Country Passed

Sep 17, 2010

Table A

Id Country city
1 US
2 US Boston
3 Boston
4 US Newyork
5 London
6 Japan Tokyo

Im looking for a query which returns results based on both city and country passed.

If i pass country US and city Boston it should return row2 with US and Boston row
If i pass country null and city Boston it should return row3
If i pass country UK and city Boston it should return row3
If i pass country UK and city London it should return row5

i.e. If country/city combination exists in DB return that row Else city row should be returned.

View 5 Replies View Related

SQL & PL/SQL :: Select Statement Is Blocking A Delete Statement

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

SQL & PL/SQL :: Write Select Query To Get Sum Of Quantity For Each Type Of Item?

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

Client Tools :: Possible To Write Select Query O/p In HTML Tag?

Jan 24, 2011

is it possible to write select query o/p in html tag?

View 6 Replies View Related

SQL & PL/SQL :: Why Blind Select Is Better Than Conditional Select Statement

Dec 29, 2010

Why Blind select is better than Conditional select Statement?

View 10 Replies View Related

SQL & PL/SQL :: Function In Oracle To Select Not-null Columns At Beginning And Null Columns At End?

Jul 12, 2012

I have 8 columns. Some of them might be null.I want to display all 8 columns in my result. Not null columns will be first and null at the end.Here is a sample data :

Employee table :
Employee_id Emp_fname emp_lname emp_mname dept salary emp_height emp_weight
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223

The expected result is :
result1 result2 result3 result4 result5 result6 result7 result8
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223

View 8 Replies View Related

SQL & PL/SQL :: Insert Into Statement Doesn't Insert All Rows Return By Select Statement?

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

If Statement In Select?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved