SQL & PL/SQL :: Conditional Sequence Usage?

Jan 31, 2013

I'd like to have a conditional that only gets the next value from a sequence is the current (or supplied) value is null.

Here are some trivial examples:

CREATE SEQUENCE ts
MINVALUE 1
START WITH 1
INCREMENT BY 1;
SELECT ts.NEXTVAL FROM DUAL;
SELECT COALESCE(ts.CURRVAL, ts.NEXTVAL) FROM DUAL;
SELECT NVL(ts.CURRVAL, ts.NEXTVAL) FROM DUAL;
SELECT CASE
WHEN ts.CURRVAL IS NOT NULL THEN ts.CURRVAL
ELSE ts.NEXTVAL
END
FROM DUAL;

Ideally multiple executions of any of these (or a better one of your design) should return the same value from the sequence, but mine do not.

View 6 Replies


ADVERTISEMENT

SQL Query To Find CPU Usage And Memory Usage

Jun 28, 2013

Is their any query to find cpu usage & memory usage of all the queries currently running on DB? 

View 5 Replies View Related

SQL & PL/SQL :: How To Do Conditional Query

Mar 22, 2010

how to do a conditional range in a query. For example, I have two tables:

table A:
a1 char(1)
a2 char(1)

table B:
b1 char(1)
b2 int

In my query I want to range on a different value in the b2 field depending on what is in the fields in the A table. For example, if the a1 field is Y, I want to look up the record in B with a b2=20. If a2 is Y, I want to look up b2=5. Otherwise I want to look up b2=1. I attempted the following:

select b1 from b, a where b2=(if a1='Y' then 20; elsif a2='Y' then 5; else 1)

but that doesn't work.

View 8 Replies View Related

SQL & PL/SQL :: Conditional Selection Of A Field

Feb 27, 2013

I'd like to select one of two fields from a table, based on which of them is greater for that row.

e.g. For every row in JOINING_TBL, I'd like to select either JOINING_DATE or REJOINING_DATE, whichever is greater. I do dream about being able to use a Java-like ternary operator in here, something like this:-

SELECT (JOINING_DATE>REJOINING_DATE?JOINING_DATE:REJOINING_DATE) FROM JOINING_TBL

However, I understand(through other fora) that this can't be used in here.

View 3 Replies View Related

SQL & PL/SQL :: Oracle 9i - Conditional Statement

May 9, 2011

The conditional statements should not only be executed in sequence, but also if any of them are true they should not be overridden by any subsequent conditional statements being true.

When actual effort Accepted or Rejected for AST proposals and calculate a flag for "enhance to AST guideline" = Y/N as follows for each employee and display at the employee level

1)If AST eligibility = N AND proposed AST % >0, then "N"
2)Else If AST eligibility = N AND proposed AST % = 0 then "n/a"
3)Else If AST eligibility = Y AND Act Rank = 3 AND proposed AST = 0 then "Y"
4)Else If AST eligibility = Y AND Act Rank = 3 AND proposed AST >0 then "N"
5)Else If AST eligibility = Y AND Act Rank = 2 AND proposed AST = 0 then "Y"
6)Else If AST eligibility = Y AND AST % is greater than or equal to the AST guideline minimum AND less than or equal to the AST guideline maximum, then "Y"
7)Else If AST eligibility = Y AND AST % is less than the minimum guideline OR greater than the maximum guideline, then "N"

I tried the following code but I am not getting the expected result .

if (upper(P_stat)='ACCEPTED' or upper(P_stat) like 'REJECTED%') then
else if NVL(P_elgi,'N') <> 'Y' AND P_prop > '0' then
P_flag := 'N';
else if(NVL(P_elgi,'N') <> 'Y' AND P_prop = '0') then
[code]........

View 5 Replies View Related

SQL & PL/SQL :: Conditional Group By Of Rows

May 8, 2013

Task I would like to do in plain SQL rather than PL/SQL.

Given this table:

create table TEMP_TEST
(
DT_START DATE not null,
DT_END DATE not null,
FIELD VARCHAR2(2)
)

and these rows:

