Include field in SELECT but don't output?

  • Hi,

    Is it possible to include a field in a query but not have the field included in the result set?

    Many thanks,

    Jason

    ___

  • 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.



    Pradeep Singh

  • 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

  • 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

    ___

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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?

  • 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