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 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;");
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.
We have Employee table, there is a field name Employee_no. field size is 6.can we restrict some one not to increase or decrease the field size of Employee_no. even User has admin role.
is there any way to restrict admin user that he should not allow to enter the value of field more or less than 6 characters through Toad or SQL Plus 8.0. ------------------------------------------------------ for example
When we enter Employee no A000001(7 digit) then database not allowed to update because its field size is 6 characters we want to restrict admin user in Toad or SQL Plus 8.0 because he is Toad and SQL Plus user.
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 need to create a query that returns record by record a field qty_progr with the cumulate qty considering previous records. The result should be the following:
The below table and functions are examples, but issue is like this.
i have one issue while forming below sql , Application will form this query at run time wih required inputs it is failing because the application will not support the clob datatype.
Table : T_E (similar to EMP table structure)
This table has data similar to emp but bulk data around 10k records.
Query formed
select empno,ename, get_employees(deptno) from t_e;
This sql query this is failing when function return varchar2 string more than 4000 size.Because in sql query size should not exceed 4000 for varchar2 data type , function return size can be upto 20000
Function which CREATE OR REPLACE FUNCTION get_employees (p_deptno in emp.deptno%TYPE) RETURN varchar2 IS l_text varchar2(32767) := NULL; BEGIN FOR cur_rec IN (SELECT ename FROM t_e WHERE deptno = p_deptno) LOOP l_text := l_text || ',' || cur_rec.ename; END LOOP; RETURN LTRIM(l_text, ','); END;
I can try this with clob but application doesn't support that..so i tried with using clob in above function , after that while returning to application i want to conver the clob to char and return the entire string
select empno,ename, to_char(substring(get_employees(deptno),1,4000)) -- first 4000 characters , to_char(substring(get_employees(deptno),4000,8000)) -- next 4000 characters from t_e;
But this is failing how to return the entire string in above sql
I need to select a count of records where a field (call it widget) is the same, so i need all records where widgets are distinct. So it would be like asking for a distinct in a where clause. Not having much SQL experience this is a difficulty for me.
lets see .. so a count of records where widget = widget or something along those lines.
I want to create a SELECT, that shall give back only a special amount of rows, depending on the sum of one of the selected fields.
At first a code sample of the complete selection:
SELECT DISTINCT mnr, ktxt, (SELECT Sum(meng_4)FROM reldb d1 WHERE d1.mnr=d.mnr)qty FROM reldb d WHERE mnr IN (SELECT mnr FROM relac WHERE Lower(rlnr) NOT LIKE 'platte geprÃĪgt%') AND saext='M' ORDER BY qty DESC,ktxt;
This selection produces some lines of output (in my case i.e. like 300). What I want to see is only that much lines that the condition 'sum of all items listed below meng_4<=sum of all items meng_4 of the whole selection * 0.9' is fulfilled.
So, if the whole selection produces a total of 10000 as sum for all items meng_4, I want to see only that amount of rows that sums a total of at least 9000 for all items meng_4. I hope, this specification is exactly enough to understand my intent.
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.
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.
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?
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'
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.