I need to find the 'name of the branch that has made the most money in 2009'. My tables are below: Rental (cid, copyid, outdate, returndate, cost) Copy (copyid, mid, bid) Branch (bid, bname, baddress) I have written the following code, and it outputs the sum of all branches, but I need the sum of the branch that made the most money...
Earlier I have created tables this way: create table workflow ( id number primary key, name varchar2(100 char) not null, is_finished number(1) default 0 not null, date_finished date ); Column is_finished indicates whether the workflow finished or not. Column date_finished is when the workflow was finished. Then I had ...
I have an abstruse sqlldr problem that's bothering me. My control file looks something like this: load data infile 'txgen.dat' into table TRANSACTION_NEW fields terminated by "," optionally enclosed by '"' TRAILING NULLCOLS ( A, B, C, D, ID "ID_SEQ.NEXTVAL" ) Data is something like this: a,b,c, a,b,,d a,b,, a,b,c,d If I do...
Is there a way in Oracle that can pull the FY? I used the script below to pull just two FY. Mytable date range is from FY1998 to FY2009. SELECT 'FY2008' as FY, Site, COUNT(*) FROM mytable WHERE date >='10-OCT-2007' AND date <'10-OCT-2008' GROUP BY site SELECT 'FY2008' as FY, Site, ...
Hi, I need to tokenize a string and reverse it in SQL. For example if the string is, 'L3:L2:L1:L0', i need to reverse it as 'L0:L1:L2:L3'. The tokenizing could be done using a delimiter ':' and then reverse it. Please suggest a Function in SQL for the same. Thanks in advance, Geetha ...
Using Oracle 9.2i I need to get the offset of various dates from one timezones in relation to another timezone, previously I did this as such select (TO_DATE('10-Oct-2010 09:00:00','DD-Mon-YYYY HH24:Mi:SS') - TO_DATE(TO_CHAR(FROM_TZ(to_timestamp( '10-Oct-2010 09:00:00','DD-Mon-YYYY HH24:Mi:SS'), 'Australia/Victoria') ...
I have found the solution to this, but what in case one of the column is a subquery, how can i include it in group by, or do i need to include that in group by. I will paste the query here.. SELECT s.customerid, s.denomid, (SELECT su.quantity FROM stockupdations su WHERE s.customerid = su.custid AND ...
i have 3 tables enquiryDetail CourseMast and FeeDetail i have to show the information of student with coursefee and remaining and deposite fee i m using the query select Name,MobileNo,CourseName,CourseFees from [AsahEnquiry].[dbo].EnquiryDetail e,[AsahEnquiry].[dbo].CourseMast c where e.CourseId=c.CourseId AND Admitted='Yes' which ...
Hello, I would like to find the distinct CLOB values that can assume the column called CLOB_COLUMN (of type CLOB) contained in the table called COPIA. I have selected a PROCEDURAL WAY to solve this problem, but I would prefer to give a simple SELECT as the following: SELECT DISTINCT CLOB_COLUMN FROM TABLE avoiding the error "ORA-00932: ...
ruby 1.9.2 rails 3.0.0 ruby-oci 2.0.4 activerecord-oracle_enhanced-adapter 1.3.1 for example table named users(id:number(38,0), name) user = User.new user.name ='test' user.save! # is successful user.id # got nil user.name # got 'test' and in the database I have sequence named "USERS_SEQ" and worked well(nextval or currentval is r...
We're currently using ojdbc14.jar, should we be using ojdbc6.jar ? Update: Oracle 10g being used ...
Hi I want to update a table as followings update userloginstats set logouttime= sysdate where logouttime= null; But it did not update the columns with null values. what is wrong ...
I haven't used Oracle for a while so I'm a bit rusty. This is my table: create table calendar( username VARCHAR2(12), content VARCHAR2(100), dateContent DATE, type CHAR(3) CHECK (type IN ('PUB', 'PRV'))); But when I try to insert a value like this: insert into calendar (username, content, dateContent, type) values (chris, assignmen...
I have this in my spring config for quartz.... <prop key="org.quartz.jobStore.selectWithLockSQL">SELECT * FROM {0} LOCKS WITH (UPDLOCK ROWLOCK) WHERE LOCK_NAME = ?</prop>` That works fine for MS SQL Server, but I don't know what to...
Hi, Is there a way in SQL SERVER or ORACLE that can use the case statment to convert the Unit of Measure below? select UoM, sum(Quantity) as 'QTY' from mytable group by UoM UoM QTY LBS 2.4 LBS 2 LBS 0.233 LBS 0.97 OZS 1.8 GMS 1236 LBS 120.459 LBS 59.1 LBS 252.82 LBS 175.23 LBS 3.42 LBS 455.4 LBS 57.6 LBS 146.8 LBS 117.78 LBS 197.92 L...
Hi All, I have to generate some million update from some table, to update themselves. I had just recently learned about parallel(tablename,threads) which really improved the performance in PLSQL developer when I had run something like this: select /* + parallel(table1,100) parallel(table2,100) */ 'update table1 set id = 1 where ...
Let's say I have table data similar to the following: 123456 John Doe 1 Green 2001 234567 Jane Doe 1 Yellow 2001 234567 Jane Doe 2 Red 2001 345678 Jim Doe 1 Red 2001 What I am attempting to do is only isolate the records for Jane Doe based upon the fact that she has more than one row in this table. (More that one seque...
Question is following: I have an oracle trigger after row insert. From trigger, I want to call a php script and pass just inserted row fields as parameters. Insert is coming from very old legacy application with a very little chance of looking at the source code. Rows are inserted frequently, could be batches of ~1000 rows at a time or ...
I have the following SQL statement: SELECT CONNECT_BY_ROOT ANIMAL_ID "ORIGINAL_ANIMAL" , ANIMAL_ID, LINE_ID, SIRE_ANIMAL_ID, DAM_ANIMAL_ID, LEVEL -1 "LEVEL" FROM ANIMALS START WITH ANIMAL_ID IN( '2360000002558' ) CONNECT BY ((PRIOR SIRE_ANIMAL_ID = ANIMAL_ID and LEVEL < 5) OR (PRIOR DAM_ANIMAL_ID = ANIMAL_ID AND LEVEL < ...
I am using Visual Studio 2010, C#, Entity Framework 4 and Oracle 10g. I need to be able to return the result of a database function as a scalar property of an anonymous type. My Oracle schema has two tables, PARENT and CHILD, and a function FNC_ADD. I have created an entity model using the Visual Studio ADO.NET Entity Data Model templ...