Oracle Error Ora 01791
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with ora-01791 not a selected expression oracle 12c us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow
Distinct And Orderby In Oracle
Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up distinct with order by ORA-01791: not a SELECTed expression up vote 0 down vote favorite I need to fetch details from DB. Any thing wrong in my code? SELECT DISTINCT FNAME, LNAME, MEMBERORG, DAYCOUNT, TIMESTAMP, COUNT(FNAME) AS total,(SELECT COUNT(*) FROM REPORT_VIEW_PAGE) AS tot FROM REPORT_VIEW_PAGE WHERE
01791 Area Code
ID = '68' AND TYPE = 'node' GROUP BY FNAME, LNAME, MEMBERORG, DAYCOUNT, TIMESTAMP ORDER BY TITLE ASC This giving me an error: ORA-01791: not a SELECTed expression 01791. 00000 - "not a SELECTed expression" *Cause: *Action: Error at Line: 6 Column: 10 sql oracle share|improve this question edited Jul 29 '15 at 5:27 PKirby 7531922 asked Jul 29 '15 at 4:54 drup 449 1 possible duplicate of Using the DISTINCT keyword causes this error: not a SELECTed expression –bish Jul 29 '15 ora-00979 at 4:56 I removed distinct from query. But now the error is ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" *Cause: *Action: Error at Line: 6 Column: 10 –drup Jul 29 '15 at 4:59 The new error tells you that you're trying to order by a column title that you're not selecting. –Justin Cave Jul 29 '15 at 5:01 Thanks. My mistake. –drup Jul 29 '15 at 5:07 add a comment| 1 Answer 1 active oldest votes up vote 2 down vote accepted The problem here is the ORDER BY column TITLE isn't selected in the DISTINCT query. Since DISTINCT is used, the SELECT query will try to group the resultset based on the selected columns. ORDER BY column isn't selected here, it doesn't ensure the uniqueness on the resultset and hence it fails to apply ORDER BY. share|improve this answer answered Jul 29 '15 at 5:17 Shishir Kumar 4,24811436 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as a guest Name Email Post as a guest Name Email discard By posting your answer, you agree to the privacy policy and terms of service. Not the answer you're looking for? Browse other questions tagged sql oracle or ask your own question. asked 1 year ago viewed 1528 times active 1 year ago Linked 22 Using the DISTI
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company
Ora-00936: Missing Expression
Business Learn more about hiring developers or posting ads with us Stack Overflow Questions
Ora-06550
Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, select distinct just like you, helping each other. Join them; it only takes a minute: Sign up Order By in Oracle cause ORA-01791: not a SELECTed expression up vote 2 down vote favorite I have the below query. http://stackoverflow.com/questions/31691470/ora-01791-not-a-selected-expression When executed it throws an error and the query is not executed. SELECT DISTINCT svc.column1 , svc.column2 , svc.column3 , svc.column4 , svc.column5 , svc.column6 , svc.column7 , svc.column8 , svc.column9 , svc.column10 , svc.column11 , svc.column12 FROM shemaName.tableName svc WHERE svc.column13 IS NOT NULL AND svc.column14 = 'DEEPAK' AND svc.column15 = '188888' AND ROWNUM <=10 AND column16 = 'N' ORDER BY svc.column13; And the error I got was java.sql.SQLSyntaxErrorException: ORA-01791: not a SELECTed http://stackoverflow.com/questions/33492254/order-by-in-oracle-cause-ora-01791-not-a-selected-expression expression. The query returns result when order by clause is removed, as below: SELECT DISTINCT svc.column1 , svc.column2 , svc.column3 , svc.column4 , svc.column5 , svc.column6 , svc.column7 , svc.column8 , svc.column9 , svc.column10 , svc.column11 , svc.column12 FROM shemaName.tableName svc WHERE svc.column13 IS NOT NULL AND svc.column14 = 'DEEPAK' AND svc.column15 = '188888' AND ROWNUM <=10 AND column16 = 'N'; What is wrong in adding order by clause? sql oracle sql-order-by distinct share|improve this question edited Nov 25 '15 at 9:09 Florin Ghita 14k32759 asked Nov 3 '15 at 5:52 user2855120 111 1 See DISTINCT results in ORA-01791: not a SELECTed expression –JRD Nov 3 '15 at 5:57 add a comment| 2 Answers 2 active oldest votes up vote 2 down vote The engine cannot process your query because it defies logic. I'll give you an lighter example to understand easier the case: Table colA colB 1 10 1 30 2 20 2 20 So: select distinct colA from table; 1 2 select colA from table order by colb; 1 2 2 1 BUT, select distinct colA from table order by colb; what shoud give? the value 1 should be first and last in the same time. This is an impossible problem. PS: In your case it may be appropriate to add column13 to the select distinct clau
CommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript https://community.oracle.com/thread/3756075 back on and reload this page. Please enter a title. You can not post a blank message. Please type your message and try again. More discussions in https://www.techonthenet.com/oracle/errors/ora01790.php Oracle Call Interface (OCI) All PlacesDatabaseDatabase Application DevelopmentOracle Call Interface (OCI) This discussion is archived 3 Replies Latest reply on Jul 17, 2015 9:41 AM by oracle error 2980326 Oracle Call Interface: "ORA-01791: not a SELECTed expression." 2980326 Jun 29, 2015 7:52 AM When calling a query through OCIStmtExecute() method an "ORA-01791: not a SELECTed expression." error gets thrown. When calling the same query via SQL developer it produces no errors. The error gets thrown when using DISTINCT + GROUP BY oracle error ora + ORDER BY clauses.Calling "SELECT DISTINCT DUMMY FROM DUAL GROUP BY DUMMY ORDER BY DUMMY" will throw an error, when called with OCIStmtExecute() method (OCI versions tested 12.1.0, 11.2.0.), but it will run fine in SQL developer. Is there a fix to this problem? I have the same question Show 0 Likes(0) 830Views Tags: none (add) ociContent tagged with oci, ora-01791Content tagged with ora-01791, oracle call interfaceContent tagged with oracle call interface This content has been marked as final. Show 3 replies 1. Re: Oracle Call Interface: "ORA-01791: not a SELECTed expression." sol.beach Jun 29, 2015 1:23 PM (in response to 2980326) submit a Bug Report to My Oracle Support. Like Show 0 Likes(0) Actions 2. Re: Oracle Call Interface: "ORA-01791: not a SELECTed expression." Bashar. Jun 29, 2015 1:39 PM (in response to 2980326) Hi,What is the database full version number?Regards,Bashar Like Show 0 Likes(0) Actions 3. Re: Oracle Call Interface: "ORA-01791: not a SELECTed expression." 2
MySQL MariaDB PostgreSQL SQLite MS Office Excel Access Word Web Development HTML CSS Color Picker Languages C Language More ASCII Table Linux UNIX Java Clipart Techie Humor Advertisement Oracle Basics ALIASES AND AND & OR BETWEEN COMPARISON OPERATORS DELETE DISTINCT EXISTS FROM GROUP BY HAVING IN INSERT INSERT ALL INTERSECT IS NOT NULL IS NULL JOIN LIKE MINUS NOT OR ORDER BY PIVOT REGEXP_LIKE SELECT SUBQUERY TRUNCATE UNION UNION ALL UPDATE WHERE Oracle Advanced Oracle Cursors Oracle Exception Handling Oracle Foreign Keys Oracle Loops/Conditionals Oracle Transactions Oracle Triggers String/Char Functions Numeric/Math Functions Date/Time Functions Conversion Functions Analytic Functions Advanced Functions Oracle / PLSQL: ORA-01790 Error Message Learn the cause and how to resolve the ORA-01790 error message in Oracle. Description When you encounter an ORA-01790 error, the following error message will appear: ORA-01790: expression must have same datatype as corresponding expression Cause You tried to execute a SELECT statement (probably a UNION query or a UNION ALL query), and all of the queries did not contain matching data types in the result columns. Resolution The option(s) to resolve this Oracle error are: Option #1 Re-write the SELECT statement so that each matching column is the same data type. For example, if you tried to execute the following UNION query: SELECT supplier_name FROM orders UNION SELECT quantity FROM orders_audit; You would receive an error message as follows: Since the supplier_name column is defined as a varchar2 and the quantity column is defined as a number, these columns can not be matching columns. You can try correcting this UNION query by using a conversion function (ie: TO_CHAR function, TO_NUMBER function, or TO_DATE function) to convert the values to matching data types. For example: SELECT supplier_name FROM orders UNION SELECT TO_CHAR(quantity) FROM orders_audit; In this example, we've used the TO_CHAR function to convert the quantity column to a data type that is compatible with the supplier_name column. Share this page: Ad