naming columns in excel with Complex sql

Question

I’m trying to run this sql using get external ..

it work but when I try to rename the subqurrys or anything for that matter it remove it.

I tried as, as and the name in '', as then the name in "", and the same with space. what is the right way to do that?

sql for completeness sake

select list_name, app_name, (select fname + ' ' + lname from dbo.d_agent_define map where map.agent_id = tac.agent_id) as agent_login, input, convert(varchar,dateadd(ss,TAC_BEG_tstamp,'01/01/1970')) from dbo.maps_report_list list join dbo.report_tac_agent tac on (tac.list_id = list.list_id) where input = 'SYS_ERR' and app_name = 'CHARLOTT' and convert(varchar,dateadd(ss,day_tstamp,'01/01/1970'),101) = '09/10/2008' and list_name LIKE 'NRBAD%' order by agent_login,convert(varchar,dateadd(ss,TAC_BEG_tstamp,'01/01/1970'))

Answer 1

You could get rid of your dbo.d_agent_define subquery and just add in a join to the agent define table.

Would this code work?

select list_name, app_name, 
map.fname + ' ' + map.lname as agent_login, 
input, 
convert(varchar,dateadd(ss,TAC_BEG_tstamp,'01/01/1970')) as tac_seconds
from dbo.maps_report_list list 
join dbo.report_tac_agent tac 
on (tac.list_id = list.list_id) 
join dbo.d_agent_define map
on (map.agent_id = tac.agent_id)
where input = 'SYS_ERR' 
and app_name = 'CHARLOTT' 
and convert(varchar,dateadd(ss,day_tstamp,'01/01/1970'),101) = '09/10/2008' 
and list_name LIKE 'NRBAD%' 
order by agent_login,convert(varchar,dateadd(ss,TAC_BEG_tstamp,'01/01/1970'))

Note that I named your dateadd column because it did not have a name. I also tried to keep your convention of how you do a join. There are a few things that I would do different with this query to make it more readable, but I only focused on getting rid of the subquery problem.

I did not do this, but I would recommend that you qualify all of your columns with the table from which you are getting them.

Answer 2

To remove the sub query in the SELECT statement I suggest the following:

SELECT list_name, app_name, map.fname + ' ' + map.lname as agent_login, input, convert(varchar,dateadd(ss, TAC_BEG_tstamp, '01/01/1970))
FROM dbo.maps_report_list inner join
  (dbo.report_tac_agent as tac inner join dbo.d_agent_define as map ON (tac.agent_id=map.agent_id)) ON list.list_id = tac.list_id
WHERE input = 'SYS_ERR' and app_name = 'CHARLOTT' and convert(varchar,dateadd(ss,day_tstamp,'01/01/1970'),101) = '09/10/2008' 
    and list_name LIKE 'NRBAD%' order by agent_login,convert(varchar,dateadd(ss,TAC_BEG_tstamp,'01/01/1970'))

I used parentheses to create the inner join between dbo.report_tac_agent and dbo.d_agent_define first. This is now a set of join data. The combination of those tables are then joined to your list table, which I am assuming is the driving table here. If I am understand what you are trying to do with your sub select, this should work for you.

As stated by the other poster you should use table names on your columns (e.g. map.fname), it just makes things easy to understand. I didn't in my example because I am note 100% sure which columns go with which tables. Please let me know if this doesn't do it for you and how the data it returns is wrong. That will make it easier to solve in needed.