I require to select a row if there is only one row exists, if there are more rows, it should select 0 rows. Answer If you’re using PL/SQL, then selecting the column using select-into will throw a too_many_rows exception if there’s more than one row returned: If you want to do this just using SQL, …
Tag: oracle
How to guarantee atomic SQL inserts with subqueries?
Given a simplified table structure like this: Can I use a subquery like this for inserting records without causing a race condition? Or are subqueries not atomic? I’m worried about simultaneous INSERTs grabbing the same value for num and then causing a unique constraint violation. Answer Yes, this can m…
Decode equivalent in postgres
There is no equivalent to the Oracle’s DECODE()’Function InPostgres`. Is there anyone who wrote decode as a Function?
UPDATING Table Column with new Entry
I am working on Oracle APEX. I want my report to show me the updated Debit value in Total amount column.The Report Query is shown in the following diagram mentioned below. Required Scenario: I want to update the TotalAmount Column with Debit and Credit. The new Debit or Credit value has to be shown in the Tot…
ORA-00928 missing SELECT keyword in Oracle
I’m using the following code to insert data. But I’m receiving an error as “ORA-00928: missing SELECT keyword” try { Class.forName(“oracle.jdbc.driver.OracleDriver”); java.sql.Connection conn = …
Oracle: how to get percent of total by a query?
I have the following query and its result is very unexpected to me: select stato, (count(1) *100) / sum(1) from LOG_BONIFICA group by stato; it returns 100 for all rows. Should it be different ?
Oracle date to string conversion
I have a string column COL1 when I am doing this The data in COL1 is in dd-mon-yy, eg: 27-11-89 and 89 is 1989 but the select returns it as 11/27/2089. I have to do an inner TO_DATE because if I don’t then I am getting an invalid number error (ORA-01722: invalid number) How can show 1989 instead of 2089…
ORA-00904 invalid identifier error
I get ORA-00904 ‘c’ invalid identifier error. What is wrong with this alias ? Answer I think you want to fetch those records having count > 1. So you can’t use Indentifier C in the WHERE clause of the same query.
Defining the sort order of children in a hierarchy query
I would like to know the Oracle SQL query that orders the children in a hierarchy query by the sequence_within_parent column. An example data set and query are: create table tasks (task_id …
oracle SQL how to remove time from date
I have a column named StartDate containing a date in this format: 03-03-2012 15:22 What I need is to convert it to date. It should be looking like this: DD/MM/YYYY What I have tried without success is: Is there a way to do this? EDIT1: the PA_VALUE column is: VARCHAR2 Answer When you convert your string to a …