Combining fields

Hello,
Within a view I've created, I have combined 2 fields to make 1.
dbo.TABLE1.STR_STRATUM + N' ' + dbo.TABLE2.STR_LAYER AS StratumLayer
This is for display (to populate a listbox in .NET).
The problem is, if there is nothing in the STR_LAYER field, the whole field
is blank.
Is it possible to display Stratum always, and Layer when it's available?
Thanks!
AmberUse functions ISNULL or COALESCE.
Example:
coalesce(dbo.TABLE1.STR_STRATUM + N' ', N'') +
coalesce(dbo.TABLE2.STR_LAYER, '') AS StratumLayer
AMB
"amber" wrote:

> Hello,
> Within a view I've created, I have combined 2 fields to make 1.
> dbo.TABLE1.STR_STRATUM + N' ' + dbo.TABLE2.STR_LAYER AS StratumLayer
> This is for display (to populate a listbox in .NET).
> The problem is, if there is nothing in the STR_LAYER field, the whole fiel
d
> is blank.
> Is it possible to display Stratum always, and Layer when it's available?
> Thanks!
> Amber
>|||SELECT dbo.TABLE1.STR_STRATUM + ISNULL( N' ' + dbo.TABLE2.STR_LAYER AS
StratumLayer, '')
Jacco Schalkwijk
SQL Server MVP
"amber" <amber@.discussions.microsoft.com> wrote in message
news:AF278105-D1AF-44DA-AD22-13E762A0690A@.microsoft.com...
> Hello,
> Within a view I've created, I have combined 2 fields to make 1.
> dbo.TABLE1.STR_STRATUM + N' ' + dbo.TABLE2.STR_LAYER AS StratumLayer
> This is for display (to populate a listbox in .NET).
> The problem is, if there is nothing in the STR_LAYER field, the whole
> field
> is blank.
> Is it possible to display Stratum always, and Layer when it's available?
> Thanks!
> Amber
>|||If you concatenate a string with a null value, it will return null.
Use ISNULL function:
dbo.TABLE1.STR_STRATUM + N' ' + ISNULL(dbo.TABLE2.STR_LAYER ISNULL(), '')
Francesco Anti
"amber" <amber@.discussions.microsoft.com> wrote in message
news:AF278105-D1AF-44DA-AD22-13E762A0690A@.microsoft.com...
> Hello,
> Within a view I've created, I have combined 2 fields to make 1.
> dbo.TABLE1.STR_STRATUM + N' ' + dbo.TABLE2.STR_LAYER AS StratumLayer
> This is for display (to populate a listbox in .NET).
> The problem is, if there is nothing in the STR_LAYER field, the whole
> field
> is blank.
> Is it possible to display Stratum always, and Layer when it's available?
> Thanks!
> Amber
>|||This worked.
Thanks!
Amber

Combining Columns and Grouping By....

Hi,
I have the following SQL

SELECT Table1.Col1, Table3.Col1 AS Expr1,
COUNT(Table1.Col2) AS Col2_No, COUNT(Table1.Col3) AS Col3_No etc,
FROM Table3
INNER JOIN Table2 ON Table3.Col1=Table2.Col1
RIGHT OUTER JOIN Table1 ON Table2.Col2=Table2.Col2
GROUP BY Table1.Col1, Table3.Col1

The output rows have a value in either Table1.Col1 or Table3.Col1 but not
both.
I'd like to combine Table1.Col1 and Table3.Col1 and group by the combined
column in the result but don't know how.
Thanks gratefullyHi

It would help if you posted the DDL (Create Table Statements) , example data
(as insert statements) and expected output. From your description it is not
100% clear how the tables relate or what results you expect.

If the values of Col1 are unique between each table your solution might be:

SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
FROM Table1
GROUP BY Col1
UNION
SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
FROM Table3
GROUP BY Col1

If not

