PL/SQL :: Calculating Correct Offset For SQL
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
ADVERTISEMENT
Oct 17, 2012
I see a strange result when I check for the offset in different databases,From the same SQL*Plus window.
SQL>
SQL> conn sys/**********@db1 as sysdba
Connected.
SQL>
SQL> col sysdate for a20
[code]....
where this difference comes from?why in db1 it shows +10:00, and on db2 it shows +11:00?
View 11 Replies
View Related
Jun 21, 2010
I have a one more query
col1 col2
3-18083017013-Standard
3-18083225012-Significant
3-18082775913-Standard
3-18082426912-Significant
3-18082097112-Significant
3-18081539512-Significant
3-18081946712-Significant
3-18080523612-Significant
3-18076873112-Significant
3-18076872712-Significant
3-18080522412-Significant
3-18080064612-Significant
3-18070899912-Significant
3-18081155713-Standard
3-18071160013-Standard
3-18077564213-Standard
3-18079220312-Significant
3-18073201313-Standard
note:
3-Standard 240 min
2-Significant 120 min
1) i need to calculate the percentage value.
general form is :
total number of col1_values(count) in 120 min/count(col1) *100
conditions to calculate percentage:
1)need to ca
View 16 Replies
View Related
Jul 15, 2011
I am trying to create a function that when called will add the salary and commission a certain way to return an employee's annual salary.Here's my code
create or replace function Get_Annual_Comp
(Sal in number, Commission in number)
return number
as
[code]...
When I run the query, I get the proper rows return; however, my function does no calculation. If I input random numbers, I get the proper value returned. What I want is for my function to return the salary and commission of the employee specified in my select's where clause to be calculated as an annual salary.
View 9 Replies
View Related
Jul 10, 2012
10g
I have a table that mirrors remote table. Time info on the source table is in milliseconds (lowest value is 1338699905613)
I'd like to have the target table with DATE column as an addition to the source table columns.
I'm wondering what would be the way to calculate DATE value on insert?
The calculation should be from UTC (in milliseconds) into DATE.
The target table is :
test (ID NUMBER(19,0), STARTTIME NUMBER(19,0), RECORD_DATE DATE);
and data comes with insert from source table (mirror in remote db)
insert test (ID, STARTTIME) select id, STARTTIME from test_o
View 5 Replies
View Related
Jul 12, 2011
How can I calculate (by SQL) outstanding stock on a First In First Out basis per month?
Table A
Month Oustanding Stock Sales Expired Stock
January 200 0 0
February 100 100 0
March 100 0 0
April 50 150 0
June 50 0 100
July 50 0 0
Desired Results
Month Outstanding Stock
January 0
February 0
March 50
April 50
June 50
July 50
View 5 Replies
View Related
Aug 15, 2011
I have a field in Customers table called shipeddate....
I wnat to check the number of hours an item which has a shipeddate is in the store room to the current datetime...
But the business hrs of the store room are from 8am-5pm..
So when a shipped date is 4pm on MOnday
and i am checking on 9 am Tuesday the number of hrs shud be 1(4-5 of Monday)+1(8-9 of tuesday) =2hrss..
How can i achieve this...
View 2 Replies
View Related
May 8, 2010
I am using the below sql query to calculate working hours. The problem which i am facing is that query is taking lot of time to calculate the working hours. reduce the execution time of this query or if there is any other way to calculate working hours
The following query take 63.499 sec
SELECT sql_calc_found_rows gstime,
MAX(stoptime) AS mx,
MIN(starttime) AS mn,
[Code].....
View 6 Replies
View Related
Nov 22, 2010
A function should accept two parameters: from_date and to_date which returns no.of Saturdays and Sundays between these dates and also show the dates of those weekends.
View 1 Replies
View Related
Sep 16, 2011
I am trying to find sum for one record for each partition but while taking that timestamp giving me bit trouble, i have tried to reproduce the table and some little data
CREATE TABLE TEST_COUNT
(END_TIME DATE
,SUCCESSFUL_ROWS NUMBER
,FAILED_ROWS NUMBER
,TBL_NAME VARCHAR (4)
,PARTITION_NAME VARCHAR (240) )
[code]....
View 11 Replies
View Related
Jan 2, 2013
I have to create the following table. The fields Trend_Date, Price and Trend are already given. I have to calculate the field permanently and to insert the value in this permanent table.
Fields:
The field price belong to the value of a product during the trade.
The field trade_date belongs to the moment of the trade.
The field trend belongs to the future behavior of the the price. Here, the price of the present moment is compared to the following price (possible characteristics: 'UP', 'DOWN', 'STABLE').
The field permanently belongs to the time (in seconds) how long the value of the field Trend_Date (depending on the price) is still true.
For example:
Row 1: The trend in row 1 is 'UP' and it has a price of '11'. Until row 3 this remains true (the price is greater or equal to 11). In this case, the difference between row 1 and row 3 are 9801 (rounded) seconds.
Row 2: The trend in row 2 is 'DOWN' and it has a price of '12'. This remains true till to the end (the price is never greater than 12) In this case, the difference between row 2 and row 11 are 97346 (rounded) seconds. To calculate the 97346 seconds the field has to consider that between row 2 and row 11 are two days. There will be no trade between 18:00 and 07:00 o'clock. This belongs to 7 hours for each days, in seconds (2*46800) 93600.
-> 190945-93600 = 97346s
Row 6: The trend in row 6 is 'UP' and it has a price of '5'. This remains true till to the end (the price is never smaller than 5) In this case, the difference between row 6 and row 11 are 65729 (rounded) seconds. To calculate the 65729 seconds the field has to consider that between row 65729 and row 11 are one days. There will be no trade between 18:00 and 07:00 o'clock. This belongs to 7 hours for each days, in seconds (1*46800) 46800.
-> 112528-46800 = 65729s
Row 9: The trend in row 9 is 'STABLE' and it has a price of '8'. Until row 10 this remains true (the price is equal to 8 ). In this case, the difference between row 9 and row 10 is 14418 (rounded) seconds.
Row 11: Is empty because there are no values to compare.
Example Table
TRADE_DATE --PRICE --TREND --permanently
02.01.13 11:21:42,720000000--11--UP--9801
02.01.13 12:44:03,236000000--12--DOWN--97346
02.01.13 14:05:03,845000000--11--DOWN--92485
[Code]....
View 16 Replies
View Related
Oct 17, 2012
[URL] ...... Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
I have the following table
CREATE TABLE "ALLOCATEASSOCIATES"
( "PROJID" VARCHAR2(30) NOT NULL ENABLE,
"ASSOCIATEID" NUMBER(*,0) NOT NULL ENABLE,
"ALLOCATIONSTARTDATE" DATE,
"ALLOCATIONPERCENT" NUMBER(*,0),
[code]...
Given that
1. An associate must be allocated to at-least and a maximum of 100% at any given point of time
2. User selects 2 dates between which inconsistency of allocation needs to be displayed
If the end user selects 1st Apr 2012 and July 31st 2012 between which reports needs to be generated, am looking for the following output
ASSOCIATE_ID FROM_DATE TO_DATE ALLOCATION_INCONSISTENCY
2 01-APR-12 15-APR-12 75
2 16-APR-12 15-JUN-12 25
2 16-JUN-12 30-JUN-12 50
The Allocation_Inconsistency denotes that the associate has a deficit of allocation between the 2 dates. The associate with ID 2 has a deficit of 75% of allocation from 1st Apr 2012 till 15th Apr 2012. Similarly 25% deficit between 16th Apr 2012 and 15th June 2012 and so on so forth. However, there is no allocation deficit for the month of July as he is allocated 100% for this month and hence is not appearing in the expected output.
View 5 Replies
View Related
Sep 20, 2012
11.2.0.2 on Solaris..I have such a large post on a very basic space calculation.
We have several tablespaces starting with WLMCS in our DB..I just wanted to calculate the total space consumed in the disk by all these tablespace combined .When I queried DBA_DATA_FILES.MAXBYTES and DBA_DATA_FILES.USER_BYTES , I've noticed that ,
When AUTOEXTEND is NO: MAXBYTES is 0 for these datafiles . But USER_BYTES won't be 0 for these files
When AUTOEXTEND is YES: MAXBYTES will be a non-zero value for these datafiles . USER_BYTES won't be 0 either for these files-- Not including datafile names for better readability.
SYS > select tablespace_name, maxbytes/1024/1024, user_bytes/1024/1024, autoextensible from dba_data_files where tablespace_name like 'WLMCS%';
TABLESPACE_NAME MAXBYTES/1024/1024 USER_BYTES/1024/1024 AUT
------------------------- ------------------ -------------------- ---
WLMCS_DATA 32767.9844 32766.9375 YES
WLMCS_DATA 32767.9844 31615.875 YES
WLMCS_DATA 32767.9844 16419 YES
WLMCS_OAT_DATA 32767.9844 32766.9375 YES
[code]....
11 rows selected.To calculate the space consumed , I made 2 assumptions.Are the below 2 assumptions right?
Assumption 1. Whenever MAXBYTES = 0 , USER_BYTES should be considered for the space calculation.
Assumption 2. Whenever you have non-zero values for both MAXBYTES and USER_BYTES , MAXBYTES should be considered for the space calcuation.I did the calculation (adding up of) based on the above assumptions. Is this calcualtion Correct ?
-- Not including datafile names for better readability.
TABLESPACE_NAME MAXBYTES/1024/1024 USER_BYTES/1024/1024
------------------------- ------------------ --------------------
WLMCS_DATA 32767.9844 32766.9375
WLMCS_DATA 32767.9844 31615.875
WLMCS_DATA 32767.9844 16419
WLMCS_OAT_DATA 32767.9844 32766.9375
[code]....
View 6 Replies
View Related
Sep 23, 2010
My time zone has the offset of 2 hrs during summer and 1 hr during winter.If I want Oracle to tell me what was offset for particular day for example I want to know the offset for February 01, 2010 and August 01, 2010, is it possible?
View 1 Replies
View Related
Sep 2, 2011
I have a large table and want to calculate just a few values. Therefore, I don't want to create a new table, I want to update the table. Here an example:
I want to calculate the VALUE_LAG with ID = 4 only (-> two values).
create table zTEST
( PRODUCT number,
ID number,
VALUE number,
VALUE_L1 number );
[Code]..
I tried this, but obviously, windows functions are not allowed in the update statement.
update zTEST
set VALUE_L1 = lag(VALUE) over (partition by PRODUCT, order by ID)
where ID = 4
How can I do this?
View 12 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
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
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
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
Feb 19, 2009
A report we developed gives correct output when run in the report designer's previewer (paper layout). But when we deploy the report in the web server, give the same inputs and run it it gives the wrong output. I.e. it shows extra rows that should not be there.
Why do you think this is? We are unable to locate the problem source.
View 2 Replies
View Related
Feb 20, 2012
I don't know what column name would there for my new view. so can i use dynamic SQL in View so that I can get correct column name?
View 2 Replies
View Related
Aug 30, 2013
1) I got column date of joining which accepts date in below format DD-MON-YYYYDD-MON-YYMON-DD-YYYYMON-DD-YYMonth DD,YYYY Question:-
how do i check whether all dates in Date of joining column are in above format or not using sql query? 2) I got one more date column which accepts date in below formatMMDDYYYYYYYYMMDDMM/DD/YYYYMM/DD/YYYYYY/DD/MM Question:-
how do i check whether all dates in date column are in above format or not using sql query?
View 26 Replies
View Related
Jun 28, 2011
When I run the code below It runs very Long. It updates SUSR5 in the TEMPTABLE3 that has 112000 records. If I Change it when c>m to 2 to test. It runs very fast. The value for m is always between 10000 and 12000. That How many times it must loop to update the correct records.
DECLARE
a VARCHAR(50);
c NUMBER:= 1;
m NUMBER;
[Code]....
View 23 Replies
View Related
Apr 9, 2013
I am testing nested blocks, but my logic is apparently not correct.
I am running this script:
DECLARE
v_one number(1) := 1;
v_two number(1) := 2;
[Code]....
I receive the output from only one of the nested blocks:
bad
PL/SQL procedure successfully completed.
SQL>
I understand that I don't need nested blocks for the example above, but this was just a condensed version of what I'm trying to do. I think nesting blocks will be easier to read and maintain, instead of having a huge CASE statement.
How can I execute only the nested block for which the condition is true and ignore the nested blocks that follow?
Are nested blocks not the correct answer here? Should I be looking at invoking procedures/functions instead?
View 8 Replies
View Related
