I am trying to run the following queries in my database.
Q-1)select * from item where trunc(approval_date)='21-MAY-13'
When Q-1 is executed it returned row that has approval_date of 21-MAY-13 from database.
Q-2)select * from item where approval_date=to_date('21-MAY-13')
When Q-2 is executed it says no rows selected.
difference or if any links related to this.
I am trying to retrieve all rows with the date of May 23rd, 2008. Our data is stored with the date and time.
How do I format the where clause so that the time is taken off? I am still a newbie with oracle and what I have is this:
Select Distinct Table1.*, Table2.*
from Table1, Table2
where Table1.file_name = Table2.file_name
and Table1.downloaded_date = Table2.date_loaded
and downloaded_date = '23/May/2008'
Of course, it's the last line not working because I don't know how to format it and no rows are retrieved when there are definately May 23rd rows stored in there.
I have homework due, and I'm so stuck: This worked when I first ran it, and now all 6 queries return the same error No Rows selected. I did have to add a column to a table, and insert data for that column before this worked.
> --------------------------
> --REPORT 1
> ---------------------------
SELECT sur.channo,
supp.supplierid,
sur.packtype,
program_name,
ratingcode,
[code]....
Can we select table's rows of 5 to 15, like
select * from emp
where rowid <=5;
It is through an error when i used the below method.
select * from emp
where rowid >=5 and rowid <=15;
I'm attempting to write a plsql for finding missing archived logs for streams.
requirement is to run a select statement and print
1. 'NOT FOUND' if name column is null
2. '<name of the files>' if rows are returned
3. 'NOT FOUND' if no rows are selected. (here is were i'm having trouble)
code i developed so for:
for cr in (select decode(name, NULL, 'NOT FOUND', name) from v$archived_log where deleted='YES'
and status!='A')
loop
if (cr.name = 'NOT FOUND')
[code]...
Can I apply Referential Integrity to only selected rows of a particular column? This is the reference key to the primary key to another table. But, the issue is, this reference column is not having mandatory data for all the rows. So, whenever this is null, I dont want it to be referred by parent table.
no, and it makes no sense to do so either
We have an appointment form in our HIS, where patients take telephonic appointment. That time they may not know their user id given by hospital. So, it remains blank & name is entered manually. But if the user-id is entered, it must fetch patient name from the master.
The candidate key must be unique within its domain.The candidate key can not hold NULL values.
ALTER TABLE HLTHCHKAPPOINTHD ADD (
CONSTRAINT SYS_C007145
FOREIGN KEY (N_PATIENTMR_ID)
REFERENCES MRREGISTRTNHD (N_PATIENTMR_ID));
MRREGISTRTNHD is a patient master & has a primary key named N_PATIENTMR_ID.
HLTHCHKAPPOINTHD is a appointment table & has a foreign key named N_PATIENTMR_ID which references N_PATIENTMR_ID of MRREGISTRTNHD.
Also, N_PATIENTMR_ID of HLTHCHKAPPOINTHD is not a unique key & it can contain null values also. I want to define constraint or any other method such that only the not null values are referenced to the master i.e. it should validate in the master. And null values should skip this reference.
Now, what happens is due to this constraint, when I'm trying to edit & update the rows having null value in N_PATIENTMR_ID, it gives the following error.
ORA-02291: Integrity constraint (SYS_C007145) violated - parent key not found
So, can I give some condition in the above constraint saying, apply this constraint to table HLTHCHKAPPOINTHD only having the not-null values in N_PATIENTMR_ID coulmn?
I have table T with 50,000 rows
create table T
(student_id number,
class_id number,
quiz_id number,
marks number)
some sample rows like
INSERT INTO T VALUES (1,1, 1, 50);
INSERT INTO T VALUES (2,2, 2, 40);
INSERT INTO T VALUES (3,1, 3, 34);
INSERT INTO T VALUES (1,1, 4, 10);
INSERT INTO T VALUES (1,1, 5, 30);
INSERT INTO T VALUES (1,1, 6, 29);
INSERT INTO T VALUES (3,2, 7, 34);
INSERT INTO T VALUES (3,2, 8, 33);
INSERT INTO T VALUES (3,2, 9, 56);
INSERT INTO T VALUES (1,1, 7, 90);
INSERT INTO T VALUES (2,2, 8, 0,);
INSERT INTO T VALUES (1,1, 8, 80);
INSERT INTO T VALUES (2,2, 8, 65);
INSERT INTO T VALUES (1,1, 9, 34);
INSERT INTO T VALUES (2,2, 9, 11);
each student belongs to one class_id. each student participates in many quizes. each quiz has its unique id. each student can appear once in a quiz_id
I am doing the below analysis and query:
1. with below query I am finding which student_id had most marks in any 3 successive quizes (see the 3-1 part below) in the query..
SELECT QUIZ_ID,
STUDENT_ID,
SUM (MARKS)
[Code]....
SQL> /
QUIZ_ID STUDENT_ID CONSECMARKS
---------- ---------- -----------
7 1 170
6 1 166
8 1 129
5 1 106
8 3 89
8 2 76
3 3 68
7 3 67
8 2 65
1 1 60
9 3 56
9 1 49
2 2 40
4 1 40
9 2 11
15 rows selected.
With above query, I can play around and find for any 'n' number of consecutive quizes, like marks in 2 consecutives quizes, 3, 4 and so on but for each 'n' value I've to run a seperate query mentioning (2-1) or (3-1) or (4-1) and so on..
since my table is big and there are about 400 quizes so what I want to find out is for each 'n' consecutive quiz (from 1 to 400) which student had most marks for each consecutie 'n' quiz. Like in 1 (consecutive) quiz which student had the highest marks and then 2 conseuctive quiz who had most marks and then in 3 consecutive quiz who had most marks and so on till 400 consecutive quiz who had most marks... rather than running query for each 'n' value seperately i want a single query that can give me a summary of most marks in each n consecutive quizes...
my sample output is:
Nth consecutive quiz student_id sum(marks)
1 1 90
2 1 170
3 1 246
4
.
.
.
100
.
.
200
.
.
300
.
400 ? ?
Is this possible to get the above output from one single query? If there are two or more students with equal most marks for any 'n' conseutive quizes then both should come in the summary.
Am using the SQL*PLus tool via a command line to execute some simple "select" scripts. Scripts work fine in what they produce - except for something that is missing occasionally.
For SOME scripts, I'm getting no "xx Rows Selected" type messages at the bottom and I can't figure out why. When no data si returned in some (different) scripts I do get a "no rows selected" message. Am looking for this message in all outputs as an "end of report"-style marker.
No complicated SQL logic and I have the following standard settings applied into each script.
set linesize 500
set pagesize 50000
set tab off
set wrap off
set colsep '|'
I have 20 years of coding experience so it's not a basic error....albeit I'm far more familiar with MS SQL Server product set as well as the PL/SQL Developer client front ends for Oracle.
I am having a requirement like below,
Scene 1:
If duplicate records found for SSN,BWE with SAME DATE_CREATED than take the record with HIGHEST DATE_MODIFIED.
Table Structure:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 02-JAN-2009 03-JAN-2014
123 01-JAN-2008 02-JAN-2009 03-JAN-2013
Output needed:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 02-JAN-2009 03-JAN-2014
Scene 2:
If duplicate records found for SSN,BWE with different DATE_CREATED than take the record with HIGHEST DATE_CREATED.
Table Structure:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 02-JAN-2009 03-JAN-2014
123 01-JAN-2008 04-JAN-2009 03-JAN-2013
Output needed:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 04-JAN-2009 03-JAN-2013
How to achieve this requirement. My source data has 25 million of records like this.
I have a I/R report with a checkbox is column 1 of each row using:
HTMLDB_ITEM.CHECKBOX
(1,R.RESEARCH_ID,
decode(:Pxx_SELECT_ALL_ROWS,'Y','CHECKED',
decode(instr(nvl(:Pxx_SELECTED_IDS,0),R.RESEARCH_ID),0,NULL,'CHECKED'))
)
AS select_flag
I want to use Javscript or a Dynamic Action to "count" how many selected rows there are, when a UPDATE button is pressed. Right now, the UPDATE button "submits" the page, then a page item computation runs the following code to get a count:
DECLARE
v_cnt number(9);
BEGIN
v_cnt := 0;
FOR i in 1..htmldb_application.g_f01.count
LOOP
v_cnt := v_cnt + 1;
END LOOP;
return v_cnt;
This works... but I dont want to submit (and rerun the report) just to get this count.
I have copied this loop into a Dynamic Action using the Click action of the UPDATE button, but it doesnt seem to recognize htmldb_application.g_f01.count .. so my value returns as 0.
I have 3 types list items:
1) :1 Day -> 31
2) Month: 1 -> 12
3) Year :1990 -> 2013
I want to check that the user has selected a valid date .. if not an error message will be displayed, for example: 31-02-2013: will be rejected
i have a requirement to create a query which gets the same month from the previous year of the selected date .
Example : i select SEP-2011 ( as sysdate )
it will return the SEP-2010 as well .
here is the code which works fine with sysdate, dual :
SELECT
TO_CHAR(ADD_MONTHS(SYSDATE,-12),'MON-YYYY') TMLY, TO_CHAR(SYSDATE,'MON-YYYY') TMTY FROM DUAL;
in my schema table Ive got a filed named PERIOD_NAME (varchar2) , which holds date values in ( Mon-YYYY ) format (e.g Sep-2011)
am unable to apply the above code on it , i guess its the data type pf the field .
When I run a query form the the Query Window in Visuial Studios 2012 all the date fields truncated to 'mm/dd/yyyy', but i need the full date returned. I am able to get full date from TO_char(MyDateField, 'yyyy-mm-dd hh24:mi:ss'), but if I do TO_DATE(MyDateField, 'yyyy-mm-dd hh24:mi:ss') it only returns 'mm/dd/yyyy'. I'm sure this is a simple setting in Visual studios but I cant find it to save my life. Is there there a way to have the full date returned by default?
View 0 Replies View Relatedcreate table call(id number(10) primary key,mobile_no number(10), other_no number(10), call_type varchar2(10),call_date_time date, duration number(10));
insert into call values(1,9818764535,9899875643,'IN','24-APR-13 02:10:43',10);
insert into call values(1,9818764535,9898324234,'IN','24-APR-13 05:06:78',10);
insert into call values(1,9818764535,9215468734,'IN','24-APR-13 15:06:78',10);
insert into call values(1,9818764535,9899875643,'OUT','25-APR-13 01:06:78',10);
insert into call values(1,9899875643,9899875643,,'OUT','25-APR-13 22:06:78',10);
Query : need to find first and last call of '9818764535' mobile number and of call_date between '24-apr-13' and '25-apr-13';
Result :
date ,mobile_no , other_no, call_type, duration
24/apr/13 , 9818764535,9899875643,'IN',10
24/apr/13 ,9818764535,9215468734,'IN',10
[Code]....
A field named xxx_date is a text item which we have to enter manually so as to update a record in that particular date. This is a mandatory field without which we cannot continue the data entry..
I am getting this error while trying to update the record
FRM-40509 :Oracle error :unable to update record
I have kept the enabled = yes
required=no
data type=Date.. in the property pallet
I want to reset my date to this format: 12/31/2012 11:59:59 PM - see code below:
DECLARE
v_latest_close DATE;
BEGIN
v_latest_close := TO_DATE ('12/31/2012 23:59:59 ','MM/DD/YYYY HH24:MI:SS');
DBMS_OUTPUT.PUT_LINE('The new date format is : '|| v_latest_close);
END;
the code above displays only : 12/31/2012 instead of 12/31/2012 11:59:59 PM
In one of the query used like below is it correct or any need to be correct for date format? currently it's returning two tyoe of sets 1.NUll 2.01-JAN-00
SELECT withdrawn_date
FROM
csd where NVL(csd.withdrawn_date,'01-JAN-1900') = '01-JAN-1900';
How do I format a date which is this format to 2/18/2012 to 18/2/2012 and still keep the field as Date.
View 3 Replies View Relatedi see my dates in Oracle 11g like this: 2012-December-30 (in all my tables)
how to configure Oracle that i can see it like this: 30/12/2012
I have a simple question, hope it has a simple answer. I changed the default date format for a SCHEMA using a TRIGGER and AFTER LOGON, so it set the NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'. It does work fine, however, the problem is when I connect using a client with JDBC driver. When using SQL PLUS I get the date in the format specified above YYYY-MM-DD HH24:MI:SS, however, when using a client (Aqua Data 6.5.8 I know it is kinda old) and SQuirrel SQL 3.4.0 I always get the date in the YYYY-MM-DD format. I started to think that the issue is with the JDBC because it works fine when I connect with SQLPLUS.
View 5 Replies View RelatedHow can I check if date is in correct format ? I have In parameter for function. It is date. Example:
2011-11-01
How can I check this format ?
iam having a table A where column ABC is a varchar field with varchar2(50).
currently data stored is like this.
02-27-2009 11:01:33
02-27-2009 11:01:46
03-06-2009 09:07:18
now i want UTC Date time should be in format 2010-02-24T17:08:09Z. in the above format.
I want to change the date format at database level in 10g express edition.
When i try to run the command,
ALTER SYSTEM SET NLS_DATE_FORMAT='DD/MM/YYYY',
it throws error like specified initialisation parametr is not modifiabale with this option.
what are the other ways to chnage the database date format to the desired one.?
follwing is my structure and data in table. COLA has datatype varchar2.data in table is date..... now i want to convert all the dates in 'yyyymmdd' format.
IS it possible?
SQL> select * from taba;
COLA
---------------
1944-02-10
1982-07-20
08/24/1974
03/14/1957
22-Nov-79
While am working with Decode just found something, why it is returning like this.
I Ran this query where i have used all the date format as DD/MM/YYYY
SELECT DECODE(TO_DATE('25/04/2001','DD/MM/YYYY') ,
TO_DATE('01/01/1900','DD/MM/YYYY'), NULL ,
TO_DATE('25/04/2001','DD/MM/YYYY')) TEST
FROM DUAL
Quote:
TEST
-------------
25-APR-01
But the resule i got in DD-MON-YY
i tried searching this forum with searching strings Decode date format.. or similer i found nothing.
I have below data
SQL> desc IMEI
Name Null? Type
----------------------------------------- -------- ----------------------------
MSISDN NOT NULL VARCHAR2(20)
IMEI NOT NULL VARCHAR2(16)
DATE_MOD NUMBER(13)
IMSI VARCHAR2(18)
ICCID VARCHAR2(20)
T_PROF RAW(20)
EXTRA_DATA VARCHAR2(100)
SQL> select DATE_MOD from IMEI;
DATE_MOD
----------
1.2199E+12
1.2348E+12
1.2278E+12
1.2263E+12
1.2278E+12
1.2378E+12
1.2414E+12
How can I change date_mod to normal date format like year/month/day hour:minutes:second ?
qry:= 'to_date(debtodt, 'dd-mon-yy')< sysdate';
or
qry:= 'debtodt < to_char(sysdate, 'dd-mm-yyyy');
or
qry:= 'to_date(debtodt, 'dd-mon-yy')< to_date(sysdate, 'dd-mm-yy')'
all these are showing error in oracle form.
in database debtodt is in char format , calculating from-- TO_CHAR(add_months(b.FRMDT,b.period)-1,'dd-mm-yyyy')
and also in oracle form it is char,thats why i am changing it either debtodt into date format or sysdate into char format. but it is showing error as: encountered the symbol "DD" when expecting one of these.................
the same query
select * from debar_reg_vu where to_date(debtodt, 'dd-mm-yyyy')<sysdate;
running correctly in database server.
I am getting daily basis data from third party in excel format which i am converting into CSV format and then uploading into oracle tables using External tables.Now problem is that every time i getting the dates in diff format i.e. sometimes dd-mon-yyyy , dd/mm/yyyy etc.
Now every time i have to open my code and change it there ...to make it as oracle date format. IS there way i can find out format of date and based on format i can do operations with getting errors every time.
I am storing the TP(Excel date) date into varchar columns only and then varchar2 column value i m inserting/updating into date format using to_Date ().
I'm trying to output 1/31/06 into January 31, 2006.Here is what I have so far
declare
v_dt DATE;
v_tx VARCHAR2(2000);
begin
[code]...
But it still won't display month as January.
CREATE OR REPLACE VIEW V_CATALOGUER_REPORT
(COUNT, EVENT_USER, TO_DATE, SHORT_NAME)
AS
SELECT COUNT (DISTINCT A.PART_REF),A.EVENT_USER, TO_DATE(A.EVENT_DATE, 'DD-MON-YY'),B.SHORT_NAME FROM J_SUPPLY_CHAIN_HIST A, J_SOURCE B
[code]...
i created one view,i link two tables ,i can see the correct data in this view,but the result from front end application is not correct.i cant see the dates are correct.
