Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 238 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 439 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Is these syntax supported in oracle?

Hi All,
I am looking for a help in knowing if below function syntax works in Oracle. I only need to know, if any version of Oracle supports these combinations of functions.
1) sign(count(request_id)) as 'Customer'
2) (COUNT(SESSION_ID) - sum(is_open)) as 'CLOSED'
-The values of is_open is from case statement as below:-
case VERDICT
WHEN 0 THEN 1
ELSE 0
END is_open
Sorry, for keeping this question too short with too less information. Please note, the above syntax works good in MSSQL Server
Answers
-
-
-
Apologies. I am using phone to browse this. I had to duplicate.
-
as it is told before, just try them. this is not worth to wait. you can simply create (or use an existing one) to test your queries. you also mentioned that they are working on another db so I assume that you are working on another database and maybe you don't have an oracle database which is accessible to you. so we also have a great environent: https://livesql.oracle.com/ an online oracle database that you can work on through your web browser.
here is an example:
of course I used Oracle's demo tables and data is not make sense but just to check the syntaxes.
edit: I couldn't load the image because interface keep telling me "image type is forbidden" which I tried almost all of them so just uploaded it onto somewhere.
edit: I was able to upload from firefox. I guess chrome has an issue.
-
Hi,
As Paulzip said in reply #1, the best way to see if something works is to try it. If you have a problem, post a complete example (including sample data, if you're not using commonly available tables, such as those in the scott schema),explain what you want to do, and ask a specific question.
For example:
"I want to see if a column is NULL in all rows in a group, or if any rows have a value. Using the comm column in the scott.emp table, I want to get output like this:
DEPTNO Customer------ -------- 10 0 20 0 30 1
This tells me that comm in NULL on all rows that have deptno=10 or 20, but when deptno=30, at least one row has a value. (I know that having lower-case letters letters in a column name is a bad idea, but I really want the column to be called Customer, with a capital C and all of the other letters in lower case.)
I tried this:
SELECT deptno, sign(count(comm)) as 'Customer'FROM scott_empGROUP BY deptnoORDER BY deptno;
but I got this error
, sign(count(comm)) as 'Customer' *ERROR at line 2:ORA-00923: FROM keyword not found where expected
pointing to the column alias 'Customer'. If I don't put quotes around the column alias Customer , then I get the right results, but the column name displayed is all capital letters. What is the problem here?
I'm using Oracle 12.2.0.2.0."
I know it's a lot harder to write a question like this, but you'll learn more, faster, and you'll get better answers sooner if you do. More often than not, you'll find the answer yourself before you finish writing the question. (In this example, it's using single-quotes instead of double-quotes around the column alias.)
-
MSSQL != ORACLE, so as always RTFM. Read Database Object Names and Qualifiers and you'll realize:
1. Although column aliases, table aliases, usernames, and passwords are not objects or parts of objects, they must also follow these naming rules
2. A quoted identifier begins and ends with double quotation marks (")
So alias Customer or CLOSED if quoted should be enclosed in double quotes, not single quotes.
SY.
-
@Mustafa KALAYCI - I saw your post at Cannot upload image to post
Using Chrome Version 85.0.4183.102 (Official Build) (64-bit), I'm able to click Insert Image - From your Computer and insert this image download.jpg
-
as I add an ss to Cannot upload image to post I am not able to. thanks for testing it Jim.