This time, I am going to provide the DML statements.
I have a simple table with 3 fields in it.I want to group by ACCT_NUMBER and sum of BALANCE field. But I want to get the description of the first row in a group.
the statements below. Here there are two groups of records 2001 and 2002. My sql(which I am working on) should return the following :
2001 EMPL TRAINING-MIS 20
2002 OTHER PROF SERV-HR 40
The following query will group by ACCT_NUMBER and sum of the BALANCE field. But how can I get the DESCRIPTION?
SELECT ACCT_NUMBER, SUM(BALANCE)
FROM TEST
GROUP BY ACCT_NUMBER
CREATE TABLE "TEST"
("ACCT_NUMBER" VARCHAR2(20 BYTE),
"DESCRIPTION" VARCHAR2(20 BYTE),
I want to use Analytical function instead of group by clause for below query..
select CASE WHEN ADMT.SOURCESYSTEM ='CLU' THEN COUNT(ADMT.TOTAL_COUNT)*5 ELSE COUNT(ADMT.TOTAL_COUNT) END TOTAL_COUNT from ESMARTABC.ABC_DRVR_MFAILS_TMP ADMT group by ADMT.SOURCESYSTEM
Say for example, I want to group by the fields "Type" and and expression called "expression" (which is a case statement). I tried running this query and it says "expression" is invalide identifier.
select type, case when SUBSTR (glnumber, 1, 2) = '05' then 'IS' when SUBSTR (glnumber, 1, 2) = '06' then 'IS' else 'BS' end "expression" , sum(balance) from table group by TYPE, "expression"
If there is any online material on how to GROUP BY on an expression(like above)
Department EmpName ____________________________________ Sales empname1 Sales empname2 Marketing empname3 Development empname4
Now I want to count the number of employees in every department. I want the output to be
Department Total ______________________ Sales 2 Marketing 1 Development 1
I am retrieving names of the department through a subquery
The query I am trying to execute is:
SELECT Department, Employee FROM ( SELECT ...query from other table) AS Department, count( A.EmpName) AS Employee FROM Employer A, EmployeeInfo B WHERE (A.EmpID = B.EmpID AND A.EmpCategory like 'Category2') GROUP BY Department
I know that you cannot group by using aliases and hence a little work around, but still the query isn't working.
I am running a GROUP BY query on a few columns of enumerated data like:
select count(*), Condition, Size group by Condition, Size;
COUNT(*) CONDITION SIZE -------- ---------- -------- 3 MINT L 2 FAIR L 4 FAIR M 1 MINT S
Well, let's say I also have a timestamp field in the database. I cannot run a group by with that involved because the time is recorded to the milisec and is unique for every record. Instead, I want to include this in my group by function based on whether or not it is NULL.
For example:
COUNT(*) CONDITION SIZE SOLDDATE -------- ---------- -------- ---------- 3 MINT L ISNULL 2 FAIR L NOTNULL 2 FAIR M NOTNULL 2 FAIR M ISNULL 1 MINT S ISNULL
I'm trying to group sets of data based on time separations between records and then count how many records are in each group.
In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5
SELECT AREA_ID AS "AREA ID", LOC_ID AS "LOCATION ID", TEST_DATE AS "DATE", TEST_TIME AS "TIME" FROM MON_TEST_MASTER WHERE AREA_ID =89 AND LOC_ID ='3015' AND TEST_DATE ='10/19/1994';
[code]....
Group 1 = 8:00:22 to 8:41:22
Group 2 = 11:35:47 to 11:35:47
Group 3 = 15:13:46 to 15:13:46
Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.
I read that rownum is applied after the selection is made and before "order by". So, in order to get the sum of salaries for all employees in all departments with a row number starting from 1, i wrote :
select ROWNUM,department_id,sum(salary) from employees group by department_id
If i remove rownum, it gives the correct output. Why can't rownum be used here ?
I'm using this code, and it performs fine, but I'm wondering if there is a more elegant way to do it--maybe with "ROLLBACK". Basically (as you can see) I need to get a normal count for each group but also for each group take a percentage of the total count (so all groups pct adds up to 100 (oh yeah, don't test for zero below, but just a test... )
select c.Event, c.code, count(1) as calls, total.total_count, count(1) / total.total_count * 100 as pct_of_total from table1 c
[Code]....
[Edit MC: add code tags, do it yourself next time]
I need to update the TEMP_GP_SEQ table and on the COLUMN COL7 based on the grouping range mentioned above using the sequence created. But the sequence should not be incremented for each and every record, it should be incremented only for change of groups. For example
Actually the group by fetched two rows with a total record count of 4 (2 records in both the rows). SO the COL7 is updated for these four rows with M as a prefixed word and the number followed after M is from the sequence. Here the number is changing (sequence is incrementing) only when there is a change in grouping criteria and it remains the same throughout a group.
I have a exp .dmp file of a database that has over 1000 tables. I would like to import all tables that don't begin with 'C'. Is this possible with IMP (without listing each table I want)?
>select level ,empno,ename,mgr from emp connect by prior empno=mgr start with mgr is null;
Output:- > LEVEL EMPNO ENAME MGR ----- ---------- ---------- ---------- 1 7839 KING 2 7566 JONES 7839 3 7788 SCOTT 7566 4 7876 ADAMS 7788 3 7902 FORD 7566 2 7698 BLAKE 7839 3 7499 ALLEN 7698 3 7521 WARD 7698 3 7654 MARTIN 7698 3 7844 TURNER 7698 3 7900 JAMES 7698
LEVEL EMPNO ENAME MGR ----- ---------- ---------- ---------- 2 7782 CLARK 7839 3 7934 MILLER 7782
Note:- I got only this that this query is alternative of self join and it is giving manager name along with mgr id for each employee but when it gives output i couldn't able to understand how to identify who is manager of whom. Tell and explain with clause in oracle , i only know it is alternative of inline view but i want to know how does it work and how to use 'WITH' in oracle query if possible.
I am getting error 'ORA-03113: end-of-file on communication channel' in Oracle 11g R2 version when I ran the below query.
WITH tab1 AS (SELECT C_NUM as col1 FROM test123_v) SELECT COUNT (distinct col1) num_assignments FROM tab1 UNION ALL SELECT COUNT (distinct col1) num_assignments FROM tab1
The same query is working fine in 10g.Here test123_v is view created using table test123, which has one column c_num.When I use the table instead of view in the with clause I am getting the output in 11g. The below query gives the output.
WITH tab1 AS (SELECT C_NUM as col1 FROM test123) SELECT COUNT (distinct col1) num_assignments FROM tab1 UNION ALL SELECT COUNT (distinct col1) num_assignments FROM tab1
I have a small doubt...Without any creation of tables as I feel it's not needed...(let me know if u need them) Error says "Missing Keyword"
select distinct record_number from meta m, procedu b, control v, fact f, calendar dc, person p where f.key = m.key and f.group_key = b.group_key and b.proc_key = v.proc_key and f.calendar_key = dc.calendar_key and f.person_key = p.person_key and VALUE BETWEEN DECODE((Select distinct operator_type [code]....
But this gives an error...If the decode gives a "single"...the statement is working fine...but If the decode gives a "range"...the above statement gives an error saying "missing keyword"..Does the above code when mapped to "Range"...is it not producing "and" like " value between 100 and 1000"
I have requirement to create an XML structure through stored procedure. I need to Order some of the columns in ascending order before I format them into the xml structure. I am pretty novice to creating an output into XML format, but attached is the query I came up with (without order by). This works perfect, but now the requirement is to order by - cls_cd, and within cls_cd, again order by - cat_cd. I am not able to do this.
You think i can do a from clause with conditions ??
The reason is that i need to retrieve fields from different schemas depending on a column in a common table
let s say the column CRITERIA_COL is in table Common If COMMON.CRITERIA_COL has value 1 then the select query should fetch results from the schema : SCHEMA1.MY_TABLE If COMMON.CRITERIA_COL has value 2 then the select query should fetch results from the schema : SCHEMA2.MY_TABLE If COMMON.CRITERIA_COL has value 3 then the select query should fetch results from the schema : SCHEMA3.MY_TABLE
Something like this:
Select my_Col1, my_Col2 from (case COMMON.CRITERIA when '1' then SCHEMA1.MY_TABLE when '2' then SCHEMA2.MY_TABLE when '3' then SCHEMA3.MY_TABLE )
but that is not working .By the way my query is not just that, it s a more complicated query, that s just the portion I am having trouble with .
I am not strong in SQL but can write easy SQL for data extraction but seem to not understand how to correctly use sysdate in a where clause.
Case: I have to create an alert that will email my IT dept once a person is terminated in Oracle HR. The alert will only run once a day with all terminations specified in the alert. Setting up the alert is not the issue, but rather the SQL code I want to use.
The alert will run everyday at CoB 17:30. Now, in my query, how do i specify that the results should be only for the current day? The problem however, is that I also retrieve Person Type which should show as Ex-Employee, but this is only shown the day after the actual termination has been done, because the employee is still active on the date of termination.
Is there a way to have a variable in the FROM clause or another way to have the table name as a variable? I'm trying to not repeat the same query three times with only the table name being different.
---the tables
create table org_a (emp_id number(5) not null, name varchar2(20)); create table org_b (emp_id number(5) not null, name varchar2(20)); create table org_c (emp_id number(5) not null, name varchar2(20));
---the records
INSERT ALL INTO org_a (emp_id, name) VALUES (00001, 'MISTER WHITE') INTO org_a (emp_id, name) VALUES (00002, 'MISTER ORANGE') INTO org_b (emp_id, name) VALUES (00003, 'MISTER PINK') INTO org_b (emp_id, name) VALUES (00004, 'MISTER BROWN') INTO org_c (emp_id, name) VALUES (00005, 'MISTER BLUE') INTO org_c (emp_id, name) VALUES (00006, 'MISTER BLOND') SELECT * FROM dual;
---verify inserts
SELECT * FROM org_a UNION ALL SELECT * FROM org_b UNION ALL SELECT * FROM org_c;
---i want the table name to be dependent on a variable. eventually, ---i intend to link v_org to a form with radio buttons (values: 1, 2, 3) ---to keep this simple, i'll just assign 1 to v_org
DECLARE
v_org number(1) := 1; v_table varchar2(5);
BEGIN v_table := CASE v_org WHEN 1 THEN 'org_a' WHEN 2 THEN 'org_b' WHEN 3 THEN 'org_c' END;
SELECT * FROM v_table;
END;
--this is what i receive
SQL> / SELECT * FROM v_table; * ERROR at line 10:
ORA-06550: line 10, column 17: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 10, column 3: PL/SQL: SQL Statement ignored
It is the simplest join or inner. An equijoin combines rows that have equivalent values for the specified columns.
SQL> select * from x;
NAME EMAIL EMPID Sam email@removed 1060 Rose email@removed 1061
[code]....
don't consider above mentioned queries I got valuable outputs.
NAMEEMAIL EMPID NAME EMAIL EMPID samemail@removed 1060 sam email@removed 1060 roseemail@removed 1061 rose email@removed 1061 sonaemail@removed 1062 sona email@removed 1062
Inner join shows matches only when they exist in both tables.so , i got records 1060,1061,1062
// Referencing columns used in a USING clause. SQL> select x.name,x.email,x.empid from x 2 inner join y 3 using (empid);
select x.name,x.email,x.empid from x * ERROR at line 1: ORA-25154: column part of USING clause cannot have qualifier
so query rewritten as
SQL> select x.name,x.email,empid from x 2 inner join y 3 using (empid); NAME EMAIL EMPID --------------- --------------- ---------- sam email@removed 1060 rose email@removed 1061 chris email@removed 1062
I mean see two different outputs.first output records twice displayed ... Yes i agree that is Inner join.second output records not displayed twice... common records only displayed once ,in x and y.
I think should n't use a table name or alias when referencing columns used in a USING clause... am i right ????
I want to know both are inner joins .how Oracle is determined both outputs ?
SELECT sno,mid,mname FROM manage_date WHERE mname IN ('KIRAN-KUMAR', 'RAHUL-RAJ', 'KAUSHAL-SONI');
IF I use this query directly in DB it's working fine. But when this query is calling in .net using parameter as below it's not giving any records for more than one value. IN (:p_mname)
If I pass one name 'KIRAN-KUMAR' from .net It's working.
If I pass multiple names from .net query not returning any records.
SELECT * FROM wc_claims WHERE part_nbr in l_part_nbr ;
I have three tables: table A has - user_id, supplier table B has - supplier, part_nbr table C , wc_claims, has part_nbr and 78 other columns.
what i need to do is when user logs in with his user_id, he should see only that info from table C, corresponding to his supplier, and the part_nbr.
SELECT supplier from table_A WHERE user_id = 'abc' ; supp_abc SELECT distinct part_nbr from table_B WHERE supplier = 'supp_abc' ; partA, partB, partC,........partZZ
SELECT * FROM wc_claims WHERE part_nbr in (partA, partB, partC,........partZZ );
I have created a function :
create or replace Function wcl_partnbr ( p_supp IN varchar2 ) RETURN CLOB IS
[Code].....
so that I can use that in the WHERE clause, because one supplier can have thousands of parts...so it works fine, if there are 200 part numbers for one supplier, but above that.. it doesnt.
EXAM2@orcl> desc search_table; Name Null? Type ----------------------------------------- -------- ---------------------------- ROL NOT NULL VARCHAR2(7) CNM NOT NULL VARCHAR2(30) FNM NOT NULL VARCHAR2(30) MNM NOT NULL VARCHAR2(30) DOB NOT NULL DATE YEAR NOT NULL NUMBER(4) EXAM NOT NULL VARCHAR2(1) MAIN_SUPP NOT NULL VARCHAR2(1)
In a dotnet application user is going to input/select all/any of the above column in where clause.If user has given value for rol column then ref cursor should be :
select * from search_table where rol like <given value> endif and/or
If user has given value for cnm column then ref cursor should be :
select * from search_table where rol like <given value> and/or cnm like <given value> endif
I mean whatever value is being given; it should be part of where clause.For it i am going to :
EXAM2@orcl> CREATE OR REPLACE PACKAGE rollsearch AS 2 TYPE t_cursor IS REF CURSOR; 3 Procedure rol_cursor(c_rol in varchar2,c_cnm in varchar2,c_fnm in varchar2, 4 c_mnm in varchar2,d_dob in date,n_year in number,c_exam in varchar2,c_main_supp in varchar2, 5 io_cursor IN OUT t_cursor); 6 END rollsearch; 7 /
EXAM2@orcl> CREATE OR REPLACE PACKAGE BODY rollsearch AS 2 Procedure rol_cursor(c_rol in varchar2,c_cnm in varchar2,c_fnm in varchar2, 3 c_mnm in varchar2,d_dob in date,n_year in number,c_exam in varchar2,c_main_supp in varchar2, 4 io_cursor IN OUT t_cursor) 5 IS 6 v_cursor t_cursor; 7 sql varchar2(1000); 8 BEGIN 9
What should i write further; so that i may get ref cursor as whereed clause for given value. So that i may populate my datagrid view for that web form.
create or replace procedure inserta_en_B (numregistros in integer) as ultimo_año_nuevo date := trunc (sysdate,'year'); dias_transcurridos number(3) := sysdate - ultimo_año_nuevo; begin [code]........
First i insert into b 400000 rows using:
execute inserta_en_b(400000)); commit;
But then i need to insert 100000 rows more using the stored procedure and without removing the 400000 rows stored before. I think i need to use the commit clause, but i dont know where.
