No question at this time
DBA Top 10
1 M. Cadot 5400
2 P. Wisse 1300
3 P. Tsongayinwe 1100
4 B. Vroman 500
4 J. Schnackenberg 500
6 M. Pagac 400
7 A. Deledda 200
7 D. Walgude 200
7 B. M 200
10 J. Alcroft 100
10 G. Trivedi 100
10 A. Hudspith 100
10 V. Sharma 100
10 L. Ywema 100
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48742
Total active users1337
Act. users last 24h1
Act. users last hour0
Registered user hits last week108
Registered user hits last month471
Go up

Help with a SQL query
Next thread: performance issues
Prev thread: Season's greetings, Oracle-mathematical way

Message Score Author Date
Hi All, Need some SQL help! I have two table...... S SPB Jan 10, 2023, 05:00
Hello, maybe somethi├žng like this: (remark: try...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Bruno Vroman Jan 10, 2023, 09:00
Thanks Bruno for the test case. Another solut...... Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts Michel Cadot Jan 10, 2023, 10:23

Follow up by mail Click here


Subject: Help with a SQL query
Author: S SPB, India
Date: Jan 10, 2023, 05:00, 144 days ago
Os info: Anything
Oracle info: Anything
Message: Hi All,

Need some SQL help!

I have two tables... one is having below data

tableA

Database_version Patch_release
---------------- --------------
12.1.0.2.220718 JUL_2022
19.16.0.0.220719 JUL_2022
12.2.0.1.220719 JUL_2022

tableB

DB_Name Description
---------- ------------
ABCD Database Jul 2022 Release Update : 12.1.0.2.220718
XYZ Database Jul 2022 Release Update : 19.16.0.0.220719
PQRS Database Jul 2022 Release Update : 12.2.0.1.220719

I want to search for TableA.Database_version in TableB and decode it to TableA.Patch_Release as below.

Kindly help with the query formation for this result?

DB_Name Patch_Release
-------- ----------------
ABCD JUL_2022
XYZ JUL_2022
PQRS JUL_2022

Thanks a lot!

Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Help with a SQL query
Author: Bruno Vroman, Belgium
Date: Jan 10, 2023, 09:00, 144 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Hello,

maybe somethi├žng like this: (remark: try to provide the "CREATE TABLE" statements to facilitate a bit the work of people helping you)
CREATE TABLE tablea AS

SELECT '12.1.0.2.220718' database_version, 'JUL_2022' patch_release FROM dual
UNION ALL SELECT '19.16.0.0.220719', 'JUL_2022' FROM dual
UNION ALL SELECT '12.2.0.1.220719', 'JUL_2022' FROM dual
;
CREATE TABLE tableb AS
SELECT 'ABCD' db_name, 'Database Jul 2022 Release Update : 12.1.0.2.220718' description FROM dual
UNION ALL SELECT 'XYZ', 'Database Jul 2022 Release Update : 19.16.0.0.220719' FROM dual
UNION ALL SELECT 'PQRS', 'Database Jul 2022 Release Update : 12.2.0.1.220719' FROM dual
;
SELECT b.db_name, a.patch_release
FROM tableb b
INNER JOIN tablea a
ON a.database_version = SUBSTR( b.description, INSTR( b.description, ' : ' ) + 3 )
;
Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Help with a SQL query
Author: Michel Cadot, France
Date: Jan 10, 2023, 10:23, 144 days ago
Score:   Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts
Message:
Thanks Bruno for the test case.

Another solution:
SQL> SELECT b.db_name, a.patch_release

2 FROM tablea a, tableb b
3 WHERE REGEXP_LIKE(b.description, a.database_version)
4 /
DB_N PATCH_RE
---- --------
ABCD JUL_2022
XYZ JUL_2022
PQRS JUL_2022

3 rows selected.


Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here