SQL & PL/SQL :: Query Related To Date

Feb 20, 2012

i have a view in a schema of my database 'vw_get_noti_calendar_data' and i want to select some field but it shows an error 'invalid number' and i have observed it but no effect,so where is the error.

my code is---------

SELECT *
FROM aepuser.vw_get_noti_calendar_data
WHERE TO_DATE (TO_CHAR (event_start_date, 'mm/dd/yyyy'), 'dd/mm/yyyy')
BETWEEN TO_DATE (TO_CHAR ('2/28/2012', 'mm/dd/yyyy'), 'dd/mm/yyyy')
AND TO_DATE (TO_CHAR ('2/28/2012', 'mm/dd/yyyy'), 'dd/mm/yyyy')
AND srnum BETWEEN 5 * 1 + 1 AND 5 * 1 + 5;

View 11 Replies


ADVERTISEMENT

Scheduler :: DBMS JOB - Interval Related To Next Date?

Sep 17, 2012

I studied the documentation and many websites about DBMS_JOB but I am still confused.How do I schedule a procedure to run i.e. every sunday 10:00 AM ?This is what I tried:

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    (
      job        => X
  
[code]...

Is the Interval related to next_date ?

View 1 Replies View Related

SQL & PL/SQL :: Query Related To Getting Columns Name

Jan 20, 2012

is there possible to get those column names on which we have applied dml operations like insert, update,delete if yes,then how .

View 5 Replies View Related

Visual Studio :: Set Query Window Default Date Format To Full Date?

Oct 24, 2013

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 Related

SQL & PL/SQL :: Date Overlap And Merge Date Query

May 11, 2011

Lets say the contents of the table are like this -

key dte_start(number) dte_end(number)
1 20010101 20011231
1 20020101 20081231
1 20030601 20071231
1 20100101 20101231
2 20090101 20091231
2 20090401 20101231
2 20101231 20111231
2 20140101 20141231
3 20080101 20091231
3 20070101 20081231
3 20100101 20101231
3 20120101 20120630

I want the query to resolve overlapping dates as well as merge contiguous segment and leave non-contiguous segments as is. The final result of the query should be like this.

key dte_start(number) dte_end(number)
1 20010101 20081231
1 20100101 20101231
2 20090101 20111231
2 20140101 20141231
3 20070101 20101231
3 20120101 20120630

I tried using the lead over partition along with the standard overlap query and case logic to get the results but couldn't get it to work.

View 14 Replies View Related

PL/SQL :: Query First Date Before Payment Date

Nov 25, 2012

How to get the payment date less than the sch date?

example:

1) for 07/24/2012 sch date the nearest payment date is 07/27/2012.

sch date should be less than the payment date.

Sch dates 07/24/2012 and 07/26/2012 are less than payment date 07/27/2012 but we take the least one.

2) sch date 07/27/2012 is less than the payment date 08/08/2012 so 07/27/2012

Below is the scripts and output needed.

create table cust_sch_tbl
(
customer_NUMBER     number,
c_CODE     char(5),
sch_DATE date
);

[Code]...

Output needed:

cust   cust code payment date      sch date
4611     c1     7/27/2012      7/24/2012
4611     c1     8/8/2012 16:57      7/27/2012
4611     c1     10/19/2012 16:37 10/11/2012
4611     c1     11/15/2012      10/30/

View 6 Replies View Related

SQL & PL/SQL :: Index Related To Primary Key

Jul 12, 2012

What is the default key created when a primary key is created in a table ?

View 1 Replies View Related

Extract Data From Three Related Tables

Aug 29, 2008

Im having a problem with writing an appropriate query for a report in my web application. I need it to extract data from three related tables:

CAR(
PK CAR_ID INT NOT NULL,
TYPE VARCHAR NOT NULL)
REPAIR_CENTER(
PK REPAIR_CENTER_ID INT NOT NULL,
NAME VARCHAR NOT NULL)
[code]...

I need the report to display only available cars. Available cars must have these characteristics:

