Oracle Analytics Cloud and Server Idea Lab

DV - Improve the SQL editor in Local Subject Areas

Needs Votes
31
Views
2
Comments

Organization Name

GSO

Description

We need a better (Logical) SQL Editor for editing Local Subject Areas.

The following ideas were raised by our Developers:

• add the possibility to use special formatting (New Lines / Tab / Space) => this will allow the developer to create the Logical SQL in another editor tool and copy it back
• add a validate SQL option before saving
• improve the error handling system (clear error messages)

Use Case and Business Need

An improved Logical SQL editor will help our users develop better queryies from a structure point of view.

This will also save a lot of time when trying to apply changes or read and understand the Logical SQL.  

More details

This request can be related with https://cloudcustomerconnect.oracle.com/posts/ba6dd6fdfc.

Original Idea Number: f6efc51c9f

3
3 votes

Needs Votes · Last Updated

Comments

  • today, when you write a case statement like

    case

    when 1=1 then 1

    end

    you will get a script error (because there is no space after the "e" in case) in order for this to work, you need to write it as follows:

    case when 1=1 then 1

    end 

    or you can put the space after the "e" in case explicitly.  This also happens with union and union all statements.

    Overall, the product would be enhanced it it had capabilities similar to the SQL Editor in APEX.  Standard features like find/replace; validate SQL; find; replace all would be a nice addition.

  • Angel Shipp
    Angel Shipp ✭✭✭✭✭

    This would definitely benefit Verizon as well. I would ask to apply these SQL improvements not just when using Local Subject Areas, but also when using external database connections. The user expectation is that when SQLs run successfully in Oracle Developer, they should run successfully using OAS SQL Editor as well. But that is not always the case. Some SQLs that run successfully in Oracle SQL Developer do not run in SQL Editor in OAS, but does not generate any error message that tell us why they do not run. 

    There are times when the SQL Editor generates an incomplete data preview (shows just the table headers without data), but does not generate an error message. This leaves users left to wonder if there was an error with their SQL or with the tool. We had to involve IT to inspect the logs to identify if there was an error with their SQL. This gap would be mitigated if the SQL Editor has more robust handling and notification of error messages. Notify the users of the exact error message whenever they occur.