Apply uniqueness to a subset of rows on a table in Oracle DB

For a table, CARS, with two columns, MAKE and MODEL, I would like to enforce that that for some particular MAKE values, their MODEL values must be unique (assuming this is an Oracle database). For example, I want to enforce duplicates for Ford, but n

SQL query: for each element, add multiple values

How to write a SQl query to create "NewTable" using Table1 and Table2 which the result look like "NewTable" below ? Table1 EmmployeeID | 1 | | 2 | | 3 | Table2 AgencyID | a | | b | | c | NewTable |EmmployeeID|AgencyID| | 1 | a | | 1 |

Recover lines with 0 Oracle account

I am woking on a query which can give back the count divided by month about the offices that will be closed this summer. SELECT qa.tmonth, COUNT(qa.tmonth) AS qtn FROM ( SELECT TO_CHAR(CLOSURE_DATE, 'yyyymm') AS tmonth FROM Holidays WHERE CLOSURE_DAT

SUM does not display the expected value in SQL

I have a table as ID TOTAL SUM(TOTAL) 1 62 62 1 53 53 2 62 62 2 47 47 I thought the SUM(TOTAL) should look like ID TOTAL SUM(TOTAL) 1 62 115 1 53 115 2 62 109 2 47 109 This is the query I used select ID, TOTAL, SUM(TOTAL) from tablename GROUP BY TOTA

Access a table during the trigger

I have three tables: Customer(CustomerID,Name) CustInvoice(InvoiceID, CustomerID (FK to Customer), CustName) ProductOrder(OrderID,OrderDate,CustomerID (FK to Customer)) I want to write the following trigger: "Whenever there's a new order in ProductOr


My sample table is below: TASKMASTER TASKKEY TASKCOLTYPE01 TASKCOLDATA01 001 1 DONE 002 2 2 003 3 3 TASKCOLTYPE values: 1 = text, 2 = yes/no, 3 = high/mid/low I have a case statement: SELECT TASKCOLTYPE01, TASCOLDATA01, CASE TASKCOLTYPE01 = 2 THEN CA

how to add a check constraint in Oracle

I have a table, create table a( id_a number(5) not null, name varchar2(15) not null, address varchar2(30), phone varchar2(15), constraint pk_a primary key (id_a) ); I want add constraint check at phone. example. phone is 08175210868 I want to only be

PL SQL loop through the ID list

I have a list of names. john, sam, peter, jack I want to query the same sql with each of above as the filter. Each query will give me a unique employee id, which I want to use to delete some other records. select emp_id from employee where emp_name l

Oracle Timestamp to DateTime SQL Server

I have multiple statements from oracle database and I need to use them in SQL Server insert into COMENZI (NR_COMANDA, DATA, MODALITATE, ID_CLIENT, STARE_COMANDA, ID_ANGAJAT) values (2456, to_timestamp('08-11-1998 07:53:25.989889', 'dd-mm-yyyy hh24:mi

Insert an empty line

This is so simple it has probably already been asked, but I couldn't find it (if that's the case I'm sorry for asking). I would like to insert an empty row on a table so I can pick up its ID (primary key, generated by an insert trigger) through an Ex

Rewriting an ORACLE SQL statement

SELECT orderid FROM (SELECT orderid, rownum r FROM (SELECT orderid FROM myorders WHERE ordertype = 'E' AND orderstatus = 'A') a WHERE rownum < 86) WHERE r > 84 What is the best way to rewrite the ABOVE sql statement in more clear way... I have tried

Creating tables in the Oracle database by JDBC

I have problem with creating tables by JDBC controller on Oracle database. When I create table by "creata table...." is ok. Table create and I see that table by SQL Developer. I have method to check if table with that name exist - and it works g

A query to display pairs based on certain conditions in a join

Write a query to display distinct pair of patients who are treated by the same doctor. The following tables are present: doctor: d_id d_name patient: p_id p_name treatment: d_id p_id disease medicine The query: Select p1.p_name, p2.p_name from patien

SQL query error in Oracle

On Exeuting Following Query: SELECT ID FROM INSTITUTE WHERE MEMBER_ID IN ( SELECT ID FROM MEMBER WHERE ID IN (765,769,753,774,778,779,781,790,799,809, 820,823,855,835,839,842,845,849,850,851) ORDER BY NAME ASC ) I am getting following error: ORA-0090

Power failure and Oracle data recovery

Database is OracleXE and here is the problem: data gets entered in tables UPS does not survive power shock Oracle server reboots after power failure everything seems normal after some time we realize that some data is missing from few tables (this is

What is the equivalent of SQL Server APPLY in Oracle?

I am new to Oracle. Is there a builtin keyword does the same job of SQL Server APPLY?I think the equivalent of the APPLY clause in Oracle is called a lateral JOIN. A lateral join in Oracle is when you join a table A with a function F that outputs row

Keep synchronized tables in Oracle

We're about to run side-by-side testing to compare a legacy system with a new shiny version. We have an Oracle database table, A, that stores data for the legacy system, and an equivalent table, B, that stores data for the new system, so for the dura

What is the best way to precompile JSPs using Ant?

I am trying to figure out the best way to use Ant to precompile JSPs that will be deployed to an Oracle application server. Even though I am deploying to an Oracle app server I would like to avoid using Oracle's version of Ant.Oracle's JSP compiler i