Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 234 Big Data Appliance
- 1.9K Data Science
- 449.7K Databases
- 221.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.2K SQL Developer
- 295.3K Development
- 17 Developer Projects
- 138 Programming Languages
- 292K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 27 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 157 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 386 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1K Español
- 1.9K Japanese
- 230 Portuguese
Getting data using IN operator
Dear Friends,
I need to get the data from T2 using T1 column data. Basically T1 is holding code and description for the codes are in T2.
In table T1 data is stored as below:
Description for above code is in table T2 data stored as below:
Now I have written a code to retrieve the description using T1 table data:
select upper('('''||replace(data1,',',''',''')||''')') from t1;Result:
('2BA','2R','BAL','HI')Using IN operator: When I run below code I am not able to get any data.
1. Is there any mistake in my code?
2. Is this the correct way to use the code like below ?
3. Do we have any other idea to get the correct data?
select data1_desc from t2 WHERE upper(data1_desc) IN (select upper('('''||replace(data1,',',''',''')||''')') from t1);Result : NULL
Codes to create the table and data:
create table t1 (data1 varchar2(200));create table t2 (data1 varchar2(200),data1_desc varchar2(2000));insert into t1 values ('2BA,2R,BAL,HI');insert into t2 values ('2BA','2 Basket');insert into t2 values ('2R','2 Room');insert into t2 values ('BAL','Ball Room');insert into t2 values ('HI','High Floor with view');select * from t1;select * from t2;select upper('('''||replace(data1,',',''',''')||''')') from t1;select data1_desc from t2 WHERE upper(data1) IN (select ('('''||replace(data1,',',''',''')||''')') from t1);Regards,
Answers
-
Are you saying that table t2 holds the descriptions for the codes in table t1, but table t2 does NOT have the codes, it only has the descriptions? And you must guess which description goes with which code?
That makes no sense. Is this a production system, or some "school project" of some kind? What are you supposed to do with the code '2BA', if in t2 you have descriptions '2 basket' and '2 balloon'? Which one is '2BA'? If you answer, how did you determine that answer?
-
First Normal Form dictates that only single value is stored in single column.
the data "model" is flawed & needs to be corrected before proceeding.
Rows in a table are like balls in a basket. There is NO inherent row order in Oracle Heap table.
-
There's so much wrong here. Storing multiple values in a CSV column. Having no relation of codes to descriptions. Building a dynamic string of CSV values and expecting it to work in an IN clause. None of those things will work
-
Normalize your design.
with nrml_t1 as
(select regexp_substr(data1, '[^,]+', 1, level, 'i') data1
from t1
connect by level <= regexp_count(data1, ',') + 1
and prior sys_guid() is not null
)
select nrml_t1.data1,
t2.data1_desc
from nrml_t1,
t2
where upper(substr(replace(t2.data1_desc,' '),1,length(nrml_t1.data1))) = upper(nrml_t1.data1);
-
My apologies. I have missed to create one column before posting. Now my question is modified. Please check.
Regards,
-
My apologies. I have missed to create one column before posting. Now my question is modified. Please check.
Regards,
-
As others have said, your data model is completely wrong.
Try doing it properly, something like this...
create table t1 (
id varchar2(5),
description varchar2(100),
constraint pk_t1 primary key (id)
);
insert into t1 (id, description) values ('2BA', '2 Basket');
insert into t1 (id, description) values ('BAL', 'Ball Room');
insert into t1 (id, description) values ('2R', '2 Room');
insert into t1 (id, description) values ('HI', 'High Floor with view');
commit;
select *
from t1
where id in ('2BA', '2R', 'BAL', 'HI')
ID DESCRIPTION '2BA' '2 Basket' '2R' '2 Room' 'BAL' 'Ball Room' 'HI' 'High Floor with view' Easy huh?
-
You should be able to solve this by the input already given to you, anyway you should change your design. This is kind of Varying IN List. The Tom Kyte Blog: Varying in lists...
with nrml_t1 as
(select regexp_substr(data1, '[^,]+', 1, level, 'i') data1
from t1
connect by level <= regexp_count(data1, ',') + 1
and prior sys_guid() is not null
)
select nrml_t1.data1,
t2.data1_desc
from nrml_t1,
t2
where nrml_t1.data1=t2.data1;
-
pradeep kateel wrote:My apologies. I have missed to create one column before posting. Now my question is modified. Please check.Regards,
Great. Now we have discussion/responses that refer to code/text that is no longer in the thread. Just adding to the confusion.
When you want to correct something you've posted, you should put it in a new message so that the subject of previous messages remains intact, preserving the integrity of the entire thread.
Also, it is not necessary to post the same exact reply to multiple people.
-
where id in ('2BA', '2R', 'BAL', 'HI')in here need to get the data from column data1 from table t1. Because data in the data1 of table t1 is not constant.
