SQL & PL/SQL :: How To Get Multiple Column Values In A Row
Oct 30, 2012
Here is my requirement..... I am pretty new to PL/SQL.
This is the procedure
CREATE OR REPLACE PROCEDURE ABCPROC.SP_ABC
(
XML IN CLOB,
P_refcursor OUT SYS_REFCURSOR
)
AS
BEGIN
[code]........
For each account number (I_AC), we have multiple rows in ABC_SVC table. I want to return these values in the refcursor. The issue with above SQL program, the row is returning only when the account(I_AC) have values for all the products 100,101,102. If the row does not exist for one account, then the account row is returned with other products.
I want my output should be in the below format
ACCOUNT_NUMBER COMMISSION CONSUL CONTRA
1YYN
2NN
3N
i'm trying to put more than one value in one column (by putting value '26','63' in column), so i have next problem:
1. Query select cr_pjid from acc_users where username='ACCBTPS121' give result CR_PJID --------- '26','63'
2. Query select * from acc_accbtp_nova_view where ed_id=2 and to_char(pj_id) in ('26','63'); return 186 rows.
3. Query: select * from acc_accbtp_nova_view where ed_id=2 and to_char(pj_id) in (select cr_pjid from acc_users where username='ACCBTPS121'); doesn't return any row...
Most of the code is working properly except when it come to a person with more than one email type in the table:
Below is the
select emal_pidm, emal_email_address, emal_emal_code, case when emal_emal_code = 'PER' and lag(emal_emal_code,1,'?') over (partition by emal_pidm order by emal_email_code) = 'EMPL' then emal_email_address [code]......
Below is Sample data for testing
INSERT INTO emal (emal_pidm, emal_email_address, emal_emal_code) VALUES (1024069, 'emmaus.ferdinand@xxxx.edu','EMPL'); INSERT INTO emal (emal_pidm, emal_email_address, emal_emal_code) VALUES (1024069, 'emfer1@xxxx.edu','PER'); [code].......
The attachment is what the output should look like.
create table teststr (indname varchar2(20), counter1 number,counter2 number,counter3 number,counter4 number); insert into teststr values('a',10,20,30,30); insert into teststr values('b',10,20,5,3); insert into teststr values('c',2,4,5,2); insert into teststr values('d',1,2,3,4); insert into teststr values('e',4,5,4,4);
Now i need the output if any of the column values are same.
output should be
select indname from teststr where counter1=counter2 or counter1=counter3 or counter1=counter4 or counter2=counter3 or counter2=counter4 or counter3=counter4
a c e
Is ther any other way to write the query instead of the numerous or conditions if i want to compare the column values in a table.
I'm trying to select id's in a table that have 2 certain values for another column. Example below explains:
idCoupon Type 123Amount 123Percent 456Amount 789Percent
I would like to write a sql statement that would select all rows where id=123, because id 123 has both coupon types "Amount" and "Percent". So the result set of the sql statement would look like:
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE i_e NUMBER(10); BEGIN FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL) LOOP FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
From two given tables, how do you fetch the values from two columns using values from one column(get values from col.A if col.A is not null and get values from col.B if col.A is null)?
On my APEX page i have region which has sql query as source and it displays as HTML table the query result to the user.
I want to display addinonal column with a hyperlink inside, and that hyperlink would have CGI/URL-parameters which contains the other values of the HTML row.
So, let's say my APEX region queryes columns as "select c1, c2, c3, c4 ..." and displays out values "V1, V2, V3, V4" then i want to have addional output column with such hyperlink:
a href="f?p=100:7:13467554876288::NO::c1,c2,c3,c4:v1,v2,v3,v4">My link column with CGI-parameters</aHow can i create such hyperlink?
The overall idea is that the link would forward to a page which loads those values "v1,v2,v3,v4" into form fields and user can proceed from there.
I want to pass multiple column values of a row in an interactive report page to hidden items in another page through column link. And I did it successfully. However, I found I need to pass more than 3 columns of a row in this report, while a column link only permits me to pass 3 column value at most. Is there anyway that I can pass more column values to hidden items in another page?
Desired Output: ====== First Second Third 11 21 31
I have tried the below query SELECT DECODE (name,'Nexus', parameter) First, DECODE (name, 'GPlay', parameter) Second, DECODE (name, 'Demo', parameter) Third FROM (SELECT name, parameter FROM TableA where name in ('Nexus','GPlay','Demo'));
This gives me the output
First Second Third 11 <Empty> <empty> <empty> 21 <empty?> <empty?> <empty?> 31
Is there any way to get the output in single line.
I want to check to see if that string contains any of the values in:
select code from codes_table;
For example, if
select code from codes_table;
returns: code ----- AB LM NO PQ
then the query should return 'True'. Using the string above it would return 'True' because 'AB' from the string exists in the table codes_table. Pseudocode would look something like this I guess:
if the input string contains any of the codes in the field 'code' from table 'codes_table' then 'OK' otherwise 'No good!'
I have used sql for my school projects and work projects. not too extensively though. I came across this issue and read a lot of blogs but still not luck.
I need to obtain all the sub_grp_nbr's that have a prodt_ctgy_cd of 05 but not 02. so according to the data above, i should only get the results of where extra_column = 3 and 6
then these values are present as substring in the particular column in the source view. So I need to flag those records. For every record, I need to check whether all the values present in the reference table matches or not. If it matches then it should be flagged.
I can use in operator as we are not checking for the exact match and we are checking whether that value is present anywhere in that column record.
Looping results in performance issue. We can use PL/SQL for this. As the source view is put into a ETL internal file.
SELECT id, pob,exp FROM emp where exp= (:exp)or @exp is null O/P: id pob exp _____________________ 1 CT 2 2 NJ 3 3 NY 2
It takes only one value of :exp but, I would like to give multiple values separated by ','. My problem is the parameter can be either single value or Blank or multiple values. My code do work for single value and Blank, Now how to implement multiple values .
with t as ( select 1 id, 101 book_id, 'MICROBIOLOGY' book_type, 1 category, 'sCIENCE AND TECH' category_name from dual union all select 1 , 101 , 'MICROBIOLOGY', 2 , 'HEALTHCARE' from dual union all
[Code]....
id book_id BOOK_TYPE category category_name
1 101 MICROBIOLOGY 1 SCIENCE AND TECH 1 101 MICROBIOLOGY 2 HEALTHCARE 1 102 CHEMISTRY 5 CHEMICAL ENGINEERING 2 105 COMP SC 1 SCIENCE AND TECH
The above is the output for a query after joining multiple tables. I have just put here the output I am getting after joining the tables.Now I want to achieve the below result.
Expected output: it should be | delimited
1|101|MICROBIOLOGY|102|CHEMISTRY|1|sCIENCE AND TECH|2|HEALTHCARE 2|105|COMP SC|1|SCIENCE AND TECH
Is there any alternative way other than SYS_CONNECT_BY_PATH? I also tried to use CONCAT_ALL but its not working.
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
I have a function that returns the total sum of an account. From reports I call the function passing the account code. The function sums the values for that specific account code and returns the value. In my function I have the following code :
where account_code = P_CODE.
Eg. The value of :P_CODE is 'CS'.
I now want to pass multiple account codes ('CS','TV',LJ') to the function. How do I change the IN clause in the function to accommodate multiple values.
I have tried using the instr function, but it does not work. eg. AND instr(o.ACCOUNT_CODES,','||P_CODE||',') > 0
assigning values to a particular variable that i need for my button trigger. I Understand that you can assign multiple values to a variable that has a varchar or char data type....is there a way to assign multiple values to a variable that has a 'number' data type?? I need this for my 'where' clause
declare usergrade varchar(4) := 'pass'; user_unitcode number(6) := ;--needs three unit codes to equal pass
CREATE TABLE prim_tbl (id NUMBER,--- id is not primary key here description VARCHAR2(30));
INSERT ALL INTO prim_tbl VALUES (1,'aad') INTO prim_tbl VALUES (1,'aads') INTO prim_tbl VALUES (2,'bb') INTO prim_tbl VALUES (2,'cc') INTO prim_tbl VALUES (2,'dd') SELECT * FROM dual;
I want to select the ids only one time, i.e my output will have only two rows: one row with id as 1 and other row with id 2 whatever be the description.
desired output sample:
Quote:1, aad 2, bb
I used: select distinct(id),description from prim_tbl;
but it did not give the required result.How can I get it??
