|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Syntax for selecting all but oneQuick question:
Is there any syntax to select all except 1 or 2 columns? I have a table with 80+ columns, and I want to return all of them except 1 or two, which I will convert when I select them. Right now, I do: "SELECT *, CONVERT(varchar, birthdate, 120) as birthdate FROM workers..." I think the problem I'm having is that there are now two "birthdate" columns in the result, and when I try to retreive a value, I'm getting the unconverted one. What I'd like is "SELECT * except for birthdate, CONVERT(..." On 13 Jul 2006 11:09:51 -0700, lord.zol***@gmail.com wrote:
>Quick question: Quick answer: No.>Is there any syntax to select all except 1 or 2 columns? >I have a table with 80+ columns, and I want to return all of them >except 1 or two, which I will convert when I select them. >Right now, I do: >"SELECT *, CONVERT(varchar, birthdate, 120) as birthdate FROM >workers..." >I think the problem I'm having is that there are now two "birthdate" >columns in the result, and when I try to retreive a value, I'm getting >the unconverted one. >What I'd like is "SELECT * except for birthdate, CONVERT(..." Roy Harvey Beacon Falls, CT NO, there is no lazy shorthand for this.
When writing the query in Query Analyzer, just drag the "Columns" node into your query. Now you have a list of all the columns. Apply CONVERT() to the ones you want to convert, and do nothing to the rest. Now save your query as a stored procedure so you only have to do it once. A <lord.zol***@gmail.com> wrote in message Show quote news:1152814191.811266.158080@m79g2000cwm.googlegroups.com... > Quick question: > Is there any syntax to select all except 1 or 2 columns? > I have a table with 80+ columns, and I want to return all of them > except 1 or two, which I will convert when I select them. > Right now, I do: > "SELECT *, CONVERT(varchar, birthdate, 120) as birthdate FROM > workers..." > I think the problem I'm having is that there are now two "birthdate" > columns in the result, and when I try to retreive a value, I'm getting > the unconverted one. > What I'd like is "SELECT * except for birthdate, CONVERT(..." > <lord.zol***@gmail.com> wrote in message
news:1152814191.811266.158080@m79g2000cwm.googlegroups.com... You have common sense.But it is many times defeated by sql.>. > What I'd like is "SELECT * except for birthdate, CONVERT(..." For what it's worth you are not alone in your thinking and there is work being done to turn sql into a language that makes sense :) Steve Dassin wrote:
> <lord.zol***@gmail.com> wrote in message Oh well... I guess for the wishlist for future improvements, I submit> news:1152814191.811266.158080@m79g2000cwm.googlegroups.com... > >. > > What I'd like is "SELECT * except for birthdate, CONVERT(..." > > You have common sense.But it is many times defeated by sql. > For what it's worth you are not alone in your thinking and there is work > being done to turn sql into a language that makes sense :) the following syntax: "SELECT [table].* - [table].[somecolumn] FROM [table]..." Which returns all the columns from [table], except for [somecolumn]. ;) <lord.zol***@gmail.com> wrote in message
>. How about: SELECT [table] REMOVE {somecolumn}> Oh well... I guess for the wishlist for future improvements, I submit > the following syntax: > "SELECT [table].* - [table].[somecolumn] FROM [table]..." > Which returns all the columns from [table], except for [somecolumn]. > ;) Check it out :) http://www.alphora.com/docs/D4LGTableExpressions-OverandRemove.html Steve Dassin wrote:
> <lord.zol***@gmail.com> wrote in message YES! That is EXACTLY what I want to do! Aphora, here I come!! :) :)> >. > > Oh well... I guess for the wishlist for future improvements, I submit > > the following syntax: > > "SELECT [table].* - [table].[somecolumn] FROM [table]..." > > Which returns all the columns from [table], except for [somecolumn]. > > ;) > > How about: SELECT [table] REMOVE {somecolumn} > Check it out :) > http://www.alphora.com/docs/D4LGTableExpressions-OverandRemove.html ....well not really, I just did it the hard way and manually inserted all the columns names except for the column in question (couldn't alias because there is code in other places that refer to this column, and I can't access that code). Now I just get to wait until we change the table and have to start editing the ~60 columns in that list... At least the query is only run in one place in the whole program... for now. <lord.zol***@gmail.com> wrote in message
news:1152818902.289791.166810@75g2000cwc.googlegroups.com... The big picture for the future:>> >> How about: SELECT [table] REMOVE {somecolumn} >> Check it out :) >> http://www.alphora.com/docs/D4LGTableExpressions-OverandRemove.html > > YES! That is EXACTLY what I want to do! Aphora, here I come!! :) :) Dlinq vs. Dataphor A nobrainer:) > What I'd like is "SELECT *, CONVERT(..." Use an Alias for the converted column.SELECT *, CONVERT( birthdate ...) AS 'Birthdate2' OR, better choice, list each column you want. And you may still need to assign a Alias name to the conversion results. QA/SSMS has drag and drop. Drag the list of columns to your query, and delete the ones you don't want. -- Show quoteArnie Rowland* "To be successful, your heart must accompany your knowledge." <lord.zol***@gmail.com> wrote in message news:1152814191.811266.158080@m79g2000cwm.googlegroups.com... > Quick question: > Is there any syntax to select all except 1 or 2 columns? > I have a table with 80+ columns, and I want to return all of them > except 1 or two, which I will convert when I select them. > Right now, I do: > "SELECT *, CONVERT(varchar, birthdate, 120) as birthdate FROM > workers..." > I think the problem I'm having is that there are now two "birthdate" > columns in the result, and when I try to retreive a value, I'm getting > the unconverted one. > What I'd like is "SELECT * except for birthdate, CONVERT(..." > A quick & dirty way, create a utility udf like:
ALTER FUNCTION dbo.udf ( @t VARCHAR( 128 ) ) RETURNS @tbl TABLE ( stmt VARCHAR(128) ) AS BEGIN INSERT @tbl ( stmt ) SELECT stmt FROM ( SELECT -1, 'SELECT ' UNION ALL SELECT ORDINAL_POSITION, SPACE(7) + COL_NAME( OBJECT_ID( @t ), ORDINAL_POSITION ) + CASE WHEN ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @t ) > ORDINAL_POSITION THEN ',' ELSE SPACE(0) END FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @t UNION ALL SELECT 9999, ' FROM ' + @t ) D ( pos, stmt ) ORDER BY pos; RETURN ; END The above udf has some caveats but it should get you though most cases. Now, you can get SELECT statement for your table like: SELECT * FROM udf( 'yourtable' ) ; -- Anith What do Humpty Dumpty and sql have in common? :)
Show quote "Anith Sen" <an***@bizdatasolutions.com> wrote in message news:e7eTWVrpGHA.1796@TK2MSFTNGP03.phx.gbl... >A quick & dirty way, create a utility udf like: > > ALTER FUNCTION dbo.udf ( @t VARCHAR( 128 ) ) > RETURNS @tbl TABLE ( stmt VARCHAR(128) ) AS BEGIN > INSERT @tbl ( stmt ) > SELECT stmt > FROM ( SELECT -1, 'SELECT ' > UNION ALL > SELECT ORDINAL_POSITION, SPACE(7) + > COL_NAME( OBJECT_ID( @t ), ORDINAL_POSITION ) + > CASE WHEN ( SELECT COUNT(*) > FROM INFORMATION_SCHEMA.COLUMNS > WHERE TABLE_NAME = @t ) > ORDINAL_POSITION > THEN ',' > ELSE SPACE(0) > END > FROM INFORMATION_SCHEMA.COLUMNS > WHERE TABLE_NAME = @t > UNION ALL > SELECT 9999, ' FROM ' + @t ) D ( pos, stmt ) > ORDER BY pos; > RETURN ; > END > > The above udf has some caveats but it should get you though most cases. > Now, you can get SELECT statement for your table like: > > SELECT * FROM udf( 'yourtable' ) ; > > -- > Anith > >> What do Humpty Dumpty and sql have in common? :) All the experts and all the gurusCouldn't put SQL together again. -- Anith "Anith Sen" <an***@bizdatasolutions.com> wrote in message Awesome! :)news:OeomqaspGHA.4032@TK2MSFTNGP03.phx.gbl... >>> What do Humpty Dumpty and sql have in common? :) > > All the experts and all the gurus > Couldn't put SQL together again. >> Is there any syntax to select all except 1 or 2 columns? I have a table with 80+ columns, and I want to return all of them except 1 or two, .. << You were the kid sleeping in RDBMS 101, weren't you? What is thedefinition of a table? Fixed number of columns!! DUH! >> SELECT * [isc] CONVERT [sic: CAST] mvarchar [sic: CHAR(n)}, birthdate, [sic] 120) as birthdate FROM workers..." << Hey. always write dialect so people will know you are a Hill Billy andnot an SQL programmers. WHY in the nasme of Ghod are you converting bertween VARCHAR() and temporal? >> I think the problem I'm having is that there are now two "birthdate" columns .. << Well, DUUUNH! How many birthdates can a person have !!! 42? Duh!Please reqad what you post. "--CELKO--" <jcelko***@earthlink.net> wrote in message Eeek...Your peddling snake oil here!news:1152826448.149768.87710@m73g2000cwd.googlegroups.com... >>> Is there any syntax to select all except 1 or 2 columns? I have a table >>> with 80+ columns, and I want to return all of them except 1 or two, .. >>> << > > You were the kid sleeping in RDBMS 101, weren't you? What is the > definition of a table? Fixed number of columns!! DUH! From 'Mash' 'Capt. Pierce did you steal a jeep?' Hawkeye - 'Why no, its right there' :) As many as he wants, and in sql it's the only place it's legal! Duh! :)>>> I think the problem I'm having is that there are now two "birthdate" >>> columns .. << > Well, DUUUNH! How many birthdates can a person have !!! 42? Duh!  |
|||||||||||||||||||||||
Other interesting topics