SQL query question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL query question

Author

Topic

hannah00
Starting Member

31 Posts

Posted - 2011-05-07 : 19:04:22

Below is the query that I have:

quote:

select a.contact, b.db_name
from MOM.db_contacts@DB_LINK a, MOM.databases@DB_LINK b,
where a.DB_ID=b.DB_ID
and b.DB_name=(SELECT unique substr(upper(t.target_name), 1, instr(t.target_name,'_',1,1)-1)
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE t.target_type = 'oracle_database'
and UPPER(t.target_name) like '%11GDB%');

How can I add two more columns into the select statement and the two columns are in the tables that were in the sub query

I would like to have something like this:

[Quote]
select a.contact, b.db_name, t.target_name, s.message
from MOM.db_contacts@DB_LINK a, MOM.databases@DB_LINK b, mgmt_targets t, mgmt_current_severity s
where a.DB_ID=b.DB_ID
and b.DB_name=(SELECT unique substr(upper(t.target_name), 1, instr(t.target_name,'_',1,1)-1)
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE t.target_type = 'oracle_database'
and UPPER(t.target_name) like '%11GDB%');
[Quote]

note: t.target_name, s.message are in the two tables that were in the sub-query.
thx

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-08 : 10:37:01

This does not look like T-SQL. This forum is for Microsoft SQL server, and so it has a lot of people who are experts on Microsoft SQL Server, but not many who have lot of skills in Oracle or other DBMSs. You may get faster and better responses at a forum such as dbforums.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-05-09 : 10:12:42

select a.contact, b.db_name, t.target_name, t.message
from MOM.db_contacts@DB_LINK a, MOM.databases@DB_LINK b,(SELECT unique substr(upper(t.target_name), 1, instr(t.target_name,'_',1,1)-1) target_name
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE t.target_type = 'oracle_database'
and UPPER(t.target_name) like '%11GDB%')
t
where a.DB_ID=b.DB_ID
and b.DB_name=t.target_name;

Better to use ANSI joins

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources