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


ADVERTISEMENT

SQL & PL/SQL :: Columns Into Rows Conversion

Mar 24, 2011

I have the following query

select a.EV_ACTOR_NAME, round((b.total_transfer/(24 * a.Max_Throughput)) * 100) AVG_ThroughPut,round(((b.total_transfer/a.Max_Throughput) * 100 )) MAX_Throughput from
(select distinct EV_ACTOR_NAME,((max(EV_TRANSFER_RATE) over ( partition by EV_ACTOR_NAME)) * 60 * 60) /1024 Max_Throughput from dprt_events where EV_ACTOR_NAME is not null ) a,
(select distinct EV_ACTOR_NAME, (sum(EV_TRANSFER_SIZE) over( partition by EV_ACTOR_NAME)/1024/1024/1024) total_transfer from dprt_events where EV_ED_ID in (40,105,112) and EV_END_TIME between (sysdate - 20) -12/24 and sysdate - 20) b
where a.EV_ACTOR_NAME = b.EV_ACTOR_NAME

This is the output i get :

EV_ACTOR_NAME AVG_THROUGHPUT MAX_THROUGHPUT
------------ ------------- --------------
actor2 1 33
actor3 0 0
actor1 2 53

How do I convert the above out put to following format.

EV_ACTOR_NAME value1 value2
-------------- -------------- --------------
actor2 AVG 1
actor2 MAX 33
actor3 AVG 0
actor3 MAX 0
actor1 AVG 2
actor1 MAX 55

I tried the following

