Complex Case Expression - Combine Two Statements To Get Desired Bucket
May 19, 2011
I have a case expression as follows:
(CASE WHEN DATEa=DATEb THEN 0
WHEN DATEa> DATEb THEN NETWORKDAYS(DATEb, DATEa)
WHEN DATEa < DATEb THEN NETWORKDAYS(DATEa, DATEb)
WHEN STATUS='PENDING' THEN NULL
ELSE NULL
END) AS RESULTa,
Now what I need to be able to do is place those results in buckets, similar to this:
(CASE WHEN RESULTa < 0 THEN '<0'
WHEN RESULTa between -1 AND 6 THEN '<=5'
WHEN RESULTa >5 THEN '>5'
ELSE ''
END) AS BUCKETa
I understand that I can't call an alias from a previous case expresson to get these desired results and how I could combine the two statements to get the desired bucket.
ive got two select statements which fetches data from different tables. I need to join the two result set . is it possible to do it from sql. Heres the query.
1)
SELECT COUNT(CASE WHEN (INTERACTION_TYPE= 'EmailED' AND CONTACT_PARTY=1) THEN 1 END)CUSTOMER_EMAIL, COUNT(CASE WHEN INTERACTION_TYPE= 'EmailED' AND CONTACT_PARTY=2 THEN 1 END)OTHER_EMAIL,
[Code]....
2)
SELECT SUM (CHEQUE_TOTAL) CHEQUE_TOTAL FROM RI_CHEQUE_VOUCHER_REFUND refund INNER JOIN CH_CASE case ON (case.id = refund.id) INNER JOIN EVA_ENTITY_DEFINITION ed ON (ed.name= 'ChequeRefundCaseED') WHERE case.creation_time<= SYSDATE AND case.creation_time>= SYSDATE-7
Again I need to combine the resultset.So the result would look like
I want to count the batch records using BATCH_ID with CASE statement ,for that i am using below query but its not working ,
SELECT COUNT(*) FROM <TABLENAME> WHERE VNBATCH_ID=CASE WHEN #SDC <10 AND #PERIOD >=10 THEN 0||#SDC||#PERIOD||#BATCH_ID WHEN #SDC <10 AND #PERIOD <10 THEN 0||#SDC||0||#PERIOD||#BATCH_ID WHEN #SDC >=10 AND #PERIOD <10 THEN #SDC||0||#PERIOD||#BATCH_ID ELSE #SDC||#PERIOD||#BATCH_ID END
below query is returning two rows.The thing now happening is the query is returning the output for both the case statements.But what is need only when the first case staement is NULL then it should go for second case.
SELECT DISTINCT CASE WHEN esc.x1 = Substr(inp.y, 0, 3) AND esc.x2 = Substr(inp.y, 4, 2)THEN esc.cc WHEN esc.mcc = Substr(inp.y, 0, 3) AND esc.mnc = Substr(inp.y, 4, 3)THEN esc.cc [code]....
I tried using rownum=1 but it filters out valid records.correcting the above query so that if the first case is null then only it should go for second case.
i am facing a very challenging situation and i am not able to write a query. consider the following data
SELECT 16 num, 17 num2 , 3 num3, 22 num4, 10 num5 FROM dual UNION ALL SELECT 9 num, 15 num2 , 21 num3, 2 num4, 24 num5 FROM dual UNION ALL
[Code]....
[URL]..... i am trying to write a query that given two values it will give you summation of the values within a particular bucket. for example, please see attachments, highlight in yellow.
if value 15 and 19 is given, i want to find the total sum of all values yellow (capture.jpg).
if value 8 and 21 is given, i want to find the total sum of all values in yellow (capture2.jpg)
if value 8 and 14 is given, i want to find the total sum all of values in yellow (capture3.jpg)
basically, when given two values, i draw a square or rectangle from starting number(first number) to end number and sum up all the values
write a query for such scenario. i heard it can be done with analytic functions but dont know how to use it. im using oracle 10g and 11g
I need joining the below 2 tables. I had used "between and " for Joining the table by using Inner Bound and Outer bound Key Columns and i use (current date - due_dt) for day calculation
My requirement is to show all the time buckets if at all no days falls in the particular time bucket.as like Outer join on Time bucket table
CREATE TABLE DATA1 ( ID NUMBER(6), DAT_ID NUMBER(6), RNK NUMBER(2) ); Insert into DATA1 (ID, DAT_ID, RNK)
[code]....
now after having this data from "data1" table , we need to get the row from "data2" table.in "data2" table there are total 6 combination on basis of "POS,ORDER" [ there are only 2 "ORDER" i.e. 'F' and 'S' , where as POS value can be changed, BUT THE "POS,ORDER" COMBINATION WILL HAVE ONLY 6 UNIQUE COMBINATION. ] so, for "POS and DAT_ID" combination we need to get the lowest rank data first, if that is not present then get the other rank given in "DATA1" table and so on and if no rank is present then select the NULL row row data from "DATA2" table for ex: in DATA1 table for count(*)>1 and id=1, we have data as
-------------- IDDAT_IDRNK
11231 11242 11253 --------------
so, in "DATA2" table, first we will see for "POS and ORDR" combination which DAT_ID is present, i.e. in case od POS=11 and ordr=F, we will select
111231FD1
as it is having lowest rank in "DATA1" table and it is present in "DATA2" table,
for POS=12 and ordr=F, we will select
121242FD1
as we don't have "123 and 1" in "DATA2" table so we will select the next rank given in "DATA1" ( i.e. 124 and 2 ), similarly , for POs=31 and ordr=S, we will select, as this is the next available rank and DAT_ID present in "DATA2" table
1D131S1253
and if there is no rank present from "DATA1" table in "DATA2" table then we will select the NULL row, i.e. for POS=21 and ordr=F, we need to select :
1D121F
"there will be 6 row for each id"
the output we want is : ----------------------------------------- IDNAMEPOSORDER DAT_IDRNK 1D111F1231 1D112F1242 1D121F 1D12321S1231 1D2322S1242
I'm trying to debug this function to get the desired results. See attachment for the function code and the test data insert script.
----Create Test Table CREATE TABLE VC_WORKINGDAYS ( WK_ID number NUMBER(10,0), WK_DATE DATE, );
-- Insert test Data INSERT INTO VC_WORKINGDAYS_1 VALUES (308, '25-MAR-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (316, '06-APR-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (324, '18-APR-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (332, '03-MAY-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (340, '13-MAY-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (348, '25-MAY-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (356, '06-JUN-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (364, '16-JUN-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (372, '28-JUN-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (380, '08-JUL-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (388, '20-JUL-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (396, '01-AUG-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (404, '11-AUG-11');
SQL ----- Result Should be WHY SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),1)) FROM DUAL 14/JUL/10 15/JUL/10 is 1 working day from today SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),2)) FROM DUAL 14/JUL/10 16/JUL/10 is 2 working days from today SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),3)) FROM DUAL 14/JUL/10 19/JUL/10 is 3 working days from today
Attached File(s)
create_Function.zip ( 6.39K ) Number of downloads: 1
Im doing some create view and create procedures for my work.In creating view, its just done perfectly.with create procedures work, I got some problem with the result.
So here is the coding:
create or replace PROCEDURE "USP_EDW_CASH_MARGIN" ( result_cursor OUT TYPES.cursor_type )
[code]....
After i run this coding. I got these errors : 1)Error(46,5): PL/SQL: SQL Statement ignored. 2)Error(46,5): PLS-00394: wrong number of values in the INTO list of a FETCH statement It says that the error is with this code "FETCH v_cursor INTO v_row;"
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,
For each TSKID/IDCODE combination there are 4 FIELDNBRS with some values. For each set of 4, there is one NBRSEQ which can change depending on the order in which the a set of same TSKID/IDCODE is entered in the table. For 1st set of 4 for a given taskid, it will be 1. For second set for same Taskid it will be 2 and so on. But if the second set has a different taskid then the number starts from 1 for the new TASKID.
Now I want to Find all the tasks which has same FIELDVALUE for a complete SET of 4. Lets say I want to find a task which meets following criteria:
I want to know if the query can be restructured to a less complex than this and fetch the same results. In our example it should fetch taskid = T222222222.
I have the following questions you can create a bulk collect in which he has 3 fields and one of them is a type collect failing index or other bulk collect?.If so, how would the procedure for when to insert the first record sub fill the bulk collect. being for example something like this:
----------------------------------------------------------------- index | codigo | nombre | telefono | | |----------------------- | index | telefono ----------------------------------------------------------------- | | | |
I have folliwng table CREATE TABLE abcd ( trans_type VARCHAR2(10) nn, original_tran_no NUMBER, original_line_item NUMBER(20), original_trans_dt DATE);
I have to create a constraint when trans_type = 'R'then (original_tran_no and original_trans_dt ) should be not null else can be null able; is the follwing stmt right?
alter table abcd add constraint t1_chk1 check ( ( case when trans_type = 'R' then (original_tran_no is not null and original_trans_dt is not null))
Can we execute more than one insert statements at a time (eg 10) in database and givecommit at the end of insert statements or else give a commit one by one after each insert statements ?
create table test ( name varchar2(50), descd varchar2(50) ) insert into test values ('kethlin','da,dad!tyerx'); insert into test values ('tauwatson','#$dfegr'); insert into test values ('jennybrown','fsa!!trtw$ fda'); insert into test values ('tauwatson','#$dfegr ,try');
how do I get the first three characters and last three characters from name field and remove all the junk characters from descd field?
I am having 7 tables consider A,B,C,D,E,F,G. whereas A is master table and others are dependent on table A. columnA is referential key for all tables. Table A is having one DateRange column.I have successfully created range partitioning with partitioning key is DateRange. and Refeernce partitioning on other tables with column referring to ColumnA with foreign key. Also created local indexes on partitioned key DateRange.
Problem is that, while fetching complex queries for reports, it is taking more time as compared to non partitioning structure. Is ref partitioning affecting on complex queries, queries returning more rows?
SELECT field1, COUNT(x) AS COUNT FROM my_table GROUP BY field1;
For field1 I want to get a count, but if field1 is like 'ABC%' then I want to combine all of those.
So if I have the following: ABC1 | 5 ABC2 | 10 XYZ1 | 3
I want results like this: ABC | 15 XYZ1 | 3
I've tried using some case statements like
SELECT CASE WHEN field1 LIKE 'ABC%' THEN 'ABC' ELSE field1 END AS field1, COUNT(x) AS COUNT FROM my_table GROUP BY CASE WHEN field1 LIKE 'ABC%' THEN 'ABC' ELSE field1 END;
but this just gives me ABC | 5 ABC | 10 XYZ1 | 3
How can I combine record 1 and 2 from the last record set example above?
i have a given pl/sql program that first deletes records out of a table and afterwards inserts new rows. now for example 2 rows out of 10 have a foreign constraint and can not be deleted that easily anymore. so i delete the ones i am able to (with the where not exists clause).
now i want to update the records who have a foreign key constraint and the rest with a regular insert. how would i do this the easiest way. i thought i could use insert with a where clause!!
I need to develop a complex report , i have information in following table.vehicle with two fields number date_of_renewal date what i need is to generate a calendar that will show all the vehicles that due for renewal based on the date.On the top it will be years , next is months for that year and below vehicles which needs renewal
There is XMLType table with structural storage. Is there a way to make schema validation disabled on some elements of complex type?
It is unpractical to maintain a schema for the element due to high volatility. Ideally it will be stored in CLOB and extracted as is a whole branch without validation, none of the elements under this complex type will be extracted separately.
1 select s.reg_no,s.course_code, 2 s.section src_sec,a.section a_sec,a.att_date,a.att_flag 3 from attendance a ,src s 4 where a.semester_code=1 5 and a.semester_year=2013 6 and s.semester_code=1 [code]....