SQL & PL/SQL :: Function Not Calculating

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


ADVERTISEMENT

SQL & PL/SQL :: Calculating Percentage Value

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

PL/SQL :: Calculating Value On Insert

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

SQL & PL/SQL :: Calculating Stock Using FIFO

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

SQL & PL/SQL :: Calculating The Business Hours

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

SQL & PL/SQL :: Calculating Working Hours?

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

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

Calculating Number Of Saturdays And Sundays

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

SQL & PL/SQL :: Calculating Sum From Duplicate Rows With Timestamp?

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

SQL & PL/SQL :: Calculating The Difference Of Timestamps In Seconds

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

PL/SQL :: Calculating Inconsistency In Associate Allocation?

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

Calculating Totalspace Consumed By Datafiles

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

SQL & PL/SQL :: Update Statement - Calculating Few Values From Large Table

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

PL/SQL :: To Create Function Based Index For Group Function Columns

Jun 15, 2012

Is anyway to create function based index for group function columns.

For example

select max(timestamp),min(age),averge(sal).... ... .. from tab;

View 5 Replies View Related

PL/SQL :: Calling External C Function / ORA-06521 Error Mapping Function

Feb 4, 2013

I have the following C code:

class Factorial {
  public:
  int getVal (int a);
};
[code]....

/When I am trying to execute this function always get the ORA-06521. I changed the data types - but nothing changed.

Just in case, listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
                   (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
[code]....

View 6 Replies View Related

SQL & PL/SQL :: Difference Between Stand Alone Function And Function Declared In A Package?

Mar 11, 2010

What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.

View 2 Replies View Related

SQL & PL/SQL :: What Is Advantage Of Deterministic Function Over Normal Function

Jun 10, 2010

What is advantage of Deterministic function over normal function?

What is the diff B/W Deterministic function and normal function and also give me a example in which scenario we use Deterministic function?

View 4 Replies View Related

Use MAX Function Without It Being In Results?

Apr 4, 2007

i have a query where i am using the max function to find the most recent record. What i want to do is use that query as part of an insert statement into a different table, however, i don't want to insert the column that i used the max function on. Is there anyway to use the max function without having the column it is being used on showing in the results?

View 1 Replies View Related

How To Use The NVL() Function In Statement

Mar 24, 2009

I have the following query.

sql

Original

- sql Code

SELECT class, COUNT (class)
FROM nv_table
WHERE l_id IN (
SELECT l_id
FROM n_table
WHERE id IN (1234)
GROUP BY class
ORDER BY class

SELECT class, COUNT (class) 
FROM nv_table 
WHERE l_id IN (          SELECT l_id            FROM n_table           WHERE id IN (1234)   
GROUP BY class   
ORDER BY class

It returns two columns: class and the total number of values in that class.

In some cases, there might not be any values under a certain class, so the query won't return anything.

In such a case I want the query to return 0. So what I want to see is:

class:A, COUNT(class):0

I'm trying to use the NVL function here, but either it doesn't work in this context, or it's not the correct syntax the way I'm writing it.

sql

Original

- sql Code

SELECT class, NVL(COUNT (class), 0)
FROM nv_table
WHERE l_id IN (
SELECT l_id
FROM n_table
WHERE id IN (1234)

[Code]....

View 3 Replies View Related

SQL & PL/SQL :: DML Operations Within Function

Dec 16, 2011

I am creating an stored function which has to do some inserts in the meanwhile, and return after all the work has done, an UDT (2 or 3 columns of NUMBER datatype).

With this scenario I have an problem. The DML operations are not supported by and "SELECT * FROM Table(MyProc(args))". I have to use this "SELECT * FROM Table(MyProc(args))" because I need to pass the stored function results directly to an dataset.

Using a Stored Procedure it gives no errors, but the arguments must be passed like OUT params, and it is not what I want.

My question is:
Is there any other way to get a result (UDT) of an Stored Function (that makes Inserts) into a DataSet?

View 12 Replies View Related

SQL & PL/SQL :: Regexp_replace Function

Jul 14, 2010

I've been tasked to parse tags from a string that look like the following:

{Date + XXX}

where XXX represents a numeric value. I have to replace this, including the brace characters with

SYSDATE + XXX

which will ultimately calculate SYSDATE plus the number of days suggested by XXX. The problem is that I am trying to use regexp_replace to achieve this goal but since XXX is completely arbitrary, I cannot search for it as a fixed value. So, ultimately, I would like to use a regular expression that ignores the numeric part of my search and only replaces the starting brace, the "Date + " part and the ending brace, leaving the numeric portion intact. I was trying to do something like the following

myString := regexp_replace(myString, '{(Date + [^[::digit:]]{1,})}', to_char(SYSDATE, 'FMMONTH DD, YYYY'));

in hopes of making it ignore the numeric part but it, instead, treats occurrences as a non match. Alternatively, the call below

myString := regexp_replace(myString, '{(Date + [[:digit:]{1,})}', to_char(SYSDATE, 'FMMONTH DD, YYYY'));

matches correctly but replaces the numeric portion as well, so I'm left with just today's date instead of the calculated future date...

View 14 Replies View Related

SQL & PL/SQL :: LAST-VALUE Analytical Function?

Sep 11, 2012

I'm posting below test case in which I'm not able to understand output for LAST_VALUE function. I'm expecting maximum value for the salary in a department. Because I'm partitioning by department and ordering a partition as assending so being last value it should give me maximum value within a partition i.e. department in this case.

CREATE TABLE EMP_MST
(
EMP_ID NUMBER(5),
EMP_NAME VARCHAR2(30),
CONSTRAINT PK_EMP_MST PRIMARY KEY(EMP_ID)

[code]...

View 4 Replies View Related

SQL & PL/SQL :: Have More Than One Blocks In Function

Jun 14, 2011

can i have more than one pl sql blocks in a function and can i use the variable of one cursor into another cursor of the same function?

View 2 Replies View Related

SQL & PL/SQL :: Regarding NVL Function Usage

Oct 20, 2011

There is an 'emp' table with a column name as 'mgr' with datatype 'number'. following is the detailed description of the table:

SQL> desc emp;

Name Null? Type
----------------------------------------- -------- ---------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

Now when I run the 'select mgr from emp e;' query the output which I get is:

7902
7698
7698
7839
7698
7839
7839
7566

7698
7788
7698
7566
7782

Note: One value in between here is null, the required to me is that I want to print a character value 'President' in place of null .

View 7 Replies View Related

SQL & PL/SQL :: How To Avoid Max Function

Feb 22, 2010

I have two tables where I have to find the record for Max value of the column sap_pkid for every sap_id as in given table create script. This script is giving correct value but looking for a better way so that when data increses it doesn't hit the performance.

way where max can be avoided or a more tuned query .

create table tab1 (sapid number,
denid number);
create table tab2 (sap_pkid number ,sapid number,
denid number,

[code]...

View 2 Replies View Related

SQL & PL/SQL :: Function To Sum Five Numbers

Apr 13, 2013

I am creating a function to sum five numbers (less 1). Is it possible to have an array of numbers in an SQL function, and how would this be implemented?

Here is the screenshot of my output (I cannot embed links until 5 posts!): flic.kr/p/eaSHBP

CREATE OR REPLACE FUNCTION sumfivenumbers ( n1 NUMBER, n2 NUMBER, n3 NUMBER, n4 NUMBER, n5 NUMBER)
RETURN NUMBER
IS
Sumnums NUMBER;
BEGIN
SELECT SUM(n1+n2+n3+n4+n5-1) INTO Sumnums FROM DUAL;
DBMS_OUTPUT.PUT_LINE(Sumnums);
RETURN 1;
END sumfivenumbers;
/
SELECT sumfivenumbers(5,5,5,5,5) AS "Five Numbers less 1" FROM DUAL;

View 7 Replies View Related

SQL & PL/SQL :: Function Without Parameter?

Aug 5, 2010

if function having retrun statement, why we need to use out or inout parameters.

View 16 Replies View Related

SQL & PL/SQL :: How To Use Decode Function

Oct 31, 2011

How can i use the decode function?

for example

I have the value of 1000 then the numbers 50-100 will be 'A' and 1-49 = 'B'?

View 9 Replies View Related

SQL & PL/SQL :: What Is Rowtocol Function

Dec 26, 2011

how to use rowtocol means a query.

View 5 Replies View Related

SQL & PL/SQL :: Decode Function

Apr 4, 2013

DECODE(:P_PERIOD_TYPE,'PJTD','PROJECT-TO-DATE','PTD','PERIOD-TO-DATE','YTD','YEAR-TO-DATE')

what does it mean..

View 3 Replies View Related







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