insert into temp_test (DT_START, DT_END, FIELD)
values (to_date('01-01-2005', 'dd-mm-yyyy'), to_date('31-08-2007', 'dd-mm-yyyy'), 'ML');
insert into temp_test (DT_START, DT_END, FIELD)
values (to_date('01-09-2007', 'dd-mm-yyyy'), to_date('31-12-2007', 'dd-mm-yyyy'), 'MT');

[Code]....

what i want to do is collapse the rows that have same field value, ONLY if the end date of the first one equals start date -1 of the next one.

that being said the result should be

01/01/2005 - 31/08/2007 - ML 01/09/2007 - 31/12/2007 - MT 01/01/2008 - 29/02/2012 - ML 15/03/2012 - 31/12/9999 - ML

I was doing this:

SELECT MIN(dt_start), MAX(dt_end), field
FROM(
SELECT dt_start,
dt_end,
field,

[Code]....

but it still misses the thing about adjacent time periods

View 6 Replies View Related

PL/SQL :: Conditional Delete Procedure?

Jul 25, 2013

I am trying to create procedure to delete data from audit table but don't know where to start with..

1. Procedure will run on first Saturday of the month (not sure is it possible via pl/sql or have to create separate job)
2. Delete data older than 2 months from Mon - Sat and date shouldn't be 1st of the month. (i.e. leave Sunday & 1st of the month data older than 2 months) e.g.{code}

Procedure delete_log
IS
BEGIN
delete from audit_logs
where created >= trunc(sysdate - 60) and created < trunc(sysdate)
and created != (Sunday)
and created != (First of the month);

View 10 Replies View Related

SQL & PL/SQL :: Conditional Insert With Multiple Subqueries

Oct 9, 2012

We have multiple environments and our dev and UAT ones are now different from staging and live (I know, but I am not in a position to get this fixed). I have a set of updates that need to go through to live and in some cases they reference rows that do not exist in the UAT environment, and yet they have to (rigid, dumb process) go through that environment.

Basically, the insert I need to do takes info from two tables and does an insert into a third. That target table has a not null constraint on the affected fields, so the insert fails, quite rightly.

There's lots of info available on how to do conditional inserts with single sub-queries, using DUAL and EXISTS (or rather NOT EXISTS, but that's easy to swap), but those don't seem to easily translate for this one.

The sql that works when everything exists is:

insert into wmcontent.wm_manda_corpserv_companies
(wm_manda_company_code, wm_corp_company_code)
values
( (select wm_company_code from wmcontent.wm_m_and_a_company where wm_company = 'SW'), (select min(oid) from wmcontent.wx_category where content_type = 2 and name = 'SW') );

In desperation I even tried using "log errors reject limit unlimited" but, no doubt due to my misunderstanding of how that works, I ended up getting the error "ORA-06550: line 38, column 1: PL/SQL: ORA-00972: identifier is too long" as a result.

View 8 Replies View Related

SQL & PL/SQL :: Conditional Average Calculation Of Column?

Jul 22, 2012

I want to write a sql qeuery to get result similar to expected average column as shown in sheet.

Record can be uniquely identified by columns ID, PRODUCT and OFFICE. I want to calculate average for each date considering the same record does not exists earlier i.e. I want to consider the latest vote while calculating average. E.g.

Average till date 1/1/2011 is Avg (2, 4, 3) = 3 (as no repeating vote value exists)
Average till date 2/1/2011 is Avg (4, 3, 5, 3) = 3.75 (excluding vote value 2 as 122_UK_LONDON was provided his vote earlier, so considered latest vote value i.e. 5)
Average till date 3/1/2011 is Avg (3, 5, 3, 6, 5, 8 ) = 5 (excluding vote value 2 for 122_UK_LONDON and vote value 4 for 967_Europe_London)

View 15 Replies View Related

SQL & PL/SQL :: Display Conditional Column Values

Apr 23, 2013

I'm using the Oracle Emp,Dept tables as my sample. I want to display certain table column values based on some criteria. If met, display those values otherwise display other column values

For example:

So when dept.deptno=10, I want to display these 2 columns values
1. dept.deptno
2. dept.dname

otherwise, display these 2 columns values
1. dept.loc
2. null

Can this be done with one case, decode or "other" type of structure going thru the table one time??

SELECT emp.empno,
emp.ename,
CASE
WHEN dept.deptno = 10 THEN
to_char(dept.deptno)
[code].......

View 6 Replies View Related

