SQL & PL/SQL :: Can Group BY Clause Have Expression Field

Apr 4, 2011

Can Group BY clause have an expression field?

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)

View 5 Replies


ADVERTISEMENT

SQL & PL/SQL :: Expression Column With A Group By Clause?

Mar 28, 2011

getting expression into a Group By query in oracle.

I have a simple table with two columns. 'ID' and 'Amount'

I want output of the SQL to the following (only 2 fields in the output): I have attached the desired output.

select sum(amountheld) from table1
where member_status = 'MEMBER'
group by ID

This group by query works. But how can i get the expression field (the first field which 'TEMPACCOUNT') in this query (based on my attached output).

View 5 Replies View Related

SQL & PL/SQL :: Combining Rownum And Group By Gives - Not A Group By Expression

Jun 23, 2011

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 ?

View 16 Replies View Related

SQL & PL/SQL :: Group Through Expression Inside Group By?

May 17, 2011

Refer to the txt file to create table and insert data.

I executed the following query-

SELECT priority, detail, COUNT(1) FROM TEST GROUP BY priority, detail

and got the following result-

PRIORITYDETAIL COUNT(1)

StandardPatch 27
StandardInitial TSS 1
StandardInitial development 10
StandardProduction deployment5
High PriorPatch 1

Now I want that Initial TSS and Initial development should be combined as Initial together and I should get the result as follows:

PRIORITYDETAIL COUNT(1)

StandardPatch 27
StandardInitial 11
StandardProduction deployment5
High PriorPatch 1

View 3 Replies View Related

PL/SQL :: Not A Group By Expression

Apr 1, 2013

when i am running the below plsql block i am getting the error like not a group by expressiong.

DECLARE
   CURSOR Cur_st
   IS                       
SELECT DISTINCT
            CAST (A.STO_NO AS VARCHAR2 (5 CHAR)) AS BU_CODE,
            CAST ('STO' AS VARCHAR2 (3 CHAR)) AS BU_TYPE,
            CAST (NULL AS VARCHAR2 (7 BYTE)) AS CUST_NO,
            CAST (A.CUR_CODE AS VARCHAR2 (3 BYTE)) AS CUR_CODE,
            TO_DATE (A.SALES_DATE, 'YYMMDD'),
            CAST (A.RECEIPT_NO AS VARCHAR2 (10 BYTE)),
[code]....

View 19 Replies View Related

SQL & PL/SQL :: ORA-00979 / Not A GROUP BY Expression

Jan 3, 2012

I wrote a query for minimum average salary of Job_id using inline view but it is not working..

The query is as follow

SELECT job_id, avg(salary)
FROM employees , (SELECT MIN(AVG(salary)) min_avg_sal
FROM employees
GROUP BY job_id) b
GROUP BY job_id
having avg(salary)=b.min_avg_sal;

It returns error as

################################
having avg(salary)=b.min_avg_sal
*
ERROR at line 6:
ORA-00979: not a GROUP BY expression
#########################

View 2 Replies View Related

SQL & PL/SQL :: ORA-00979 - Not A GROUP BY Expression At Runtime?

Oct 13, 2010

I ran into something that has me wondering about which errors are caught at compile-time, and which are caught at runtime. There is a pretty big package with lots of complicated queries that heavily use GROUP BY, and in the last couple of years I thought I never had the case of an ORA-00979 happening an RUNTIME, which is now happening.

The last heavy changes in that package did I make in the Oracle 9.2 days, and I *believe* (although my memory might be wrong) that back then the ORA-00979 errors came up at compile time.

So is it normal that the error doesn't show up during compile time, only during runtime, or should it be considered a bug?

SQL> CREATE TABLE test_tab(a NUMBER(1), b NUMBER(1));

Table created.

SQL> INSERT INTO test_tab VALUES (1,1);

1 row created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE test_prod AS
2 v_a NUMBER;
3 v_b NUMBER;
4 BEGIN
5
6 SELECT a,b
7 INTO v_a, v_b
8 FROM test_tab
9 GROUP BY a;
10
11 END;
12 /

Procedure created.

SQL>
SQL> BEGIN
2 test_prod;
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
ORA-06512: at "PFK.TEST_PROD", line 6
ORA-06512: at line 2

