Is these syntax supported in oracle?

RameshSagar

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

Paulzip

Yes they are, but why didn't you try it yourself to check?

KayK

I ask you to move your thread to this community. Not duplicate it

RameshSagar

Apologies. I am using phone to browse this. I had to duplicate.

Mustafa_KALAYCI

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.

Frank Kulash

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

Solomon Yakobson

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.

Jim-D

@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

Mustafa_KALAYCI

as I add an ss to Cannot upload image to post I am not able to. thanks for testing it Jim.