SQL & PL/SQL :: Conditional Predicates Is Not Working With MERGE

Aug 6, 2012

I realized that in statement level trigger conditional predicates(INSERTING, UPDATING & DELETING ) are not working.

For example please run below sql:

create table merge_test(col1 number, col2 number);
create or replace trigger merge_test_tr_stat
after INSERT OR UPDATE OR DELETE on merge_test
begin

if inserting or updating or deleting then
dbms_output.put_line('conditional predicates is working ');
else
dbms_output.put_line('conditional predicates is not working ');
end if;
end;

set serveroutput on;
insert into merge_test values (1,1);

Output
------
1 rows inserted.
conditional predicates is working
conditional predicates is working

set serveroutput on;
merge into merge_test mt using ( select 1 col1 from dual ) tab
on ( mt.col1 = tab.col1 )
when matched then
update set col2 = 2
when not matched then
insert values (1,1);

Output
------
1 rows merged.
conditional predicates is not working
conditional predicates is not working

How to use those conditional predicates in statement level trigger.

View 3 Replies View Related

Forms :: Get Conditional Window Appear In Form

Jun 2, 2011

I want to achieve functionality like below:

Let's say there are two almost identical window and some specific functions associated: W1 and W2.

Depending upon the login profile into the application only one window and associated functions will be appeared. This time the other window will be disabled.

View 3 Replies View Related

PL/SQL :: Conditional Transpose Of Column Data Into Rows

Jul 26, 2013

I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production versionI have following table - 
drop table t2;

create table t2(BATot,Ly_BATot,LLy_BATot,BScTot,Ly_BScTot,LLy_BScTot,BAMSTot,Ly_BAMSTot,LLy_BAMSTot) as select
5000,2000,12600,20000,45600,35000,45000,56000,65000 from dual ;

select null class, batot,ly_batot,lly_batot,bsctot,ly_bsctot,lly_bsctot,bamstot,ly_bamstot,lly_bamstot from t2; Simple DML I am using -
SELECT * FROM T2;

C      BATOT   LY_BATOT  LLY_BATOT     BSCTOT  LY_BSCTOT LLY_BSCTOT    BAMSTOT LY_BAMSTOT LLY_BAMSTO
- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
        5000       2000      12600      20000      45600      35000      45000      56000       65000

[Code]....

View 7 Replies View Related

Application Express :: Conditional List Of Values

Nov 15, 2012

Is there any way I could set the condition on the list of values.

For example if Value of the item x is null then use select ...

and when value of the item x is not null use this select ....

View 4 Replies View Related

SQL & PL/SQL :: Create Oracle Objects (Views) Using Conditional Compilation?

Jun 6, 2011

it is possible to create Oracle objects(Views) using Conditional compilation?

View 5 Replies View Related

SQL & PL/SQL :: Conditional Date Range Using CASE Statement In WHERE Clause

Mar 28, 2011

Is it possible within a CASE statement to put conditions on the date range that I want to pull? IE: am versus pm. The query has to pull specific time ranges for an AM run versus a PM run.
.....
FROM
table
WHERE
CASE
WHEN TO_CHAR(SYSDATE,'AM') = 'AM'
THEN table.date BETWEEN TRUNC(SYSDATE) AND SYSDATE
ELSE table.date BETWEEN TRUNC(SYSDATE+12/24) AND SYSDATE

View 6 Replies View Related

Application Express :: Button Conditional On Existence Of Another App In Same Workspace

Aug 24, 2012

Is there a way to make a button conditional on the existence of another app in the same workspace. What I am trying to do is have a MAIN app and several other MODULE apps in the same workspace. Then a button on a form in the MAIN app that will redirect to a specific page in one of the MODULE apps. If the MODULE app does not exist, meaning not installed, then the button would be invisible.

View 1 Replies View Related

Application Express :: Conditional Display With Re Captcha Plugin

Aug 22, 2012

I implemented the re Captcha plugin and it works ok. What I want to do is, on the same page where the captcha is displayed, to have a report region that is displayed conditionally based on the captcha result.

More specifically, the page has two items for user input (the captcha and a text item) and a Submit button. If the captcha is successful, then the text item will be submitted and the region displayed. The problem I'm running into is that the text item is always submitted regardless of the captcha.

View 3 Replies View Related

