Like Statement With Multiple Values

Dec 1, 2009

I have a field that may look like this:

AS01 AB CD EF

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

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Create SQL Insert Statement With Multiple Values?

Jun 3, 2010

I need to create a SQL insert statement with mutipleValues and an select statement I am trying this it works -

insert into uwa_crew_ids(crew_ID, CREATION_DATE, CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_NAME, first_name )
values
( uwa_crew_ids_s.nextVal,sysdate, 1767,sysdate, 1767,'TEST1', 'NITA')

This statement does not work (with or without keyword) Is there any alternate syntax

insert into uwa_crew_ids(crew_ID, CREATION_DATE, CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_NAME, first_name )
( uwa_crew_ids_s.nextVal,sysdate, 1767,sysdate, 1767,(select last_name, first_name from uwa_crew_ids where guid = '8795EAAFBE694F8EE0440003BA2AEC90' ))

View 3 Replies View Related

Update Multiple Rows With Different Values In A Single Statement

Jul 24, 2009

Updating multiple ROWS with different values using single statement. Requirement is to update one column in a table with the values in the other table.

Say we have 3 tables, CORPORATION,CORPORATE PROFILE and MEMBER.

Each MEMBER has CORPORATE PROFILE which in turn is associated with CORPORATION. Now I need to update MEMBER table with CORPORATION identifier for members who belong to corporations with identifiers say 'ABC' and 'DEF'.

MEMBER table contains column 'CORPIDENTIFIER '. CORPORATEPROFILE table contains MEMBERID and CORPORATIONID,this will associate a member with the corporation. CORPORATION table contains ID and CORPIDENTIFIER.

Using the below query I am getting error,ORA-01427:single-row subquery returns more than one row

UPDATE MEMBER M SET M.CORPIDENTIFIER=
(SELECT A.IDENTIFIER FROM CORPORATION A,CORPORATEPROFILE B
WHERE B.CORPORATIONID=A.ID AND B.MEMBERID=M.ID AND (A.IDENTIFIER LIKE 'ABC' OR A.IDENTIFIER LIKE 'DEF'))

Sub query in the above query returns multiple rows and hence it is throwing the error.More than one members are associated with Corporations ABC and DEF. Is there any way possible to update all the rows in single query with out iterating the result set of sub query.

View 1 Replies View Related

SQL & PL/SQL :: Updating Multiple ROWS With Different Values Using Single Statement?

Feb 16, 2011

The requirement I have is :

I have two tables eim_asset and eim_asset1.I want to update the table eim_asset1 using the following update SQL (Or Logic)

