PL/SQL :: Order By Business Units?
Mar 12, 2013i have business units for which i have multiple department ids so just wanted to have dept ids order by business units
View 12 Replies
ADVERTISEMENT
SQL & PL/SQL :: Units Of Cost?
Sep 28, 2010I was questioned in an interview what is the unit of cost and based on what was it calculated.
View 1 Replies View RelatedStored SQL And Returning Data In Different Units?
Jan 30, 2007I work on a client-server application, where users need to be able to run rather complex queries.
We currently have most of the queries defined in views on the Oracle database server and the client application simply downloads the data (i.e. SELECT * from example_view). This is good for us as we can maintain these queries without releasing new versions of the client tool.
However we have some queries implemented by a colleague that have caused a lot of trouble (efficiency and quality) and these are stored client-side.
The issue I have is that these client side queries can return records in different units (i.e. in standard cubic metres, or barrels of oil etc), as the SQL is defined at runtime on the client, and I want to know the best way to replicate this with SQL stored server-side.
The client-side SQL has column definitions such as: SELECT oil_production * decode(&unit,'Nm3',.948,'Sm3',1,'MMBOE',6.0924,1) ... The &unit parameter is then replaced by the appropriate text (i.e. 'Sm3') before the query is sent to Oracle.
Is there anyway to pass variables to server-side SQL and get a recordset back? I don't think PL/SQL procedures can do this? and views can't contain bind variables.
SQL & PL/SQL :: First Business Day Of A Given Month?
Dec 6, 2010I need to calculate first business day of a given month . Below is complete explanation
Business day=sould not include weekends and holidays.
In a table say ACTIVITY_XX I have all the month begin dates say 01-JAN-2010,01-FEB-2010,01-MAR-2010,01-APR-2010 and so on..and I have a HOLIDAY table where all the holidays are stored.
So using the above info I need to calculate the first business day for a given month. I guess this cannot be done by using a simple SQL query? I was wondering how could it be written using a PL/SQL function.
I'll be passing the month begin date as parameter..so the function should return the first business day for that month.
Reports & Discoverer :: Calling Program Units?
May 8, 2013How to call program unit(procedure/function/package) in to report is it like formula column & placeholder?if not, how to call external (procedure/function/package) in to report
View 1 Replies View RelatedSQL & PL/SQL :: Calculating The Business Hours
Aug 15, 2011I 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...
PL/SQL Program Units - Global Variable Specification / Type
Jul 31, 2012This assignment allows you to pick your own subject in which you Code a Package Header that contains the following:
Global Variable Specification
Type
Four (4) Procedure Specifications
Two (2) Function Specifications
Submit your code in a series of Notepad or Wordpad documents. Include the code for the assignment and the output that is realized from running the code. Note: You are responsible for creating the database on which the project will run. This includes the responsibility for loading the database with sufficient data.Grading for this assignment will be based on answer quality, logic/organization of the project to include the compilation, and execution of the project code.
SQL & PL/SQL :: Store Sub / Super Script Characters Which Is Used In Chemical Units?
Mar 16, 2011I want to store sub/super script characters which is used in chemical units like NO3 or H+. I read blogs. Using select to_char('1'||chr(185))from dual returns 1¹. But how can i handle sub three and super plus ?
View 1 Replies View RelatedSQL & PL/SQL :: Create Update Trigger That Allows Any Quantity Reduction Of 5 Units
Oct 27, 2011i am trying to create a update trigger that allows any quantity reduction of 5 units and if the reduction is more than 5 units it blocks it and just reduces it by 5.here are two scenarios
SQL Update statement where the new Quantity value being smaller than 5 is allowed by the trigger.
-- old qty 20
update purchase_order
set quantity = 18
where po_no = 'PO11';
-- new qty 18
SQL Update statement where the new Quantity value being larger than 5 is only reduced by 5 by the trigger.
--old qty 25
update purchase_order
set quantity = 19
where po_no = 'PO15';
-- new qty 20
i just have the basic trigger code, but i think it is the math that i am not getting (i was never good at math)
create or replace trigger purchase_quantity_updt
before update of quantity on purchase_order
for each row
when (new.quantity < old.quantity )
begin
-- not sure what to put here
end;
Performance Tuning :: Order Of Steps In Execution Path And Order Of Predicates In The Plan
Mar 20, 2012Which step in the following plan is the first step of execution
I reckon it is "TABLE ACCESS BY INDEX ROWID| BANK_BATCH_STATE"
Is that correct?
In the "Predicate Information (identified by operation id):"
section the predicates - access and filter for the step "TABLE ACCESS FULL | PYMNT_DUES" are displayed first
Isn't there any relation between the order of execution steps and the order in which predicates are displayed?
Execution Plan
----------------------------------------------------------
Plan hash value: 538700484
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2364 | 15 (14)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 2364 | 15 (14)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 2364 | 14 (8)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 2313 | 13 (8)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 2281 | 12 (9)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 2255 | 11 (10)| 00:00:01 |
|* 7 | HASH JOIN | | 1 | 175 | 6 (17)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | INDX_2 | 12 | 612 | 2 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | PYMNT_DUES | 43 | 5332 | 3 (0)| 00:00:01 |
| 10 | VIEW PUSHED PREDICATE | | 1 | 2080 | 5 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 154 | 5 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 103 | 4 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID| BANK_BATCH_STATE | 1 | 32 | 2 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | INDX_BBS_1 | 3 | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID| DAILY_CHECK | 1 | 71 | 2 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | INDX_SEARCH | 1 | | 1 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | INDX_2 | 1 | 51 | 1 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | INDX_IAM_SR_NO | 1 | 26 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | INDX_2 | 1 | 32 | 1 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | INDX_2 | 1 | 51 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------
Business Rules For Employee Table
Aug 19, 2013We need some transformation rules on Source employee table which comes from SAP and want to load in Target table.Basic idea is we need to calculate time when position was not held by sub owner so . . . Etc
Source looks like:
POSITION_TCD START_DT END_DT SUBTYPE POSI_HOLDER
Example 1:
10005822 10/16/2003 11/20/2008 A008 105404
10005822 10/16/2003 3/31/2009 A999 105404
10005822 6/23/2008 7/5/2009 A008 124530
10005822 11/21/2008 8/31/2009 A008 105404
10005822 8/31/2009 4/16/2010 A008 105323
10005822 4/19/2010 12/31/9999 A999 131995
10005822 7/5/2010 12/31/9999 A008 131995
Example 2:
10084408 5/3/2010 12/31/9999 A008 130591
10084408 3/21/2011 5/17/2011 A008 132725
10084408 5/3/2010 1/2/2011 A999 130591
After business rules data should look like:
Target
Example 1:
POSITION_TCD POSI_HOLDER SUBS_OWNER EMP_ASSI_TYPE START_DT END_DT
10005822 105404 105404 Hold=Subs 10/16/2003 11/20/2008
10005822 124530 105404 Hold<>Subs 6/23/2008 3/31/2009
10005822 124530 No Substantive No Substan 4/1/2009 7/5/2009
10005822 105404 No Substantive No Substan 11/21/2008 8/31/2009
10005822 105323 No Substantive No Substan 8/31/2009 4/16/2010
10005822 No Holder 131995 No Holder 4/19/2010 7/4/2010
10005822 131995 131995 Hold=Subs 7/5/2010 12/31/9999
Example 2:
10084408 130591 130591 Hold=Subs 5/3/2010 1/2/2011
10084408 130591 No Substantive No Substan 1/3/2011 12/31/9999
10084408 132725 No Substantive No Substan 3/21/2011 5/17/2011
SQL & PL/SQL :: Difference Between Business Days And Hours?
Sep 19, 2010format of dtActivityStartDate/dtActivityFinishDate: 2010-09-17 14:50:51.150 Note: Both dtActivityStartDate/dtActivityFinishDate
vcActivityName = Process Request
usdFuncTimeCalc (vcActivityName,dtActivityStartDate, dtActivityFinishDate)
i need to calculate time elasped for that type of activity following are the rules:
(If Process Request is the activity)
Working Days: Monday through Saturday
Hours of Operation: 9AM 5PM
only working hours of day need to the counted like for example if it is sep 15 11 Am is dtActivityStartDate & Sep 17 is dtActivityFinishDate is 10 Am. then time elapsed is 11am to 5pm on sep 15 , 9 to 5 on sep 16 & 9 to 10 on sep 17 so total should be
6+ 8 + 1 = 15 hours + minutes.
format of date time: 2010-09-17 14:50:51.150
vcActivityName = Process Request
Don't worry about process request..
PL/SQL :: Query To Calculate Business Days
May 20, 2013i have table name order table . in that i have column name install date,order date.
the difference bet ween order date and install date should be greate than or equal to 3 working days.
fri and sat are holidays.
i need one query to find the difference betwwen orderdate and install date is greate than 3 working days
PL/SQL :: Calculate Business Hours Between Two Dates Within A Cursor
Jul 24, 2013I need a similar function to determinate difference between two dates, but i need other business hours; Monday - Friday: 9:00 - 21:00 (this is OK)Saturday: 09:00 - 14:00 (and this is my problem, how to add this condition in this function)
View 4 Replies View RelatedInstall Oracle E-Business Suite R12 On Mandriva - Domain Name?
Sep 9, 2010I want to try installing Oracle E-business suite on Mandriva But when I try e.g. domainame I get blank.
A) How can I check my domainname ?How can I set it?Here is the hosts file
[Select all] [Show/ hide]
127.0.0.1 localdomain
192.168.0.2 david.domain.com david
Reports & Discoverer :: Access To Multiple Business Areas?
May 5, 2011I have been asked to see why a sister company cannot access multiple business areas through a single responsibility. At present, if they log in through a GL responsibility they can see all GL folders, items and the returned data. If they then go to the AP business area, they can see the folders and items, but when they run a report they get an error message 'Query returned no data' - but if they run the same query using an AP responsibility they get the correct results. I have looked at their user set up, and it seems fine i.e. has access to all business areas and required priveledges.
View 4 Replies View RelatedFunction To Find Business Days Between Two Dates - Query Tuning
Mar 17, 2011I have a query that uses a function to find the business days between two dates.It sums the total number of days between two dates per employee to find the total days for the past 30, 90, or 365 days.
The problem is that the query takes 21 second to return the last 30 days.Over 70 second to return the last 90 days and over 140 second to return the last 365 days.Do you know how I could tune the query to return faster? Below is the query for the last 30 days:
select dwt_emp_id, SUM((SELECT GET_BDAYS(DWT_DATE,DWT_CREATE_DATE) FROM DUAL))
from dwt_dvt_work_time where dwt_create_date > sysdate - 30
and dwt_hours > 4 and dwt_usr_uid_created_by <> -1 group by dwt_emp_id order by dwt_emp_id
Here's the function:
CREATE FUNCTION get_bdays (d1 IN DATE, d2 IN DATE)
RETURN NUMBER
IS total_days NUMBER(11,2);
holiday_days NUMBER(11,2);
[code]....
SQL & PL/SQL :: Result Order In 2 Table Joins Without Order By
Sep 6, 2011I have below tables,
describe rpthead
Name Null Type
--------------------------- -------- -------------
RPTNO NOT NULL NUMBER
RPTDATE NOT NULL DATE
RPTD_BY NOT NULL VARCHAR2(25)
PRODUCT_ID NOT NULL NUMBER
describe rptbody
Name Null Type
------------- -------- -------------
RPTNO NOT NULL NUMBER
LINENO NOT NULL NUMBER
COMMENTS VARCHAR2(240)
UPD_DATE DATE
The fact is that we store some header in RPTHEAD and store real data in RPTBODY, the question is that if I use below SQL to query all data for a 'PRODUCT_ID'.
SELECT t0.LINENO, t0.COMMENTS, t0.RPTNO, t0.UPD_DATE
FROM RPTBODY t0 , RPTHEAD rpthead
WHERE
(
t0.RPTNO = rpthead.RPTNO
AND
t0.UPD_DATE>=to_date('1970/01/01 00:00:00','YYYY/MM/DD hh24:mi:ss')
AND
rpthead.PRODUCT_ID IN ('4647')
)
I do not want to have 'ORDER by' clause since data set is too large, the sorting takes long time, is there any way to get the result rows in the order sorted by RPTNO? We have the index for RPTNO on RPTBODY.
PL/SQL :: Wait Event Starts With SQL*Net Message From Client - Time Wait 178577 Units
Dec 11, 2012I am trying to look at wait events for a long running query in TOAD.I start the query on one instance of TOAD and open the Session Browser on another instance.But I am surprised to find that in "TOtal Waits" on the RHS-> SQL*Net message from client is the longest time taking and is already -> 178577 units whereas I have just started the query.
Whereas in the Current Waits it shows DB File Scattered Read currectly as some seconds.
SQL & PL/SQL :: Order By With IN
May 24, 2010Its a very small query
SELECT * from EMPLOY
WHERE fk_dept_id IN ( select id
from DEPARTMENT
ORDER BY END_DT DESC)
I cant use IN.
NOTE: Select * should be done from Employ only no joins and all.
SQL & PL/SQL :: ORDER BY In A Particular Order
Jul 17, 2012oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
I have data like:-
event_idiss_nbr
171350 2012051WR
171350 2012041WR
171350 2011081CC
171350 2012041WA
171350 2012031WW
171350 2011081WW
171350 2011081CR
171350 2011081CA
The possible last two characters of the iss_nbr can be:-
WW, WR,WA,CR,CA,CC
And I want it to be ORDER BY as follows.
WR
WA
CR
CA
WW
CC
So for example, in above case, it should be
event_idiss_nbr
171350 2012051WR
171350 2012041WR
171350 2012041WA
171350 2012031WW
171350 2011081CR
171350 2011081CA
171350 2011081WW
171350 2011081CC
How can I do it while loading the table?
SQL & PL/SQL :: Order By Row ID
Mar 20, 2012I am having table without any primary key. In this table, only inserts and deletes are performed , no update operation.
Is it safe to use order by rowid on such a table ? Does by applying order rowid, is it possible to check order in which rows were inserted in this table ?
Select Vs Order By
Nov 9, 2006In a query which gets executed first:
Select clause or Order by clause? First the columns specified in the select clause are fetched and then ordered or is it the vice -versa? In a query if a psuedo-column rownum is selected, then if we use order by on the rownum column, what happens?
What would be the output of the following query and why?Which part of the query is first executed, is it select clause or order by clause?
select empno,ename,rownum from emp order by rownum desc;
Tab Order On A Form
Feb 25, 2010I am having trouble with tab order on a Form.
I rearranged the fields on a form, so I want the tab order to be different. I rearranged the fields in the object navigator to be in the order I want them to tab. I made sure the Previous Navigation Item and Next Navigation Item were set to null for all the fields.
Still it keeps the old tab order.
I also tried explicitly specifying the Previous Navigation Item and Next Navigation Item. Same thing, it kept the old tab order before I rearranged the fields. Previous Navigation Item and Next Navigation Item are both null.
There are no triggers on these fields. All fields are in the same block. I am using Oracle Form Builder version 9.
SQL & PL/SQL :: Use Of Order By In Varchar2
Feb 21, 2011I have a column named "col1" with datatype "varchar2(10)" and row wise entries like "1,1A, 2,3...,10,2A,..." like. I want to order it like "1, 1A ,2,2A, 2B,3... 10...".I tried it with to_number() but it gives me
1,10,11,2,....like that.
SQL & PL/SQL :: Order By Clause?
Mar 12, 2013I have requirement to create an XML structure through stored procedure. I need to Order some of the columns in ascending order before I format them into the xml structure. I am pretty novice to creating an output into XML format, but attached is the query I came up with (without order by). This works perfect, but now the requirement is to order by - cls_cd, and within cls_cd, again order by - cat_cd. I am not able to do this.
View 2 Replies View RelatedSQL & PL/SQL :: How To Get Order By A Column
Jun 17, 2013I am one table create tt1(A varchar2(6),B number(5));
insert data in this column
tt1
A B
---------------
F1 1
F365 2
F10 3
F65 4
F55 5
Q. how to get order by a column. i wan't to
tt1
A
--------------------
F1
F10
F35
F65
F365
Q. how to get order by a column , i wan't to
tt1
A
--------------------
F0001
F0010
F0035
F0065
F0365
SQL & PL/SQL :: Order By Alphanumeric?
Aug 1, 2012create table x(
sno varchar2(5)
);
insert into x values('A-1');
insert into x values('B-1');
insert into x values('B-2');
insert into x values('B-3');
insert into x values('1');
insert into x values('A-2');
insert into x values('2');
insert into x values('3');
insert into x values('A-4');
insert into x values('B-4');
insert into x values('C-4');
insert into x values('D-4');
SQL>select * from x;
SNO
-----
A-1
B-1
1
A-2
2
3
A-4
B-4
C-4
D-4
B-2
B-3
How can I select it ike this
1
A-1
A-2
A-4
B-1
2
B-2
3
B-3
B-4
C-4
D-4
PL/SQL :: Order By Month Name
Jun 20, 2012I've got a TRANSACTION table with about 4, 681 transactions going on over the course of a given year (this is a project for my DB class). I'm trying to create a query that will give the base revenue for each month in that year; so far I've come up with the following:
SELECT DISTINCT
CASE
WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN 'JAN' || ' ' || EXTRACT(YEAR FROM transaction_date)
WHEN EXTRACT(MONTH FROM transaction_date) = 2 THEN 'FEB' || ' ' || EXTRACT(YEAR FROM transaction_date)
WHEN EXTRACT(MONTH FROM transaction_date) = 3 THEN 'MAR' || ' ' || EXTRACT(YEAR FROM transaction_date)
WHEN EXTRACT(MONTH FROM transaction_date) = 4 THEN 'APR' || ' ' || EXTRACT(YEAR FROM transaction_date)
[code]....
The query returns twelve months, but they're all jumbled up. I tried extracting the month in the ORDER BY subclause
ORDER BY EXTRACT(MONTH FROM TO_DATE(month, 'MM YYYY'));
But I got an ORA-01866: the datetime class is invalid. I'm using Oracle 10g xe (outdated, I know). get the months to show up in order?
PL/SQL :: Order By With Union?
Aug 24, 2012I have the following sql
select htf.escape_sc(su.sukey) a, htf.escape_sc(su.sukey) b
from udm_su su, udm_lde lde
where su.ldeid = lde.ldeid
and su.sukey in (select su_generic
[code]...