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.
I would like to populate a text box based on selection from a LOV. If someone selects a LOV value and then tabs off off of that element, I would like the text box populated from a sql statement based on the LOV value in the predicate.Application Express 4.1.1.00.23
I am trying to develop an ongoing interview schedule.
interviews may be held on FRIDAY, SATURDAY, TUESDAY or any other day or Days in a every week selected by the user and also at some specific time.
we need to schedule the closest comming day based on Application date.
for example: today is WEDNESDAY and our schedule interview day is FRIDAY and SUNDAY. If one application is received today we need to schedule interview on FRIDAY because this is the nearest/closest day of WEDNESDAY. and if the application is received on Saturday then of course Sunday will be scheduled.
if Application is received on same day e.g. FRIDAY is scheduled and we receive Application on FRIDAY then comes the concept of time. interview is schedules on FRIDAY 11:30 AM.
if application is received on before 11:30AM, the same day will be scheduled. If application is received after 11:30 AM then of course next closest day will be scheduled.
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)
i have a parameter form where user will input from item to item and items within this range will be come as output , but i want an option to select the random items from the list, like i may not select all the items from 1 to 10 , but instead i want to pick or select randomly , i may opt for 1,3,4 and 7.
Our term (strm) is dictated by the term_begin_dt and term_end_dt dates but I want to keep selecting that term until 1 week before the next term opens and then switch to that term.
Basically, I don't want any gaps between a term.
Output:
select strm when sysdate is between term_begin_dt and term_end_dt (strm would equal 3943) select strm until 1 week before the start of the next term (4027) (strm would equal 3943) select strm when 1 week before term_begin_dt (strm would equal 4027)
Repeat for the next term and so on 12/16/2011 select strm from term where trunc(sysdate) between trunc(term_begin_dt) and trunc(term_end_dt)
Output: 3943 12/17/2011 - 01/01/2012
select strm from term where ?
Output: 3943
01/02/2012 - 5/4/2012 select strm from term where ?
Output: 4027
Repeat.
Test Case:
CREATE TABLE TERM ( STRM VARCHAR2(4 BYTE), DESCR VARCHAR2(20 BYTE), TERM_BEGIN_DT DATE, TERM_END_DT DATE )
Insert into TERM (STRM, DESCR, TERM_BEGIN_DT, TERM_END_DT) Values ('3943', '2011 Fall Semester', TO_DATE('08/22/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/16/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into TERM [code].....
I have a question regarding a selection limit from a list of values. The table where my IDs (unit_codes) come from has another column called 'points'. each ID has its own number of points. Is there a way to put a selection limit on the display field that points will be placed on?
FOR EXAMPLE: a course block has a selection limit of 70 points, A person named someguy selects : music = 20 points maths = 20 points mathsXTRA = 40 points **ALERT!!! - U can ONLY select 70 points worth of units**
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.
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]........
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);
I've got a Source Data in complex relational formFor reporting purposes, a simpler, less normalized data model is neededThere are two Target views from the Source Data: one of them with full access to all datathe second one with access only to a subset of the data (same columns, but not all the records)For both target groups, a separate schema shall be available, each containing only relevant dataToday, these schemas are physically located on the same DB instance and host as the source dataA daily refresh is sufficientA later relocation of the reporting schemes to other DB instances shall be possible without major changes neededOracle 10g should be used I tried to accomplish this using Materialized Views (Materialized seems better since there will be sometime a need to have all the apropriate data somewhere else, geographically, AND it provides Complete Refresh from the Source), but there is a problem: when creating the MV there is a possibility to type 'SELECT *' - but after execution it changes into real columns names. It is important because later after adding a new column into Source Data it WILL NOT appear in MV after refresh.
I also thought about Data Guard, Streams and RAC, but I think only in the Materialized Views you may choose the data to show (rows, columns).
i am facing a problem in functionality of a form as there are different search parameters: date from: text item-->mandatory date to: text item---->mandatory house type: list item-->optional form_status: list item->optional order by: list item---->optinal
untill i don't select house type by list item everything is working fine and gives desired result, even with house type if i select any house type result is fine but as i select last list item of house type which is null as i want result without house type then it gives no any data as data is present in database.
for example: date from: 20-05-2009 date to: 21-05-2009
with these two i got the correct result, then i select house type: 3
i got the correct result and when i change list item to any value like: 1,2,3.....it is working fine but as i want result without house type by selecting the null value in list item as: date from: 20-05-2009 date to: 21-05-2009
it is not picking any data from database. but the same search criteria in 1st case as i described above giving result. declare qry varchar2(5000); n number; alert number;
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.
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)
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].......
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.
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.
I have a requirement to select multiple values from a list item and do some manipulations with those values. I tried by integrating the LOV with a text item, but couldn't able to select multiple items. Is there a way to do multiple selection in a list item ?
I have created three accordians where there will be sub tabs under each Accordian. There will be two logins. Where when i login with Admin all the 3 accordians will be shown. But in the user logins 1st Accordian will be hidden and only 2nd & 3rd will be shown with their sub tabs.
In order to highlight the tab & also for Accordian to be open based on the selection, i wrote code as follows
in the page under the Execute When Page Loads region where ('activate',0) means the position of the accordian which will be taken as 0,1,2..etc.But when i login as user then as the 1st accordian will be hidden & the 2nd and 3rd accordians will become 1st & 2nd. So 1st & 2nd accordian wil be consider as 0,1 positions here.
So when i click on the 2nd accordian tabs then the accordian is not opening because in the pages of this accordian tabs i have given the code as
as the active is 1 so the accordian in position 1 is opening but i want the accordian in 0 position to be opened as i selected the tab under that accordion only.
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;
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