SELECT IsNULL(T1.Col1,T3.Col1), COUNT(CASE WHEN T1.Col1 IS NULL THEN T1.Col2
ELSE T3.Col2 END ) AS Col2No, COUNT(CASE WHEN T1.Col1 IS NULL THEN T1.Col3
ELSE T3.Col3 END ) AS Col3No
FROM Table1 T1
LEFT JOIN Table3 T3 ON T1.Col2 = T3.Col2
GROUP BY IsNULL(T1.Col1,T3.Col1)

or more probably

SELECT Col1, SUM(Col2No) as Col2No, SUM(Col3No) as Col3No
FROM (
SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
FROM Table1
GROUP BY Col1
UNION
SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
FROM Table3
GROUP BY Col1 ) A
GROUP BY Col1

John

"JackT" <turnbull.jack@.ntlworld.com> wrote in message
news:ovWhb.854$_54.168325@.newsfep2-win.server.ntli.net...
> Hi,
> I have the following SQL
> SELECT Table1.Col1, Table3.Col1 AS Expr1,
> COUNT(Table1.Col2) AS Col2_No, COUNT(Table1.Col3) AS Col3_No etc,
> FROM Table3
> INNER JOIN Table2 ON Table3.Col1=Table2.Col1
> RIGHT OUTER JOIN Table1 ON Table2.Col2=Table2.Col2
> GROUP BY Table1.Col1, Table3.Col1
> The output rows have a value in either Table1.Col1 or Table3.Col1 but not
> both.
> I'd like to combine Table1.Col1 and Table3.Col1 and group by the combined
> column in the result but don't know how.
> Thanks gratefully|||Thanks John,
I didn't explain too well so I'll detail tables, releationships and what I'm
trying to do. I have managed to reduce & simplify the issue to two tables:-

Targets table which has columns:
target id - key identity autoincrement integer
locationid - integer

Actions table which has columns:
actionid - key identity autoincrement integer
targetid - integer
locationid integer

relationship is Targets RIGHT OUTER JOIN Actions ON Targets.targetid =
Actions.targetid (I want results from all rows in Actions).

I want to count all rows from Actions and group by locationid combined from
both tables.

Targets content:
targetid locationid
1 1
2 1

Actions Content:
actionid targetid locationid
1 NULL 1
2 NULL 2
3 NULL 3
4 1 NULL
5 1 NULL
6 2 NULL

If I use:
SELECT Actions.locationid, Targets.locationid, COUNT(actionid) AS actions
FROM Targets RIGHT JOIN Actions ON Targets.targetid = Actions.target id
GROUP BY Actions.locationid, Targets.locationid

I get:
Actions Actions.locationid Targets.locationid
1 1 NULL
1 2 NULL
1 3 NULL
3 NULL 1

I want to combine both locationid columns in result giving:
Actions locationid
4 1
1 2
1 3

There are more columns than illustrated but if you the above can be cracked,
I'll be away!
Cheers,
Jack

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f886ac8$0$11451$afc38c87@.news.easynet.co.uk. ..
> Hi
> It would help if you posted the DDL (Create Table Statements) , example
data
> (as insert statements) and expected output. From your description it is
not
> 100% clear how the tables relate or what results you expect.
> If the values of Col1 are unique between each table your solution might
be:
> SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
> FROM Table1
> GROUP BY Col1
> UNION
> SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
> FROM Table3
> GROUP BY Col1
> If not
> SELECT IsNULL(T1.Col1,T3.Col1), COUNT(CASE WHEN T1.Col1 IS NULL THEN
T1.Col2
> ELSE T3.Col2 END ) AS Col2No, COUNT(CASE WHEN T1.Col1 IS NULL THEN T1.Col3
> ELSE T3.Col3 END ) AS Col3No
> FROM Table1 T1
> LEFT JOIN Table3 T3 ON T1.Col2 = T3.Col2
> GROUP BY IsNULL(T1.Col1,T3.Col1)
> or more probably
> SELECT Col1, SUM(Col2No) as Col2No, SUM(Col3No) as Col3No
> FROM (
> SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
> FROM Table1
> GROUP BY Col1
> UNION
> SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
> FROM Table3
> GROUP BY Col1 ) A
> GROUP BY Col1
> John|||John,