Application Express :: Conditional Read Only That Saves To Database?

Nov 27, 2012

I have an item that does not save to the database.it is conditional read only based on the value in another column, when it is not read only it displays as a Select list.. then whatever is chosen triggers a dynamic action that sets other items.so setting it as a display only is not an option.

after upgrading from 4.0 to apex 4.2 this gives error:   Session state protection violation: This may be caused by manual alteration of protected page...

the only workaround I have found is to set the Read Only to none, then create a dynamic action that conditionally sets the field to disabled..This works, but my problem with it is that it looks different than the read only..read only was Black Text and no textbox..disabled is a text box with greyed out text that is hard to read for some of the users..

View 4 Replies View Related

Application Express :: Conditional HTML Expression In A Classic Report

Sep 6, 2013

Apex 4.2I currently have a report column in a classic report that has a Column Formatting Attribute as below. This works fine it shows a map icon that when you click on it goes off to google maps and put 2 geo points on a map. However I need to change this report column so that the HTML expression will be different dependent on the value returned so if the column returns 1 then the HTML Expression will be slightly different. 

HTML EXPRESSION <span class="map_#BROKE_GEO#_#VALID_GEO_CODES#"><a target=_blank href="f?p=&APP_ID.:323:&SESSION.::::P323_PARCEL_ID,P323_SCAN_ID,P323_PROP_ADDR1,P323_PROP_POSTCODE,P323_PROP_LAT,P323_PROP_LONG,P323_SCAN_LAT,P323_SCAN_LONG:&P341_PARCEL_ID.,#SCAN_ID#,#LINE_1#,#POST_CODE#,#PROPERTY_GEO_LAT#,#PROPERTY_GEO_LONG#,#SCAN_GEO_LAT#,#SCAN_GEO_LONG#"><img src="#WORKSPACE_IMAGES#&P341_IMG_MAP_#BROKE_GEO#." alt="Google Maps" title="Google Maps"></a></span>  

I think I can do this in the report source directly with a case statement and include the HTML in that for the column but how do you handle all the quotes within the SQL statement. e.g.

Original Query SELECT     parcel_id    ,status    ,LOCATION    ,scan_date    ,scan_id    ,driver_comments    ,card_id    ,valid_geo_codes -- this is my html column    ,property_geo_lat    ,property_geo_long    ,scan_geo_lat    ,scan_geo_long    ,broke_geo    ,line_1    ,post_code    FROM vw_parcel_history_details  WHERE parcel_id = :p341_parcel_id  ORDER BY scan_date  New Query SELECT     parcel_id    ,status    ,LOCATION 
[code].....

various iterations of that large html string but I do not know how to format it because off all the quotes?

View 3 Replies View Related

Application Express :: Cannot Use Username Into Conditional Part Of Action Item

Sep 14, 2012

For our customer we have build a custom login page. The form we currently made has de following items:

- Username
- Password
- Select list for selecting the task type

When the customer enters his username, automatically the select list of the task type will be refreshed to get only the task type where the customer has the rights to do it. This part works...It can occur that a customer has only one task type (which is default) then we want to hide the select list. This part doesn't work

We used APEX version 4.1.1.What we did is:

- One action item to refresh the selection list (works)
- One action item to hide the selection list by starting the application (works)
- One action item to show the selection list only when the selection list has more than one result. For this part we used the conditional part of the action item (Function returning a boolean).

In the query (inside the function) we want to use the entered username, looking into the application in the session state I saw the correct username, but for some reason the function cannot use this entered username, it becomes empty. When I use a hardcoded username the code works.

Why I cannot use a username into the conditional part of the Action Item.

View 3 Replies View Related

Application Express :: Bug Report - Conditional Regions Affect Grid Layout

Mar 20, 2013

I had a few regions on my page, the 2nd did not start a new row so it appeared horizontally where possible.During some experimentation, I created a new region with a sequence in between that started new row, but even when that region was conditionally never, my original 2nd region started on new row.

Original:
Region 1 - Region 2(empty) - Region 3 (new row/col no)>
New region added:
Region 1Region 1.5 (added, new row)
Region 2
Region 3>
Region 1.5 not shown:
Region 1Region 2
Region 3>

Let me know if you would like a test case for better visualisation.

View 3 Replies View Related

SQL & PL/SQL :: Conditional Select - Query To Returns Results Based On Both City And Country Passed

Sep 17, 2010

Table A

Id Country city
1 US
2 US Boston
3 Boston
4 US Newyork
5 London
6 Japan Tokyo

Im looking for a query which returns results based on both city and country passed.

If i pass country US and city Boston it should return row2 with US and Boston row
If i pass country null and city Boston it should return row3
If i pass country UK and city Boston it should return row3
If i pass country UK and city London it should return row5

i.e. If country/city combination exists in DB return that row Else city row should be returned.

View 5 Replies View Related

Application Express :: Page Input Item Label Conditional Style At Run Time

Feb 6, 2013

We have an issue after migrating to apex 4.2.1. We have a select list and use another couple of dummy items to be used as its label instead of giving the text value to the LABEL control. Only one of the dummy items is shown as a label for the select list conditionally based on another value. It was working fine with the earlier versions upto 4.1, but with the introduction of grid in 4.2, its now displaying the select list on a new row (ie the lable in one row and the select list on the next row).

However, we really would like to solve this by having a way to display the label of the select list either in "required format style" or as an "optional style". The display style should be determined only at runtime.

Example. Say P100_MY_SELECT_LIST is the select list

The label for this item is "My Select List Label"

I have another dummy item P100_DUMMY

if P100_dummy = 1 then the label "My Select List Label" should be displayed in red and with an * *My Select List LabelOtherwise it should be in black and without * My Select List Label.

View 3 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 :: NULL Usage?

Mar 31, 2011

when to use || NULL oerator or what is the meaning of this statement when it is used with the column name.

and A.RtmtCd = B.RtmtCd|| NULL

or

A.RtmtCd||NULL IS NOT NULL

View 5 Replies View Related

PL/SQL :: How To Check CPU Usage

Mar 6, 2013

I have one procedure which is executing daily two times(i.e morning run and evening run) . In morning run it's executing around 150 mins and evening run executing around 25 mins.

It's happening for all procedures executing in longer run in morning. There is NO LOCKS, I have checked LOCKS while executing morning run. I suspects CPU usage in morning run.

How to check CPU usage ? AND also each session how much utilizing CPU. I'm using oracle 8i database, I know it's older version but my company is using oracle 8i. So i have to look in this database version only.

View 6 Replies View Related

SQL & PL/SQL :: Usage Of Hints In The Views

Oct 11, 2011

I have a table "tl" which is partitioned--say 30 partition and for each partition there is a seperate view like as follows

create view view_t130 as select * from tl partition (p30);
create view view_t129 as select * from t1 partition (p29);
.
.
.
create view view_t101 as select * from t1 partition (p01);

my question is how to use hints on this table if your are using view to access the data from internal table.

Normal structure is if i don't wrong:-

index( <<view name|view alia name>> <<table name|table alias name>> name of index)

Consider my case

select * from view_t130 where <index_column> --not picking up index

i want to give expicit index hint.so i used the same structure that i specified above but it didn't work.

select /*+ index( view_t130 t1 <index_name) */ * from view_t130 where <index_name>

how to give explicit index hint..but one constraint is i cannot give any alias names for internal tables because those(view structure) are generated by predefined scripts..so it's not possible to change it.

View 1 Replies View Related

SQL & PL/SQL :: Function BFILENAME() Usage?

Jul 31, 2011

I had a question on BFILENAME() :
first,

create or replace directory UTL_FILE_DIR as 'k:lob_test';
GRANT READ,WRITE ON DIRECTORY UTL_FILE_DIR TO user1;

and then ,I login use user : user1

create or replace package body p_utl_mail_attach_raw as
procedure send_single_user_attach_raw(........)
fil BFILE;
filenm VARCHAR2(50) := 'aaa.jpg'
begin
fil := BFILENAME('UTL_FILE_DIR', filenm);
end ;

this result it show :
UTL_FILE_DIR/aaa.jpg, exists=F, length=0, open=F

View 1 Replies View Related

Created Partition - Usage?

Nov 9, 2012

I have one table and I created partition month wise. I also creates index on that table. How can I check whether parition will be used when I query that table? In explain plan I can see PARTITION RANGE as ALL. Is it using the partition?

View 2 Replies View Related







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