No question at this time
DBA Top 10
1 M. Cadot 7000
2 B. Vroman 3000
3 P. Wisse 1300
4 P. Tsongayinwe 1100
5 J. Péran 1000
6 A. Kavsek 600
7 D. Walgude 400
7 J. Schnackenberg 400
9 J. Alcroft 200
9 B. M 200
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48717
Total active users1324
Act. users last 24h1
Act. users last hour0
Registered user hits last week17
Registered user hits last month178
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, 30 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, 29 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, 29 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