SQL & PL/SQL :: How To Use Case When Return Multiple Values
Mar 13, 2012
I have a table second_table which has a username and code field. A username (not the primary key) may be entered multiple times with different codes, or a single code 'ALL', in which case, the codes have to be fetched from 'third_table'. I am unable to form a 'case' clause to handle the fact that a list has to returned when the 'code' field from the second_table returns 'ALL'.
e.g.
Second_table
username code
A ALL
B 23
B 56
B 33
Third_Table
code
67
78
So, when the user asks the codes for user A, he should get 67 and 78 and when he asks for the user B, he should get 23,56 and 33
View 13 Replies
ADVERTISEMENT
Aug 28, 2010
I want multiple values from a function. I want to use this function in a SQL query. Here i'm giving my try.
SQL> CREATE TABLE TEMP
2 (
3 ID NUMBER(1),
4 SAMPTYPE VARCHAR2(20 BYTE),
5 SALARY NUMBER(10)
6 )
7 /
Table created.
SQL> INSERT INTO TEMP VALUES(1,'ABC',10000);
1 row created.
SQL> INSERT INTO TEMP VALUES(2,'PQR',20000);
1 row created.
SQL> INSERT INTO TEMP VALUES(3,'JPD',5000);
1 row created.
SQL> COMMIT;
Commit complete.
[code]...
Here i get result as ABC*10000, but i want two separate values as ABC,10000. how can i do this via function.
View 6 Replies
View Related
Nov 6, 2012
I would like to know, if there is a solution under 10g for something like this
select case when "C" = 1
then
(select 1,2,3,4,5 from dual)
else
(select 6,7,8,9,0 from dual)
end
from dual,
(select 1 "C" from dual)
What I really need is the full result of (select 1,2,3,4,5 from dual) or (select 6,7,8,9,0 from dual) if a condition as a result of a different sql-statement is true or false. It doesn't matter if the solution uses "case when" or anything else.
View 6 Replies
View Related
May 4, 2010
find the Test Case below.
--Creation of Table
create table tb1
(ID number(4),
event varchar2(20),
vdate date);
--Inserting Values into the Table.
INSERT ALL INTO tb1 (ID, event, vdate) VALUES (01, 'V1', '01-JAN-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V2', '02-FEB-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V3', '04-MAR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V4', '03-APR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V5', '05-MAY-2009')
[Code]...
--Selecting data from Table.
SELECT * FROM TB1;
ID EVENT VDATE
---------- -------------------- ---------
1 V1 01-JAN-09
1 V2 02-FEB-09
1 V3 04-MAR-09
1 V4 03-APR-09
1 V5 05-MAY-09
2 V1 01-JAN-10
2 V2 02-FEB-10
2 V3 04-MAR-10
2 V4 03-APR-10
2 V5 05-MAY-10
10 rows selected.
how can i display the data as below format using Oracle 9i SQL.
IDV1 V2 V3 V4 V5
--- ---------------- ------------ --------------- -------------- ------------
11-Jan-092-Feb-094-Mar-093-Apr-095-May-09
21-Jan-102-Feb-104-Mar-103-Apr-105-May-10
View 4 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
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 15, 2012
I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production version
I am having the data in following table -
drop table stud_fact;
create table stud_fact(stud_NM, LVL_CD,ST_DT_DIM_KEY,OVRNK) as select
'ABG Sundal','H','20110630','175' from dual union all select
[Code].....
View 8 Replies
View Related
Apr 22, 2010
Client managerCont. Start DateCont. End Date
abcman11-Jan-0830-Jun-08
abcman21-Jul-0831-Dec-08
abcman11-Jan-0930-Jun-09
abcman11-Jul-0931-Dec-09
abcman21-Jan-1031-Mar-10
abcman21-Apr-1031-Aug-10
I need to code a SQL statement (Not PL/SQL) to display following records:
Client managerCont. Start DateCont. End Date
abcman11-Jan-0830-Jun-08
abcman21-Jul-0831-Dec-08
abcman11-Jan-0931-Dec-09
abcman21-Jan-1031-Aug-10
View 4 Replies
View Related
Jun 20, 2013
I am using: Desktop / Discoverer 4.1 / Windows XP.
I am attempting to add a new calculated column and have had some success with the CASE function but need to add additional criteria.
What I have that works is:
SUM(CASE WHEN Expenditure Type = 'Supplier Rebates' THEN Total Spend Plus Commit ELSE 0 END)
What I need to add are a few additional criteria. I attempted and failed with a few variants of this:
SUM(CASE WHEN Expenditure Type = 'Supplier Rebates' AND Capitalizable = 'Y' AND
Task Owing Company = '534' OR '915' THEN Total Spend Plus Commit ELSE 0 END)
The three criteria points that I am looking to includea are:
•Expenditure Type = 'Supplier Rebates'
•Capitalizable = 'Y'
•Task Owing Company = '534' OR '915'
View 8 Replies
View Related
Apr 23, 2008
how to retrieve values if user entered in Mixed case means (case insesitive)?
We don't have control when user enters values and it might be SMITH or Smith or smith or SmIth.
How can write the query to get this kind of mixed case data?
View -1 Replies
View Related
May 6, 2010
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?
View 6 Replies
View Related
Apr 30, 2010
I need to return results for the following query only when there are two or more rows found by the following:
SELECT DISTINCT D.PUBLICATION,
D.ADI,
D.DISTRICT,
D.ACCOUNT,
D.ROUTE,
[code]......
Expected results would be:
1 145 CRF TUANR000 VM BILLETING 16-APR-10 5 5 23-APR-10 48623
1 145 CRF TUANR000 VM BILLETING 16-APR-10 5 4 23-APR-10 48629
View 19 Replies
View Related
Apr 28, 2011
I want to search a some values in oracle table and then return the corresponding column names.
View 1 Replies
View Related
Aug 2, 2010
whether a function can return two values?
View 4 Replies
View Related
Aug 26, 2010
I want to return 100 values by using the procedure. In that we have to declare 100 OUT parameters. So it's a time consuming process.
Instead of declaring 100 OUT parameters. How can we return 100 values in a single shot.
View 1 Replies
View Related
Dec 19, 2012
the following procedure successfully compiled and executed. How to access the values returning from this procedure in another procedure or PLSQL block.
CREATE OR REPLACE PROCEDURE test IS
cursor c1 is SELECT a.idnumber, a.idp FROM holdings a;
r1 c1%rowtype;
BEGIN
open c1;
loop
fetch c1 into r1;
exit when c1%notfound;
DBMS_OUTPUT.put_line (r1.idnumber||'--'||r1.idp);
end loop;
close c1;
END;
View 14 Replies
View Related
Jun 23, 2006
can i have a pl/sql function that can return multiple rows
may be the syntax will be like
create or replace function multiple() returns ...
begin
select candidateid from tbl_candidateinfo;
..code to return the result of above statement to calling program..
end;
and functions will be called as
select candidateid from .. where candidateid in( select multiple());
View 13 Replies
View Related
Jun 6, 2013
I need a function that should return output of this query
SELECT b.branding_code, c.name_desc
FROM
development.brandings b, godot.company c
WHERE b.company_id = c.company_id;
This above function return 30 rows and I am not giving any input
Function using cursor,pipeline
View 5 Replies
View Related
Jun 7, 2013
If I return multiple rows says ex:100 rows then what will be data type for that return values.
View 1 Replies
View Related
Sep 6, 2012
I have a sql query as below :
select order_number,
(select decode(hcp.contact_point_purpose,'ABC',hcp.email_address,'CDE',hcp.email_address,null)
from hz_contact_points,
hz_parties hz
WHERE hz.party_id=hcp.owner_table_id) Email
FROM oe_order_headers_all h
WHERE h.order_number='102'
....................
..............
Actually the problem i am facing is the inner select query is returning multiple row , so my main query is erroring out, i need to capture the multiple row.
In the above example the inner decode statement returning two mail address, I need to capture that, but while executing the whole query it is erroring out as saying single query returns multiple values. capture multiple values
View 3 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
Dec 16, 2009
I am trying write a script that will return all values (based on the minimum tarif) from the Germany table for any duplicate values. Duplicate values are any values with the same UFI, ZC,limitid,depot. The German table also contains the fields tarif, city, supplier, etc.
Below is the script I have previously used to sort out duplicates. I have tried 50 different ways get it to return just lines for the minimum tariff but haven't been successful.
select *
from Germany t
where (ufi,zc,limitid,depot) in (
select ufi,zc,limitid,depot from (
select ufi,zc,limitid,depot, count(*) n
from Germany t
group by ufi,zc,limitid,depot)
where n<>1
)
View 4 Replies
View Related
Nov 29, 2012
I have a stored procedure which has varchar2 as IN and sys_recursor has OUT parameters.
CREATE OR REPLACE PROCEDURE check_values (
my_values IN emp.dept_no%TYPE,
p_cursor OUT sys_refcursor
)
[Code]....
/The problem I am facing is in where condition, if I give quoteValues it doesn't fetch me any records when I execute the procedure from sqlplus, but if I am giving my_values it does fetch me records. I am receiving IN parameters like 9856,9712,8723, so first I put single quote around the emp_no and pass that to where condition.
How can I resolve this issue?
View 15 Replies
View Related
Apr 8, 2013
I am using functions to return multiple values of two rows or multiple rows.
For example emp id = 100 and i need to return the value for this(empid) input and output of this first_name and salary.
I am tried in this way below but got errors (ORA-00932: inconsistent datatypes: expected NUMBER got HR.EMP_TYPE)
create or replace type emp_type as object(first_name varchar2(20),salary number);
create or replace function f1(empid in number)
return emp_type
as
emp_record emp_type;
begin
select first_name,salary into emp_record.first_name,emp_record.salary from employees where employee_id = empid ;
return emp_record;
end;
/
select f1(100) from dual;
View 12 Replies
View Related
Feb 16, 2011
I am new to SQL and I am just wondering if there is a solution to a problem I am having.I am using the piece of code below.Essentially what I am doing is selecting a field from a table and ordering that field in descending order. Using the Row_Number feature I then specify which Row I want to return.
Every day the row I will want is the Count of field1 for that day divided by 100 minus 1. This returns a single value of field1 and a single value of R.
I perform this operation every day. The only fields I change every day are the dates and the value of R. I use a seperate piece of SQL code to calculate R each day.
My problem is I have to often populate historical tables with this data. I can only run the code once for each day and for each value of R. Is there anyway I can alter this code such that it can return multiple values of field1 over several dates?The only way I can think of is to repeat the code multiple times using UNION but I am hoping there is a more efficient way.
SELECT *
FROM (SELECT Field1,
ROW_NUMBER() OVER (ORDER BY field1 desc ) R
FROM table
WHERE date >= TO_DATE ('20110215', 'YYYYMMDD')
AND date < TO_DATE ('20110216', 'YYYYMMDD')
)
WHERE R = 1227
--Note: 1227 = (count(field1)/100)-1
View 5 Replies
View Related
May 26, 2010
How to return multiple rows from the stored procedure in ORACLE..
View 2 Replies
View Related
Mar 27, 2013
EMP table
create table emp1 (empno number,deptname varchar2(30),deptno number,sal number);
insert into emp1 values (1,'Bank',10,1000);
insert into emp1 values (1,'Finance',20,400);
insert into emp1 values (2,'Finance',20,4000);
insert into emp1 values (3,'Account',30,3000);
commit;
select * from emp1;
actual output :
empno deptname deptno sal
1 Bank 10 1000
1 Finane 20 400
2 Finane 20 4000
3 Account 30 3000
Expected output :
empno deptname deptno sal
1 Bank 10 1000
2 Finane 20 4000
3 Account 30 3000
I am looking a output like above one. if any empno belongs to deptname Bank then give priority to that values else go to other dept like Finance but empno 1 should return only one row.i.e Bank dept only.
I have to return only one row based on dept values for one empno. how to do in sql or plsql?
View 11 Replies
View Related
Jul 3, 2013
I have a PL/SQL function where i call java class. In java i`m making some calculations and i need to return to body of PL/SQL function some values ( like 6 to 10, few numbers, string, 1 xml and 1 html).
I will show pl/sql function..
CREATE OR REPLACE FUNCTION GET_TAB(P_CURS SYS_REFCURSOR) RETURN TYP1_TAB PIPELINED IS
OUT_REC TYP1:=TYP1(NULL,NULL,NULL,NULL);
V_NAZWA VARCHAR2(5);
V_NUMER NUMBER;
[code].....
View 26 Replies
View Related
Dec 22, 2011
I have a table with multiple rows for the KEY attribute(its not a primary key) and a Rank for each row.
I want a query which fetches one row per KEY attribute.The row with lesser Rank should be considered. But in-case if the value is null for any column the value for next Rank should be considered.
WITH TMP_TBL AS
(
SELECT * FROM (
SELECT 'A' DUN,'1' RNK,'A21' col1,NULL col2,'A41' col3,NULL col4 FROM dual
UNION ALL
SELECT 'A','2','A122','A23',NULL,NULL FROM dual
UNION ALL
SELECT 'A','3','A32','A33',NULL,'A35' FROM dual
[code].......
DUN is the KEY attribute . RNK is the Rank for each Row. COL1... COL4 are data attributes
The results I am expecting is
DUNCOL1 COL2 COL3 COL4
AA21 A23 A41 A35
BB12 B23 B15
CC12 C13 C33 C14
I want this to be done with SQL only. So I tried various ways but none were successful.Finally I created a Multi Row function row_nvl and it worked.
SELECT DUN,
row_nvl(rownvl_param_type(RNK,col1)),
row_nvl(rownvl_param_type(RNK,col2)),
row_nvl(rownvl_param_type(RNK,col3)),
row_nvl(rownvl_param_type(RNK,col4))
FROM TMP_TBL
GROUP BY DUN
But I don't think my manager will allow me to deploy a Multi Row function .
View 2 Replies
View Related
Feb 26, 2013
I have the following database function.
GetRegionDetails(id in varchar2, o_lat out number, o_lon out number);
The problem is, the output values are returning as whole numbers ie. 38.108766567 is being returned as 38 and -78.16423574566 is returned as 78
what data type I should use so that my output is returns all the decimal values?
View 5 Replies
View Related