Thanks for putting me on the right track. With ref to the example in my
reply post I used:

SELECT ISNULL(Actions.locationid, Targets.locationid) AS Location,
COUNT(Actions.actionid) AS Actions_No
FROM Actions LEFT OUTER JOIN
Targets ON Actions.targetid = Targets.targetid
GROUP BY ISNULL(Actions.locationid, Targets.locationid)

All the other columns I want to count are in the Actions table so I just
need to add them to the SELECT statement.
Thanks again,
Jack

"JackT" <turnbull.jack@.ntlworld.com> wrote in message
news:Fu0ib.1525$_54.280845@.newsfep2-win.server.ntli.net...
> Thanks John,
> I didn't explain too well so I'll detail tables, releationships and what
I'm
> trying to do. I have managed to reduce & simplify the issue to two
tables:-
> Targets table which has columns:
> target id - key identity autoincrement integer
> locationid - integer
> Actions table which has columns:
> actionid - key identity autoincrement integer
> targetid - integer
> locationid integer
> relationship is Targets RIGHT OUTER JOIN Actions ON Targets.targetid =
> Actions.targetid (I want results from all rows in Actions).
> I want to count all rows from Actions and group by locationid combined
from
> both tables.
> Targets content:
> targetid locationid
> 1 1
> 2 1
> Actions Content:
> actionid targetid locationid
> 1 NULL 1
> 2 NULL 2
> 3 NULL 3
> 4 1 NULL
> 5 1 NULL
> 6 2 NULL
> If I use:
> SELECT Actions.locationid, Targets.locationid, COUNT(actionid) AS actions
> FROM Targets RIGHT JOIN Actions ON Targets.targetid = Actions.target id
> GROUP BY Actions.locationid, Targets.locationid
> I get:
> Actions Actions.locationid Targets.locationid
> 1 1 NULL
> 1 2 NULL
> 1 3 NULL
> 3 NULL 1
> I want to combine both locationid columns in result giving:
> Actions locationid
> 4 1
> 1 2
> 1 3
> There are more columns than illustrated but if you the above can be
cracked,
> I'll be away!
> Cheers,
> Jack|||Hi

It sounds like it worked then!

Here is usable DDL and example data in case you need it again.

create table Targets (
targetid integer NOT NULL identity (1,1) CONSTRAINT PK_Targets PRIMARY KEY,
locationid integer,
)

create table Actions (
actionid integer NOT NULL identity (1,1) CONSTRAINT PK_Actions PRIMARY KEY,
targetid integer NULL,
locationid integer,
CONSTRAINT FK_Actions FOREIGN KEY (TargetId) REFERENCES Targets(TargetId)
)

INSERT INTO Targets (locationid) VALUES (1)
INSERT INTO Targets (locationid) VALUES (1)

INSERT INTO Actions (targetid, locationid) VALUES (NULL,1)
INSERT INTO Actions (targetid, locationid) VALUES (NULL,2)
INSERT INTO Actions (targetid, locationid) VALUES (NULL,3)
INSERT INTO Actions (targetid, locationid) VALUES (1,NULL)
INSERT INTO Actions (targetid, locationid) VALUES (1,NULL)
INSERT INTO Actions (targetid, locationid) VALUES (2,NULL)

SELECT * FROM Targets

/*
targetid locationid
---- ----
1 1
2 1

(2 row(s) affected)
*/
SELECT * FROM Actions

/*
actionid targetid locationid
---- ---- ----
1 NULL 1
2 NULL 2
3 NULL 3
4 1 NULL
5 1 NULL
6 2 NULL

(6 row(s) affected)
*/

-- Your attempt
SELECT A.locationid, T.locationid, COUNT(A.actionid) AS actions
FROM Targets T RIGHT JOIN Actions A ON T.targetid = A.targetid
GROUP BY A.locationid, T.locationid

/*
locationid locationid actions
---- ---- ----
1 NULL 1
2 NULL 1
3 NULL 1
NULL 1 3

(4 row(s) affected)
*/

