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.

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: Combine Two Independent Select Statements?

Dec 10, 2010

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

Customer_Email Other_Email Customer_Whitemail Other_whitemail Customer_telephone Other_Telephone Cheque_total

View 3 Replies View Related

PL/SQL :: How To Use CASE And COUNT Statements In SELECT QUERY

Oct 13, 2012

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

View 11 Replies View Related

Query Is Returning Output For Both Case Statements

Jun 2, 2011

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.

View 2 Replies View Related

PL/SQL :: Summation Of A Bucket

Jun 13, 2012

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

View 4 Replies View Related

SQL & PL/SQL :: Time Bucket Outer

Aug 16, 2012

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

Loan_Account_summary

SNo Due_dt AMT
-----------------------------
102-08-201250000
203-05-201245000
309-05-201230000
415-09-201225000
531-05-201215000
608-06-201235788
719-10-201255000

Time Bucket

TB_IDTB_DESCReserve%Innner Bound outer Bound
------------------------------------------------------------------
1011-72.25 1 7
1028-302.5 8 30
10331-603 31 60
10461-901.75 61 90
10591-1201.5 91 120
106121-1801.25 121 180
107180+0 181 99999

Expected Output

TB_DESC Reserve AMT
----------------------------
1-7500000
8-30154822
31-600
61-90750000
91-1200
120-1800
180+65842

View 2 Replies View Related

SQL & PL/SQL :: Query To Get Data In Desired Format?

May 29, 2012

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

[code]....

View 4 Replies View Related

Function Not Returning Data (Desired Results)

Jul 14, 2010

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

 InsertData.txt ( 84.34K )
Number of downloads: 0

View 1 Replies View Related

PL/SQL :: Create View And Procedures - Not Getting Desired Results?

Jul 16, 2012

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;"

View 6 Replies View Related

PL/SQL :: ORA-06592 / CASE Not Found While Executing CASE Statement

Oct 16, 2013

My DB version is

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,

[code]....

View 23 Replies View Related

SQL & PL/SQL :: Expression Must Have Same Datatype As Corresponding Expression

Dec 22, 2010

ved>create table test900 ( a number, b number);

Table created.

ved>insert into test900 values( 9,'');

1 row created.

ved>insert into test900 values( 10,null );

1 row created.

ved>select * from test900;

A B
---------- ----------
9
10
ved>select nvl(length(b),0) from test900;
[code]....

ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression..Why the above sql ( case 2 ) gives error?

View 9 Replies View Related

SQL & PL/SQL :: Combine Both Selects Into One?

May 11, 2012

Is it possible to combine both these selects into one.?

SELECT a.grouping_set_member_id,
a.service_agreement_id,
a.enrolment_group_id,
a.enrolment_group_class_id,
a.effective_date,

[code]...

Where l_service_agreement_id,l_enrolment_group_id,l_ENROLMENT_GROUP_CLASS_ID,l_effective_date
are vaues from the First SELECT.

View 2 Replies View Related

SQL & PL/SQL :: Query Can Be Restructured To A Less Complex

Apr 27, 2010

I have a table "TSKIDENT" with below structure:

CREATE TABLE TSKIDENT
(
TSKID CHAR(11 BYTE) NOT NULL,
NBRSEQ INTEGER,
IDCODE INTEGER,
FIELDNBR INTEGER,
FIELDVALUE CHAR(50 BYTE) NOT NULL
[code]....

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:

IDCODE = 97
FLDNBR = 1
FIELDVALUE = 1
IDCODE = 97
FLDNBR = 2
[code]....

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.

View 5 Replies View Related

SQL & PL/SQL :: Complex Bulk Collection?

Mar 25, 2011

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
-----------------------------------------------------------------
| | | |

[code]...

View 2 Replies View Related

SQL & PL/SQL :: Facing Complex Constraint?

Sep 27, 2013

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))

View 10 Replies View Related

SQL & PL/SQL :: Insert Statements / Give A Commit One By One After Each Insert Statements?

Oct 11, 2012

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 ?

View 8 Replies View Related

SQL & PL/SQL :: Removing Special Characters And Get Desired Characters From Column Values

Jul 23, 2013

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?

so my o/p be like;

Quote:('ketlin','dadadtyerx')
('tauson','dfegr')
('jenown','fsatrtw fda')
('tauson','dfegr try')

View 6 Replies View Related

SQL & PL/SQL :: To Get Output From Table - Possible To Combine A Row?

Mar 6, 2012

To get output from a below mentioned table? can we combine a row like that?

Table: Test_Fruits

