logo
down
shadow

Not a single function error in Oracle


Not a single function error in Oracle

Content Index :

Not a single function error in Oracle
Tag : sql , By : snk
Date : November 28 2020, 01:01 AM


Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Oracle SQL-Error: ORA-00937: not a single-group group function


Tag : sql , By : user187301
Date : March 29 2020, 07:55 AM
it should still fix some issue your question isn't very clear.. what tables are the fields from?
you will need to have sub queries for your 3 fields, since they all require group by clauses
 create or replace view DeptInfo as
 SELECT q1.dnumber, 
        q1.dname, 
        q1.AvgSal,
        q2.MaxSal,
        q3.MinSal
 FROM (SELECT dnumber, dname, AVG(salary) as AvgSal 
       FROM department, employee
       GROUP BY dnumber, dname) q1, 
      (SELECT dnumber, dname, MAX(salary) as MaxSal 
       FROM department, employee
       GROUP BY dnumber, dname) q2, 
      (SELECT dnumber, dname, MIN(salary) as MinSal 
       FROM department, employee
       GROUP BY dnumber, dname) q3
 WHERE q1.dnumber = q2.dnumber AND
       q2.dnumber = q3.dnumber AND
       q1.dname = q2.dname AND
       q2.dname = q3.name

why oracle user defined function shows error in Oracle Sql Developer 4.0.3.16


Tag : sql , By : 40a
Date : March 29 2020, 07:55 AM
wish help you to fix your issue In Oracle SQL Developer Version 1.5.4, same code doesn't give any error. I am currently connected to oracle 11g database server. ,
v_datevalue date;
begin
v_datevalue := '01-APR-2015';
SQL> ALTER SESSION SET NLS_DATE_FORMAT='MM/DD/YYYY';

Session altered.

SQL>
SQL> CREATE OR REPLACE
  2    FUNCTION GETDATE
  3      RETURN VARCHAR2
  4    IS
  5      v_datevalue DATE;
  6    BEGIN
  7      v_datevalue := '01-APR-2015';
  8      RETURN (CAST(v_datevalue AS VARCHAR2));
  9    END;
 10    /

Function created.

SQL>
SQL> SELECT GETDATE FROM DUAL;
SELECT GETDATE FROM DUAL
       *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "LALIT.GETDATE", line 6


SQL>
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY';

Session altered.

SQL>
SQL> SELECT GETDATE FROM DUAL;

GETDATE
-----------------------------------------------------
01-APR-2015

SQL>
SQL> ALTER SESSION SET NLS_DATE_FORMAT='MM/DD/YYYY';

Session altered.

SQL>
SQL> CREATE OR REPLACE
  2    FUNCTION GETDATE
  3      RETURN VARCHAR2
  4    IS
  5      v_datevalue DATE;
  6    BEGIN
  7      v_datevalue := TO_DATE('01-APR-2015','DD-MON-YYYY');
  8      RETURN (CAST(v_datevalue AS VARCHAR2));
  9    END;
 10    /

Function created.

SQL>
SQL> SELECT GETDATE FROM DUAL;

GETDATE
-------------------------------------------------------------
04/01/2015

SQL>

Oracle : Error ORA-00937: not a single-group group function in Oracle Join Query


Tag : sql , By : Paulh
Date : March 29 2020, 07:55 AM
I wish this help you I suppose you have to add cy.continent to the select statement of your first try when you group by Cy.continent.
Try something like that (floor is for the "round it down to the nearest integer" - part):
SELECT floor(AVG(City.population)),Country.continent FROM City JOIN Country ON City.CountryCode = Country.Code
GROUP BY Country.continent;

Error when query Oracle: not a single-group group function


Tag : oracle , By : Asbie
Date : March 29 2020, 07:55 AM

ORACLE SINGLE GROUP FUNCTION ERROR WITH SUBQUERY


Tag : sql , By : Juan Pablo
Date : March 29 2020, 07:55 AM
will be helpful for those in need I'd avoid subqueries in SELECT statement; they rarely do something good, might cause bad performance and can - usually - be rewritten as
select count(*), t2.email
from table1 t1 join table2 t2 on t2.id = t.1.id
group by t2.email
Related Posts Related QUESTIONS :
  • How to create a SQL Server function to "join" multiple rows from a subquery into a single delimited field?
  • How to pass a comma separated list to a stored procedure?
  • Multiple foreign keys?
  • Inheritance in database?
  • What point should someone decide to switch Database Systems
  • SQL Case Expression Syntax?
  • Drop all tables whose names begin with a certain string
  • What program can I use to generate diagrams of SQL view/table structure?
  • Suggestions for implementing audit tables in SQL Server?
  • Distribution of table in time
  • Backup SQL Schema Only?
  • SQL query for a database scheme
  • Timer-based event triggers
  • SQL query, count and group by
  • Paging SQL Server 2005 Results
  • SQL Server 2005 For XML Explicit - Need help formatting
  • How do I use T-SQL Group By
  • How do I split a string so I can access item x?
  • Date Condition with Case when Holiday exclude
  • SQL Query Select from 1 table and return data based on 2 columns
  • how to use rank/join and where together
  • How to create new column with values counting up every 9th value with SQL?
  • Most frequent combination of words/characters SQL Server
  • Why can't I access a field defined as "Select 1" from a subquery in the outer query?
  • How to solve Msg 8115, Level 16, State 2, Line 2 Arithmetic overflow error converting expression to data type int.?
  • UPDATE row inserted earlier in CTE
  • Display current month values based upon abbreviation (Oct) SQL Server
  • SQL Modify returned select value without union
  • How to put a variable in a like statement in vba?
  • More Elegant way to Batch Validate Phone Numbers
  • SQL find parent where children match over multiple rows
  • A combo box that has to be filter the same data in that field
  • Exclude updated record from trigger function
  • Insert into table from select only when select returns valid rows
  • How to fix SQLite3::SQLException: near "to": syntax error: SELECT "".* FROM "" WHERE "
  • Filter msdb.dbo.sysjobsteps.command into seperate columns
  • How to extract year or month,day from oracle function without using sub-query?
  • Reset sum when condition is met in Oracle
  • Date Between (Start & Now)
  • Reset running sum when condition is met in Oracle
  • Why is my date criteria filtering incorrectly in a MsysObjects query using DCount?
  • Stripping date/time delimiters in an SSIS csv export
  • SSIS Error on insertion - Error when new primary keys in Dimension table
  • How can I make a temp table of specific datetime data within a range?
  • AnalysisException: Syntax error in SQL After multiple join
  • SQL Query Multiple Columns Using Distinct on One Column Only and Using Order By
  • Sum inventory item groups
  • TSQL: Parsing strings with various characters
  • Using SQL Server : how to use select criteria based on sum
  • Inserting dynamic pivot Result into Temp table
  • Joining multiple fields in a dataset joined with descriptions
  • Conditional WHERE EXISTS in Oracle query
  • How to get the count of each unique value in a column between two dates?
  • SQL: Select only if multiple values are IN 1 column from CatalogTable
  • In a query (no editing of tables) how do I join data without any similarities?
  • Create nested json blobs in PostgreSQL
  • Getting extra dates while using cte
  • Way to alter one table if another was altered
  • Proper use of semicolons in functions and procedures
  • Cursor Not looping all the Records
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com