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
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
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
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
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
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
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
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
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
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
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
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
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
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
Jun 6, 2011
it is possible to create Oracle objects(Views) using Conditional compilation?
View 5 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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