CODEWITHSUNDEEP
sql-server
reach2khan
reach2khan

Reputation: 67

Query without using alias name in SQL Server

I am firing the below query in SQL server, but it fails with the error

"No column name was specified for column".

I don't want to give the alias name as the default values passed to the query are huge giving to each one of them is time consuming.

Is there a way in SQL Server to fire the query without alias name?

The below query still goes on with other union clauses added, pasted query only part of it.

I don't want to create a script, want to achieve this in the form of query. 

select count(*) 
from 
    (select distinct 
         a.Company, a.BilltoCustomerNo, 0, 'No Customer', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 0, 0, 0, 'n/a', 'n/a', 'n/a', 0, 0, 'n/a', 'n/a', 0, 0, 'n/a', 'n/a', 'n/a', 0, 0, 'n/a', convert(datetime, '19000101', 112), 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 0, 0, 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 0, 'n/a', 'n/a', convert(datetime, '19000101', 112), 'n/a', convert(datetime, '19000101', 112), 0, 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 0, 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 0, 'n/a', 0, 'n/a', 0, 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 0, 'n/a', 'n/a', 'n/a', 'n/a', 'n/a', 0, 'n/a', 'n/a', 0, 'n/a', 'n/a', 'n/a', 'n/a', 0, 0, 0, 0, 0, 0, 0, 0, 0, 'na', 'na'
     from 
         dJob a 
     left outer join 
         dCustomer b on a.Company = b.Company and a.BilltoCustomerNo = b.No
     where 
         b.Company is null) x

Thanks

Upvotes: 0

Views: 110

Answers (2)

Sean Lange
Sean Lange

Reputation: 33581

You could simplify that query into a single statement, and get rid of all those pointless scalar values.

select count(*)
from dJob a 
left outer join dCustomer b on a.Company = b.Company and a.BilltoCustomerNo = b.No
where b.Company is null
group by a.Company, a.BilltoCustomerNo

Upvotes: 2

Colin Mackay
Colin Mackay

Reputation: 19185

You could drop all those columns as they add nothing. The query will be functionally the same without them as the all return constant values, so the only thing the distinct has to work with is the first two columns (which have a name) and you're throwing them all away in the outer query (The SELECT COUNT(*) part) anyway.

select count(*) 
from (select distinct a.Company, a.BilltoCustomerNo
      from dJob a 
      left outer join dCustomer b on a.Company = b.Company and a.BilltoCustomerNo = b.No
      where b.Company is null) x

Upvotes: 1

Related Questions