PL/SQL :: Get DAY Based On Current / Given Territory From Number Between 1 And 7?

Jan 1, 2013

Looks like I am at the end of my wits here but how to get the "DAY" based on current/given territory from a number between 1 and 7? Oracle has functions to extract day of the week but I can't find the other way round.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> alter session set nls_territory=AMERICA;

Session altered.

SQL> select to_char (sysdate, 'D') from dual;

T
-
3

SQL> alter session set nls_territory=GERMANY;

Session altered.

SQL> select to_char (sysdate, 'D') from dual;

T
-
2

I need a way to know that number "3" on database with NLS_TERRITORY = AMERICA means a "Tuesday" or number "2" on a database with NLS_TERRITORY=GERMANY means "Tuesday" as well.

View 9 Replies


ADVERTISEMENT

Reports & Discoverer :: How To Compare Current Page Number With Total Page Number

Feb 19, 2013

I have a report created in Reports6i. I have two fields at the bottom

1.Page Total
2.Voucher Total

I do not want to print the Page Total if the total number of pages = 1.

How can I achieve this?

View 1 Replies View Related

Does Column V$SQL.CHILD_NUMBER Related On Number Of Current Cursor Only

Jan 11, 2013

Does the column V$SQL.CHILD_NUMBER related on number of current cursor only ? V$SQL lists statistics on shared SQL areas without the GROUP BY clause and contains one row for each child of the original SQL text enteredI see in V$SQL big numbers in V$SQL.CHILD_NUMBER ( HASH_VALUE and PLAN_ HASH_VALUE is in my quewry fixed ) , am I wrong that this column displayed not summary count of "objects"(in terminology of Steeve. URL....

View 0 Replies View Related

SQL & PL/SQL :: Count Number Of Record Based On Some Condition

Jun 28, 2013

i have two tables one as test_master and other one limit master.

test master table structure

ct_id ct_a ct_b ct_C ct_d ct_e...... etc
1 -- -- -- -- --
1 -- -- -- -- --
2 -- -- -- -- --
limit master structure

limit_id ct_a_limit ct_b_limit ct_c_limit ct_d_limit ct_e_limt ..... etc
1 -- -- -- -- -- -- -- --
2 -- -- -- -- -- -- -- --

ct_id and limit_id is match column used for mapping.

now i want the total count of no of records in test_master in which test_master column value not exceed limit master column limit value if any one column exceeds its limit then the same row will be in my count criteria.

how to achieve the same using sql/oracle query.

View 7 Replies View Related

SQL & PL/SQL :: How To Get Total Number Of Occurrences Based On Value Of Column

Apr 20, 2013

I have a query that should return count the number of rows depending on the value of SLOT. The expected result will be like this:

WIPDATAVALUE SLOT N M
1-2 TRALTEST43S1 1 3
1-2 TRALTEST43S1 2 3
3 TRALTEST43S1 3 3
4-6 TRALTEST43S2 1 4
4-6 TRALTEST43S2 2 4
4-6 TRALTEST43S2 3 4
7 TRALTEST43S2 4 4

The M column is used to count the total number of occurrences of a SLOT. Now, as for the N field, this is used to count the occurrence of the SLOT. In my example for the SLOT TRALTEST43S1, it has three occurrences so M will be 3. Why 3, is because of the WIPDATAVALUE. The WIPDATAVALUE of TRALTEST43S1 is 1-2 and 3. 1-2 WIPDATAVALUE signifies two occurrences (one to two) and 3 signifies only one occurrence. As for N, it should just count the number of occurrence. To further explain, see below:

WIPDATAVALUE SLOT N M
1-2 TRALTEST43S1 1 3 -> First occurrence in the total of 3
1-2 TRALTEST43S1 2 3 -> Second occurrence in the total of 3
3 TRALTEST43S1 3 3 -> Third occurrence in the total of 3
4-6 TRALTEST43S2 1 4 -> First occurrence in the total of 4
4-6 TRALTEST43S2 2 4 -> Second occurrence in the total of 4
4-6 TRALTEST43S2 3 4 -> Third occurrence in the total of 4
7 TRALTEST43S12 4 4 -> Fourth occurrence in the total of 4

This is the query that I have so far:

SELECT DISTINCT
WIPDATAVALUE, SLOT
, LEVEL AS n
, m
FROM
(
SELECT
WIPDATAVALUE
, SLOT
, (dulo - una) + 1 AS m
[code]....

I think that my current query is basing its M and N results on WIPDATAVALUE and not the SLOT that is why I get the wrong output. I have also tried to use the WITH Statement and it works well but unfortunately, our system cant accept subquery factoring.

View 2 Replies View Related

PL/SQL :: How To Get Total Number Of Occurrences Based On Value Of Column

Apr 20, 2013

here on your forum but has been following several threads ever since.So anyway here is the thing, I have a query that should return count the number of rows depending on the value of SLOT. Something like this:

WIPDATAVALUE          SLOT             N            M
1-2                   TRALTEST43S1     1            3
1-2                   TRALTEST43S1     2            3
3                     TRALTEST43S1     3            3
4-6                   TRALTEST43S2     1            4
4-6                   TRALTEST43S2     2            4
4-6                   TRALTEST43S2     3            4
7                     TRALTEST43S2     4            4-----

As you can see above, on the SLOT TRALTEST43S1, there are three occurrences so M (Total number of occurrences) should be three and that column N should count it. Same goes with the SLOT TRALTEST43S2. This is the query that I have so far:

SELECT DISTINCT
WIPDATAVALUE, SLOT
, LEVEL AS n
, m
FROM
(
  [code]...

I think that my current query is basing its M and N results on WIPDATAVALUE and not the SLOT that is why I get the wrong output. I have also tried to use the WITH Statement and it works well but unfortunately, our system cant accept subquery factoring.

View 23 Replies View Related

SQL & PL/SQL :: Generate The Number Of Rows Based On Table Field?

Mar 29, 2013

generate the number of rows based on table field.

Sample code is given below.

CREATE TABLE T
(
docno VARCHAR2(10),
CODE VARCHAR2(8),
QTY NUMBER(3)
)
LOGGING

View 4 Replies View Related

SQL & PL/SQL :: Generate Range Based On Start And End Number Dynamically?

Jul 14, 2011

I have following requirement. Let say i have to generate a range based on "start number" and "end number" dynamically.Some kind of hash buckets.

e.g Start Number : 1 and End Number : 1001.

Also i want to divide that range based on some dynamic value like for above example 10 ranges of 100 each. and both 1 and 1001 should be included only once and the next row start number cannot be same as previous row end number.

Means

Range 1 1 -101
Range 2 102-200
Range 3 201-300
...
Range 10 901-1001

Is there any way of doing it automatically. I tried with Model clause. it works fine for even cases but for odd i have issues also when i take small start and end number i get an error.

SELECT case when ranges=1 then ranges else ranges+1 end Start_Id, ranges+round((1001-1)/10) End_Id
from
(

[Code].....

I want this to be generic for any values here 1 is Start Number 1001 is end number and 10 is the bucket. I need these parameters dynamic and want's the same kind of results for any values.

View 22 Replies View Related

PL/SQL :: Assign Number To Row Based On Condition Analytic Function

Oct 24, 2013

Oracle 11g databaseidval1val2100a110b120c200a220b 
WITH input AS
(SELECT 1  id

[Code].....

input; Output:idval1val2assigned_number100a0110b0120c2200a0220b1 The dense numbering sequence should be assigned to each row based on id and val1 column. For a given Id, the numbering only starts after val1 > 1 till then the assigned_number will be zero.

View 1 Replies View Related

SQL & PL/SQL :: Generate Hourly Based Number Of Orders Placed Report

Oct 11, 2011

I have a table called 'orders' and having date and time of order placed in the field timeplaced. I need to generate a report which is having hourly based number of orders placed . If no order is placed in an hour then it should show 0 for that hour in the result .

create table orders (order_id integer, timeplaced date,last_updated date);
Insert into ORDERS
(ORDER_ID,
TIMEPLACED, LAST_UPDATED)
Values
(1, TO_DATE('10/11/2011 12:53:39', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/11/2011 10:53:39', 'MM/DD/YYYY HH24:MI:SS'));
[code]........

But I need the result for all the 24 hours. Actually row for the hour 1,2,3,4,5,6,8,9,13-23 are not present in the DB

TO_CHAR(TIMEPLACED,'HH')COUNT(*)

00
10
20
30
40
50
60
71
80
90
102
111
122
130
140
150
160
170
180
190
200
210
220
230

View 3 Replies View Related

SQL & PL/SQL :: Difference Between Sysdate / Current Date / Current And Local Timestamp

May 30, 2012

What is the difference between the following . In my schema all are giving the same results with some different format

SQL> SELECT sysdate , current_date , current_timestamp , localtimestamp from dual;

SYSDATE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP
----------- ------------ ------------------------------------------------- -------------------------------------------------
5/30/2012 8 5/30/2012 8: 30-MAY-12 08.27.22.037703 AM -04:00 30-MAY-12 08.27.22.037703 AM

View 1 Replies View Related

SQL & PL/SQL :: Verify Current Date Is Greater Than 15th Of Current Month

Sep 22, 2010

I need to verify if the current date is grater than the 15th of the current month. If its grater than the 15th of the current month i need to do an action or if else its lesser than 15th of the current month i need to do an other operation.

View 5 Replies View Related

SQL & PL/SQL :: Dynamically Concatenating Column Values Based Upon Number Of Grouped Columns

Feb 17, 2011

My requirement is to concatenate two column values and place them in a new column.I have done it using self join but it limits the purpose,meaning when I have more than 2 values for grouped columns then it won't work.How to make this dynamic,so that for any number of columns grouped,I can concatenate.

SELECT a.co_nm, a.mnfst_nr, a.mnfst_qty,
a.mnfst_nr || ':' || a.mnfst_qty || ';' || b.mnfst_nr || ':'
|| b.mnfst_qty
FROM vw_acao_critical a JOIN vw_acao_critical b
ON a.co_nm = b.co_nm AND a.mnfst_nr = b.mnfst_nr
[code]......

What will be the case when I need to concatenate for more number of values.

like when co_nm has three bahs and manfst_nr and manfst_qty has 3 values for each for bah.and if three are having same_mnfst nr then I should use something dynamic.how to achieve this.

View 10 Replies View Related

Forms :: Auto Populating Records In Other Fields Based On Inventory Number Field

Jul 13, 2011

I am having a problem with auto populating different fields based on inventory no. field.. This is a bug giving to me to work on and i not able to figure out how to populate the other fields.

How to set any triggers for the items to auto populate and i am suppose to finish this work today.

View 2 Replies View Related

Application Express :: Auto Populate Phone Number Based On Select List In Tabular Form

Dec 27, 2012

Ive a requirement which ive overlooked for a while now as below,

On my tabular form i have fields: Empid as Select List and when the value is picked from this Select List EmpID i want the associated/corresponding phone# displayed in its TEXT FIELD in that same row,

apex 4.1.1/Oracle 11gR2,

also i tried the below as a workaround but it doesnt fulfill my requirement, [URL]....

View 1 Replies View Related

SQL & PL/SQL :: Dynamic Column Creation / Create Column Based On Number Of Child In Hierarchy

Oct 15, 2013

I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically

ORG_UNITCOST_CENTERORG_UNIT_NAMEPARENT_ORG_UNITLLSYS_CONNECT_BY_PATH(ORG_UNIT,'/')

500171960000022000Managing Director - LUL500169965/00000001/50000001/50017588/50016996/50017196
500018370000021241FSO500171966/00000001/50000001/50017588/50016996/50017196/50001837
502894940000021241Knowledge Management500018377/00000001/50000001/50017588/50016996/50017196/50001837/50289494
508014980000021241Finance500018377/00000001/50000001/50017588/50016996/50017196/50001837/50801498

View 1 Replies View Related

Reports & Discoverer :: How To Find Page Number And Total Number Of Pages

Jan 26, 2010

i am using oracle developer 6i report builder i required this type of query

example

if (:page number LIKE '1')
then
srw.set_text_color('darkred');
end if;

return (TRUE);
end;

but page number is not my table database item how can i use builtan page &<pagenumber> use for conditional format.

View 34 Replies View Related

SQL & PL/SQL :: Replacing 4 Digit Number In A Given String With The Same Number Incremented By 10000?

Jun 17, 2010

i want to replace 4 digit number in a given string with the same number incremented by 10000.

That mean in the given sting 1201 should be replace by 11201 (Icremented BY 10000).

Input String:

<query><matchAll>true</matchAll><row><columnId>1201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>1224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>

Required output :

<query><matchAll>true</matchAll><row><columnId>11201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>11224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>

View 7 Replies View Related

SQL & PL/SQL :: Extract Number And Previous Character From Where 5 Digit Number Starting

Oct 21, 2011

I have a text field and if the text field has 5 consecutive numbers then I have to extract the number and the previous character from where the 5digit number starting

For example i/p asdfasfsdS251432dasdasd o/p should be S251432

View 10 Replies View Related

Express Edition (XE) :: Why Maximum Number Of Voting Disk Is Even Number (32)

Oct 3, 2013

I gone through many forums and found that the number of voting disks should be always in odd number. Then why the maximum number of voting disk is 32?

View 1 Replies View Related

SQL & PL/SQL :: Count A List Of Number Value And Find Maximum Number?

May 21, 2010

how do I count a list of number value eg 1,1,1,1,3,3,6,4 and find the one with maximum number which is 1

View 5 Replies View Related

Where Current Of - For Update Of

Oct 5, 2010

I have an employee table that has a paygrp_id that will be used for my subset of employees. For all the employees that have the paygrp_id = 10212 on the employee table I need to update the workbrain_user table to set the flag wbu_cansee_self to 'N'. The join between the employee table and the workbrain_user table is the emp_id.I get the following error when I run this cursor.

[error]
Error on line 0
DECLARE
CURSOR wbuFlag_cur
IS SELECT e.emp_fullname, e.paygrp_id,wbu.WBU_CANS
[code]...

View 3 Replies View Related

SQL & PL/SQL :: How To Get The Name Of Current Procedure

Jul 13, 2011

I have a procedure like below:

create or replace procedure sp_test
is
vs_proc_name varchar2(40);
begin
--get the name of current procedure ,here is "sp_test"
--[color=red]but ,i do not want the hard code here[/color]
insert into test_Page(proc) values(vs_proc_name);
---
--
commit;
end;

I don't want to coding like this:

vs_proc_name:='sp_test';

so ,is there any building function to get that ?

View 5 Replies View Related

SQL & PL/SQL :: How To Get Next Not Null Value For Current Row

Dec 14, 2010

following is my input data:

SELECT TO_DATE('21-NOV-2010') DAY, 0 RATE FROM DUAL
UNION
SELECT TO_DATE('22-NOV-2010') DAY, 10.5 RATE FROM DUAL
UNION
SELECT TO_DATE('23-NOV-2010') DAY, 0 RATE FROM DUAL
UNION
SELECT TO_DATE('24-NOV-2010') DAY, 0 RATE FROM DUAL
UNION

[code]....

View 4 Replies View Related

Update Where Current Of

Sep 27, 2010

I have a table I need to update called employee_history. It has almost the exact same values as another table called Employee. The difference being that the employee_history has a start date and end date. So it allows you to have overrides in the future. The employee table is static. So it only allows you to see the current data as of today.

I need to write an insert statement that will pull values from employee table and populate the employee_history table but I also have columns in the employee_history table that requires a next value and a start and end date.

From the employee table I want to select e.emp_id, e.emp_firstname, e.emp_lastname, e.shftpat_id, e.client_id from employee e

I want to INSERT INTO EMPLOYEE_HISTORY (EMHIST_ID, EMPHIST_START_DATE, EMPHIST_END_DATE, EMP_ID, EMP_FIRSTNAME, EMP_LASTNAME, SHFTPAT_ID, CLIENT_ID)

with the VALUES being (SEQ_EMPHIST_ID.nextval, TRUNC(sysdate), 01/01/3000 and the employee data from the employee table)). I bought the book Easy Oracle PL/SQL Programming but I am not finding what I need. It gives an example of a CURSOR FOR UPDATE and a reference to an UPDATE WHERE CURRENT OF.

The examples are straight forward where you take two columns from table one and update a new table with those values, or where you select a subset of employees that need a commission and update the emp table with that commission.

View 1 Replies View Related

SQL & PL/SQL :: How To Generate Number (not Sequence Number) In Query

Mar 9, 2011

I have the following select query that works perfectly fine. Returns 25 rows based on the descending order of the price.But, I want add one more expression to this list of columns in this query (apart from customer_id).

the expression should look like Cust-01 for the first customer from the below query all the way to Cust-25 for the last customer.But how can I can generate 01 to 25 in oracle?

select customer_id from
(select customer_id from capitalPLAN
where member_status = 'MEMBER' AND customer_id NOT in ('156','201','1385','2125','3906','165')
order by price desc
)
where rownum <= 25

View 4 Replies View Related

PL/SQL :: Insert Number Into Column With Type  NUMBER(10,0)

Sep 28, 2012

my column type is NUMBER(10,0) ,it accept the input value from text field I using TO_NUMBER(?) to insert value into table, is the a way to handle if the input is 'aaaaaaaaaa' not digit?

View 6 Replies View Related

Get Current Date And Time?

Apr 24, 2005

I only know to retrieve data (date & time) by the following statement : "select sysdate from ..."

but now I want to make the fields current date and current time separately and put in a adult file with these two fields. how to do it.

View 11 Replies View Related

SQL & PL/SQL :: Bulk Collections And Current OF

Sep 2, 2011

I have a driver table from which I need to update another table while, at the same time, record the fact that I have processed each record on the driver table.

The driver table will contain around 3.5 million records, therefore I intended to handle this using some bulk collections, with a LIMIT option so that I don't hit any memory problems.

I would also prefer to commit in batches, or at least handle exceptions using the SAVE EXCEPTION clause. The problem is I seem to be running into an error when trying to make the update to the driver table (the commented out code). With this in, I get the error:

ORA-01410: invalid ROWID
ORA-06512: at "CUST_MAIL_UPDATE", line 217
ORA-06512: at line 38

Can the CURRENT OF not work with the FORALL? What is my best approach here? If I use a FOR LOOP I lose my SAVE EXCEPTIONS exception handling.

The Procedure is as follows:

-- declare some object structures to hold the retrieved data
TYPE driver_rec IS RECORD (
account_no ext_driver.account_no%TYPE,
update_action ext_driver.update_action%TYPE,
customers_rowid ext_driver.customers_rowid%TYPE);
TYPE driver_recs_tt IS TABLE OF driver_rec;
-- cursor to get the records from the driver table
[code].......

View 10 Replies View Related

Forms :: Getting Current Hostname

May 9, 2011

how can I get the form hostname that it is currently running to? I already tried the form's (forms developer 6i) built in host functionality but it doesn't return the hostname.

Our current approach is to have a shell script that will get and insert the hostname into the table and the form will just query from the table to get the hostname, but that approach has involved too many modules.

Is there an easiest way to get the hostname?

View 5 Replies View Related







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