PL/SQL :: Retrieve Data By Using Both Group By And Order By Clauses?
Nov 16, 2012
I have a table name as angdata77 having attributes like asigno..i want to retrieve data from angdata77 by using both group by & order by clauses.. for total count..am using the query as
select asigno,count(*) from angdata77 group by asigno order by asigno;
Is there any other query for retrieving the data from angdata77
select to_char(order_date,'mon-yyyy') "months", sum(nob) "number of bags" from p_in where order_date between '1-apr-11 and '31-mar-12' group by to_char(order_date,'mon-yyyy')
the above query returns me a resultset which gives me number of bags sold in every month, but it does not sort the month in their chronological order. i want to sort my resultset in ascending order of month s.,means jan-feb-march-apr-may ams so on...QUOTE
Why is it necessary to precede group by before order by and not vice versa in a sql query? as the foll code gives an error select name from student order by age group by name;
I have been asked for a query to write by using all sql clauses like group by, where, having, order by in a single query. I just wrote the below query but I did not get out put. select deptno, max(sal) from emp group by deptno having deptno > ( select distinct deptno from emp where deptno >= 20)
I have a requirement where I have to return data as per filter clauses provided on the front end, which may or may not be filled as per the users' choice.
To create a test case, I have created a query joining the emp and dept tables and I add the where clauses dynamically depending on whether the filter has been provided or not.
CREATE OR REPLACE TYPE emp_ot AS OBJECT ( empno NUMBER(4), ename VARCHAR2(10),
[Code]....
It works very well, the 'literals' are being converted into bind variables (due to CURSOR_SHARING being set to SIMILAR) and the optimizer is able to figure out the correct execution path every time, although it is true that potentially 5 versions of this query will be sitting in the shared pool, but the DBAs are happy to live with that.
Going forward in version 12, CURSOR_SHARING=SIMILAR will be deprecated and the DBAs are not happy for us to write this sort of code anymore.
Is there a suitable way to achieve what this piece of code does?
I have tried and tested this method: SELECT emp.empno, emp.ename, emp.job, dept.deptno, dept.dname FROM emp, dept WHERE emp.deptno = dept.deptno AND emp.empno = NVL(p_empno, emp.empno) AND emp.ename LIKE NVL(p_ename, emp.ename)||'%' ...
but the query takes far longer to execute in this manner (that is using my real tables).
I am writing a SQL inside an application to retrieve data from a table for certain IDs or for All employees based on the user's input. I create a variable :SELECTION for the where clause. When I run the SQL, I receive the error of missing right parenthesis. There is no error if I hard code the IDs.
If the input is 'All' -> the variable :SELECTION is blank so if the SQL :
select name, address, ID from employee :SELECTION
will be
select name, address, ID from employee
If the input of users ID as '1234','9999' - > the :SELECTION = ' where ID in (:VARIABLEID)' :VARIABLEID = '1234','9999'
so the SQL: select name, address, ID from employee :SELECTION
should be
select name, address, ID from employee where ID in ('1234','9999')
However, I got the missing right parenthesis error. I suspect it is something with the single quote, so I tried different ways, but it is still not working.
I am interested about the fast way to access all data in physical block. what is the quick way to bring data blocks using the rowid, I found this script but soon as I can have faster access:
select * from table_name t WHERE ROWID between 'AAAUaOAAEAAHkJiAAA' and 'AAAUaOAAEAAHkJiAA8'; where 'AAAUaOAAEAAHkJiAAA' is the last element in the block and 'AAAUaOAAEAAHkJiAA8' is the first one
my question is can retrieve all the data in one block more quick than this query.
I want to insert 1st two rows into Hierarchy table, then I would like to update Supervisor_2 to Supervisor_4. Here I don't want to use 'CONNECT BY PRIOR', as it take more time to execute (there are millions of records).SQL code for same.
1. For each of the Filename, if originating_site is not the same as receiving_site, it means that the file has been sent to receiving_site but has not been acknowledged received yet.
2. For each of the Filename, if originating_site is the same as receiving_site, it means the file has been sent and received by the receiving_site.
My task is to list out all the Filename per receiving_site that has been sent, but not received yet by the receiving_site. For example from the sample data above, I am expecting to see that siteA, fileB has not been received yet.
How can I do that? I had tried MINUS and NOT EXISTS command, but I am just not able to get the result that I want.
CREATE TABLE MAT (matrl varchar2(100), date_man date, weight number(10) );
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat1','12-DEC-10',100); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat2','13-DEC-10',200); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat3','21-DEC-10',300); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat4','26-DEC-10',400); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat5','22-DEC-10',500); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat6','02-DEC-10',600); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat7','23-DEC-10',700); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat8','07-DEC-10',800);
I am using LOV on some field to retrieve data, but sometimes it does not retrieve anything cause of unavailability of date its fine but i want to put N/A in that field when such condition is occurring .
select * from testing Quote: ID NAME REDATA -------------------- -------------------- -------------------- 1 ABC,DEF,GHI,LKJ 000001 2 MNC,GHI,CTF 000002
2 rows selected
select * from testing where name like ('GHI,TCF');
now my output should be as above because GHI is common in both the names.if any of the where condition string matches the Name field then I need that record to be retrieved.
,i have a table called travel detail from which i have retrieved few rows based on 1 condition by using LOV and on next few rows modification is allowed. The rows are getting modified but it is also allowing me to enter in next blank row and insert any data in that row.how can i retrieve only that data which is based on the given condition. Further blank rows should not b displayed.
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.
I have a detailed block with a user ID column. The user name is not available in the block. Therefore, I have created a non-database column to retrieve the user name into it.
Here is the code I've used.
PROCEDURE get_details IS
iLoop number := 1;
CURSOR c is
SELECT FULL_NAME FROM GRP_EMPLOYEE WHERE EMPLOYEE_NUMBER = :USER_ID;
I have a "select ... into" in my PL/SQL and it doesn't retreive any data(I suppose just certain columns because if it doesn't retreive a row then the PL/SQL should throw an error) when there are many users accessing this table. It shouldn't be some table locking right? Because if it is, it should wait until the table is available then it will retreive data. Currently it just proceeds with the PL/SQL and selecting nothing in that query. what details should I look at to trace the cause of this problem.
how to connect to Oracle to retrieve data using SQL language. I've started with a new company which is pretty much a sink or swim type of environment meaning you're an idiot if you don't figure it out on your own. I was told that we use Toad for Oracle 10g. I was given a server name; location and an .ora file named tnsnames that was given to me but I have no clue what to do with. I've come from companies where this was already setup. You just go into access to retrieve the data.
II have totally hit a brick wall with all the other roadblocks I encounter and all researched out. Where I can get step by step instructions on how to connect to start sql in Toad/Oracle?
I use the oracle 10g database.I am trying to insert and retrive the image.Inserting an image is done.but while retrieving an image iam getting an run time exception in java "java.sql.sqlexception:general error".i am not able to understand this.
The code to insert the image is FileInputStream fi=new FileInputStream(f); int size = fi.available(); System.out.println("j"+size); byte b1[] = new byte[size]; i=fi.read(b1,0,size); System.out.println("i"+i); st.executeUpdate("insert into image1 values('b1',"+k+")"); when i am retrieving the image i tried like this ResultSet rs=st.executeQuery("select imagecolumn from tablename"); here iam getting an exception as i named above.
I have one query regarding how to retrieve the CLOB data.
The requirement is something that in the select statement there are around 20+ columns which i need to retrieve from around 5 tables after joining.
Since the result set after joining also will get duplicate values i need to use distinct keyword to filter the resultset. But in the 20+ columns there are 2 CLOB data columns which i need to retrieve.
Whenever i use DISTINCT i'm getting ORA-00932: inconsistent datatypes: expected - got CLOB error. I know that DISTINCT keyword cannot be used for CLOB datatypes.
I wanna have a form which has a list box which is including of all table names, by selecting the required table by user all column name and its related data should be retrieve.I have a cursor which give me all field names related to the table by I do not know how can I retrieve table data:
1. How to add item through block base on number of column which selected table has.
2. How to set value of table column in these mentioned column which added in the block.