SQL & PL/SQL :: Separated Result With Multiple Values
Aug 24, 2010
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.
Here is what i have in ms-sql, how to convert this into t-sql ?
@MortgagePurposeID is parameter with comma seperated values ('1,2,3,4') if(substring(@MortgagePurposeID, LEN(@MortgagePurposeID)-1,1)<>'','') Set @MortgagePurposeID = @MortgagePurposeID + '','' Set @pos=0
I am building a search for use in one of our major applications. I have written a PL/SQL package that deals with it. I would like to present the requirement list to the group and see what, if anything, you may have done differently than I have.
1.) The search interface must have a single box, like google.
2.) Multiple search terms will be separated by a comma.
3.) The table has the following columns: -- Name -- Title -- addr -- addr2 -- city -- state -- zip -- phone -- email
4.) Number of Search Terms per query will be unlimited. (for now, as practicality dictates)
5.) Each search term will be checked against various columns.
6.) Search terms must not have a preference in order. Name, Address = Address, Name
7.) Records will be returned only for the rows where all search terms are found.
I have the following query : for :P_LEG_NUM Parameter when i am passing values like 1,2,5 as string type i am getting invalid number error... I have defined in clause for it but still it does not work.. For individual values like 2, etc it works... how can i pass comma separated values for this bind variable
select trip_number as prl_trip_number, flight_number as prl_f_number, trip_leg_id as prl_trip_leg_id, leg_number as prl_leg_num, dicao as prl_dicao, [code]........
So each Level(L1 ..L4) has zero or many child levels which further has more levels.With out using PL/SQL how can we write a Select query to give me a distinct of all children, all the way to the lowest level (L4).Example: give me all the children where L1 = 3.Result: 31, 32, 33, 311, 322, 333, 3111, 3222, 3333Is it possible to write such a query or am I asking too much logic out of a select and should go with PL/SQL.
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?
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.
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'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.
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 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 .
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 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