View 5 Replies View Related

SQL & PL/SQL :: Conditions Of Clause IF / PLS-00382 Expression Is Of Wrong Type

Jun 4, 2010

I putted in a table "conditions" some rules (if conditions) and I want to read and execute those conditions in another table "list_parameters" in pl/sql procedure.

conditions :
ID||||||||||||||RULE
1-----------(param1 = F)
2-----------(param2 is null)
.....

list_parameters :
id_task|||||param1|||||param2|||||param3|||||param4
--x-----------F---------Z----------NULL-------NULL
--y----------- ---------A----------K-----------L
.........

How can I use the conditions of clause IF from table "conditions"? Is it possible to do:

CURSOR cur_rules IS select * from conditions;
BEGIN
FOR c1_cur in cur_rules

[Code]....

View 12 Replies View Related

SQL & PL/SQL :: To_char Function In Combination With Distinct Clause - ORA-00936 / Missing Expression

Mar 2, 2012

I have a very simple table with 2 columsn. As_of_date is one of the column. This column is "Date" data type.

When I use distinct clause inside a to_char function it gives the following error:

ORA-00936: missing expression
00936. 00000 - "missing expression"

The Sql is

select to_char(distinct(as_of_date),'mm-dd-yyyy') from sales

I can't see any syntax error in the sql..but forsome reason, it doesn't work.

View 2 Replies View Related

JDeveloper, Java & XML :: Missing Expression And Then Group Function Not Allowed Error

Feb 23, 2011

I have 2 tables used in this problem: ODETAILS and ORDERS.

ODETAILS has the following columns: ONO, PNO, QTY, COST
ORDERS has the following columns: ONO, CNO, ENO, RECEIVED, SHIPPED, ORDER_COST

UPDATE ORDERS
SET ORDER_COST= 1 * ( select SUM(
SELECT COST
FROM ODETAILS
WHERE ORDERS.PNO=ODETAILS.PNO
)
);

In ODETAILS there can be more than 1 row for 1 order. So I'm trying to add all the COSTs in ODETAILS.

View 3 Replies View Related

JDeveloper, Java & XML :: Extract Data From Clob Field Using Regular Expression

Oct 6, 2011

I want to extract the data from the Clob field. I have the following table,create table test123(col1 char(24), col2 clob); And following data,

Insert into test123 (col1,col2) values ('ABCDE','<?xml version="1.0" encoding="UTF-8"?>
<Attributes>
<Attribute DataType="Text-40" DisplayName="DropDirectory"
IsNotDeletable="Y" Modifiable="Y" Name="DropDirectory" Value="${Dir1}"/>
<Attribute DataType="Text-40" DisplayName="PrinterAlias"
IsNotDeletable="Y" Modifiable="Y" Name="PrinterAlias" Value="\Printer3Printer4"/>
<Attribute DataType="Text-40" DisplayName="PrintServerHostName"
IsNotDeletable="Y" Modifiable="Y" Name="PrintServerHostName" Value=""/>
<Attribute DataType="Count" DisplayName="PrintServerPort"
IsNotDeletable="Y" Modifiable="Y" Name="PrintServerPort" Value="2723"/>
</Attributes>');
[code]....

The data will be available like this only, The same XML tag is used everywhere.

View 1 Replies View Related

SQL & PL/SQL :: Group By Clause

Mar 31, 2011

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),

[code]...

View 5 Replies View Related

SQL & PL/SQL :: Analytical Function Instead Of Group By Clause?

Sep 18, 2012

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

View 1 Replies View Related

Performance Tuning :: Against Count With Group By Clause

Mar 31, 2012

This query is taking 7 hours to execute as I am retrieving data from history table dept_hist.

select count(distinct empid), e.group_nm, d.date,
from emp e, dept_hist d
where e.deptno = d.deptno
and e.up_ts > sysdate -30

[Code]...

Its taking 7 hours to execute.restructing this query.

View 4 Replies View Related

PL/SQL :: Count Number Of Employees In Every Department - Group By Clause Not Working

Oct 31, 2012

I have two columns Department and EmpName:

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.

View 2 Replies View Related

SQL & PL/SQL :: Counting NULL Vs NON-NULL In A GROUP BY Clause