1. if the CAR_REPAIR table is empty, displays all entries from CAR table...
2. if car has multiple entries in the CAR_REPAIR table display only the latest DATE_RETURN if its lower than todays date (SYSDATE), otherwise don't display that car...
3. don't display cars that are in the CAR_REPAIR table and have DATE_RETURN value of NULL

View 3 Replies View Related

SQL View Showing Non-related Records?

Jun 27, 2011

I'm trying to get a list of values from a table, and I am just not seeing the answer -I have a table with 4 records (say A, B, C, and D), each record has a GUID to uniquely identify it.I have a 2nd table that holds the relationships (parent-child).

At this point, I have 2 rows:
A>B
A>C

So, A is a parent to B, and A is a parent to C (D is not related to anything at this point)

I would like to be able to go into the D record and see that it is NOT related to A (A would be the parent in this instance) - the trick however is I only want 1 instance of A to show up.attempted to use some inner/outer join qualifications and I just cannot seem to get the data to display properly.

View 1 Replies View Related

SQL & PL/SQL :: Retrieving Related Records As Groups

Mar 12, 2008

I have a table of N records with: Name SeqNo ID Col4 ... ColX

where Name and ID are non-unique, and SeqNo is a monotonic non-consecutive sequence 0 .. N that is unique within ID..I'd like to generate the following 'groups': For each record where SeqNo = 0, sorted by Name, create the 'group where ID is the same, ordered by SeqNo irrespective of the

values of any of the other columns. For instance, if the table contained:

NameSeqNoIDCol4 ... ColX
RPL2975...
TLM0444...
AAB2801...
AZZ0801...
ABA3444...
KTT4975...
ABA1801...
YHG0975...
DEF1444...

I'd like to generate:

NameSeqNoIDCol4 ... ColX
AZZ0801...
ABA1801...
AAB2801...
TLM0444...
DEF1444...
ABA3444...
YHG0975...
RPL2975...
KTT4975...

I got my desired results by brute-forcing via four sub-queries:

Sub-query 1 - Generate the sorted Names with SeqNo = 0
Sub-query 2 - Expand above with the additional columns,
maintaining original order
Sub-query 3 - For each of the records from sub-query 2,
generate the 'dependents' having the same ID
and SeqNo != 0
Sub-query 4 - Expand above with the additional columns,
maintaining original order of sub-query 1
Main query - Create UNION of 2 and 4, sorting by original
order and SeqNo

if there were not a simpler approach - after all, this must be a fairly common issue when generating BOMs.

View 4 Replies View Related

Query For Group By Date?

Apr 30, 2011

We have one oracle table, which has one column "Creation_Date" of the datatype date.

This column has data like
"14-Aug-2010 01:30:30"
"14-Aug-2010 03:30:30"
"15-Aug-2010 01:30:30"
"16-Aug-2010 01:30:30"
"16-Aug-2010 03:30:30"

We want to write SQL query which will group rows by date wise without considering time..I mean for above rows the date wise output should be

Count Date
------- -------
2 14-Aug-2010
1 15-Aug-2010
2 16-Aug-2010

View 2 Replies View Related

SQL & PL/SQL :: Query To Find MIN Date

Nov 5, 2010

building SQL query to get the result as shown below.

Create Table Temp

CREATE TABLE TEMP
(
CASEID NUMBER,
SATUS VARCHAR2(1 BYTE),
TRANS_DATE DATE
)
Insert data

[Code]...

I want to build a query which should give output as shown below. Basically i want to select those rows which having minimum trans_date for a given CASEID & Status.

OUTPUT:

CASEIDSATUSTRANS_DATE
100A11/02/2010
100B11/07/2010
100A11/12/2010
200A11/02/2010
200B11/07/2010

View 6 Replies View Related

SQL & PL/SQL :: Query Only Specific Date Only

May 6, 2010

query only specific date only. example: '06-MAY-2010'.

1.from the statement below, it will display out more than 06-mAY-2010.

2.if i want more the date from 03-MAY-2010, 04-MAY-2010 and 05-MAY-2010.

select * from PNG_ORA_SERVER_PERF where SERVER_NAME = 'MLYDESPINTF1' and DATE_TIME >= TO_DATE('06-MAY-2010','DD-MM-YYYY');

