Application Express :: Get Nested Case Statement To Evaluate One Result / Instead Of Multiple?
Mar 26, 2013
OK, Now that the syntax has been corrected with "Chanchal Wankhade" I have an entirely new issue. I am sure this issue has to do with my case statement logic. I am getting multiple rows, when I am only looking for one. Here is my code:
SELECT
CASE
WHEN EP.PHYSICAL_DATE IS NULL
THEN
CASE
WHEN EC.ORIGINAL_CONTRACT_START < ((SYSDATE) - 365)
THEN 'NEEDS PHYSICAL'
[code].....
However, only one of these rows should be the output, which is "No". How do you get a nested case statement to evaluate to one result, instead of multiple? I'm quite sure it is in the logic. To spell it out, this is what I am trying to accomplish with the above code: If the "EP.PHYSICAL_DATE" is null, then use these sets of formula's to evaluate the output, BUT if the "EP.PHYSICAL_DATE" is not null, then use these set's of formula's to evaluate the output.
As it stands now, it appears as if my nested case statement is doing exactly what I told it to do, which is to evaluate both conditions, and output both.
I'm working on a nested case statement, can't seem to get it right.We have a table that has injury_codes. What I'm trying to do is come with a nested case statement that will put the codes in a specific_cat, and based on the specific_Cat, assign a Generic_cat.
Example. I have codes 11,12,13,14,15, 15, 17, 18, 19, 20, 21, 22 Codes 11, 12, 13 have a specific_cat "Head Injury" Codes 14, 15, 16 have a specific_cat "Spinal Injury" Codes 17, 18 have a specific_cat "Burns".
All 3 of these specific_cat come under the generic_cat "Trauma"
Codes 19, 20 have a specific_Cat "High Risk Pregancy" Codes 21, 22 have a specific_cat "Premature Birth".
All 2 of these specific_cat come under the generic cat "OBGYN"...So my case stement should return :
I am trying to put transactions into 3 different "buckets" for each month of the year.
I am trying to figure out if the syntax for the "else" part of this code is correct... this will basically appear 12 times for each month of the year changing the value for month:
case when floor(sum(total_adjusted_net_amount)/100) < 0 and month in (01) then 0 when floor(sum(total_adjusted_net_amount)/100) > 29 and month in (01) and then 30 else when floor(sum(total_adjusted_net_amount)/100) and month in (01) end Groupin_01
I need to recreate my table in order to change the column order. When I use the SQL Commands window and run the SQL, it runs successfully, creates the new table, but doesn't reorder the columns. Is this expected behavior?
BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production
I'm getting this error while executing a package.But this is unpredictable because sometimes it's coming and sometimes it's not. Everytime I'm passing the value as 'ALERT' for the transaction name. Sometimes it's successful and sometimes it's throwing ORA-06592
CASE UPPER(IC_TRANSACTION_NAME) WHEN 'ALERT' THEN SELECT A.FACILITY_ID INTO VN_FACILITY_ID FROM ALERT A WHERE A.ALERT_ID = IN_PARENT_NODE_ID; INSERT INTO TRANSACTION_HISTORY (TXN_HISTORY_ID,
How can I search in Nested Tables ex: (pr_travel_date_range,pr_bo_arr) using the SQL below and insert the result into a new Nested Table: ex:g_splited_range_arr.
Here are the DDL and DML SQLs;*Don't worry about the NUMBER( 8 )*
CREATE OR REPLACE TYPE DATE_RANGE IS OBJECT ( start_date NUMBER( 8 ), end_date NUMBER( 8 ) ); CREATE OR REPLACE TYPE DATE_RANGE_ARR IS TABLE OF DATE_RANGE; DECLARE g_splited_range_arr DATE_RANGE_ARR := DATE_RANGE_ARR( );
[code]...
Or can I create a VIEW with parameters of Nested Tables in it so I can simply call
SELECT * BULK COLLECT INTO g_splited_range_arr FROM view_split_date(g_travel_range,g_bo_arr);
I've created a classic report that sums a couple of columns in two different regions:(Why can't I insert an image?)This is a simple example:
Region1col1 col2 col3 col4 col5 a b 20 5 25%c d 40 4 10%e f 120 6 5%report total: 180 15 x Region 2col1 col2 col3 col4 col5 g h 30 6 20%i j 70 14 20%report total: 100 20 yMy
How do I access the values generated by APEX (in bold) so that I can use them in further calculations? e.g. create a grand total from both regions
2) Having calculated a value, assuming 1 above is possible, how do I insert it at position x or y? e.g I want to calculate a % so that x would be 8.33% and y 20%
i am doing a data transformation using the following function
FLOOR(ONSET_TPQ-VAX_DATE)
Where ONSET_TPQ and VAX_DATE are both dates. Now in a situation where the ONSET_TPQ precedes VAX_DATE I want the result to be null for those records. As an example if ONSET_TPQ= 2000 and VAX_DATE=2010
I want the result to be NULL as I dont want negative values. Any exact SQl syntax on how to do that. DO i write a case statement?
I have multiple application set-up using session sharing within the workspace.Technically all works fine, however it does not play nice with user behaviour.
Example: Users logs into APP_ID:100. Since he isn't authenticated yet, he provides user/pass and the APP_SESSION is authenticated. Now if the user switches using to another application (using a link provided in application 100) he gets redirect to APP_ID:101:APP_SESSION all is fine.
The session is already authenticated, the application shares the session and the user gains access to app 101 without having to authenticate again. All's fine!
However users don't behave they way. Instead of using an easy link in the application. They will use their own bookmark or type in the url for app 101 manually. That way the next application is either called with f?p=101:1:[SOME OLD SESSIONID FROM BOOMARK] or f?p=101 (with no further page or session info) In both cases instead of using the already authenticated session apex spawns a new session, which of course isn't authenticated yet thus forcing the user to authenticate and come complaining they have to login again.
Similar behaviour problems exists when the user opens a browser and tries to open both applications in each in a tab next to each other.Both tabs fetch there own initial session id and start writing it to the same cookie each in turn invalidating the other tab's session.These can lead to some fanatic ping pong actions. Thus it's impossible to open 2 applications sharing session in the same browser.
Is there any remedy for these situations?Can apex be as smart as for instance first trying to resume the session stored in the cookie and only if that session is invalid, start a new session?
I have created a workspace to use oracle apex, after a while I tried to run a script, the following message has been displayed to me, The script output reached the maximum workspace result size limit. Delete existing results or contact your administrator to increase the maximum workspace result size.
I want to translate my application into multiple language,I did this by using translate application option available in shared component.This is working fine for me but ,the messages which is inside PL/SQL procedures are not getting converted.how can I translate those messages.
select empno,ename,deptno,employee_status from emp,dept where emp.deptno=dept.deptno and ( employee_status in(Case employee_status when {?Status}=1 then 'A' when {?Status}= 2 then 'T' When {?Status}= 3 then 'A'||','||'T')) OR ( end_date >= {?START_DATE} AND end_date <= {?END_DATE} ) )
Since when i pass employee_status as input 1 it have given me 4 records. When I pass employee_status as input 2 it have given me 3 records. When I pass employee_status as input 3 it should give me 4 records + 3 records=7 records.
4 records for employee_status 'A' 3 RECORDS for employee_status 'T' 7 records for employee_status 'A' AND 'T'
Can we use the sequence.nextval,sequence.currval inside case block.
CREATE TABLE EQUALITY_TEST(NUM1 NUMBER, NUM2 NUMBER, SEQ NUMBER); INSERT INTO EQUALITY_TEST VALUES ( 2 ,0 , NULL); INSERT INTO EQUALITY_TEST VALUES ( 2 ,2 , NULL); INSERT INTO EQUALITY_TEST VALUES ( 2 ,2 , NULL); INSERT INTO EQUALITY_TEST VALUES ( 12 ,2 , NULL); INSERT INTO EQUALITY_TEST VALUES ( 12 ,12 , NULL); INSERT INTO EQUALITY_TEST VALUES ( 12 ,12 , NULL); CREATE SEQUENCE SEQ_TEMP START WITH 100 INCREMENT BY 1;
Now i need to update SEQ column with SEQ_TEMP sequence. When NUM1,NUM2 values are unequal sequence should be incremented otherwise need to use the same sequence number(CURRVAL)
I have tried like this
UPDATE EQUALITY_TEST SET SEQ= CASE WHEN NUM1=NUM2 THEN SEQ_TEMP.NEXTVAL ELSE SEQ_TEMP.CURRVAL END ; SELECT * FROM EQUALITY_TEST;
insert into lookups values ('Rent' , 500); insert into lookups values ('Breakpoint' , 10);
create table products (id number, cost number, year varchar2(4));
insert into products values (1, 1000, '2011'); insert into products values (1, 2000, '2011'); insert into products values (2, 100, '2011'); insert into products values (3, 50, '2011');
commit;
I want to write a query which lists the IDs and the sum(cost), and a Y/N indicator which is set to 'Y' IF sum(cost) > ( (lookups.rent value) * (100 - lookups.breakpoint value))/100
I have written this query:
SELECT id, sum(cost)cost, year, CASE WHEN cost > ((SELECT amount [code]....... ORDER BY id;
This returns
ID COST YEAR YN --------- ---------- ---- - 1 1000 2011 Y 1 2000 2011 Y 2 100 2011 N 3 50 2011 N
The YN is correct, but it needs to sum the amounts. So there should only be one row for id1 = 3000.e.g.
ID COST YEAR YN --------- ---------- ---- - 1 3000 2011 Y 2 100 2011 N 3 50 2011 N
I am not sure how to do this. Or is there a better way of doing this than using CASE.
Table X includes multiple transactional records per household.I need to create a mailing list pulling only one record per household, choosing the one with the most recent date.
Each record within this table will have a Household_ID and a Date, so as an example, the table could have
When I tried to have a Cursor with SELECT CASE statement in Forms 6i , it is not working. But the same query is working in SQL PLUS . We cannot use case in Forms 6i ?
select iloan_code,inst_due_date,paid_flag,late_fee,case late_fee when sysdate-inst_due_date between 1 and 10 then 10 when sysdate-inst_due_date > 10 and late_fee <>10 then 5 when sysdate-inst_due_date > 10 and late_fee = 10 then 15 else 0 end as new_late_fee from st_il_schedule where paid_flag='N'; i am getting error
The line highlighted in Bold is where I have the issue. I want it to pick up NULL or Blanks in the method type has well as types E,G,T. I've tried NULL is the statement, "", and '' put none of these work. What am I doing wrong? I'm using 11g by the way
select specialty, member_id, sp_id, service_date, sum(case when method_type = 'C' then (payment_total) end) "CONSULTATION", sum(case when method_type = 'M' then (payment_total) end) "MOT", sum(case when method_type = 'D' then (payment_total) end) "TESTS",
supplier_name = 'IBM' and supplier_type = 'Hardware' THEN
only 'North office' is being retrieved but how to get 'South office' also along with it.Is there a way to write multiple conditions in THEN clause of case stmt something like THEN 'North office' AND 'South office'.
I have an APEX form that pulls up your Compensation Statement. The statements are on a secured windows environment. I need to disable the URL so employees cannot alter the URL and pick up another employees URL.
I am very new to Oracle APEX and having difficult with a SQL statement in a report page. I have the following sql code in the page region source.
Essentially, there are two drop down lists connected with the report. I want the first one (:P2_Retailer_ID) to be mandatory, i.e., the user has to select a value from the list, but the second one (:P2_JOBTYPES) to be optional.
So, if the user leaves :P2_JOBTYPES as "No Job Type Chosen" then the SQL returns all records where the retailer ID = P2_RETAILER_ID whereas, if the user also selects a value for P2_JOBTYPES then the SQL returns all records matching both on Retailer ID and Job Type.
However, for some reason, if the user leaves P2_JOBTYPES as "No Job Type Chosen" then no records are returned as it appears to be taking the '%' as a literal value rather than a wildcard.
SELECT * FROM RETPERFFW Where "Retailer_No"= :P2_RETAILER_ID AND (CASE WHEN :P2_JOBTYPES <> 'No Job Type Chosen' THEN :P2_JOBTYPES ELSE '%' END )LIKE "Job_Type_Schema"
The code written in PL SQL.I am using '&' operator to enter the values at run time in nested case statements.But when I chosen a value for case statement even though it is asking all the values which are not part of the selected case statement.I need only the values related to my choice in case statement.
choice1:= &CHOICE_NO1; CASE(choice1) WHEN 1 THEN DBMS_OUTPUT.PUT_LINE('** ENTER YOUR CHOICE TO PERFORM THE OPERATION: **'); DBMS_OUTPUT.PUT_LINE('** (1)INSERTION (2)DELETION (3) UPDATION **'); [code]....
that is my exact code.when I enter value 1 for choice1 and 2 for choice2 It should ask the values only for student_ scorecard. delete_ record (& STUDENTID); &studentid. But it is asking all the functions (student_scorecard.existing_marks(&ID,&SUBJECTNAME));(student_ scorecard. std_ major_ name (&STD_ID)) and procedures.I dont want that.
CASE WHEN (EP.ORDER_NUMBER IS NOT NULL AND ATD.IS_DISCONNECT_CREDIT = 1 ) THEN ......... ...... END
In my case when I club two conditions after WHEN in case statement , everytime the first condtion is satisfied (i.e EP.ORDER_NUMBER IS NOT NULL) it is entering into the loop.
Irrespective of using AND or OR after the first condition I am getting the same results.
I want to enter the CASE statement only when both the conditions (1st and 2nd )are satisfied