-- Your second attempt
SELECT ISNULL(A.locationid, T.locationid) AS Location,
COUNT(A.actionid) AS Actions_No
FROM Actions A LEFT OUTER JOIN Targets T ON A.targetid = T.targetid
GROUP BY ISNULL(A.locationid, T.locationid)

/* Gives
Location Actions_No
---- ----
1 4
2 1
3 1

(3 row(s) affected)
*/

John

"JackT" <turnbull.jack@.ntlworld.com> wrote in message
news:498ib.4706$_54.349437@.newsfep2-win.server.ntli.net...
> John,
> Thanks for putting me on the right track. With ref to the example in my
> reply post I used:
> SELECT ISNULL(Actions.locationid, Targets.locationid) AS Location,
> COUNT(Actions.actionid) AS Actions_No
> FROM Actions LEFT OUTER JOIN
> Targets ON Actions.targetid = Targets.targetid
> GROUP BY ISNULL(Actions.locationid, Targets.locationid)
> All the other columns I want to count are in the Actions table so I just
> need to add them to the SELECT statement.
> Thanks again,
> Jack
> "JackT" <turnbull.jack@.ntlworld.com> wrote in message
> news:Fu0ib.1525$_54.280845@.newsfep2-win.server.ntli.net...
> > Thanks John,
> > I didn't explain too well so I'll detail tables, releationships and what
> I'm
> > trying to do. I have managed to reduce & simplify the issue to two
> tables:-
> > Targets table which has columns:
> > target id - key identity autoincrement integer
> > locationid - integer
> > Actions table which has columns:
> > actionid - key identity autoincrement integer
> > targetid - integer
> > locationid integer
> > relationship is Targets RIGHT OUTER JOIN Actions ON Targets.targetid =
> > Actions.targetid (I want results from all rows in Actions).
> > I want to count all rows from Actions and group by locationid combined
> from
> > both tables.
> > Targets content:
> > targetid locationid
> > 1 1
> > 2 1
> > Actions Content:
> > actionid targetid locationid
> > 1 NULL 1
> > 2 NULL 2
> > 3 NULL 3
> > 4 1 NULL
> > 5 1 NULL
> > 6 2 NULL
> > If I use:
> > SELECT Actions.locationid, Targets.locationid, COUNT(actionid) AS
actions
> > FROM Targets RIGHT JOIN Actions ON Targets.targetid = Actions.target id
> > GROUP BY Actions.locationid, Targets.locationid
> > I get:
> > Actions Actions.locationid Targets.locationid
> > 1 1 NULL
> > 1 2 NULL
> > 1 3 NULL
> > 3 NULL 1
> > I want to combine both locationid columns in result giving:
> > Actions locationid
> > 4 1
> > 1 2
> > 1 3
> > There are more columns than illustrated but if you the above can be
> cracked,
> > I'll be away!
> > Cheers,
> > Jack
>|||Thanks John,
Appreciate your informative close-out post and will certainly file for
reference.
Cheers,
Jack

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f891931$0$11446$afc38c87@.news.easynet.co.uk. ..
> Hi
> It sounds like it worked then!
> Here is usable DDL and example data in case you need it again.
> create table Targets (
> targetid integer NOT NULL identity (1,1) CONSTRAINT PK_Targets PRIMARY
KEY,
> locationid integer,
> )
> create table Actions (
> actionid integer NOT NULL identity (1,1) CONSTRAINT PK_Actions PRIMARY
KEY,
> targetid integer NULL,
> locationid integer,
> CONSTRAINT FK_Actions FOREIGN KEY (TargetId) REFERENCES Targets(TargetId)
> )
> INSERT INTO Targets (locationid) VALUES (1)
> INSERT INTO Targets (locationid) VALUES (1)
> INSERT INTO Actions (targetid, locationid) VALUES (NULL,1)
> INSERT INTO Actions (targetid, locationid) VALUES (NULL,2)
> INSERT INTO Actions (targetid, locationid) VALUES (NULL,3)
> INSERT INTO Actions (targetid, locationid) VALUES (1,NULL)
> INSERT INTO Actions (targetid, locationid) VALUES (1,NULL)
> INSERT INTO Actions (targetid, locationid) VALUES (2,NULL)
> SELECT * FROM Targets
> /*
> targetid locationid
> ---- ----
> 1 1
> 2 1
> (2 row(s) affected)
> */
> SELECT * FROM Actions
> /*
> actionid targetid locationid
> ---- ---- ----
> 1 NULL 1
> 2 NULL 2
> 3 NULL 3
> 4 1 NULL
> 5 1 NULL
> 6 2 NULL
> (6 row(s) affected)
> */
> -- Your attempt
> SELECT A.locationid, T.locationid, COUNT(A.actionid) AS actions
> FROM Targets T RIGHT JOIN Actions A ON T.targetid = A.targetid
> GROUP BY A.locationid, T.locationid
> /*
> locationid locationid actions
> ---- ---- ----
> 1 NULL 1
> 2 NULL 1
> 3 NULL 1
> NULL 1 3
> (4 row(s) affected)
> */
> -- Your second attempt
> SELECT ISNULL(A.locationid, T.locationid) AS Location,
> COUNT(A.actionid) AS Actions_No
> FROM Actions A LEFT OUTER JOIN Targets T ON A.targetid = T.targetid
> GROUP BY ISNULL(A.locationid, T.locationid)
> /* Gives
> Location Actions_No
> ---- ----
> 1 4
> 2 1
> 3 1
> (3 row(s) affected)
> */
>
> John