View 3 Replies View Related

PL/SQL :: Date Query - Time In /out

Apr 19, 2013

Create table time_in (
Emp_id varchar2(6),
at_date date,
time_in varchar2(6),
status varchar2(2)
)

[Code]....

-------------- required restul----------
emp_id    at_date    time_in    at_date    time_out
------    -------    -------    --------   --------
00009     16/03/2013  09:47      17/03/2013  09:45
00009     17/03/2013  09:48      17/03/2013  10:43
00009     18/03/2013  09:57     
00009     19/03/2013  09:38      19/03/2013  05:22
00009     20/03/2013  09:48      20/03/2013  05:24

status 01 = time in
status 02 = time out

View 13 Replies View Related

Replication :: Reorganize One Table That Related To Several Other Tables

May 15, 2009

I have to reorganize one table that related to several other tables. The reorg is too slow when it runs on this table. I would like to create one image of the table and synch it with the original one in real time. So when I run the reorg, I will use the image table that does not constrained by indexes and other objects. Once the reorg is done, I would like to rename the table. how could I do the replication in real time?

View 2 Replies View Related

SQL & PL/SQL :: How To Delete A Data Which Is Related To Many No Of Child Table

Apr 13, 2012

how to delete a data which is related to many no of child table and according to setnull and casecad by using subprogram?

View 6 Replies View Related

Server Administration :: Application Related Tablespaces

Oct 31, 2011

How to find out the tablespaces specific to application (i.e. not related to oracle like system, sysaux,temp,undo etc..)

View 1 Replies View Related

Server Administration :: Cannot Start Listener (/tmp Related)

May 9, 2012

OS: AIX 6.1 64bit
DB: Oracle 10gR2 (10.2.0.5.0)

What we did is..

1. Install patch 6613550 (./rootpre.sh)
2. Install 10.2.0.1 for AIX 64-bit
3. Install 10.2.0.5 Patch Set
4. Create Database
5. Created/Setup LISTENER and TNSNAMES (test connection successful)

After installation of database, everything went well until we tried to restart the server, what happened is that we cannot start the listener, the error was:

TNS-12555: TNS:permission denied
TNS-12560: TNS:protocol adapter error
TNS-00525: Insufficient privilege for operation
IBM/AIX RISC System/6000 Error: 1: Not owner

What i did to resolve this problem is to change the owner of /tmp to oracle, after that i was able to start the listener. ($lsnrctl start) The problem is that our sys admin said that the /tmp directory is being used by the OS and it's owner must be root. But if i return it to root, the listener will not start again. (After server restart)

BTW, our /tmp has enough space so we did not perform the following steps from the installation guidelines:

$TEMP=/directory
$TMPDIR=/directory
$export TEMP TMPDIR

I was thinking if that we must make a temp directory for oracle and set it in the profile, or change the owner to root again and perform this:

$chown -R oracle:oinstall /tmp
$chmod -R 777 /tmp

View 2 Replies View Related

Why A Certain Date Makes Query Faster

May 25, 2012

I have a query with a few tables in join, and I filter data with something like

where mytable.initial_date > sysdate-30

If I use any date, it takes about 5 seconds to run, but if I use

to_date('01010001','ddmmrrrr')

instead of any other dates, it takes just a few milliseconds. Now, it's just a curiosity, but why that date makes the query VERY fast? Does Oracle treat it in some special way? Maybe it knows every date is greatest than that date and doesn't consider the filter?

View 7 Replies View Related

Specify Month Start And End Date In Query?

Oct 27, 2008

I want a monthly report where the month wise sum of qty should be displayed in a row for last 12 months. I need to specify the month start date and end date in the query to pick the sum for the particular month. How can i do it in a SQL query?

SUM(decode(to_char(t1.trx_date,'mm/rrrr'),
to_char(add_months(SYSDATE,
-1),'mm/rrrr'),
nvl(t2.quantity_invoiced,
0))) AS qty_01
from t1,t2
where t1.col1=t2.col1

instead of sysdate i have to use start and end of month. Also i am using group by clause on some columns.

View 1 Replies View Related

