SQL & PL/SQL :: Deduping Using CASE Statement
Jan 4, 2012
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
Household_ID Date
000001 1/1/2011
000001 3/1/2011
000001 12/30/2011
000002 3/15/2011
000002 6/30/2011
000002 9/15/2011
I would want my results to include:
Household_ID Date
0000001 12/30/2011
0000002 9/15/2011
View 5 Replies
ADVERTISEMENT
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
Feb 1, 2012
Depending on which month the user is running this select the TAG_YEAR needs to be calculated differently. I have a feeling that I'm over thinking it.
SELECT DOG_MASTER.DOG_MASTER_ID,
DOG_NAME,
TAG_YEAR,
TAG_NUMBER AS PREVIOUSTAGNUMBER,
ISSUE_DATE
FROM DOG_OWNER
[code].......
View 1 Replies
View Related
Aug 10, 2011
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'
How I should write a query to get 7 records.
View 2 Replies
View Related
Oct 11, 2011
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;
Output
NUM1NUM2SEQ
120100
222101
322102
4122103
51212104
61212105
But Required Output
NUM1NUM2SEQ
120100
222100
322100
4122101
51212101
61212101
View 4 Replies
View Related
Feb 11, 2011
I have the following tables:
create table lookups (code varchar2(20), amount number);
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.
View 4 Replies
View Related
May 16, 2013
How to use CASE stmt in WHERE clause?
View 3 Replies
View Related
Jul 13, 2010
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 :
CODESSPECIFIC_CATGeneric_Cat
11Head InjuryTrauma
12Head InjuryTrauma
13 Head InjuryTrauma
14Spinal InjuryTrauma
15Spinal InjuryTrauma
16Spinal InjuryTrauma
[code]...
just a small sample of codes, specific_Cat and generic_cat. I hundreds of these codes I need to categorize.
View 8 Replies
View Related
Mar 24, 2011
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 ?
View 5 Replies
View Related
Jun 24, 2011
select GAM.FORACID,
SOL.SOL_DESC,
GAM.ACCT_NAME,
LHT.LIM_SANCT_DATE,
LHT.SANCT_LIM,
to_char( GAM.CLR_BAL_AMT,'9,99,999.99'),
[code]...
i am getting error like this
ERROR at line 9:
ORA-00911: invalid character
View 4 Replies
View Related
Oct 24, 2013
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
View 3 Replies
View Related
Oct 26, 2012
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",
[Code]....
View 3 Replies
View Related
Dec 14, 2012
Here is one an example with CASE WHEN statement.
SELECT empno,ename,sal,
CASE
WHEN sal BETWEEN 800 AND 1200
THEN 'Lowest Pay'
[Code]...
even DECODE can be a replacement for this scenario, but it cannot have as flexibility as CASE can have.
Now, I want Equivalent code for DECODE..
View 11 Replies
View Related
Jul 10, 2013
In the following query WHEN
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'.
create table suppliers_temp(supplier_id number(5),supplier_name varchar2(10),supplier_type varchar2(10));
table suppliers_temp created.
insert into suppliers_temp(supplier_id,supplier_name ,supplier_type)values(1,'IBM','Hardware');
1 rows inserted.
insert into suppliers_temp(supplier_id,supplier_name ,supplier_type)values(2,'IBM','Hardware');
1 rows inserted.
insert into suppliers_temp(supplier_id,supplier_name ,supplier_type)values(3,'TCS','Software');
1 rows inserted.
[code]....
View 4 Replies
View Related
Feb 2, 2010
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"
View 2 Replies
View Related
Jun 3, 2010
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.
View 4 Replies
View Related
Apr 12, 2012
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
View 3 Replies
View Related
Oct 28, 2010
I am trying to use a CASE statement
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
View 10 Replies
View Related
Mar 12, 2013
I am trying to use the following case statement in my where clause. My problem here is, I get no rows.
tab1.col1 =
case
when (tab1.col1 = 'VAR') and (tab1.col2 is null or tab1.col2 >= tab2.datecol) then
tab1.col1
else
null
end
View 13 Replies
View Related
Feb 21, 2013
case when age <= 17 then '<= 17'
when age >= 40 then '>= 40'
else to_char(t.age)
end age
the case statement above doesn't work in my 8.1.7 cursor statement within my pl/sql block so I need an equivalent decode
View 10 Replies
View Related
Aug 28, 2013
I need to pull most recent status from a table with date field in the case statement.
status date
1 08/28/2013
2 05/12/2-13
3 02/11/2013
I need the status result of 1 (i.e most recent) and have to do in case statement only. Not interested in the date field in the final result.
View 1 Replies
View Related
Feb 20, 2012
Is there a way of selecting the value of a previous column in a case statement, without having to duplicate. E.g.
SELECT prod_id as Product,
Some complex calculation... as Total,
CASE
[Code]....
I do not want to re-write the same calculation in the else statement but just want it to be be the value of Total column.
View 1 Replies
View Related
May 29, 2013
Want to filter a data using CASE statement in WHERE clause for the following scenario.
Need to Filter tb1.fallback_keyword if the fallback_flag is "Y' or 'N' and pg_number is null.Else no partial search of keyword.
where CASE WHEN (fallback_flg = 'Y' OR fallback_flg = 'N') and (pg_number is NULL )
THEN tb1.fallback_keyword = SUBSTR(key_word,1, INSTR(key_word,'#',-2))
ELSE (tb1.keyword = key_word ) AND (tb1.keyword like regexp_replace(key_word, '[*]+', '%'))
END
View 3 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
Jul 10, 2013
In the following query which is highlighted ,I need to consider the records which have T.CURRENT_STATE='COMPLETE' AND 'CMPSCSRC' AND 'FINISHED' when M.MAINTAINED_FLAG = 'Y' AND S.SALE_LOCATION_ID = 118443 .So when i tried to write by using case stmt as follows it is giving me records only with T.CURRENT_STATE='COMPLETE'.But i want the records that satisfies all three current_states .
SELECT INI.UPC_ID,S.SALE_LOCATION_NAME,S.SALE_LOCATION_ID,I.KEYCAT_ID AS INITIAL_KEYCAT_ID,M.XLONG_NAME AS INITIAL_KEYCAT_NAME,
CASE WHEN M.MAINTAINED_FLAG = 'Y' THEN 'MAINTAINED' ELSE 'NON MAINTAINED' END AS INITIAL_MAINTAIN_DESC,
I.APPROVAL_USER_ID AS INITIAL_APPROVED_USER_ID,I.APPROVAL_DATE AS INITIAL_APPROVAL_DATE
[code]...
View 3 Replies
View Related
Aug 14, 2012
I have been trying to use case statements with oracle table type by really not sure how to go about it. I know it might be simple but it been giving me hard time.
Here is my Cursor:
CURSOR c_chk_style IS
SELECT DISTINCT 1
FROM TABLE(CAST(I_message.ExtOfXOrderDesc_TBL(1).ExtOfXOrderSkuDesc_TBL AS "RIB_ExtOfXOrderSkuDesc_TBL")) item_diff,
[code]...
Now i know that the table type "RIB_ExtOfXOrderSkuDesc_TBL" will be always populated but the table type "RIB_ExtOfXOrderPackDesc_TBL" may not be populate and can be null. So i want to run the exists against the "RIB_ExtOfXOrderPackDesc_TBL" aliased pack_diff only if it is populated. If its null i dont want to run the exists clause.
View 15 Replies
View Related
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.
View 0 Replies
View Related
Jun 13, 2013
Can I use CASE statement Inside a Oracle Report Query. I'm using Oracle Reports Builder 10g.
My Report Query looks like this,
select invh_code, invh_number, invh_date, invh_cm_code, im_description
from invoice_head, invoice_det, unit_of_measurement, item_master
where invd_invh_code = invh_code and im_code = invd_item_code
AND
(case :p_flag when 1 then (substr(invd_item_number,0,(length(invd_item_number)-4)) BETWEEN :P_V_ITM_FRM AND :P_V_ITM_TO)
else 1
end)
order by invh_number
:p_flag is a parameter that i pass from oracle form and based on that value (:p_flag=1) i need to include this specific condition
else omit that condition.
But the CASE statement is throwing Error
ORA-00907 :Missing Right Paranthesis
(case :p_flag when 1 then (substr(invd_item_number,0,(length(invd_item_number)-4))
==> BETWEEN :P_V_ITM_FRM AND :P_V_ITM_TO)
View 6 Replies
View Related
Aug 10, 2011
HOW to use variable P_TMPLID in following statement
TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;
because its throwing error while compiling
and also in statement
FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE
EXCEPTIONS
--STRSQL := '';
--STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i);
-- EXECUTE IMMEDIATE STRSQL;
INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement
commit;
--dbms_output.put_line(unrecondata(2).TRANSID);
EXCEPTION
View 2 Replies
View Related
Sep 13, 2013
In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition
ON(source.DNO = target.DNO
AND source.BNO=target.BNO);
I thought that using UNIONALL for select statement of the schemas as below.
SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....
View 5 Replies
View Related