Jun 21, 2010

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

View 9 Replies View Related

SQL & PL/SQL :: Expression Must Have Same Datatype As Corresponding Expression

Dec 22, 2010

ved>create table test900 ( a number, b number);

Table created.

ved>insert into test900 values( 9,'');

1 row created.

ved>insert into test900 values( 10,null );

1 row created.

ved>select * from test900;

A B
---------- ----------
9
10
ved>select nvl(length(b),0) from test900;
[code]....

ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression..Why the above sql ( case 2 ) gives error?

View 9 Replies View Related

Group Records With Less Than One Hour Separation And Count How Many Per Group

Nov 1, 2013

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.

View 4 Replies View Related

Server Utilities :: Field In Datafile Exceeds Maximum Length For Number Field?

Apr 23, 2010

Even though i am using COL1 CHAR(500) NULLIF COL1=BLANKS, then also i am getting same error for those columns.

View 13 Replies View Related

Forms :: Update Field Based On Another Field Checkbox

May 3, 2013

I have a table where i need to update one field values based on another field of the same table , simply as it is.I have done this using one select all check box , on clicking that all check boxes of item_trans table will get selected , then i will un select some of check box and then using one button, i will update the value of the fields which are checked only.

I have put the sample code but when i am updating its taking long time and hanging.I am also attaching the form based on the test case provided.

--tables with insert statement
create table item_trans (trans_item varchar2(12),trans_qty number,trans_act_qty number)

insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE1',40,NULL);
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE2',20,NULL);
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE3',20,NULL);

--i want to set the value of trans_Act_qty as trans_qty

--i create one dummy or test block to keep the select all check box. for that table test script is

CREATE TABLE TEST
(
C VARCHAR2(2000 BYTE),
B NUMBER,
A NUMBER
);

insert into test (C,B,A) values ('A',1,1);

--code written in select all check box which is created on test.block.

BEGIN
GO_BLOCK('item_trans');
FIRST_RECORD;
LOOP
:M_END_YN := :M_END_ALL;
[code].......

--code written in M_END_YN ( actual check boxes where i will uncheck).

IF :M_END_YN = 'N' THEN
:M_END_ALL := 'N';
END IF;

--code written on button to update those values which are checked.

BEGIN
GO_BLOCK('item_trans');
FIRST_RECORD;
LOOP
IF :M_END_YN = 'Y' THEN
[code]......

View 5 Replies View Related

How To Enable And Disable A Field Dependent On Another Field In Apex

May 9, 2013

I am New to Apex. Im using Apex Version 4.2.

I have a tab named APPROPRIATIONS it has 10 fields. Among which is PROJECT NUMBER, AUTHORIZAION DATE (CALENDER TYPE POPUP), And FUNDS CHECK FLAG.

The Scenario is depending upon PROJECT NUMBER the Fields are Populated including FUNDS CHECK FLAG .

But the AUTHORIZAION DATE is a MANDATORY field has to be filled by User

The Requirement is to make AUTHORIZAION DATE as MANDATORY only when the FUNDS CHECK FLAG is *"BLANK "* or *“Y”*

If the FUNDS CHECK FLAG is *“N”* then the AUTHORIZAION DATE to be made OPTIONAL.

View 2 Replies View Related

Update Field With 1st 9 Characters Of Another Field In Same Record?

Apr 23, 2009

I need to update a field with the 1st 9 characters of another field in the same record.

View 1 Replies View Related

SQL & PL/SQL :: Updating A Date Field With Field From Another Table?

Nov 14, 2011

I have a table called Customer_Type with following fields

Customer_type ,Active_date, Inactive_date
regular,11/01/2011
daily,11/04/2011
monthly,11/05/2011/11/11/2011

Tbale 2:Customer

Customer_name,Customer_type,Customer_Inactive_date
John,regular,
James,monthly,
Jake,daily,
Jill,monthly

What i wnat is to update the Customer_inactive_date with the Incative_date field from Customer_type based on their Customer_type... So james and Jill would have their rows updated in this scneario ..How can i achive this in pl/Sql

I have teh code using merge function..I want something in traditional old fashion..

The sql statements are below