SQL & PL/SQL :: Query To Get Timestamp From Only One Date Column?

Apr 10, 2013

I want to write a query to get the time stamp from only one date column,

I tried using a group by clause but getting error "not a group by exp."

Below is the query

SELECT ProdID,ProdRequestID, SUBSTR((max(EVENTTIMESTAMP) - min(EVENTTIMESTAMP)), 18,2)Execution_Time
FROM LOG_TIMESTAMPS where ProdID = 1680988889
group by ProdRequestID
ProdID||ProdRequestID ||EVENTTIMESTAMP

[code]....

In the above i am looking for a diference on ProdRequestId,

Output

ProdIDProdRequestIDEVENTTIMESTAMP
16809888892013-04-09T02-56-34.5025kqcxy03

View 24 Replies View Related

SQL & PL/SQL :: Query To Join Two Date Fields?

May 19, 2011

TABLE - 1

CIDDOB(DATE)
12312-MAR-63
58918-JAN-78
658927-DEC-43
46515-FEB-80

TABLE - 2

DIDDOB_INFO(VARCHAR2)
34425 Mar 1967
123 12 MAR 1963;25 FEB 1974;25 AUG 1978
46515 FEB 1980

I want to join the columns DOB from table -1 and DOB_INFO from table 2 but the datatype of DOB is DATE and DOB_INFO is VARCHAR. TO_DATE function is not working here.

View 13 Replies View Related

SQL & PL/SQL :: Analytical Query To Find First And Last Value In Date?

Jul 2, 2013

My table has two date columns EFF_DT which is the start date and TERM_DT is the end date. The EFF_DT of the next record should be the next date of the TERM_DT record.

My table looks like this.

Input Table:
-----------
CK_IDPI_IDEFF_DT TERM_DT
Mem1ABC1-Jan-1331-Mar-13
Mem1ABC1-Apr-1331-May-13
Mem1ABC1-Jun-1330-Sep-13
Mem1ABC15-Oct-1331-Dec-13
Mem1ABC1-Jan-1431-Mar-14
Mem1XYZ1-Apr-1430-Jun-14
Mem1XYZ1-Jul-1431-Dec-14

Expected Output:
----------------
CK_IDPI_IDEFF_DT TERM_DT
Mem1ABC1-Jan-1330-Sep-13
Mem1ABC15-Oct-1331-Mar-14
Mem1XYZ1-Apr-1431-Dec-14

In the fourth record, the effective date should be 1-Oct-13 which is the next date to the last TERM_DT 30-Sep-13.As the is the break in the date, the output should show 15-Oct-13 sa the second start date.

Note: Refer to the PI_ID columns, there is a break in the date for the sale PI_ID 'ABC'.

Here I am trying to generate a pseudo column, so that the table with the pseudo column looks like as shown below. and I can use first_value and LAST_value by partitioning on the pseudo column to get the desired output.

1) CNT_VAL is the pseudo column:
-----------------------------
CK_IDPI_IDEFF_DT TERM_DT CNT_VAL
Mem1ABC1-Jan-1331-Mar-131
Mem1ABC1-Apr-1331-May-131
Mem1ABC1-Jun-1330-Sep-131

[code].....

My Query :
----------

I not getting the desired output here as the value in pseudo column is 3.

