How to select last 10 days rows only (from sysdate)
Error
1) SQL> select * from Test where to_date(start_ts, 'DD-MON-YYYY HH24:MI:SS') > to_date(sysdate, 'DD-MON-YYYY HH24:MI:SS')-10 ; (or)
2) SQL> select * from Test where to_date(start_ts, 'MM/DD/YYYY HH24:MI:SS') > to_date(sysdate, 'MM/DD/YYYY HH24:MI:SS')-10 ;
ORA-01858: a non-numeric character was found where a numeric was expected
i have an employee table where there is a column called join date. Now i have to select the employee according to the days. Means how many employee joined on Monday/Tuesday etc.
I have installed Oracle Database 11g.2 by database configuration assistant on windows XP as and adminstrator on my laptop(no connection to network),but when I want to create database I face this warning: error securing database control ,Datatbase control has been brought up in non-secure mode . to secure the database conntrol execute following command....(error is attached).
I am trying to find records in table A that don't have any corresponding records in table B
select a.ID,a.templatenames from cbe a where not exists (select 1 from cbe_child b where a.ID=b.cbe_id)
Both tables have about 100 mil rows. And there is no Parent/Child relationship here, A.ID is a PK column.how can i write this select to perform better.
I would like to know how to increase the result set of a 'Select' statement? I did a 'Select' that should have returned 36,000 rows and got only 5000 rows. What access level do I need to change this and what do I need to change? I am trying to do a migration of data from a delimited file to a table in Oracle. Yet, the data has to be filtered out prior to loading to the table?
SQLSELECT MIN(major_zipcode) FROM TEMP WHERE MAJOR_CITIES IN (select distinct major_cities from temp);
My table has the follwoing 3 columns (in addition to others)
Col Name = active ; type=number ; values=1 (true) or 0 (false) col name start_date ; type=date; format=dd-mmm-yy col name end_date ; type=date; format=dd-mmm-yy
I need to select all rows where all active=1, start_date=<today and end_date=>today
my sql is: SELECT id, start_date, end_date FROM offers WHERE (active='1' AND start_date<='14-SEP-09' AND end_date>='14-SEP-09');
However the results are not right. Example, the first row returned is: Offer5000312 01-JAN-09 11-DEC-08
This is not correct. Due to the end_date this row should not be part of the results.
There are two tables like I posted below.I want a SQL query which selects all the rows from TABLE A which are not present in TABLE B. Also the select statement should pick all those rows which has updated value of COL_A2 in TABLE A.
TABLE A COL_A1COL_A2
AAAMOBILE BBBTABLET CCCDESKTOP DDDUNKNOWN
TABLE B COL_B1COL_B2
AAAMOBILE BBBUNKNOWN CCCDESKTOP
The select statement should return following rows from TABLE A
I have a column COL1 in table TAB1 which is varchar2. I want select only rows which has number and not alphanumeric value? I don't want to use regexp for this since
FOOTBALL PLAYERS < ID, NAME, ATTRIBUTE> 100-JIM-TALL 101-BOB-STRONG 102-MARK-SMART ...etc
I want to form a query that regardless of the total returned records, I will be able to specify how many of each "kind" of players I want returned. There are several good reasons that it has to be one query and not many.
I am trying to come up with a sql select statement that provides all rows for employees with 2 or more cities.
with sample_table as ( select 'John' name,'city' ValueType,'Toronto' Value from dual union all select 'John' name,'city' ValueType,'Vancouver' Value from dual union all select 'Susan' name,'city' ValueType,'Toronto' Value from dual union all select 'Susan' name,'city' ValueType,'Seattle' Value from dual union all select 'Susan' name,'age' ValueType,30 Value from dual union all select 'Susan' name,'city' ValueType,'Atlanta' Value from dual union all
[Code]...
NAME VALUETYPE VALUE ----------- ------------- ------------ John City Toronto John City Vancouver Susan City Toronto Susan City Seattle Susan Age 30 Susan City Atlanta David City Chicago David age 35 David Status married David City Dallas
The above code is just to describe the sample table and the desired result set. Please note that Mary is not on the result set since she has no city assigned to her. Also Julia is not on the result set since she only has one city assigned to her. The others are there because they had at least 2 cities assigned to them.
I need the sql syntax that would return this result set.
I have a select statement that selects all columns from the join of 3 oracle views. I would like to change it to select only the distinct rows, not sure how to code this. Here is my sql statement:
select * from myschema.view_1 acct Left JOIN myschema.view_2 freq
I want to create a SELECT, that shall give back only a special amount of rows, depending on the sum of one of the selected fields.
At first a code sample of the complete selection:
SELECT DISTINCT mnr, ktxt, (SELECT Sum(meng_4)FROM reldb d1 WHERE d1.mnr=d.mnr)qty FROM reldb d WHERE mnr IN (SELECT mnr FROM relac WHERE Lower(rlnr) NOT LIKE 'platte geprÃĪgt%') AND saext='M' ORDER BY qty DESC,ktxt;
This selection produces some lines of output (in my case i.e. like 300). What I want to see is only that much lines that the condition 'sum of all items listed below meng_4<=sum of all items meng_4 of the whole selection * 0.9' is fulfilled.
So, if the whole selection produces a total of 10000 as sum for all items meng_4, I want to see only that amount of rows that sums a total of at least 9000 for all items meng_4. I hope, this specification is exactly enough to understand my intent.
I am trying to insert 100K rows, I have written this proc using cursor. But it is saying anonymous block completed, and no rows are inserted. If I just run the select it returns the rows.Could if just insert into select would be fine or should I use cursor.
CREATE OR replace PROCEDURE Insert_data (l_from_date IN VARCHAR2, l_to_date IN VARCHAR2) IS lc_err_msg VARCHAR2 (2000); ln_count NUMBER := 0; CURSOR ins_d IS SELECT a.col1 AS url, b.col1 AS ref_url, COUNT (*) AS total_views [code]....
I need to view the rows of the result of a select query in table format in GUI application.I have used XMLELEMENT to somewhat match the scenario to display as ','(comma) separate values as b belwo
