June 22, 2009 at 8:02 am
Hi,
Is it possible to include a field in a query but not have the field included in the result set?
Many thanks,
Jason
___
June 22, 2009 at 8:04 am
jason (6/22/2009)
Hi,Is it possible to include a field in a query but not have the field included in the result set?
Many thanks,
Jason
___
if u're writing like
select col1, col2.... from table1, all these columns will be displayed.
U can however write different columns in condition clause
select col1, col2 from table1 where col3='something'. here col3 is present as a part of query but wont be displayed.
Refer BOL for details on SELECT.
June 22, 2009 at 1:06 pm
Jason,
Why would you want the field in the query and not in the result? I've seen this in MS Access when building the query via the GUI, but that is not necessary in SQL Server.
Eli
June 23, 2009 at 1:32 am
Hi Eli,
The query includes a calculated field that contains a correlated subquery which itself has 3 WHERE conditions. I can only get the query to work if i include all the fields referenced in my subquery's where in my outer query's select & group by clauses. One of which I don't really want in the output.
It's no real bother, as I'm only dumping the query output to excel for my own use. I just wondered if there was some way to have a field listed in the select but not output in the result set.
Cheers,
Jason
___
June 23, 2009 at 2:06 am
Post the query?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 23, 2009 at 2:27 am
Hi Gail,
My query is below. I've commented the field that I had to add to get the query to work. I'm only a sql novice so the code is probably very clunky 🙂
SELECT
'Range' = RTRIM(Rd_Description),
'CAPId' = yl_id,
'March CAPClean' = cast(yl_baseprice3 as int),
'Subs %' = isnull(case when (select yl_baseprice3 from CAR.dbo.yearletter
where
yl_year = year(getdate()) and
yl_mastermonth = 1 and
yl_rangename = yl.yl_rangename
) = 0 then ''
else ((yl_baseprice3/(select yl_baseprice3 from CAR.dbo.yearletter
where
yl_year = year(getdate()) and
yl_mastermonth = 1 and
yl_rangename = yl.yl_rangename
))-1)*100 end,''),
'Master?' = CASE WHEN yl_mastermonth = 1 then 'MASTER' ELSE '' END,
yl_rangename ---- this is the field that I had to add to the select and group by
from
CAR.dbo.RangeDescriptions rd
inner join CAR.dbo.yearletter yl on rd.Rd_rangename=yl.yl_rangename
where
yl_year = year(getdate()) and
RTRIM(Rd_Description) !='Z NON VALUES' and
case when substring(Rd_Description,patindex('% (%',Rd_Description) +5,2)=' )' then YEAR(getdate()) else
case when substring(Rd_Description,patindex('% (%',Rd_Description) +5,2)='9)' then YEAR(getdate()) else '' end end = 2009
group by
RTRIM(Rd_Description),
yl_baseprice3,
CASE WHEN yl_mastermonth = 1 then 'MASTER' ELSE '' END,
yl_id,
yl_rangename ---- this is the field that I had to add to the select and group by
order by
Range,
CAPId
June 23, 2009 at 2:58 am
What error do you get if you remove yl_rangename from the select? (leave it in the group by)
One other thing. You should be able to remove the RTRIM from the where clause. SQL ignores trailing spaces when checking equality of strings so 'abc' = 'abc '
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 23, 2009 at 3:01 am
You can group by a column that is not in the select list... though it may produce a confusing result.
e.g.
BEGIN TRAN
CREATE TABLE fred
(number INT, name VARCHAR(5))
INSERT
INTO fred
( number, name)
VALUES
(1, 'Bill'),
(2, 'Joe'),
(3, 'Bob'),
(4, 'Joe'),
(5, 'Bob')
SELECT SUM(NUMBER)
FROM fred
GROUP BY name
ROLLBACK TRAN
Gives the results:
1
8
6
You have the results, grouped as asked, but no way to know which result correspnds to which group?
Cheers,
Dave.
Edit - sorry but I used SQL 2008 syntax for the inser statement - I think you have to insert the rows separately in 2005?
June 23, 2009 at 3:02 am
Doh! Told you I was a novice. The query runs fine when I remove it from the select but leave it in the group by....it's always the little, simple things 🙂
Thanks again Gail,
regards,
Jason
___
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply