PL/SQL :: Calendar Month Dates In Horizontal Line
Aug 14, 2012Is there anyway to show calendar month dates in horizontal line?
First line will have dates and then second line will have day i.e Sat, Sun, Mon.......
1 2 3 4 5 6 7 8 9 ........31
ADVERTISEMENT
PL/SQL :: Find Invalid Date And Month Not Match With Calendar?
Mar 21, 2013I want to find the row with invalid day, month which are not matching with calendar day and month. Also the program should capture the data if the year <1900
with xx as
(select 101 as ID, '24/05/1899' as create_date from dual
union all
select 101, '32/03/2012' from dual
union all
select 102 ,'30/02/2012' from dual
union all
select 101 , '29/02/2013' from dual
[code]...
SQL & PL/SQL :: Month Wise Days For Between Given Dates?
Apr 28, 2010I have a table with starting date and ending date.
I want to fetch monthwise days for given two dates
IE starting 25/12/2009 ending 25/03/2010
Result should like below.
Dec-09 7
Jan-10 31
Feb-10 28
Mar-10 25
I have tried this but it is not giving me the result which want..
select to_char(thedate,'mon-yy') mnth,count(to_char(thedate,'mon-yy')) days from
(SELECT TRUNC(to_date('25/12/2009','dd/mm/yyyy'),'Y')+ROWNUM-1 THEDATE
FROM ( SELECT 1 just_a_column
FROM dual
CONNECT BY LEVEL <= 366
[code].....
SQL & PL/SQL :: Query To Find Missing Dates Between Two Columns In Given Month?
Sep 26, 2012I 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
SQL & PL/SQL :: Function That Accepts 2 Dates - Not A Valid Month Error
Apr 15, 2011I have function that accepts 2 dates namely sDate and eDate i.e start and end dates. See the declaration part of the function:
startDate eab.eod_date%type;
endDate eab.eod_date%type;
begin
startDate := to_date(sDate,'DD-MON-YY');
endDate := to_date(eDate,'DD-MON-YY');
end
When I called the function it gives me the following error: ORA-01843: not a valid month..And I tried to simulate the process, the below code also show the same error:
select TO_DATE(to_date('13-DEC-09', 'dd-MON-yy', 'nls_date_language=english'),'DD-moN-YY') from dual;
how I can check that error. I need to pass dates to my function.
SQL & PL/SQL :: How To List Data Dynamically Month Wise Picking Month From The Same Table
Jun 4, 2013I have a requirement to list the data month wise dynamically where month data is also in the same table, hopefully the below posts should bring more clarity to my requirements.
1. Table creation:
Create table T1 (account_no varchar2(15), area_code varchar2(2), bill_month date, consumption number);
2. List table content:
select * from T1;
account_no area_code bill_month consumption
Q00001Q31-Jan-12125
Q00002Q31-Jan-1265
Q00003Q28-Feb-12219
Q00004Q28-Feb-12805
Q00005Q28-Feb-1254
Q00001Q31-Mar-12234
Q00002Q31-Mar-12454
Q00003Q31-Mar-12232
Q00004Q30-Apr-1221
Q00005Q30-Apr-12218
Q00001Q30-Apr-1254
Q00002Q31-May-1219
Q00003Q31-May-1287
Q00004Q30-Jun-12187
Q00005Q30-Jun-1278
so on......so on......so on......so on......
3. Expected output:
account_no area_code Jan-12 Feb-12 Mar-12 Apr-12 May-12Jun-12Jul-12Aug-12Sep-12Oct-12Nov-12Dec-12
Q00001 Q 125 548 2345487423154821518738721512
Q00002 Q 65 127 45487819357831585683152878
Q00003 Q 545 219 2328738735188745897313
Q00004 Q 78 805 1221218187885718387389787138
Q00005 Q 541 54 2621878778386538698182
With the conventional query I hope this is impossible,
Spatial :: Convert Multi-line To Simple Line
Feb 13, 2013Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
We are processing spatial data from another source to display in our GIS environment. The data is a set of multi lines. The gtype is 2006. A typical geometry looks like:
SDO_GTYPE 2006
SDO_SRID 31370
SDO_POINT.X NULL
SDO_POINT.Y NULL
SDO_POINT.Z NULL
SDO_ELEM_INFO (1,2,1, 7,2,1)
SDO_ORDINATES (105094.84, 195084.96,
105094.54, 195080.22,
105094.84, 195084.96,
105094.84, 195084.96,
105094.68, 195082.47 )
Now, this is not an actual multiline... it's just encoded as a multi line, but if you look at the coordinates you'll see that the end point of the first line is the same as the beginning of the second line (105094.84, 195084.96).
A better way to encode this geometry would be:
SDO_GTYPE 2002 <---
SDO_SRID 31370
SDO_POINT.X NULL
SDO_POINT.Y NULL
SDO_POINT.Z NULL
SDO_ELEM_INFO (1,2,1)
SDO_ORDINATES (105094.84, 195084.96,
105094.54, 195080.22,
105094.84, 195084.96, <---
105094.68, 195082.47 )
Is there a standard function in Oracle that does this conversion for me? Or do I have to write my own :)
Finding Individual Dates Having Dates Plus 2 Days
Jan 26, 2011I 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.
SQL & PL/SQL :: Split A Date Into New Dates According To Black Out Dates?
Mar 10, 2011Split 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 ?
PL/SQL :: Code Erases Previous Month Record While Updating Current Month Record
May 16, 2013Using 11gR2, windows 7 client machine. I need to update the table missing_volume (below), where I need to calculate the estimated_missing column. The calculation of estimated_missing column for current month needs previous month numbers (as commented inside the code below). I want the output like the first table. Notice the records start from January, hence estimated_missing for January can't be calculated, but for the the rest of the months it can be done by simply changing 'yr' and 'mnth' (commented inside the code towards the end).
yr mnth location volume actual_missing expected_missing estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013 January loc1 48037 24 57
2013 February loc1 47960 3660 53 24
2013 March loc1 55007 78 57 28
2013 April loc1 54345 72 58 77The code:
UPDATE missing_volume g
[Code]....
The code does calculate correct number for 'estimated_missing' as I run the code for each month, but the problem is while updating the current month it also erases the record for previous month. E.g. as can be seen below, after I updated April the column only has the record for April, previous month record is gone, similarly updating March removed February, etc. I can't understand why it's happening!! Here is the output I get:
yr mnth location volume actual_missing expected_missing estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013 January loc1 48037 24 57
2013 February loc1 47960 3660 53
2013 March loc1 55007 78 57
2013 April loc1 54345 72 58 77
why it's happening (I mean where is the flaw in the code) and how to get the desired output (first table).
SQL & PL/SQL :: How To Make Horizontal Partitioning
Mar 4, 2013how should i do to make horizontal partitioning?
View 18 Replies View RelatedForms :: Get The Horizontal Scroll Bar
Aug 28, 2013get the horizontal scroll bar as shown in the attached pdf.
The first screenshot in attached pdf shows how my form look like, it is having more then 30 columns. And i want a scroll bar as shown in second screenshot in attached pdf.
SQL & PL/SQL :: Format Horizontal UNION
Mar 5, 2013select to_char(sysdate, 'mm/dd/yyy') "DATE", to_char(sysdate, 'HH24:MI AM') "TIME", sum(bytes)/1024/1024/1024 "TOTAL_GB"
from dba_data_files
UNION ALL
select to_char(sysdate, 'mm/dd/yyy') "DATE", to_char(sysdate, 'HH24:MI AM') "TIME", sum(bytes)/1024/1024/1024 "USED_GB"
from dba_segments
[/HTML]
I like to get the output from the queries above.
DATE TIME TOTAL_GB USED_GB %USED
3/5/2013 06:00 AM 3151.24316 2330.40381 73.95
3/5/2013 12:00 PM 3151.24316 2347.41551 74.49
3/5/2013 06:00 PM 3151.24316 2349.76498 74.56
.....
[/HTML]
how I can format.
Forms :: Set Horizontal For Each Tab Pages In A Tab Canvas
Apr 16, 2012I have a tab canvas with 4 tab pages.I want to set scroll bars for each tab page. I draw 4 stack canvas on 4 tab page but it doesn't transferred fields in each tab pages.
View 4 Replies View RelatedSQL & PL/SQL :: Making Vertical Records To Horizontal
Aug 18, 2010I need to build a query on making a vertical records to horizontal records. I did read somewhere saying that we can use decode function. But im still trying to understand how decode function can make a vertical records to horizontal records.
View 4 Replies View RelatedSQL & PL/SQL :: Convert The Table From Vertical To Horizontal
Apr 21, 2010I have a table like the
WEEK STORE SALES
1 A 12
2 A 18
3 A 20
4 A 25
I need a query to build out put like
store week1 week2 week3 week4
A 12 18 20 25
SQL & PL/SQL :: Query With Horizontal Running Totals
Dec 20, 2012I'm trying to create a report in the following format
Year Name Reg1 Reg2 Reg3
2001 Al 3 4 5
2001 Le 4 1 1
2001 7 5 6
2002 Sue 2 4 1
2002 Al 1 3 6
2002 Jim 6 1 3
2002 16 15 16
2003 Jim 4 -3 2
2003 Le -2 4 5
2003 20 16 23
Note that the totals are accumulating horizontally, broken on year.
Sql To Display Output In Horizontal View?
Sep 8, 2012I need to display the sql query output in horizontal manner. Below I have given the script.
create table TestTable (id number, name varchar2(10))
insert into TestTable values (1, 'John')
insert into TestTable values (2, 'Mckensy')
insert into TestTable values (3, 'Valneech')
insert into TestTable values (4, 'Zeebra')
[code]....
But for my requirement, I need to display in horizontal manner.
ID 1 2 3 4
Name John Mckensy Valneech Zeebra
SQL & PL/SQL :: For Each Month Sum Values From Month Before?
Nov 25, 2010I need for each date sum the values from the begin of the year to present date. In January I will have the value of this month, on February I must sum the value of this month and the value of the month before, and so on, at the end of the year.
Date input
SELECT ID_CLIENT, DT_REG, VAL
FROM (
SELECT 1 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100401', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 220 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 500 as VAL FROM DUAL UNION
SELECT 3 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION
SELECT 3 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL);
Result
ID_CLIENTDT_REGVAL
101/01/2010200
101/02/2010300
101/03/2010500
101/04/2010650
101/05/2010750
201/01/2010100
201/03/2010320
201/05/2010820
301/02/2010150
301/05/2010250
Forms :: Horizontal And Vertical Scroll Bar For Tabular
Aug 20, 2011i need to create a form with 15 col and 10 records in tabular format to display records from the table....i done it with vertical scroll bar ..bt.i need both horizontal and vertical scroll bar for that tabular.
View 2 Replies View RelatedSQL & PL/SQL :: Horizontal Fragmentation In Oracle 10g Express Edition?
Feb 28, 2012I want to do horizontal fragmentation of a table say employee. But fragmentation is often related to distributed databases. So i want to ask how can i do this on my single home computer? list out the steps that should be performed. I saw the concept of link but really failed to understand that.
View 14 Replies View RelatedForms :: Horizontal Scroll Bar In Stacked Canvas
May 11, 2011i just try example in URL.....but i try it in oracle 10g, it about horizontal scroll bar in stacked canvas , where emp information ,employee has been split into two part ename,empno ,will be in a frame and other info will be in stacked canvas in same frame ,i just try same thing as say in website , but information in content canvas is not visible in tried but it is not coming..
View 4 Replies View RelatedApplication Express :: Implement Horizontal Accordion
Dec 16, 2012I want to implement Horizontal accordion... I've found a good example @ the following location
[URL]..........
but can't seems to follow the first step to Download easyaccordion.zip file and upload to the #IMAGE_PREFIX#libraries..
I am using APEX.ORACLE.COM environment.
Application Express :: Display Horizontal Sub Regions
Mar 4, 2013I am using APEX 4.0, my regions need to be displayed at "Display Point: Display Template Body 2" in column 2.I have 2 regions I want displayed horizontally, side by side.I want to use a parent region to contain them. I create my parent region, called it A, template = Reports, assign it to sequence = 10.I create my 2 sub region (B) and (C), sequence 20 and 30, template = Navigation, picking parent = A.By default, B displays above C. URL....
I copied the Navigation template, creating a new one called NAV2 and added this in the sub-region template section:(this cut/paste) doesnt display well here: it is the exact DIV begin/end block as posted above.
div style="float: left;"
#SUB_REGION#
div
I changed regions (B) and (C) to use template = NAV2, without any change in display behavior.I read further in the above post where it was recommended to reset the clear property, so I changed the above style like to: style="float: left;clear:none;".
Forms :: Design Mode - Horizontal And Vertical Scroll Bar
Oct 25, 2010How to create horizontal and vertical scrollbar at a time while design mode??
View 2 Replies View RelatedSelect All Dates Between Two Dates
Oct 22, 2003How 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.
SQL & PL/SQL :: Week On Particular Day According To Arabic Calendar
May 26, 2013As my requirement is that to find week for particular date according to Arabic calender.As per Arabic calender first day for year starts from saturday.
so when i execute query :
select to_char(to_date('05/26/2013','MM/DD/RRRR'),'WW')
from dual;
Then it gives 21,but as per arabic calender it should show 22nd week.
getting result according to Arabic calender as it should return 22,because weak starts from saturday according to Arabic calender.
Forms :: Calendar LOV Position (x / Y) On 6i
Aug 1, 2011I have included a calender control in my form and its working fine.Now, my problem is how can I automatically place the calender next to the text-item (for example, "block.invdate")if the push button is pressed to call a calender.
View 1 Replies View RelatedForms :: How To Make A Calendar
Mar 4, 2003I have read the messages below about making a calendar but am still unsure of how to go about doing this. I have the STNDRD20.OLB file, but I don't know where to put it or what to do with it! What comes next?
View 20 Replies View RelatedForms :: How To Find Table Names For The Fields In A Horizontal Form Of ASCP
Jun 25, 2013I have a requirement of pulling the data from a field in horizontal form of Advanced Supply Chain Planning responsibility(ASCP). When I go into help-> Diagnostics, it shows the block name of the form as 'Horizontal Plan' and field name as 'Pivot Table'. Not pretty sure on how to pull the data source into horizontal form with the info I have.
View 3 Replies View Related