Forum Stats

  • 3,825,693 Users
  • 2,260,550 Discussions


Is these syntax supported in oracle?

RameshSagar Member Posts: 53 Red Ribbon
edited Sep 15, 2020 9:42AM in SQL & PL/SQL

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
    Paulzip Member Posts: 8,693 Blue Diamond
    edited Sep 15, 2020 3:08AM

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

  • KayK
    KayK Member Posts: 1,717 Bronze Crown
    edited Sep 15, 2020 3:15AM

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

  • RameshSagar
    RameshSagar Member Posts: 53 Red Ribbon
    edited Sep 15, 2020 4:15AM

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

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,383 Bronze Crown
    edited Sep 15, 2020 5:32AM

    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:  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
    Frank Kulash Member, Moderator Posts: 42,044 Red Diamond
    edited Sep 15, 2020 6:17AM


    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"

    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
    Solomon Yakobson Member Posts: 19,467 Red Diamond
    edited Sep 15, 2020 6:48AM

    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.


  • Jim-D
    Jim-D Member Posts: 584 Bronze Crown
    edited Sep 15, 2020 8:41AM

    @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 download.jpg

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,383 Bronze Crown
    edited Sep 15, 2020 9:42AM

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