column name as group in a table

Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
MangeshGroup is a reserved word. It's a best practice not to use column names that
are reserved words. If you insist on using reserved words, use square
brackets:
[Group]
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
Mangesh|||Thanks Tom. So what is the difference when you put a square bracket and
when you don't
"Tom Moreau" wrote:
> Group is a reserved word. It's a best practice not to use column names that
> are reserved words. If you insist on using reserved words, use square
> brackets:
> [Group]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
> message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
> Hi
> I have a table created by user as
> use base
> insert into table1 values ( 'x')
> select * from table1
> where group = 'x'
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'Group'.
> My question is :
> I tried to create a table with
> create table x ( group varchar(10))
> and it gives error
> But same table I Can create from EM with group column.
> But I Can not write a where clause on the group column.
> Is it a bug or what'
> Mangesh
>|||"Mangesh Deshpande" schrieb:
> Thanks Tom. So what is the difference when you put a square bracket and
> when you don't
Object names between square brackets are called 'delimited identifiers', and
delimited identifiers allow you to break each and every restriction of name
creation (e.g. VERY long names, names that contain spaces or other forbidden
characters, names that are reserved words, etc.).
Delimited identifiers are not comfortable to handle though - why don't you
translate the name 'group' into Hindi or Punjab? It is rather unlikely that
it will still conflict with a reserved word ... ;-)
Another solution is using the hungarian notation: the character field
'group' would then be 'cGroup' (or 'vcGroup') which both wouldn't conflict
with the reserved words.|||Hi,
If u understand the process of query execution then u should know that
microsoft compiler detects the reserverd name and from the information
of BOL microsoft suggest to use the reserved names in brakets becasue
on compiltion time microsoft doesnt detects as reserved becasue of
deliminated identifier.
create table ff([group] varchar(20))
from
doller|||Thanks a lot.
"doller" wrote:
> Hi,
> If u understand the process of query execution then u should know that
> microsoft compiler detects the reserverd name and from the information
> of BOL microsoft suggest to use the reserved names in brakets becasue
> on compiltion time microsoft doesnt detects as reserved becasue of
> deliminated identifier.
> create table ff([group] varchar(20))
> from
> doller
>

column name as group in a table

Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what?
Mangesh
Group is a reserved word. It's a best practice not to use column names that
are reserved words. If you insist on using reserved words, use square
brackets:
[Group]
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what?
Mangesh
|||Thanks Tom. So what is the difference when you put a square bracket and
when you don't
"Tom Moreau" wrote:

> Group is a reserved word. It's a best practice not to use column names that
> are reserved words. If you insist on using reserved words, use square
> brackets:
> [Group]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
> message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
> Hi
> I have a table created by user as
> use base
> insert into table1 values ( 'x')
> select * from table1
> where group = 'x'
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'Group'.
> My question is :
> I tried to create a table with
> create table x ( group varchar(10))
> and it gives error
> But same table I Can create from EM with group column.
> But I Can not write a where clause on the group column.
> Is it a bug or what?
> Mangesh
>
|||"Mangesh Deshpande" schrieb:

> Thanks Tom. So what is the difference when you put a square bracket and
> when you don't
Object names between square brackets are called 'delimited identifiers', and
delimited identifiers allow you to break each and every restriction of name
creation (e.g. VERY long names, names that contain spaces or other forbidden
characters, names that are reserved words, etc.).
Delimited identifiers are not comfortable to handle though - why don't you
translate the name 'group' into Hindi or Punjab? It is rather unlikely that
it will still conflict with a reserved word ... ;-)
Another solution is using the hungarian notation: the character field
'group' would then be 'cGroup' (or 'vcGroup') which both wouldn't conflict
with the reserved words.
|||Hi,
If u understand the process of query execution then u should know that
microsoft compiler detects the reserverd name and from the information
of BOL microsoft suggest to use the reserved names in brakets becasue
on compiltion time microsoft doesnt detects as reserved becasue of
deliminated identifier.
create table ff([group] varchar(20))
from
doller
|||Thanks a lot.
"doller" wrote:

> Hi,
> If u understand the process of query execution then u should know that
> microsoft compiler detects the reserverd name and from the information
> of BOL microsoft suggest to use the reserved names in brakets becasue
> on compiltion time microsoft doesnt detects as reserved becasue of
> deliminated identifier.
> create table ff([group] varchar(20))
> from
> doller
>

column name as group in a table

Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
MangeshGroup is a reserved word. It's a best practice not to use column names that
are reserved words. If you insist on using reserved words, use square
brackets:
[Group]
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
Mangesh|||Thanks Tom. So what is the difference when you put a square bracket and
when you don't
"Tom Moreau" wrote:

> Group is a reserved word. It's a best practice not to use column names th
at
> are reserved words. If you insist on using reserved words, use square
> brackets:
> [Group]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
> message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
> Hi
> I have a table created by user as
> use base
> insert into table1 values ( 'x')
> select * from table1
> where group = 'x'
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'Group'.
> My question is :
> I tried to create a table with
> create table x ( group varchar(10))
> and it gives error
> But same table I Can create from EM with group column.
> But I Can not write a where clause on the group column.
> Is it a bug or what'
> Mangesh
>|||"Mangesh Deshpande" schrieb:

> Thanks Tom. So what is the difference when you put a square bracket and
> when you don't
Object names between square brackets are called 'delimited identifiers', and
delimited identifiers allow you to break each and every restriction of name
creation (e.g. VERY long names, names that contain spaces or other forbidden
characters, names that are reserved words, etc.).
Delimited identifiers are not comfortable to handle though - why don't you
translate the name 'group' into Hindi or Punjab? It is rather unlikely that
it will still conflict with a reserved word ... ;-)
Another solution is using the hungarian notation: the character field
'group' would then be 'cGroup' (or 'vcGroup') which both wouldn't conflict
with the reserved words.|||Hi,
If u understand the process of query execution then u should know that
microsoft compiler detects the reserverd name and from the information
of BOL microsoft suggest to use the reserved names in brakets becasue
on compiltion time microsoft doesnt detects as reserved becasue of
deliminated identifier.
create table ff([group] varchar(20))
from
doller|||Thanks a lot.
"doller" wrote:

> Hi,
> If u understand the process of query execution then u should know that
> microsoft compiler detects the reserverd name and from the information
> of BOL microsoft suggest to use the reserved names in brakets becasue
> on compiltion time microsoft doesnt detects as reserved becasue of
> deliminated identifier.
> create table ff([group] varchar(20))
> from
> doller
>