select CK_ID, PI_ID,EFF_DT,TERM_DT,
(case
when case_CONT - LAG(case_CONT,1) over (ORDER BY EFF_DT) = 0 then to_char(case_CONT)
when case_CONT - LAG(case_CONT,1) over (ORDER BY EFF_DT) <> 0 then to_char(LAG(case_CONT,1) over (ORDER BY EFF_DT) + 1)
else to_char(nvl(case_CONT,0))

[code].....

Scripts:
--------------------
Create table lead_test(
CK_ID varchar2(10),
PI_IDvarchar2(10),
EFF_DTDate,
TERM_DT date);

[code].....

View 1 Replies View Related

SQL & PL/SQL :: Date Difference In YY MM Day HH:MI:SS Format Through Query

Feb 21, 2011

I just want to calculate difference between two dates in YY MM DD HH:MI:SS format through a SQL Query (not function).

Sample data is as follows-

to_timestamp('11-Feb-2008 12:23:00','DD-MON-YYYY HH24:MI:SS')
to_timestamp('2-Dec-2010 04:23:22','DD-MON-YYYY HH24:MI:SS')

or

how to calculate age in YY MM Day HH:MI:SS format. Suppose my DOB is '2-Feb-1988 11:10:15 AM'

View 4 Replies View Related

SQL & PL/SQL :: Replace Date Range In Query?

Aug 14, 2013

I am trying to replace a date range in a query. Don't understand why it's not working, below is the dummy query for the same:

/* v_Employee:= 'select emp_name from employee_table where Join_Year=~Replace1~ and Release_Month <=~Replace2~';
v_Replace1:=2013;
v_Replace2:=7;
pos := 1;
WHILE (INSTR(v_Employee, '~', 1, pos) <> 0) LOOP
IF (pos = 1) THEN

[code]....

Since actual code is like CLOB type, so I have not provided the same. Here after executing this query i found that v_Employee query is getting replaced by v_Replace1 at both of his position i.e "select emp_name from employee_table where Join_Year=2013 and Release_Month <=2013"

But my actual result should: "select emp_name from employee_table where Join_Year=2013 and Release_Month <=7"

View 5 Replies View Related

Performance Tuning :: PLSQL And Related Cursors Generated In V$sql

Jan 26, 2012

I wanted to know how to find the links between cursors that have been created in v$sql after compiling a PLSQL procedure.

For example, if i compile the following procedure :

create or replace
PROCEDURE PROCEDURE1
AS
BEGIN
insert into t values(1,1);
END PROCEDURE1;

, the 2 following cursors are created in v$sql :

select SQL_ID, SQL_TEXT from v$sql
where sql_id in ('71pj8t5nz1d80','2s567zb6684sh');

"SQL_ID""SQL_TEXT"
"2s567zb6684sh""BEGIN PROCEDURE1; END;"
"71pj8t5nz1d80""INSERT INTO T VALUES(1,1)"

Thus, i would like to know how to find that the cursor 71pj8t5nz1d80 is called/linked by the cursor 2s567zb6684sh.

This would be useful for interpreting some sql statistics in v$sqlstats in where such cursors appear and related sql statistics are accounted twice.

View 1 Replies View Related

Forms :: Select Table Name And Retrieve Its Related Data

May 29, 2012

I wanna have a form which has a list box which is including of all table names, by selecting the required table by user all column name and its related data should be retrieve.I have a cursor which give me all field names related to the table by I do not know how can I retrieve table data:

1. How to add item through block base on number of column which selected table has.

2. How to set value of table column in these mentioned column which added in the block.

3. And at the end retrieve the table data.

View 1 Replies View Related

Does Column V$SQL.CHILD_NUMBER Related On Number Of Current Cursor Only

Jan 11, 2013

Does the column V$SQL.CHILD_NUMBER related on number of current cursor only ? V$SQL lists statistics on shared SQL areas without the GROUP BY clause and contains one row for each child of the original SQL text enteredI see in V$SQL big numbers in V$SQL.CHILD_NUMBER ( HASH_VALUE and PLAN_ HASH_VALUE is in my quewry fixed ) , am I wrong that this column displayed not summary count of "objects"(in terminology of Steeve. URL....

View 0 Replies View Related

Application Express :: Related Pages And Components Report?

Jun 5, 2013

in APEX 3 i always had a report showing all linked pages and components...

The Report was located here: Home>Application Builder>Application ID>Application Reports>Page Components>Related Pages and Components I have now updated to APEX 4 and i can't find this report anymore.

View 3 Replies View Related

Query To Search Empty Room Between Date

Mar 13, 2009

i have project for hotel , i can't solve query to search empty room between date . i have table for room , when room record under reservation between date . then i want to found empty room between date, which is query to make do.

Table Room
Room_No
Type_Room
From_Date
To_Date
Status

Now i need to found empty room between date search room empty between

11/03/2009 - 13/03/2009

select Room_No from Room where From_Date Not between '11/03/2009' and '13/03/2009' i found hard to query.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved