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
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.
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
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%');
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=?
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:
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]....
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;");
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)
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.
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.
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.
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.
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 ?
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?
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 :