SQL & PL/SQL :: Month Ends Not Coming Correct?
Sep 7, 2011
I am using following query to get last 3 month ends. Here If month end falls on weekend(sat/sun) then it should have prior date
Where
COB- Table name
COB_DTE_ID_C- Date in numeric format (YYYYMMDD)
COB_DAY_N- Days
COB_MO_C- Month Number
I am not getting how to modify below query without much case when statements so that if I pass monthend date (20110531), It should give me
20110228
20110331
20110429
Its giving me
20110331
20110429
20110530
select * from (
select DISTINCT MAX (COB_DTE_ID_C) OVER (PARTITION BY COB_MO_C)
from COB
where COB_DTE_ID_C < 20110531
and COB_DTE_ID_C > to_char(add_months(to_date(20110531,'YYYYMMDD'),-3),'YYYYMMDD')
and upper(COB_DAY_N) NOT IN ('SATURDAY','SUNDAY')
ORDER BY 1
) where rownum < 4
View 13 Replies
ADVERTISEMENT
Aug 13, 2010
Past 13 MonthEnds from a input date
(i.e.: 6/13/2010 is supplied, then query should be able to retrieve 5/31/2010, 4/30/2010, 3/31/2010, 2/26/2010, 1/29/2010, 12/31/2009?.....5/29/2009)
If the month end date falls on a weekend (Sat or Sun), then it should have the prior Friday's date.
View 18 Replies
View Related
Jun 4, 2013
I 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,
View 2 Replies
View Related
May 15, 2013
I'm a software developer, not an Oracle DBA
Our product runs a lot of stored procedures in the background to do various things. These stored procedures obviously include a ton of select statements, insert statements, etc. Some of them get pretty complex. Once in a while, we run across the following error: "ORA-03127: no new operations allowed until the active operation ends(3127)." Once this happens, pretty much everything breaks with this error for a while. Eventually (LOOOONG time), this error "resolves itself" and things start working again. Conceptually, I understand that there seems to be some blocking operation on the DB, but because we run a LOT of stored procedures and SQL statements, it's extremely difficult to pin this down.
View 7 Replies
View Related
Dec 6, 2011
I have a table:
create table FIELDS
(
FIELD_NAME VARCHAR2(30) not null,
PRG_FIELD NUMBER not null,
LENGTH NUMBER
);
with
INSERT INTO FIELDS VALUES('FIELD1', 1, 3);
INSERT INTO FIELDS VALUES('FIELD2', 2, 3);
INSERT INTO FIELDS VALUES('FIELD3', 3, 4);
INSERT INTO FIELDS VALUES('FIELD4', 4, 2);
INSERT INTO FIELDS VALUES('FIELD5', 5, 1);
I need to insert in a table:
create table STUFF
(
FIELD_NAME VARCHAR2(30) not null,
FSTART NUMBER not null,
LENGTH NUMBER
);
And the output I want is:
INSERT INTO STUFF VALUES('FIELD1',0,3);
INSERT INTO STUFF VALUES('FIELD2',3,3);
INSERT INTO STUFF VALUES('FIELD3',6,4);
INSERT INTO STUFF VALUES('FIELD4',10,2);
INSERT INTO STUFF VALUES('FIELD5',12,1);
So each field starts where the previous (ordered by PRG_FIELD asc) ends.
I think the query should use both lag and connect by but I haven't had any luck writing it. The problem is that all the examples I've seen around, using connect by prior, utilize 2 fields with different names, es connect by prior emp_id = mgr_id. Instead I should do something like connect by prior prg_field = prg_field-1 but that doesn't seem to work.
PS: I don't necessarily need to do this, I have a guy manually writing the inserts, this is just an exercise I would like to figure out
View 1 Replies
View Related
Jun 29, 2011
I am trying to insert a row in a table and getting the below error.
SQL> insert into tbl_force_charging(ANI, date_time, durations,src, circleid)
2 values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR'
)
3 ;
values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR')
*
ERROR at line 2: ORA-01830: date format picture ends before converting entire input string
Table Structure is
Name Null? Type
----------------------------------------- -------- ---------------------------
ANI VARCHAR2(10)
DATE_TIME DATE
DURATIONS VARCHAR2(10)
SRC VARCHAR2(10)
CIRCLEID VARCHAR2(10)
SQL>
View 11 Replies
View Related
Feb 26, 2013
when i run this query i am facing date format error.
select sbrueregister.UEIMSI,sbrueregister.fapid,sbrfapslid.slid,sbrfapslid.ACTIVATION_TS,sbrfapslid.DEACTIVATION_TS from SBRFAPSLID INNER JOIN sbrueregister ON sbrfapslid.fapid=sbrueregister.fapid where sbrfapslid.slid='1234567890' and sbrueregister.registeredat between TO_DATE('2013-02-1.12.0. 10. 123000000','YYYY-MM-DD HH.MI.SS.SSSSSS')and TO_DATE('2013-02-1.12.9.10.123000000', 'YYYY-MM-DD HH.MI.SS.SSSSSS');
ORA-01830: date format picture ends before converting entire input string
View 1 Replies
View Related
Feb 21, 2010
I am using
SELECT TO_CHAR(TO_DATE((:BILL_VALUE),'J'),'Jsp') FROM sys.dual;
its givinig right result for
1023411->One Million Twenty-Three Thousand Four Hundred Eleven
but if i will change it to 10234111 then this query giving an error
ORA-01830: date format picture ends before converting entire input string.
Is there any limitation or is there any other built in function to get the number to a word.
View 2 Replies
View Related
May 16, 2013
Using 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).
View 5 Replies
View Related
Aug 25, 2013
We have to get some data from U98 (saixdbU98) in UTF-8 format in Excel sheet.We are having queries ready for this. These queries bring data which have Japanese characters.But when we run the Select queries then in the result Japanese chars are garbled. It comes in ????.
let us know what process we have to follow to get the result in UTF-8 format in Excel sheet.here is the code
set sqlblanklines on;
SET DEFINE OFF;
set linesize 1000
set long 1000000
[code]....
View 4 Replies
View Related
Mar 31, 2010
The thing is i am generating a report in Excel through forms and excel template in 10g. After generating the Excel trough my code and Macros, i.e save excel, close excel and open excel.
After the excel report which got generated is opened the focus is not coming back to form, it keep on going busy and busy
I tried
form_exit,
go_block and also
Timer:DECLARE
timer_id Timer;
one_minute NUMBER(5) := 90000;
BEGIN
timer_id := CREATE_TIMER('Motor_Timer', one_minute, NO_REPEAT);
END;
etc and etc.
When i am using exit_form, the focus is coming back but the excel is not opening again.
View 2 Replies
View Related
Aug 13, 2012
For a couple of employees created in our system, the workflow display name is coming with 'AMERICAN' at the end which clearly looks like is the language setting for the user. ORIG_SYSTEM for this users in WF_LOCAL_ROLES is WF_LOCAL_USERS.
There is no option called WF_LOCAL_USERS in the Synchroinze WF Local Roles program.
View 2 Replies
View Related
Sep 17, 2013
I have a table of below structure:
CREATE TABLE Emp_addrs
(
EMP_ID NUMBER(15) NOT NULL,
ADDRESS_ID NUMBER(15) NOT NULL,
SITE_USE_ID NUMBER(15) NOT NULL,
SITE_USE_STATUS VARCHAR2(1 BYTE) NOT NULL,
SITE_USE_CODE VARCHAR2(30 BYTE) NOT NULL)
Insert Script code :
insert into Emp_addrs values ( '1207' , '1846', '2342','A');
insert into Emp_addrs values ( '1207' , '1846', '2343','I');
insert into Emp_addrs values ( '61618' , '165200', '261449','A');
[Code]...
A combination of emp_id & address_id can have multiple site_use_id's. I want to select the max(site_use_id) where site_use_status ='A'.
Now Site_use_status can have either = 'I' or 'A' value.
For a combination of emp_id and address_id , there could be cases when there is no record with site_use_status ='A'. In such cases I need to select the max(site_use_id) (and obviously site_use_status ='I').
Just to clear my requirements, out of the above records I want the following records:
'1207' , '1846', '2342','A'
'61618' , '165200', '261449','A'
'1003' , '1004', '1007','A'
'1005' , '1010', '2002','I'
'2005' , '2010', '3002','A'
View 9 Replies
View Related
Oct 22, 2012
i am using one query but not getting correct minutes.
here is my query:
v_Interval:= to_timestamp(v_temphrs,'HH24:MI:SS')-to_timestamp(v_outpunch1,'HH24:MI:SS');
v_TotalHrsMin1 := extract(hour from v_interval) * 60 + extract(minute from v_interval);
here v_interval datatype is "interval day to second" and v_temphrs datatype is varchar2 and value is : 12:00:00 and v_outpunch1 datatype is varchar2 and value is: 06:10:00
and v_totalHrsMin1 datatype is number.
here i should get value 370.
but i am getting value 350.
View 3 Replies
View Related
Jan 30, 2011
CREATE OR REPLACE VIEW V_CATALOGUER_REPORT
(COUNT, EVENT_USER, TO_DATE, SHORT_NAME)
AS
SELECT COUNT (DISTINCT A.PART_REF),A.EVENT_USER, TO_DATE(A.EVENT_DATE, 'DD-MON-YY'),B.SHORT_NAME FROM J_SUPPLY_CHAIN_HIST A, J_SOURCE B
[code]...
i created one view,i link two tables ,i can see the correct data in this view,but the result from front end application is not correct.i cant see the dates are correct.
View 1 Replies
View Related
Feb 7, 2013
1. I have 6 data bases, one process is running slowly, how do you find which data base that process belongs to?
2. I have 6 data bases, only one data base we have ASM instance, how do you find which data base has ASM?
View 1 Replies
View Related
May 15, 2012
I have made below query :
select job,case when deptno=10 then ename ELSE 'null' end e10
,case when deptno=20 then ename ELSE 'null' end e20
,case when deptno=30 then ename ELSE 'null' end e30
from emp
order by 1;
the output is:
JOB E10E20E30
ANALYSTnullSCOTTnull
ANALYSTnullFORDnull
CLERKnullSMITHnull
[code]...
but I want data in one row, i mean for example in case of JOB as 'CLERK' the output should be : i.e.
JOB E1 E2 E3
CLERK MILLER ADAMS JAMES
CLERK SMITH
the complete correct output which I want is:
JOB E1 E2 E3
ANALYST SCOTT
ANALYST FORD
CLERK MILLER ADAMS JAMES
[code]....
View 3 Replies
View Related
Nov 22, 2012
formulating sql query
Basically what I want is that I need to get desired result in such a way that, whenever Transaction type is Sales Order Issue, I want last TRANSACTION_COSTED_DATE of 'Intransit Shipment'
INVENTORY_ITEM_ID TRANSACTION_COSTED_DATE TRANSACTION_TYPE R
123 28-06-2012 21:36 Intransit Shipment
123 23-07-2012 01:25 Sales order issue 28-06-2012 21:36
123 30-07-2012 05:20 Sales order issue 28-06-2012 21:36
[Code]...
Lag with offset 1 doesn’t work as it will only go to previous row, What I want is that it should go to row above where transaction type is Intransit Shipment
Sample data and query I tried
with sampl_rownum_reset as
(select '123' inventory_item_id,
to_date ('28-Jun-2012 9:36:23 PM ', 'DD-MON-RRRR HH:MI:SS AM')
transaction_costed_date,
to_date ('28-Jun-2012 9:35:23 PM ', 'DD-MON-RRRR HH:MI:SS AM')
[Code]....
View 2 Replies
View Related
Nov 25, 2010
I 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
View 17 Replies
View Related
Aug 12, 2013
column with datatype Number contains length 1.Its not null.Tried to trim then also length is 1 ....How to find what is the character there..?
View 1 Replies
View Related
Dec 10, 2010
I have a list item with two values,0 and 1. However when i click on the list item scroll bar appears. My tester is saying for two items she doesn't need a scroll bar.
View 3 Replies
View Related
Oct 15, 2012
I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production version
I am having the data in following table -
drop table stud_fact;
create table stud_fact(stud_NM, LVL_CD,ST_DT_DIM_KEY,OVRNK) as select
'ABG Sundal','H','20110630','175' from dual union all select
[Code].....
View 8 Replies
View Related
Aug 25, 2013
We have to get some data from U98 (saixdbU98) in UTF-8 format in Excel sheet.We are having queries ready for this. These queries bring data which have Japanese characters.
But when we run the Select queries through pbrun (Power Broker) then in result Japanese chars are garbled. It comes in what process we have to follow to get the result in UTF-8 format in Excel sheet.
here is the code
--
set sqlblanklines on;
SET DEFINE OFF;
set linesize 1000
set long 1000000
set pages 50000
[code]...
View 1 Replies
View Related
Aug 3, 2011
I want to know which is the most efficient insert method among the followiing
1)using the hint append
or
2)bulk collect. Preferably I'd like to know which method of inserting for say 2-5 millions rows is the best.
View 6 Replies
View Related
Mar 22, 2011
Test case: drop table test;
create table test (id number, last_name varchar2(15), first_name varchar2(15), empno varchar2(15))
select * from test;
insert into test values (143,'frank','kadel,watson','j2098,k09876');
insert into test values (143,'steve','kadel,watson','j0987i,kuy765');
[code]....
The requirement is as follows: I need to split the rows by first_name and assign the respective empno in the results child rows if there is any.
For example:- Where id = 143, the resultset should be like this.
ID LAST_NAME FIRST_NAME EMPNO
---------- --------------- --------------- ---------------
143 frank kadel j2098
143 frank watson k09876
143 steve kadel j0987i
143 steve watson kuy765
sofar, i am able to come with the query to split the records by last name but unable to find the way to extract the respective empno and assign to the splited records correctly.
SQL> select id, last_name, EXTRACTVALUE(x.COLUMN_VALUE, 'e') first_name, empno
2 from test,
3 TABLE(XMLSEQUENCE(XMLTYPE('<e><e>' || REPLACE(first_name, ',', '</e><e>') || '</e></e>')
4 .EXTRACT('e/e'))) x
5 ;
[code]...
View 8 Replies
View Related
Sep 26, 2013
I have a table with following structure:
CREATE TABLE ID_comments
(
ID CHAR(10 BYTE) NOT NULL,
S_COMMENTS VARCHAR2(255 BYTE),
P_COMMENTS VARCHAR2(255 BYTE),
C_COMMENTS VARCHAR2(255 BYTE)
);
For each Id, I can have multiple records.
Below is the insert script of one of the ID:
Insert into ID_comments values ('0813654254','','JR/0813653606 single','');
Insert into ID_comments values ('0813654254','','JR/0813653606 single','');
Insert into ID_comments values ('0813654254','','JR/0813653606 SINGLE','');
Insert into ID_comments values ('0813654254','','JR','');
[code].......
Now I want to select only one record from this table for an ID, which will have "not null" values for s_comments,p_comments,c_comments columns. If for some ID , there is no "not null" row for any column, then pick up the "null" row/value for that column.
View 14 Replies
View Related
Jan 30, 2012
My colleague sent me a sql script to copy and execute in Toad for Data Analysts (see attached). Once I execute I get error messages on the date and a few other lines. My question is should I completely rewrite the script?
I would rather have this in a grid format anyway with the same data instead of how this script is pulling. I'm very green when it comes to this (if you didn't notice) and need some insight esp. on the date format considering the date column has 10 digits i.e. 1209105576.
Attached File(s)
Doc1__1_.doc ( 41K )
Number of downloads: 2
View 1 Replies
View Related
Oct 3, 2012
I have a form which has a Number field. I set it's Format Mask property to 9,99,99,99,99,999 so that I can format it comma separated and to restrict the user to enter decimal values. If the user enter any format other than this for example any decimal values, it is showing the message FRM-40209:Field must be of the form 9,99,99,99,99,999 which is fine. But when the user enters a decimal value and try to save the form it pop up this message several times.
I tried to catch this error in ON-ERROR trigger and display a message. But this is also pop up several times. I tried to raise form_trigger_failure also. This is also not working. I want this error message to come only once.
View 2 Replies
View Related
Feb 28, 2013
I am using view AB_EV_VIEW created with UNION ALL of 10 tables (ex. AB_EV_1, AB_EV_2.... )
I want to know the data comming in select * from AB_EV_VIEW is from which table ?
Suppose AB_EV_1 is having column ID, NAME
sql> select * from AB_EV_VIEW
100, A1
200, A2
300, A3
400, A4
500, A5
View 3 Replies
View Related
Oct 2, 2013
Grid Version: 11.2.0.3 OS: Red Hat Enterprise Linux 5.6 Node2 of our two node RAC got rebooted. Upon reboot, CRS and ASM instance came up. But the DB didn't come up. How can I check if DB is linked to CRS startup ?How can I enable DB startup upon CRS startup ?
View 3 Replies
View Related
