New answers tagged

1

ORA-00913 too many values Cause: The SQL statement requires two sets of values equal in number. This error occurs when the second set contains more items than the first set. For example, the subquery in a WHERE or HAVING clause may return too many columns, or a VALUES or SELECT clause may return more columns than are listed in the INSERT. Action: ...


-1

Just copy your table with a new name and apply the partioning, drop the original table and rename the copied file to the original table.


0

Actually your query doesn't return anything in my test :(. The reason is the attendance_date doesn't fall in between the condition that you have specified(To_date('05','MM') and To_date('05','MM') is returning the same value). You can change your code as ...where attendance_date between To_date('05','MM') and To_date('06','MM'); to select all the ...


0

If you sort by dates formatted in DD/MM/YYYY, then the sorting will put all the first of month first (of every month), then the seconds of month, etc. You can't sort by a column not included in a select distinct, though. To sort by ascending date, group by and use the actual date column instead of the formatted date via to_char: select To_Char ...


0

SELECT p2.ID, p2.NAME FROM person p1 INNER JOIN person p2 ON p1.ID = p2.ID+1 WHERE p1.ID <> p2.ID AND p2.ID <> 1 You can join the table on itself having ID = ID+1 The AND p2.ID <> 1 should only be there if 1 is the lowest value in the ID column. SQLfiddle example


1

Oracle supports LAG so you should be able to do something like. SELECT T.ID, T.NAME FROM ( SELECT ID, NAME, LAG(ID) OVER (ORDER BY ID) AS PREVID FROM YourTable ) T WHERE ID > PREVID + 1; To compare the Id with the previous one and only return ones with a gap greater than 1. (Untested as SQL Fiddle is currently returning errors ...


0

Sorry it was a such a dumb thing to ask, but again if this is a dumb answer please feel free to delete it. I've used the intersect instead of inner joining the two queries as subqueries.


0

That is just the paging in SQL*Plus. By default the paging value is 14, that includes a blank line at the start of each page, the line with column headers, the line with heading separators (hyphens) and the rest (11 rows) is for user data. If you increase the pagesize, the result of your query will fit in to 1 page: set pagesize 20 It has nothing to do ...


1

Whenever you create a table with constraints in Oracle, an entry is made into the user_constraints table and if constraints involve creation of an index (like in this case, primary key constraint also creates a clustered index), an index is also created on the table which you can find querying user_indexes. select * from user_constraints where ...


1

After renaming the table, rename the constraint and the index as well: alter table cba rename constraint abc to cba; alter index abc rename to cba;


1

The standard approach to get rows N through M is to do something like SELECT * FROM (SELECT a.*, rownum rnum FROM (SELECT emp_id, last_name FROM employees WHERE positionID in (1,3) ORDER BY <<something>>) a WHERE rownum <= 60) b WHERE rnum > 50 Note that you need ...


0

I will write the solution here for those who might encounter such error in the future. I solved the error by re-compiling invalid objects.


0

DBTIMEZONE is different than host machine timezone, and it needs to be set up separately (https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions039.htm) . The first step is to change DBTIMEZONE to Moscow so it matches server settings which always makes sense. As a side effect, you will have your current issues "solved" because now db ...


1

You can achieve the same goal by having a UNIQUE composite index on the desired fields. ALTER TABLE YourTable add CONSTRAINT YourTable_unique UNIQUE (A, B,....); (A, B, ...) should be NOT NULL. This might be helpful if you have a surrogate PRIMARY KEY which you may want to keep. This was recently asked for MySQL here, but the principle is the same. ...


0

Yes you can. If you define composite primary key then one of the filed should contain unique value. Composite Primary Key Example: SQL> create table t1(id number, dt date,constraint pk_t1 primary key(id,dt)); Table created. SQL> insert into t1 values(1,sysdate); 1 row created. SQL> insert into t1 values(1,sysdate); 1 row created. SQL> ...


1

The first query you have used returns Cartesian product of the rows from these three tables. For details: Join The following is the simple example to join three tables. SQL> select * from student; STUDENT_ID FIRSTNAME LASTNAME ---------- -------------------- -------------------- 1 Will Smith SQL> select * from module; ...


1

This is definitely possible both with VMware and VirtualBox and any other mainstream virtualization product as well I guess. On my VMware vSphere sandbox, I have created an additional virtual switch that is not mapped to any physical network adapters. This network is not visible/reachable from the outside world, but VMs attached to it can reach each other ...



Top 50 recent answers are included