SQL & PL/SQL :: Parameter That Exclude Some Dates

Sep 22, 2011

Any SQL Query

1) Attendance and All type of Leave including Week rest.
2) Parameter that exclude some Dates.

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Conversion Of Dates Parameter Into Columns

Apr 27, 2012

I have a query in which i am using two parameter start date and end date. Both are of date type. And i want these parameter to treat like a column

FOR e.g.

stdate :1/1/2012
endate :30/3/2012

I want to display report like below:

January February march

View 25 Replies View Related

Forms :: Data Between Dates But Parameter Not Working And Showing All?

May 4, 2011

i am seting runtime where clause on Forms as follow:

set_block_property('FORMC', onetime_where,'where to_char(INVOICE_DATE,'||'DD-MON-YYYY'||') >='||'to_date('||:search.inv_frm||','||'DD-MON-YYYY'||')'||
' and to_char(INVOICE_DATE,'||'DD-MON-YYYY'||') <' || 'to_date('||:search.inv_to||','||'DD-MON-YYYY'||')');

:search.inv_from is from date
:search.inv_to is up to date

i wann data between this dates but date parameter is not working it showing all data...... where synatx get wrong..

View 11 Replies View Related

Finding Individual Dates Having Dates Plus 2 Days

Jan 26, 2011

I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.

Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.

If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.

seq date
------------------------------
101.01.10

205.01.10
306.01.10

410.01.10
512.01.10

613.01.10
714.01.10
815.01.10

916.01.10
1018.01.10

1119.01.10
1220.01.10
1321.01.10

1423.01.10

1526.01.10
1627.01.10

1729.01.10
1831.01.10

The result should be (Don't use Pl/Sql)

seq date
------------------------------
101.01.10
205.01.10
310.01.10
413.01.10
516.01.10
619.01.10
723.01.10
826.01.10
929.01.10

After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).

I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.

View 2 Replies View Related

SQL & PL/SQL :: Split A Date Into New Dates According To Black Out Dates?

Mar 10, 2011

Split a date into new dates according to black out dates!

Here is my tables:

CREATE TABLE travel
(
start_date,
end_date
)
AS
SELECT DATE '0000-01-01', DATE '9999-12-31' FROM DUAL;

[code]....

I have lets say a "travel date" and black out dates. I will split the travel date into pieces according to the black out dates.

Note: Travel Date can be between 0000-01-01 - 9999 12 31

Sample:

Travel Date:

Travel | START DATE | END DATE
T | 2011 01 04 | 2011 12 11

Black Out Dates:

BO | START DATE | END DATE
A | 2010 11 01 | 2011 02 11
B | 2011 01 20 | 2011 02 15
C | 2011 03 13 | 2011 04 10
D | 2011 03 20 | 2011 06 29

Excepted Result:

New Travel | START DATE | END DATE
X1 | 2011 02 16 | 2011 03 12
X2 | 2011 06 30 | 2011 12 11

Visually:

Travel Date : -----[--------------------------]--

A : --[------]-------------------------
B : ------[---]------------------------
C : --------------[---]----------------
D : ----------------[------]-----------

Result :

X1 : -----------[--]--------------------
X2 : -----------------------[--------]--

Sample 2:

Travel Date : -[--------------------------------]--

BO Date A : ----[------]-------------------------
BO Date B : -------------------------[---]-------
BO Date C : ----------------[---]----------------
BO Date D : ------------------[------]-----------

Result X1 : -[-]-------------------------------
Result X2 : -----------[--]--------------------
Result X3 : -----------------------------[--]--

How can I do it using PL SQL ?

View 5 Replies View Related

SQL & PL/SQL :: Exclude Records

Sep 19, 2011

I have a table zTEST with several products and ID's. Now I want to exclude all products which are in table zTEST_EXCL and have ID's up to ID+2. The Result is then in table zTEST_RESULT.

What is the smartest way to do this?

create table zTEST
( PRODUCT number,
ID number);
insert into zTEST (PRODUCT, ID) values ( 1 , 1 );
insert into zTEST (PRODUCT, ID) values ( 1 , 2 );
insert into zTEST (PRODUCT, ID) values ( 1 , 3 );
insert into zTEST (PRODUCT, ID) values ( 1 , 4 );

[Code]...

View 7 Replies View Related

SQL & PL/SQL :: How To Exclude Value From Random Generating Function