WITH data AS
(select level a from dual connect by level <= 3)
select EE_ACTOR_NAME,decode(a, 1, AVG_ThroughPut, 2, MAX_Throughput) value from
(

[Code]...

but i always get the following error ORA-00904: "A": invalid identifier

View 5 Replies View Related

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 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

Reports & Discoverer :: Dates As Columns

Feb 18, 2012

I have a small requirement for report, i want to develop a report based on dates and employee performance.Suppose i give the parameter from date as 01'st jan 2011 and end date as 31'st Jan 2011 then employee hrs will be as summary as rows distributed among days with the given date range as columns.How to make each day as column automatically.

View 5 Replies View Related

SQL & PL/SQL :: Query To Find Missing Dates Between Two Columns In Given Month?

Sep 26, 2012

I need query to find the missing dates between two columns in given month.

CREATE TABLE emp_shift (
empno NUMBER(4),
fr_date DATE,
TO_DATE DATE,
shift VARCHAR2(1));
CREATE TABLE emp (
empno NUMBER(4)
);

[code].....

Required output is

MISSING_DATES EMPNO
---------------------- ----------
09-SEP-12 TO 11-SEP-12 7499
23-SEP-12 TO 26-SEP-12 7499
01-sep-12 TO 30-SEP-12 7521
01-sep-12 TO 30-SEP-12 7788

View 8 Replies View Related

Server Utilities :: SQLLDR / Dates And Null Columns?

Nov 10, 2010

I am experiencing somewhat same issue...but have been unable to resolve it(new to Oracle) I am getting the infile from flat file(data dump from SQL) using sqlldr to upload data to the Oracle table...since the data is already in the flat file...I cannot do anything in the SQL to pre-format the data...

Sample of ERROR I am getting - Column CREATE_DATE which has date and time - happens to other date time columns also if remove the CREATE_DATE from Control file(happens to every single line of record):
==========================================
Record 2: Rejected - Error on table LGCY_CHS.METS_CHS_USER_PRIV, column CREATE_DATE.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

[code]...

Flat file: (3 lines of data)

5|Annie|1|AR|84601D0A-6D9D-4D0F-86EB-2FDD9D7E680B|0|0|1|1|1|0|1|0|kgarbin|XPLTMCE01|2005-04-07 13:54:42.087|Annie|VAXP60|2008-10-03 16:54:59.583|2008-10-03 16:54:59.583
11|Beverly|1|BA|9A2D6304-E997-4B40-96E5-2221E521B077|1|0|0|1|0|0|0|0|kgarbin|XPLTMCE01|2005-04-07 13:54:42.087|BEVERLY|VAXP60|2008-10-03 09:39:33.973|2008-10-03 09:39:33.973
29|KGarbin|1|KG|B229FCF9-BED0-4E50-9804-83324B677C67|0|0|1|1|1|1|0|1|kgarbin|XPLTMCE01|2005-04-07 13:54:42.087|Gfoote|VAXP60|2008-09-08 10:05:01.690|2008-09-08 10:05:01.690

View 1 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

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

Reports & Discoverer :: Create A Report By Using One Field / Text As Columns Name In Layout But Display All Columns

Jun 16, 2010

I want to create a report by using one field and one text as columns name in layout but display the all the columns. I mention the 5 column names in query.how can I write function in summary column.

View 4 Replies View Related

Performance Tuning :: Index 15 Columns When Cannot Predict Columns Will Be Used In Where Clause?

Apr 4, 2011

I am running a fairly busy Oracle 10gR2 DB, one of the tables has about 120 columns and this table receives on average 1500 insertions per second. The table is partitioned and the partitioning is based on the most important of the two timestamp columns. There are two timestamps, they hold different times.

Out of these 120 columns, about 15 need to be indexed. Out of the 15 two of them are timestamp, at least one of these two timestamp columns is always in the where clause the queries.

Now the challenge is, the queries we run can have any combination of the 13 other columns + one timestamp. In reality the queries never have more than 7 or 8 columns in the where clause but even if we had only 4 columns in the where clause we would still have the same problem.

So if I create one concatenated index for all these columns it will not be very efficient because after the 4th or 5th column the sorting would no longer be very useful and I believe the optimiser would simply not use the rest of the index. So queries that use the leading columns of the index in sequence work well, but if I need to query the 10th column the I have performance issues.

Now, if I create multiple single column indexes oracle will have to work a lot harder to maintain all these indexes and it will create performance issues (I have tried that). Besides, if I have multiple single column indexes the optimiser will do nested loops twice or three times and will hit only the first few columns of the where clause so I think it will kind of be the same as the long concatenated index.

What I am trying to do is exactly what the Bitmap index would do, it would be very good if I could use the AND condition that a Bitmap index uses. This way I could have N number of single column indexes which the optimiser could pick from and serve the query with exactly the ones it needs. But unfortunately using the Bitmap index here is not an option given the large amount of inserts that I get on this table.

I have been looking for alternatives, I have considered creating multiple shorter concatenated indexes but this still would not address the issue since many queries would still not be served properly and therefore would take a very long time to complete.

What I had in mind would be some sort of multidimensional index, I am not even sure if such thing exists. But essentially it would be some sort of index that could serve a query efficiently regardless of the fact that the where clause has the 1st, 3rd and last columns of the index.

So considering how widely used Oracle is and how many super large databases there are out there, this problem must be common.

View 12 Replies View Related

SQL & PL/SQL :: How To Select All Columns From Table Except Those Columns Which Type In Query

Jan 21, 2011

I have a two question.

Question 1:How to select all columns from table except those columns which i type in query

Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data

View 5 Replies View Related

SQL & PL/SQL :: Value Conversion?

Apr 23, 2012

Some app (wich I can't touch) stores a value that I need to modify with a trigger or even with a view.

Original value: 7B751BF0 (2071272432 in decimal)
Stored value: 4028331387 (F01B757B in hexa)

To make things short, I'm getting 4028331387 and I need to convert it to 2071272432.

View 6 Replies View Related

LMT To DMT Conversion?

Mar 12, 2013

I need to move one of my LMT tablespace to DMT, Can I do it , I know that the DMT's are depreciated since Oracle 9i but still need to know this.I am trying the below mentioned method to achieve the same.

SQL> exec DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('USERS');But I am facing the below mentioned error:
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('USERS'); END;

*
ERROR at line 1:
ORA-10616: Operation not allowed on this tablespace
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 216
ORA-06512: at line 1

View 10 Replies View Related

SQL & PL/SQL :: Date Conversion

Jan 12, 2012

I have an issue with date conversion. In my table dates are in this formate:

04-DEC-90 10:46:46

I need to convert it in To_date.

SQL> select to_date('04-DEC-90','dd-mon-yy') from dual;

TO_DATE('04-DEC-90','DD-MON-YY
------------------------------
04/12/2090

SQL>

But the year shoul be 1990 not 2090.

View 3 Replies View Related

SQL & PL/SQL :: Number Conversion

Jan 4, 2013

I have a table datatype number (12,10) that I am reading out of. I am taking the value from this source table and inserting it into a destination table of datatype number (12,15).

I do not have the ability to alter the tables. How can i convert this number so i can insert. I am currently getting the error "ORA-01438: value larger than specified precision allowed for this column"

I am trying to use the to_number, but it not working. How can i format this number field so i can read it from source where i have number (12,10) and insert it successfully in a higher precision table of number(12,15)

View 1 Replies View Related

Example For Conversion Functions

Mar 27, 2012

Again i getting confused with conversion function especially Explicit data type conversions. some cases oracle server automatically converts the data to the required type. This is called IMPLICIT CONVERSION. Explicit conversions are done by using the conversion functions.

Oracle Explicit Data Type Conversions are

1 TO_CHAR
2 TO_DATE
3 TO_NUMBER

View 1 Replies View Related

SQL & PL/SQL :: Row To Column Conversion 2

Jan 13, 2011

SELECT EMPNO ,ESAL,DEPTNO FROM EMP;

O/P

EMPNO ESAL DEPTNO
101 1000 10

I WANT O/P LIKE THIS

REQUIRED O/P

101
1000
10

View 5 Replies View Related

PL/SQL :: Column To ROW Conversion?

Jan 21, 2013

My requirement is to converts row to Column information and sum of column in Weight kg in below manner Data in columns are not of same nature they can keep on changing

Item Supplier Consumer Package DWP_NO DWP_ED Quality_code Pakaging Material WEIght kg
80026020 13984
90225217 21385 1 1 3 Not Applicable 22-paper .001
90218863 16578 1 1 1 Not Applicable 04-low density polyethylene (0.002+.002+.002)=.008
90218349 14507 1 1 1 Not Applicable 05-polypropylene,22-paper (.02+.05) =.07
40221108 21519 3,2,1 1 2 IKEA-CB-60 22-paper (.32+0.3+0.45)=1.07

find table structure

CREATE TABLE TEST_ROW_TO_COL
(
ITEM_CODE VARCHAR2(10 CHAR),
SUPPLIER_CODE VARCHAR2(10 CHAR),
"Consumer Package" NUMBER(2),

[code]...

ans insert statement of sample data

--SQL Statement which produced this data:
-- select * from Test_row_to_col where
item_code in ('80026020','90225217','90218863','90218349','40221108')

[code]...

I am using Oracle 11g

View 1 Replies View Related

SQL Server To Oracle Conversion

Aug 29, 2013

I have one query in SQL server. I want to convert that query to Oracle. Well i am not that good with writing queries in Oracle. Following is the query in SQL server.

DECLARE @StartYear AS INT = 2010;
DECLARE @EndYear AS INT = 2014;

WITH
years
AS (SELECT YYYY = @StartYear
UNION ALL
SELECT yyyy + 1
FROM years
WHERE yyyy < @EndYear)
[code].......

Which Gives output in following format.

id_datedateYearMonthNumberMonthDayOfMonthDayOfWeekNumberDayOfWeekWorkingDay
201001011/1/201020101January16Friday1
201001021/2/201020101January27Saturday0
201001031/3/201020101January31Sunday0
201001041/4/201020101January42Monday1
201001051/5/201020101January53Tuesday1

View 3 Replies View Related

Epoch Date Conversion

May 31, 2011

My company uses Remedy which stores date/time as an integer. I use the following formula to convert it to a readable date:

TO_CHAR(TO_DATE('01/01/1970', 'MM/DD/YYYY HH:MI:SS AM')+ ((createdate - 4*3600)/(60*60*24)),'MM/DD/YYYY HH:MI:SS AM') as Create_Date

I have to use the "4*3600" in order to get the date to show up correctly, but even then the date sometimes comes up wrong. If the date occurs in the morning, then the date shows up as the previous day. I am sure this is probably due to the offset I am adding in the above formula. If I don't add the 4 hour offset, then the date shows up 4 hours off.

View 3 Replies View Related

Number To Float Conversion

Aug 11, 2010

I have coluum value like 00000000399. i need to convert the float like i mentioned

00000000399 should be loaded as 3.99

00000001197 should be loaded as 11.97

00000000003 should be loaded as 0.03

wat option i try to change the value

View 1 Replies View Related

Conversion From SQL Server To Oracle

Nov 6, 2011

is there any licensed version tool available to convert objects from sql server to oracle especially procedures? I have tried with open source tools but that tool didn't convert properly.

View 7 Replies View Related

Ora-01460 With No Conversion Attempts?

Feb 1, 2007

This is my query:

DELETE FROM GR_GUEST WHERE GUEST_NAME='Ambassador Jack Binns bring Patrick Clawson' AND RESERVATION_ID_FK=21635

It gives an ORA-01460 which is "unimplemented or unreasonable conversion requested"Removing the " AND RESERVATION..." gives the same error.

The problem is that GUEST_NAME is a VARCHAR (150) and RESERVATION_ID_FK is an INT so I don't see where any conversion is coming in.I had changed the name a little bit as it used to a (, ) and a : in the GUEST_NAME. I thought that might be causing the issue so I removed them with a REPLACE through an UPDATE query. However, the record still needs to be deleted.

It should be a simple query as it happens on this table all the time, so I don't know why this one is different.

View 3 Replies View Related

Time Conversion To Yyyy - Dd - Mm

Mar 5, 2008

1075329297.572

This is unix time where 1075329297 is seconds 572 is milliseconds.

first il store this time in oracle database.when i am retrieving it i want this date into yyyy-dd-mm format. Is it possible to do it in oracle.(using convert function) or is there some other way?

View 8 Replies View Related

SQL & PL/SQL :: Char To Date Conversion?

Feb 7, 2013

converting a string to date, my string looks like '01022013' '04022013', I tried to_date('01022013', 'dd-mm-yyyy') but couldn't work.

View 6 Replies View Related

SQL & PL/SQL :: Conversion Varchar2 To Number

May 14, 2011

I have 2 tables.The column in table A is number and Column in table B is a varchar2 datatype.I have to use the Column of table B as a filter to column of Table A.Below is the example.

create table A(Col1 number);
Inert into A values(1);
Inert into A values(2);
Inert into A values(3);
Inert into A values(4);

Create table B(Col1 Varchar2(100));
Insert into b value ('1,2,3');

Select * from A where col1 in (select col1 from b)
Error: Invalid Number

Is there a way to convert the varchar to number.The varchar field have multiple characters (numbers) seperated by commas.

View 7 Replies View Related

SQL & PL/SQL :: Blob To Long Raw Conversion

Dec 19, 2010

I have a problem i need to convert a blob column contains pic file to long row

i had many tries but no one succeeded

-----------------------------------
Source table | destination table
id number | id number
img blob | img long raw
------------------------------------

1 - INSERT INTO destination table SELECT id , img FROM Source table WHERE ROWNUM < 2

i have this error ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 146092, maximum: 2000)

2 - INSERT INTO destination table SELECT id , dbms_lob.SUBSTR(img,0,2000) FROM Source table WHERE ROWNUM < 2

now errors but the lengh is 0 (i have no file )

What can i Do

View 7 Replies View Related

SQL & PL/SQL :: Conversion Of CLOB To NCOLB

Sep 19, 2011

I want to convert Clob column datatype to NClob datatype. I tried to modify the column by using below method but got the mentioned error:

SQL> alter tab1 TABLE_NAME modify COLUMN_NAME nclob;
*
ERROR at line 1:

ORA-22859: invalid modification of columns

One of the other method with which I am familiar with is :

Quote:

First to Create a new column of the desired type and copy the current column data to the new type using the appropriate type constructor.

how to change the clob to Nclob datatype without using the temp column usage?

View 1 Replies View Related







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