select statement to assign values to multiple variables using min() |
|
Author |
Message |
slamdunkinpool
|
Posted: Thu Apr 13 15:21:05 CDT 2006 |
Top |
SQL Server Developer >> select statement to assign values to multiple variables using min()
Hello,
A solution or workaround to this problem would be much appreciated!
CREATE TABLE [IndexTables]
([Indexnm] [varchar] (50) NOT NULL ,
[SortOrd] [int] NOT NULL ,
[Tablenm] [varchar] (50) NOT NULL ,
[Columnnm] [varchar] (50) NOT NULL )
Lets say "IndexTables" has the following rows:
'Testindex', 1, ' TEST', 'COL1'
'Testindex', 2, ' TEST', 'COL2'
'Testindex', 3, ' TEST', 'COL3'
'Testindex', 4, ' TEST', 'COL4'
DECLARE
@SortOrder int,
@IndexName varchar(50),
@ColumnName varchar(50)
When I execute the following the SELECT statement:
FROM IndexTables
WHERE Tablenm='TEST'
I get the following error message:
Server: Msg 8118, Level 16, State 1, Line 5
Column 'TEST.dbo.IndexTables.Indexnm' is invalid in the select list because
it is not contained in an aggregate function and there is no GROUP BY clause.
--
Message posted via SQLMonster.com
http://www.hide-link.com/
SQL Server210
|
|
|
|
|
Aaron
|
Posted: Thu Apr 13 15:21:05 CDT 2006 |
Top |
SQL Server Developer >> select statement to assign values to multiple variables using min()
FROM IndexTables
WHERE Tablenm='TEST'
GROUP BY Indexnm, Columnnm
Just a curiosity question, do you find that the sacrifice in readability is
worth the savings you get from typing two less characters? I find the
following about 10,000 times easier to read:
SELECT
FROM
IndexTables
WHERE
TableName = 'TEST'
GROUP BY
IndexName,
ColumnName;
> Hello,
>
> A solution or workaround to this problem would be much appreciated!
>
> CREATE TABLE [IndexTables]
> ([Indexnm] [varchar] (50) NOT NULL ,
> [SortOrd] [int] NOT NULL ,
> [Tablenm] [varchar] (50) NOT NULL ,
> [Columnnm] [varchar] (50) NOT NULL )
>
> Lets say "IndexTables" has the following rows:
> 'Testindex', 1, ' TEST', 'COL1'
> 'Testindex', 2, ' TEST', 'COL2'
> 'Testindex', 3, ' TEST', 'COL3'
> 'Testindex', 4, ' TEST', 'COL4'
>
> DECLARE
>
> When I execute the following the SELECT statement:
>
> FROM IndexTables
> WHERE Tablenm='TEST'
>
> I get the following error message:
>
> Server: Msg 8118, Level 16, State 1, Line 5
> Column 'TEST.dbo.IndexTables.Indexnm' is invalid in the select list
> because
> it is not contained in an aggregate function and there is no GROUP BY
> clause.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200604/1
|
|
|
|
|
Cismail
|
Posted: Tue Apr 18 09:26:50 CDT 2006 |
Top |
SQL Server Developer >> select statement to assign values to multiple variables using min()
Thank you very much for the solution.
I agree the way you spaced your SELECT statement is easier to read, however I
don't see where there are two less characters typed from one statement to the
other.
>FROM IndexTables
>WHERE Tablenm='TEST'
>GROUP BY Indexnm, Columnnm
>
>Just a curiosity question, do you find that the sacrifice in readability is
>worth the savings you get from typing two less characters? I find the
>following about 10,000 times easier to read:
>
>SELECT
>FROM
> IndexTables
>WHERE
> TableName = 'TEST'
>GROUP BY
> IndexName,
> ColumnName;
>
>> Hello,
>>
>[quoted text clipped - 30 lines]
>> it is not contained in an aggregate function and there is no GROUP BY
>> clause.
--
Message posted via http://www.sqlmonster.com
|
|
|
|
|
Aaron
|
Posted: Tue Apr 18 09:46:40 CDT 2006 |
Top |
SQL Server Developer >> select statement to assign values to multiple variables using min()
> don't see where there are two less characters typed from one statement to
> the
> other.
Your column names are not names, they're nms, which could mean different
things to different people. For example, Indexnm != IndexName. Your schema
would be much better at self-documentation if you spell out the name instead
of saving two characters using a short-form that is nothing more than
obfuscation.
A
|
|
|
|
|
Jim
|
Posted: Tue Apr 18 09:43:47 CDT 2006 |
Top |
SQL Server Developer >> select statement to assign values to multiple variables using min()
I think he means a carriage return at the end of each line, and a space at
the beginning.
> Thank you very much for the solution.
> I agree the way you spaced your SELECT statement is easier to read,
however I
> don't see where there are two less characters typed from one statement to
the
> other.
>
> >FROM IndexTables
> >WHERE Tablenm='TEST'
> >GROUP BY Indexnm, Columnnm
> >
> >Just a curiosity question, do you find that the sacrifice in readability
is
> >worth the savings you get from typing two less characters? I find the
> >following about 10,000 times easier to read:
> >
> >SELECT
> >FROM
> > IndexTables
> >WHERE
> > TableName = 'TEST'
> >GROUP BY
> > IndexName,
> > ColumnName;
> >
> >> Hello,
> >>
> >[quoted text clipped - 30 lines]
> >> it is not contained in an aggregate function and there is no GROUP BY
> >> clause.
>
> --
> Message posted via http://www.sqlmonster.com
|
|
|
|
|
Aaron
|
Posted: Tue Apr 18 10:02:50 CDT 2006 |
Top |
SQL Server Developer >> select statement to assign values to multiple variables using min()
>I think he means a carriage return at the end of each line, and a space at
> the beginning.
No, I was talking about using IndexName instead of the much less readable
and clear "indexnm"
|
|
|
|
|
Jim
|
Posted: Tue Apr 18 10:14:26 CDT 2006 |
Top |
SQL Server Developer >> select statement to assign values to multiple variables using min()
Actually, last week I understood that, but after a 4 day weekend I am a
little slow on the take.
> >I think he means a carriage return at the end of each line, and a space
at
> > the beginning.
>
> No, I was talking about using IndexName instead of the much less readable
> and clear "indexnm"
>
>
|
|
|
|
|
|
|