SQL & PL/SQL :: Using Dates As Column Heading?

Apr 3, 2012

I have the following table with some sample data. I just need to transform (unknown no of) rows into (unknown no of) columns. like a matrix report.

Test case

---create table

create table attendance
(
REG_NO NUMBER(7),
COURSE_SEQ_NO NUMBER(4),
SECTION VARCHAR2(1),
SEMESTER_CODE NUMBER(1),

[code]....

--insert data

Insert into attendance (REG_NO,COURSE_SEQ_NO,SECTION,SEMESTER_CODE,SEMESTER_YEAR,ATT_DATE,ATT_FLAG) values
(2014035,149,'A',1,2012,to_timestamp('12-MAR-12','DD-MON-RR HH.MI.SSXFF AM'),0);
Insert into attendance (REG_NO,COURSE_SEQ_NO,SECTION,SEMESTER_CODE,SEMESTER_YEAR,ATT_DATE,ATT_FLAG) values
(2014048,149,'A',1,2012,to_timestamp('12-MAR-12','DD-MON-RR HH.MI.SSXFF AM'),1);
Insert into attendance (REG_NO,COURSE_SEQ_NO,SECTION,SEMESTER_CODE,SEMESTER_YEAR,ATT_DATE,ATT_FLAG) values

[code]....

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: Getting Column Data As Column Heading?

Apr 20, 2012

I would like to have column data as column headers.

Tables:

skill_table
SKILL_ID | NAME
3431060 | Stomach
3431064 | Hand
3437806 | Finger

localnode_table (which actually has the order/alignment (like what is next and what is previous) of the name from skill table.

NODE_ID | PREVIOUS_ID | NEXT_ID
3431060 | | 3431064
3431064 | 3431060 | 3437806
3437806 | 3431064

How to make it appear like:

Stomach | Hand | Finger
3431060 | 3431064 | 3437806

View 3 Replies View Related

Client Tools :: How To Display The Column Heading

Aug 2, 2010

how to dispaly the column heading.

I tried to do the below.

03-AUG-10> set pages 0
03-AUG-10> set heading on
03-AUG-10> select count(*) numberofrows
2 from iceberg_mig_acnts_stage2 s2, tvp109workorder t109
3 where s2.no_account = t109.no_account

[Code]....

View 9 Replies View Related

Forms :: How To Display Table Column Heading On Oracle 6i

Jun 8, 2013

How to display table columns on oracle form?

View 7 Replies View Related

Client Tools :: How To Have Column Heading As Example Of Substr Function

Jan 23, 2013

I would like to have a column heading as follows in double inverted comma. but sqlplus environment returns column heading length equal to output value.

SQL> select substr('The independence day', 5,12) "Example of substr function" 2 from dual;

Example of s
------------
independence

I know that default column heading length is 30 character long.

but my column heading is less than 30 character long (which is 26)

How can i have column heading as Example of substr function?

View 7 Replies View Related

Application Express :: Print Column Heading On Each Page In PDF

Nov 13, 2013

Version 4.1.1.00.23 

I'm having a really hard time finding a solution to print the column headings on each page after the page break when printing.  We're using a tool called PDF Creator to create the PDF's and they're opened in Adobe.The server is GlassFish Server 3.1.2. What other information can I provide?

View 7 Replies View Related

Application Express :: How To Display Custom Report Column Heading

May 13, 2013

I am having report region with different columns, in that one column heading needs to be start with lower case like "iST Status" but always it is showing "IST Status".

View 5 Replies View Related

Forms :: How To Populate Table Column Heading In List Item Of Oracle

Jun 26, 2013

how should i populate table column heading in list items of forms? I've create lov to select the column, then i have 10 separate list items. once i select the table from lov then list item should get populated with selected table column.

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

Client Tools :: Turn Off Heading For Every 10 Rows?

Jul 1, 2010

I would like to turn off the heading which it gives for every 10-12 rows of output in a select stmt.

I want that to be printed only at the top. not for every 10 rows. Could I know how to do it?

View 4 Replies View Related

Application Express :: IR Report With Heading Label

May 15, 2013

i have IR report with 15 column with heading.

The end user can accessing from different department, when the user can see the report shown only their particular department rows only. each department have different column value and Header label .how can display the column header depend on the department user.

e.g. IR report view

Seq# -- Department -- date -- Subject/Customer Message
1                          Communication done with Jony(from HR department)
2                          low sales from East region(from Sales Department)

IN the IR report Table data structure.Heading_code value as LOV in IR form(1.Subject/2.Customer Message/ect...) and Column_value as Text Field.

Seq# -- Dept_name -- Heading_code --  Column_value
1        HR              1           Communication done with Jony
2        Sales           2           low sales from East region

Apex have limitation of one IR report in one page. so i could not create different Report region even i don’t want create another page due to the further functionality issue.

View 12 Replies View Related

Application Express :: How To Use Value Of Report Rows For Another Item Heading

Jun 27, 2012

I have a classic report which has 4 rows with date values. The Row1 has Date1, Row2 has Date2 and so on.

The report is structured like this

DATE_COL COL1 COL2

Row1 Date1 100 101
Row2 Date2

I want to use the values of DATE_COL of ROW1 which is Date1 in the label of ITEM1 which can be on same page or different page.

Also I want to sue the value of DaTE_COL of ROW2 which is Date2 in the label of Column 1 of Report 2.

How to accomplish this?                                                             

View 2 Replies View Related

Reports & Discoverer :: Printing Heading In Excel Using OLE2 Objects Only

Oct 8, 2011

I am using oracle developer suit(form 10g), Using OLE2 object to initialize excel, On "BEFORE REPORT TRIGGER" i am printing report heading in excel using OLE2 objects only. if i run report from developer suit report builder it give output in excel normally and what expected but when ever i put same report on server and run report from application it gives error :

" Terminated with error : <br> REP-1401 'before report': FATAL pl/sql error occured. ORA-43356; message 43356 not found; Product=RDBMS; facility = ORA."

View 1 Replies View Related

Application Express :: How To Add Icons On Right Corner Of Breadcrumb Or Tab Page Heading

Jan 8, 2013

I'm using 11gr2 DB, APEX 4.2 and connected using pl/sql gateway.

I see several sample APEX app like the checklist manager where the tab screen heading (home/checklist/reports) has 3 icons on the right corner for Administration, Mobile. I want to use the same concept but this app is locked so I cannot see how it was done.

I also see some application where the breadcrumb has an icon for home and not the standard Home text.!

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

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

SQL & PL/SQL :: Min And Max Rates With Corresponding Dates

Dec 7, 2012

I am looking for a query to find out minimun and maximum rates of an item with corresponding dates. findout a query to get the required result.

Here is sample data

CREATE TABLE scott.item_rate
(
code VARCHAR2(3),
rate NUMBER(10,4),
vdate DATE
);

[Code]..

COD RATE VDATE
--- ---------- ---------
001 108.97 25-MAY-12
001 108.97 07-APR-12
001 105 05-DEC-12
001 105 11-OCT-12
001 91 02-JUL-10
001 1 05-JUL-10
001 1 31-AUG-10

7 rows selected.

The required result is

CODE MIN_RATE MIN_DATE MAX_RATE MAX_DATE

001 1 05-JUL-10 108.97 25-MAY-12

View 11 Replies View Related

SQL & PL/SQL :: Aggregate Between Two Dates

Jul 13, 2010

I need to aggregate the data based the two dates criteria.

I have two tables

1. Table1
Number Date1 Date2
1 10-Jun-2010 30-Jun-2010
2 10-Feb-2010 30-Feb-2010
----------------------------------
2. Table2
Number Date Revenue
1 11-Jun-2010 100
1 09-Jun-2010 100
1 12-Jun-2010 100
2 11-Feb-2010 100
2 12-Feb-2010 100
2 13-Feb-2010 100
......................
......................
So on

Output:

Number Revenue Date2
1 200 30-Jun-2010
2 300 30-Feb-2010

View 14 Replies View Related

SQL & PL/SQL :: Data Between 2 Dates

Dec 21, 2011

below is the table and data

create table bday (name varchar2(30),bdate varchar2(10));

insert into bday values('jeffery','0110');
insert into bday values('boss','1231');
insert into bday values('raj','1225');

[Code]...

the BDATE column is in the form "mmdd".

I just want to select the name between 2 dates ( not including years). lets say between sysdate and sysdate+20, i.e

select to_char(sysdate,'dd-mon'),to_char(sysdate+25,'dd-mon') from dual;

TO_CHAR(SYSDATE,'DD-MON') TO_CHAR(SYSDATE+25,'DD-MON')
21-dec 15-jan

but when I run the below query, it is not showing me 'january' data

select name from bday
where to_date(bdate,'mm/dd') between sysdate and sysdate+25;

o/p comes as:
NAME BDATE
boss 1231
raj 1225

but the actual o/p should be:

NAME BDATE
boss 1231
raj 1225
jeffery 0110

it seems to me that because of year change the rows are not displayed.how to handle this in single SQL

View 8 Replies View Related

PL/SQL :: Difference Between 2 Dates

Sep 24, 2012

for the below table

create table RM_TR_INVESTMENT
(
  AS_ON_DT                DATE not null,
  EXP_ID                  NUMBER(10) not null,
  BO_REF_ID               VARCHAR2(30),
  FO_REF_ID               VARCHAR2(30),
  BK_PRODUCT_ID           VARCHAR2(20),
  BK_INV_TYPE_ID          VARCHAR2(20),
 [code]....

I want to find the sum of NP_AMT_CCY where difference between Start_Dt and Maturity_Dt >= 14 days and <=28 days.How can I build the query for the above statement ?

View 6 Replies View Related

PL/SQL :: SQL Pivot Dates

Jan 28, 2013

I'm trying to use a PIVOT on the following data set:

ID      STATUS_DESC         PAY_STATUS         PAID_DATE                          TRANSACTION_TYPE                               TRANSACTION_DESC                   DEBIT                                  TOTAL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9876        In Progress       2nd Payment Made       11-DEC-12 19.38.57       Card Payment                                 Payment 2                             349                             349
9876   In Progress   2nd Payment Made       06-DEC-12 14.33.57       Card Payment                                 Payment 1                             100                             100

However I'm still getting two rows as per the below. Ideally all data should be on a single row.

ID      STATUS_DESC   PAY_STATUS        PAYMENT_1_DATE            PAYMENT_1_AMT            PAYMENT_2_DATE             PAYMENT_2_AMT            TOTAL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9876        In Progress       2nd Payment Made       06-DEC-12 14.33.57       100                                                                                                                           100
9876        In Progress       2nd Payment Made                                                                                      11-DEC-12 19.38.57            349                                   349

I have constructed my pivot using the following on the outer select:

PIVOT (MAX (insert_timestamp) AS paid_date
,SUM (debit) AS amt

FOR transaction_desc IN ('Payment 1' AS payment_1,
'Payment 2' AS payment_2)) ;

I've used MAX to pivot the date and also tried using NVL on the insert_timestamp but still no luck.

View 7 Replies View Related

PL/SQL :: Between Certain Moving Dates

Jun 25, 2012

I'm trying to run a report that has a moving date and I need to find data that's within 12 months of that certain date.

So for example... customers come in everyday all year long. I wanted to find the number of unique customers in a year. But the year is moving... So 1 year from 1/15/2011 is 1/14/2012. And 1 year from 1/16/2011 is 1/15/12. So I had something like this but doesn't quite work..

SELECT ...

NVL(COUNT(DISTINCT CASE WHEN TX.DATE_OF_FIRST_VISIT BETWEEN TO_DATE(TX.DATE_OF_FIRST_VISIT,'MM-YYYY') AND ADD_MONTHS(TO_DATE(TX.DATE_OF_FIRST_VISIT,'MM-YYYY'),12) THEN (TX.CLINIC_ID||TX.PATIENT_UNIQUE_ID)END),0) AS "YEAR_1"

View 10 Replies View Related

Inserting Dates Into Table

Mar 10, 2012

My homework requires me to create a booking table for a hotel and I have created the table but I'm having trouble inserting the dates.

This is my table:

DROP TABLE BookingDM CASCADE CONSTRAINTS PURGE;
CREATE TABLE BookingDM (
hNo NUMBER(3),
gNo NUMBER(5),
dFrom DATE NOT NULL,
[code]......

This is the first set I'm attempting to insert
hNo = 148
gNo = 11169
dFrom = 09/03/2009
dTo = 09/10/2009
rNo = 202

This is my attempt to insert the set:

SQL> INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202');
INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202')
*
ERROR at line 1:
ORA-01843: not a valid month

I need my dates to be in the format "MM/DD/YYYY".

View 4 Replies View Related

Group By With Order By For Dates?

Mar 9, 2013

ii have written a query like thi:

select to_char(order_date,'mon-yyyy') "months", sum(nob) "number of bags"
from p_in where order_date between '1-apr-11 and '31-mar-12'
group by to_char(order_date,'mon-yyyy')

the above query returns me a resultset which gives me number of bags sold in every month, but it does not sort the month in their chronological order. i want to sort my resultset in ascending order of month s.,means jan-feb-march-apr-may ams so on...QUOTE

View 1 Replies View Related

Number Of Days Between Dates?

Jan 14, 2008

What I need to do is take 2 dates from a table named 'projects' and insert the number of days between them into a table named 'time_record'.How do I go about this?

View 1 Replies View Related







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