Jun 25, 2011

I am in urgent need of Generating Random numbers for one of the application .The number once generated is getting stored in table

For this purpose i am using Oracle In-Built function

Quote:Select round(dbms_random.value(1,30)) into a from dual;

Problem is :How to eliminate those numbers to generate which are already stored in Table.

View 6 Replies View Related

SQL & PL/SQL :: Exclude Rows While Inserting Into A Table

Jul 12, 2011

I have an insert statement like below.

insert into emp (select empno,ename,sal);

Here I need to exclude the rows having sal<0 from the SELECT query and insert those into some other table simultaneously.

View 4 Replies View Related

SQL & PL/SQL :: Include / Exclude In One Query Statement

Aug 31, 2011

how to write this query using all three tables in one query statement.

Result:

Input value of "GM" would return "GM@EMAIL.COM"

Input value of "GM" and "GMC" would return "GMC@EMAIL.COM"

If "GM" is supplied, then only return the "GM@EMAIL.COM" email.

If "GM" and "GMC" is supplied, then only return the "GMC@EMAIL.COM" email

Test Case : GM would return "GM@EMAIL.COM" email

Test Case : GM and GMC would return "GMC@EMAIL.COM" email

select email
from email a,
company b,
sub_company c
where (company = "GM" but no match found in sub_company)
(company = "GM" and sub_company_name = "GMC" match is found in sub_company)

[code]....

View 11 Replies View Related

Server Utilities :: How To Exclude Table

Feb 6, 2012

I tried to export a schema excluding some table, but expdp exit with this error:

ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00936: missing expression

The command that I use is:

expdp system/password@ORADB directory=EXPORT_ORA_DIR schemas=maxdb logfile=maxdb.log
dumpfile=maxdb.dump EXCLUDE=TABLE:"IN ('max_table_1','max_table_2')";

Where I made a mistake?

View 2 Replies View Related

Exclude Several Tables With Specific Prefixes

Jun 5, 2013

I need to exclude several tables with specific prefixes, all the tables where names do not starts with 'A_' or 'B_' or 'C_'I tried this:

EXCLUDE=TABLE:"NOT LIKE 'A!_%' ESCAPE '!' and NOT LIKE 'B!_%' ESCAPE '!' and NOT LIKE 'C!_%' ESCAPE '!'"
I have this errors:
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00936: missing expression
ORA-06512: at "SYS.KUPM$MCP", line 3174

View 1 Replies View Related

Reports & Discoverer :: Exclude 1 Group In Report?

Nov 29, 2010

I want to show all the data except 1 organization, Report is grouped on organization.

View 2 Replies View Related

How To Exclude Externally Authenticated Users In Sql Statements

Sep 13, 2012

I want to exclude externally authenticated users from the following sql statement. i give the following command but still i get the ops$ schema.

SELECT DISTINCT GRANTEE FROM dba_role_privs WHERE GRANTED_ROLE LIKE '%DBA%' AND GRANTEE NOT IN ('SYS', 'SYSTEM','OPS$%');

GRANTEE
------------------------------
OPS$ORAB
OPS$BPRA

View 8 Replies View Related

PL/SQL :: Exclude Duplicate Values On SQL Where Clause Statement?

Jul 15, 2013

Are some posibilities to exclude duplicate values do not using sql aggregate functions in main select statement?  Priview SQL statement

SELECT * FROM
(
select id,hin_id,name,code,valid_date_from,valid_date_to
from diaries
)

[Code]....

 In this case i got duplicate of entry TT2 id 50513  In main select statement cant use agregate functions are even posible to exclude this value from result modifying only the QLRST WHERE clause (TRUNC need to be here)

View 5 Replies View Related

Query To Exclude All Rows Based On Single Row Filter?

Jun 10, 2011

I'm using Oracle 10g.

Question: How can I write query to return just ID only if all the codes for that ID end in 6.

If I use
SQLselect id from table_a where code like '%6'
then I also get ID=1, which I don't want.

TABLE_A
ID code
=======
1 100
1 106
2 206
3 306
3 336
4 400

Desired Result
ID
==
2
3

View 1 Replies View Related

Backup & Recovery :: Check The DB Size Exclude Particular Table A?

Aug 3, 2012

I am trying to calculate the compressed size of backup of my database by excluding table space ( and all the tables in that table space).