ID_NO_1 LABELS1 ID_NO_2 LABELS2
------- -------- ------- --------
1Fruit
2Vegitable
4 Apple
3 Potato
-----------------------------------------
Expecting output:
Pls guid me how to get like this?
ID_NO_1 LABELS1 ID_NO_2 LABELS2
------- -------- ------- --------
1Fruit 4 Apple
2Vegitable 3 Potato
-----------------------------------------

View 6 Replies View Related

SQL & PL/SQL :: Combine Query Output In One Row

Aug 13, 2010

If I have below two sql statment.

select count(*) from table_a;
select count(*) from table_b;

How can I output it in one row together like below

Table_a | Table_b|
10 | 20|

give me detailed example ....

View 2 Replies View Related

SQL & PL/SQL :: Combine 2 Queries Into 1 Without Using UNION

Jun 5, 2012

SELECT b.KPCNO
,b.KPC_FULL_NAME
,min(c.time) in_time
FROM xxkpc_hr_personnel_v2 b
,xxkpc_fingerscan_data_v c
[code]........

View 5 Replies View Related

SQL & PL/SQL :: How To Combine 3 Decodes Together On One Line

Mar 4, 2011

I am having trouble putting all 3 of these decodes together on one line. I would like my output to return like this:

ytd_state_tax ytd_ss_tax ytd_medicare_tax
============= ========== ================
182.29 163.28 56.37

if deduct_type=6012 return sum(pdt1.deduct_amount) YTD_MEDICARE
else
if deduct_type=6020 return sum(pdt1.deduct_amount) YTD_STATE_TAX
else
if deduct_type=6010 return sum(pdt1.deduct_amount) YTD_SOCIAL_SEC

[Code]....

View 7 Replies View Related

SQL & PL/SQL :: How To Combine Queries When WITH Clause Is Used

Jun 21, 2010

I have 4 select queries.

Query1
Query2
Query3
Query4

First Step:Combine Query1 and Query2,but the requirement is Query2 should only use the Acct_ID and Bill_ID which are output from Query1.

Second Step:Combine Query3 and Query4,but the requirement is Query4 should only use the Acct_ID and Bill_ID which are output from Query3.

Third Step: Is to now combine both the data set from First Step and Second Step.

In order to achieve my First and Second Steps I used WITH clause.

With S1 as ( Query1 ),
S2 as ( Query1 Union Query2)
Select S2.* from S1,S2
where S1.ACCT_ID=S2.ACCT_ID
AND S1.BILL_ID=S2.BILL_ID

With S3 as ( Query3 ),
S4 as ( Query3 Union Query4)
Select S2.* from S3,S4
where S3.ACCT_ID=S4.ACCT_ID
AND S3.BILL_ID=S4.BILL_ID

1. Is that approach right for achieving my First and Second step requirements ?

2. How to achieve Third Step ?

View 3 Replies View Related

REF Partitioning Is Affecting On Complex Queries

Apr 30, 2013

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?

View 1 Replies View Related

Aggregate Function - Combine Results

Nov 24, 2009

I have a query more or less like this:

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?

View 4 Replies View Related

SQL & PL/SQL :: Combine Two Queries To Get Full Output

Jun 8, 2011

how to combine 2 queries as attached in notepad file in order to get full output?

View 3 Replies View Related

SQL & PL/SQL :: Combine Update And Insert Command?

Jul 23, 2012

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!!

here i have some part from the original

declare
procedure add(
i_id pls_integer,

[Code].....

View 3 Replies View Related

Reports & Discoverer :: Develop Complex Report?

Feb 1, 2011

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

View 2 Replies View Related

PL/SQL :: Update Table Column Using Complex Sequence

Feb 6, 2013

I use a sequence to generate an alphanumeric code and I need to update all rows of a table with that sequence. The sequence is created with this code:

SELECT (CHR(TRUNC(s.nextval/1000) + ASCII('A')) || LTRIM(TO_CHAR(MOD(s.currval,1000),'099'))
FROM dual;

I have tried many things - most recently

Update t3_temp_table
SET mergecode =
SELECT (CHR(TRUNC(s.nextval/1000) + ASCII('A')) || LTRIM(TO_CHAR(MOD(s.currval,1000),'099'))
FROM dual;

I get missing expression error.

It works fine if I use a straight s.nextval

View 5 Replies View Related

XML DB :: Optional Validation Of Some Complex Type Elements

Jul 31, 2012

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.

View 3 Replies View Related

SQL & PL/SQL :: Delete Rows Returned By Complex Join Query

Apr 5, 2013

How Can I delete the returned two rows?

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]....

View 6 Replies View Related







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