CREATE TABLE CUSTOMER_TYPE
(
type_code VARCHAR2(10),

[Code]....

View 5 Replies View Related

SQL & PL/SQL :: OrderBy Clause Before From Clause?

Apr 23, 2010

can we use something like this

"select ... order by emp from emp"

what is to be done? so that this qurey runs. no co-related subquery to be used.

View 6 Replies View Related

PL/SQL :: How To Format A Value Using Reg Expression

Jan 11, 2013

I need to format a value using Regular Expression.

9911223344, 9911223344
9911223344
11223344Result
(99) 1122-3344, (99) 1122-3344
(99) 1122-3344
1122-3344

View 4 Replies View Related

SQL & PL/SQL :: Regular Expression In Oracle 10g

Apr 13, 2013

What is the use of Regular Expression?

View 1 Replies View Related

SQL & PL/SQL :: How To Use Substr Or Regular Expression

Jan 21, 2011

I have a following table,

CREATE TABLE checkdata
( col1 VARCHAR2(2000)
);
INSERT
INTO checkdata VALUES
[code]......

I need output as ,
INT8144925446-20110118T123723Z
INT8144925448-20110118T123730Z

How do i use Substr and Regular expression here ?

View 11 Replies View Related

SQL & PL/SQL :: Objects Containing Expression Cannot Be Used As Assignment

Apr 4, 2012

I'm working with Object types containing other object types, and I'm getting the error PLS-00363 (Expression cannot be used as an assignment).I'm putting exlpicity all 'SELF' parameters as 'IN OUT', but still get the error...

CREATE OR REPLACE TYPE TYP_PERSON AS OBJECT (
strName VARCHAR2(100),
--
CONSTRUCTOR FUNCTION TYP_PERSON RETURN SELF AS RESULT,
--
MEMBER FUNCTION getName (SELF IN OUT TYP_PERSON) RETURN VARCHAR2,
MEMBER PROCEDURE setName (SELF IN OUT TYP_PERSON, pNewName VARCHAR2)
) NOT FINAL;
[code]....

How can I do this parentObject.getChildObject().setChildFunction()?

View 5 Replies View Related

SQL & PL/SQL :: Breaking Up Expression To Evaluate?

Sep 4, 2013

I have a complex expression coming as an input and I have to break that into smaller one to evaluate each of them. Explained in example:

Let's assume, I have an input string as "(({A}&{B})||({C}||{D})&({E}||({F}&{G})))". My requirement is to break into logical smaller groups like

1. ({F}&{G})

2. ({E}||({F}&{G}))

3. ({C}||{D})

4. (({A}&{B})

View 15 Replies View Related

ORA-00936 :: Missing Expression?

Jul 9, 2010

im trying to take a query that pulls all the info in the current tables, and narrow it down to one day plus current. here is the error i am getting.

(SELECT OH.ORD_CNTRL_NO, OH.ORD_NO, OH.ASSGN_SHIP_DATE, oh.account_no, sum(OL.ORD_QTY) AS ORD_QTY
*
ERROR at line 5:
ORA-00936: missing expression

old query:

 select a.assign_no, a.assign_type, a.act_end, X.ord_no, x.account_no, 
X.ASSGN_SHIP_DATE, X.ORD_QTY, td.from_loc, sum(td.act_qty) AS ACTQTY, sum(td.exp_qty) AS EXPQTY 
from clsd_assign a, clsd_task t, clsd_task_detail td,  sku s,
(SELECT OH.ORD_CNTRL_NO, OH.ORD_NO, OH.ASSGN_SHIP_DATE,  oh.account_no, sum(OL.ORD_QTY) AS ORD_QTY

[code]...

new query

 select a.assign_no, a.assign_type, TRUNC(A.ACT_END), to_char(a.act_end), X.ord_no, x.account_no, 
X.ASSGN_SHIP_DATE, X.ORD_QTY, td.from_loc, sum(td.act_qty) AS ACTQTY, sum(td.exp_qty) AS EXPQTY 
from clsd_assign a, clsd_task t, clsd_task_detail td,  sku s
where  a.act_end  between to_char(sysdate -1, 'dd-mon-yyyy') and to_char(sysdate, 'dd-mon-yyyy')

[code]...

View 2 Replies View Related







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