update eim_asset1
set emp_emp_login = (select login from s_user where row_id in
(select row_id from s_emp_per where row_id in
(select pr_emp_id from s_postn where row_id in
(select position_id from s_accnt_postn where ou_ext_id in
(select row_id from s_org_ext where row_id in
(select owner_accnt_id from s_asset where owner_accnt_id is not null)))))

It gives me the ORA error : ORA-01427:single-row subquery returns more than one row.know why I am getting it, because of the one-to-many relationship between owner accounts and their assets.

View 1 Replies View Related

SQL & PL/SQL :: How To Update Multiple Rows With Different Values Using Update Statement

Mar 21, 2011

I have one doubt about update command in sql. How to update the multiple rows with different values using update statment.

Eg:-

SQL> set linesize 500;
SQL> set pagesize 500;
SQL> select * from emp;
SQL> select empno,ename,sal from emp;
SQL> select empno,ename,sal from emp;

EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
7698 BLAKE 2850
7782 CLARK 2450
7566 JONES 2975
7654 MARTIN 1250

[Code]....

The above table contains 14 records. Now i would like to update the salary column with different values like

EMPNO SAL
===========
7839 18000
7698 20000
7782 5000
...
...
...
7934 25000

How to update above values with single update query.

View 11 Replies View Related

SQL & PL/SQL :: Displaying Multiple Row Values As Multiple Column And Row Values

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

SQL & PL/SQL :: Trying To Get Two Values From Case Statement

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

SQL & PL/SQL :: Case Statement With Multiple Parameters?

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

SQL & PL/SQL :: Update Statement Using Multiple Tables

Aug 26, 2010

I am issuing an update statement in which I am using multiple tables it is giving me an error " set keyword missing"

update E_CONT_DETAIL_NUMB_VALUE ecdnv, y_obj_category yoc, t_contact tc
set ecdnv.ContTPRecCount = 1000
where tc.default_category_id = (select primary_key from y_ojb_category where tree_position = 'CONT')
and ecdnv.detail_field_id=tc.default_category_id;

update E_CONT_DETAIL_NUMB_VALUE ecdnv, y_obj_category yoc, t_contact tc
*
ERROR at line 1:
ORA-00971: missing SET keyword

View 4 Replies View Related

PL/SQL :: Multiple IF Else Statement In A Single Select?

Oct 17, 2012

I want to run multiple IF Else statements in a single select SQL, each statement is one SQL operating on the same table, what is the best way to write this select SQL query ? If it is PL/SQL, when i get the result from the first IF statement I will skip the remaining execution, and so on..

View 9 Replies View Related

Multiple Select Statement In 1 Query?

Nov 19, 2012

I have following queries:-

#select name from v$database;
#select log_mode from v$database;
#select count(*)"INVALID_OBJECTS" from dba_objects where status='INVALID';
#select count(*) "INVALID_N/A_INDEXES" from dba_indexes where status!='VALID';
#select count(*)"Invalid Triggers" from user_objects where OBJECT_NAME like '%TRIGGERS%' and status='VALID';
#select count(*) "Broken Jobs" from dba_jobs where broken!='Y';
#select count(*) "Block Corruption" from v$database_block_corruption;

i want a table which can be generated just by select cmd and it will list the result of all the above queires as follow:-

DB_NAME ARCH_MOD INV_OBJ INV_IDX INV_TRG B_JOB BLK_CRP
---------- -------------------------------------- -------------------------------------- ---------- ---------- ---------- ----------
PROD NOARCHIVELOG 0 86 6 3 0

I mean to say i want multiple select queries into 1 table (note:- i m not saying to create a tables and then insert,update(using select from other tables), its just a sheel script that will fetch these record into a txt file)

View 7 Replies View Related

Performance Tuning :: Multiple SELECT Statement

Apr 8, 2011

I'm working on a query that will show how many differents SKUs we have on-hand, how many of those SKUs have been cycle-counted, and how many we have yet to cycle-count.I've prepared a sample table and data:

CREATE TABLE SKU
(
ABC VARCHAR2(1 CHAR),
SKU VARCHAR2(32 CHAR) NOT NULL,
Lastcyclecount DATE,
[code]....

What I also want to do is select another column that will group by sku.abc and count the total number of A, B, and C SKUs where the lot.qty is > 0:

SELECT sk.abc AS "STRATA",
COUNT (DISTINCT sk.sku) AS "Total"
FROM sku sk,
(SELECT sku
FROM lot
WHERE qty > 0) item
WHERE item.sku = sk.sku(+)
GROUP BY sk.abc

Finally, I need the last column to display the DIFFERENCE between the two totals from the queries above (the difference between the "counted" and the "total"):

COUNT (DISTINCT sk.sku) - COUNT (DISTINCT s.sku)

View 6 Replies View Related

PL/SQL :: Exclude Duplicate Values On SQL Where Clause Statement?

Jul 15, 2013

Are some posibilities to exclude duplicate values do not using sql aggregate functions in main select statement?  Priview SQL statement

SELECT * FROM
(
select id,hin_id,name,code,valid_date_from,valid_date_to
from diaries
)

[Code]....

 In this case i got duplicate of entry TT2 id 50513  In main select statement cant use agregate functions are even posible to exclude this value from result modifying only the QLRST WHERE clause (TRUNC need to be here)

View 5 Replies View Related

SQL & PL/SQL :: Updating Multiple Rows With Single Update Statement?

Aug 20, 2013

create table temp_tst
(
FILENAME VARCHAR2(200),
EDITED_BY VARCHAR2(50),
EDITED_TO VARCHAR2(50)
)

[code]....

Can I write a single update statement to update filename column replacing "_tst" with "_check"?

View 1 Replies View Related

SQL & PL/SQL :: Write Nested Decode Statement With Multiple Searches

Nov 27, 2010

I was trying to write nested decode statement with multiple searches. For example

Case when seq = 1 and date1 > date2 then Record_Name End

How can i modify this to decode.

View 4 Replies View Related

SQL & PL/SQL :: Multiple Values In One Column?

Nov 20, 2010

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

View 8 Replies View Related

SQL & PL/SQL :: Multiple Values In Column

Jan 10, 2013

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.

View 5 Replies View Related

SQL & PL/SQL :: Multiple Values In Constant?

Jun 10, 2010

I am trying to do something like this..

declare
a constant variable (1,2,3);
b number;
begin
select x
into b
from y

if b in a then...

What is the correct way of declaring multiple values in a constant and then using the IN clause.

View 9 Replies View Related

SQL & PL/SQL :: Decode Using Multiple Values

Jan 23, 2013

i am using the following within queries:

decode(qt_flag,'KT','Y',decode(qt_flag,'IT','Y','N'))

It works fine but when i use in ETL tool it is not working. Is there a way to use decode just once with multiple values like above.

View 4 Replies View Related

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

View 4 Replies View Related

SQL & PL/SQL :: One Record With Multiple Values

Jun 13, 2011

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.

hopefully my issue can be solved here.

Data:
Sub_grp_nbr Prodt_ctgy_cd Extra_column
ABC123 05 1
ABC123 02 2
ABC012 05 3
ABC456 02 4
ABC456 05 5
ABC789 05 6

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

View 7 Replies View Related

SQL & PL/SQL :: Like Operation For Multiple Values

Jun 12, 2013

I have a source view where I have some invalid records and those should be found based on codes present in another table.

For eg. from source the records come like

****************SIINNSFDFD****FDFDF2******8
**********TABLE****************FDFSFSSFASFAS********

and if my reference table has values

SIINNSFDFD
TABLE

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.

View 2 Replies View Related

PL/SQL :: Multiple Values In Condition

Aug 30, 2012

I am trying to do something like this

SELECT clave_grupo FROM SS_TTABLAS
WHERE CLAVE_GRUPO IN
(
CASE WHEN (condition) THEN 'v1'
ELSE ('v1','v2',v3')
end
)

This is the error msg:
ORA-00907: missing right parenthesis

just before ,'v2',v3')

View 2 Replies View Related

SQL & PL/SQL :: Update Statement - Calculating Few Values From Large Table

Sep 2, 2011

I have a large table and want to calculate just a few values. Therefore, I don't want to create a new table, I want to update the table. Here an example:

I want to calculate the VALUE_LAG with ID = 4 only (-> two values).

create table zTEST
( PRODUCT number,
ID number,
VALUE number,
VALUE_L1 number );

[Code]..

I tried this, but obviously, windows functions are not allowed in the update statement.

update zTEST
set VALUE_L1 = lag(VALUE) over (partition by PRODUCT, order by ID)
where ID = 4

How can I do this?

View 12 Replies View Related

SQL & PL/SQL :: Multiple Report Parameter Values

Oct 21, 2012

For the below query:

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 .

View 3 Replies View Related

SQL & PL/SQL :: Check If Multiple Column Values Are Same?

Sep 16, 2011

I have a table as follows

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.

View 6 Replies View Related

SQL & PL/SQL :: How To Return Multiple Values From A Function

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

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.

View 13 Replies View Related

SQL & PL/SQL :: Multiple Unique Values In One Column?

Aug 26, 2011

I have a problem with some tables in database.

Table has three columns: userid, pname, pvalue.

Userid has unique values, for example: 234, 123, 587, etc.

In the field pname, there is three possible values: MAC, IP, S/N.

So if I go like this:

select pvalue
from table
where pname = 'MAC';

i get the values of MAC.

If I go like this:

select pvalue
from table
where pname = 'IP';

i get the values of IP.How can I join MAC with IP that is matching this MAC? I need an SQL statement for this.

View 11 Replies View Related

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







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