I know how to calculate the size of particular user DB size.

select sum(bytes/1024/1024)"size" from dba_segments where owner='ABC';

View 7 Replies View Related

Server Utilities :: ORA-39071 / Value For Exclude Badly Formed

Sep 1, 2011

Im trying to use IMPDP, this is my EXCLUDE:

Exclude=INDEX,STATISTICS,SCHEMA:"IN ('AWTEST','AWPROD','DBSNMP','ORCACLE_OCM','MGMT_VIEW',
'OUTLN','PAFO','PERFSTAT','RMAN','SYS','SYSTEM','SYSMAN','TSMSYS','WMSYS')

But I get this:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00920: invalid relational operator

How do I write a correct EXCLUDE?

View 3 Replies View Related

Export/Import/SQL Loader :: How To Exclude All Tables Of A Certain Type

Aug 15, 2013

How can I exclude during the import (with impdp) all tables of a certain type, es. statistics?I tried with .... STATISTICS=NONE, but this command seem ignored during the import,

View 6 Replies View Related

SQL & PL/SQL :: Exclude Single Schema From Autostats Gathering Feature In 11g

Jul 5, 2011

I need to exclude a single schema from the autostats gathering feature in 11g. The tables in this schema are analyzed at the appropriate time via the application code. The autostats gathering job sometimes kicks in at a time in which the tables are getting updated or loaded which can skew explain plans during the updates/inserts.

I've searched through the oracle documentation and cannot find a way to simply "exclude" the schema without locking it. I see it is possible to disable the autostats at the entire db level but not at the schema level.

View 5 Replies View Related

Select All Dates Between Two Dates

Oct 22, 2003

How can I select all of the dates between two dates? For example, given the start date 12/2/2003 and the end date 12/5/2003 I want to return:

12/2/2003
12/3/2003
12/4/2003
12/5/2003

Is there a built in function for this? Is there a way for a function to return multiple rows? It has to be a function because I need to use it within other SQL statements.

View 14 Replies View Related

Server Utilities :: Using Expdp To Exclude The Stream Objects While Doing Import?

Feb 1, 2012

I am having one prod and one devl with prod having stream setup.

I have to refresh devl with prod , but if i will go by full expdp then db_links also get imported into the devl and may cause problem in devl.

Is there any other way using expdp to exclude the stream objects while doing import.

View 1 Replies View Related

SQL & PL/SQL :: Syntax To Substring Only English Character And Exclude Sanskrit Characters

Nov 6, 2010

i have a column full name which has the name of customer in both English and Sanskrit alphabets. I need to susbtring only the English name and exclude the Sanskrit name .

View 2 Replies View Related

Server Utilities :: Exclude View In IMPDP Using Network Link Giving Error

Jan 14, 2011

I am using below command to import a schema using network link. Command is :

impdp system directory = IMP_DIR schemas = XYZ network_link = PQR remap_schema = XYZ:XYZ exclude=view: "= 'XYZ.VW_ACCEPTDETAILS'"

This command is giving below error

LRM-00116: syntax error at 'view:' following '='

When I have tried Like in place of '=' sign i.e. EXCLUDE = VIEW:"LIKE '%VW_ACCEPTDETAILS%'" , it gives me below error:

UDI-00014: invalid value for parameter, 'exclude'

View 4 Replies View Related

Forms :: Setup Form And Database Table For Time Entry Only (exclude Date)?

Feb 19, 2010

I have a form that the user has to enter a time of an event.

The time entered will most likely be in the past and NOT the current time.

What is the best way to set up a form and a database table for time entry only (exclude the date)?

I have tried datetime on the form with format HH12:MM PM and date on the database.

I have also tried the same with a timestamp on the database.

The time always appears to get entered correctly. However, the time is always stored as AM regardless of what is specified by the user.

View 2 Replies View Related

Export/Import/SQL Loader :: How To Exclude A Partition From Schema Mode Export

Aug 2, 2012

I am using Oracle 10g Data Pump Export utility expdp. What I am trying to do is to export a single schema, except for a certain partition P in table T.

I have tried:

expdp user/pass@db dumpfile=... logfile=... exclude=table:" = 'T:P' "

It doesn't work. The whole table T gets exported.

Is there a way to exclude partitions from schema mode export?

If not, is there a way I can achieve the same with DBMS_DATAPUMP API?

View 2 Replies View Related

Get All Dates Between 2 Dates

Aug 5, 2010

I am newbie to oracle and using oracle 10g as database. I want to get dates between two dates .... let me give an example
suppose a user enters 1-Aug-2010 - 31-Aug-2010 , so i should get all dates in between from date and to date.

something like "select date..or whatever from dual where date between 1-Aug-2010 and 31-Aug-2010 " like this type or other.

View 2 Replies View Related

SQL & PL/SQL :: Dates Between 2 Dates

Nov 9, 2010

I want the dates between 2 dates. Suppose i give the dates 01-jan-2010 and 31-jan-2010 and i need the following output.

01-jan-2010
02-jan-2010
03-jan-2010
.
.
.
.
31-jan-2010

View 11 Replies View Related

All Months Between Two Dates

Sep 17, 2008

I got all the month_numbers when i did this

[/b]select distinct t.f_month_number
from time_dim t
where
f_date between (select start_date from employee where emp_id = 111 ) and
(select add_months(start_Date,12) from employee where emp_id = 111)[b]

but when i add

select distinct t.f_month_number,p.start_date,round(replace(p.total_sal,',','')/12,2) as Monthly_sal
from time_dim t, employee p
where
t.f_date = p.start_date and
f_date between (select start_date from employee where emp_id = 111 ) and
(select add_months(start_Date,12) from employee where emp_id = 111)

i got only one month value.

View 6 Replies View Related

SQL & PL/SQL :: Difference Between Dates

Jan 23, 2013

I am looking to subtract two columns and get the difference.

select to_char('06-NOV-2012 20:00','DD-MON-YYYY HH24:MI') - to_char(systimestamp,'DD-MON-YY HH24:MI') from dual;
select to_char('06-NOV-2012 20:00','DD-MON-YYYY HH24:MI') - to_char(systimestamp,'DD-MON-YY HH24:MI') from dual
*
ERROR at line 1:

ORA-01722: invalid number

View 9 Replies View Related

SQL & PL/SQL :: Days Between Dates

Mar 14, 2012

I have not defined the table ( I only have privileges to query data).

I am unable to copy and paste my real code here, and the actual results from the run, as my company will fire me if I do so... so here is how things approximately look like (tried to keep it as real as possible).

Let's say that the table CYCLE has client numbers (clientid), cycle number (cycleno), date of visit (visdt).

I am trying to create a query to calculate how many days there are between each two consecutive visits/cycles for a single client(let's say 1200004)

clientid / cycleno / visdt
---------------------------
1200004 / 1 / 10OCT2011
1200004 / 2 / 31OCT2011
1200004 / 3 / 21NOV2011
1200004 / 4 / 05DEC2011
1200004 / 5 / 03JAN2012
...
1000005 / 1 / 04NOV2011
1000005 / 2 / 03DEC2011
1200004 / 1 / 10JAN2012
1200004 / 2 / 15FEB2012
.
.
.

The code below is the only one that kind of seemed to work, but it is definitely not giving me the right results.

SELECT cycleno1, visdt1, cycleno2, visdt2, to_date(visdt1) - to_date(visdt2) days

FROM (SELECT clientid, cycleno cycleno1, visdt visdt1,
LEAD (visdt, 1) OVER (ORDER BY cycleno) visdt2
FROM CYCLE) a

[Code]....

I am getting a mess of a result of the kind:

cycleno1 / visdt1 / cycleno2 / visdt2 / days
--------------------------------------------
1 / 10OCT2011 / 1/ 18OCT2011 / -8
1 / 10OCT2011 / 2/ 18OCT2011 / -8
1 / 10OCT2011 / 3/ 18OCT2011 / -8
1 / 10OCT2011 / 4/ 18OCT2011 / -8
1 / 10OCT2011 / 5/ 18OCT2011 / -8

I need my result to look like:

cycleno1 / visdt1 / cycleno2 / visdt2 / days
--------------------------------------------
1 / 10OCT2011 / 2/ 31OCT2011 / 21
2 / 31OCT2011 / 3/ 21NOV2011 / 22
3 / 21NOV2011 / 4/ 05DEC2011 / 15
4 / 05DEC2011 / 5/ 03JAN2012 / 30
5 / 03JAN2012 / / /
.
.
.

View 3 Replies View Related







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