Combining two rows in a view

I have created a view for reporting. Im basically just joining a few
tables. It is for a University so the results shows students names and
the credits they are currently taking and the school code (There is 3
Colleges under one ownership)
The problem is some students attend two colleges and appear twice,
one for each enrollment. For example
FName LName Credits SchoolCode
John Smith 12 1468
John Smith 4 1469
I need to combine these results so it would look like this
John Smith 16 1468
This is not for all students just certain ones. I would like to do
this in the view if possible. Any help is appreciated.
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Programming...50.h
tml
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=904750Looks like you want to return just one of the school codes? In that case,
just group the data by student, and aggregate the measures:
SELECT StudentID, FName, LName, SUM(Credits) AS TotalCredits,
MIN(ScheelCode) AS MinSchoolCode
FROM ViewName
GROUP BY StudentID, FName, LName;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"TheCount" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_904750_a05cfa9ea57158f694c614723c
ee26e9@.dbforumz.com...
>I have created a view for reporting. I'm basically just joining a few
> tables. It is for a University so the results shows students names and
> the credits they are currently taking and the school code (There is 3
> Colleges under one ownership)
> The problem is some students attend two colleges and appear twice,
> one for each enrollment. For example
> FName LName Credits SchoolCode
> John Smith 12 1468
> John Smith 4 1469
> I need to combine these results so it would look like this
> John Smith 16 1468
> This is not for all students just certain ones. I would like to do
> this in the view if possible. Any help is appreciated.
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/Programming...pict262850.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=904750|||Take a look at this example:
http://milambda.blogspot.com/2005/0...s-as-array.html
ML

Combining two columns as third column

Maybe a dumb question or me being burnt out.
The people that wrote the DB I am working on were not the brightest in the
world.
They created an inventory item with the manufacturer post pended to the
number.
Example.
81335C12 AMP
Where 81553C12 is the part number and AMP is the abbreviation for the
Manufacturer.
Please don't ask me why.
But I am pushing data to the DB and I need to combine the part number from
the new DB which is kept in a column by itself and then concatenate the
Manufacturer code which is kept in a column by itself in to one column on an
append query.
It is possible or do I need to do an intermedate table?
It is partnumber space manufacturercode. That is there primary key.
Suggestions appreciated
George
Assuming this is just an INSERT and assuming you don't have any NULLs to
worry about, could this be what you're looking for:
INSERT INTO NewTable (part_number, ...)
SELECT partnumber+' '+manufacturercode, ...
FROM OtherTable
David Portas
SQL Server MVP
|||Thanks, more than you can know, brain burnt out today.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:fLOdnQ8cJ5zoOQfcRVn-sw@.giganews.com...
> Assuming this is just an INSERT and assuming you don't have any NULLs to
> worry about, could this be what you're looking for:
> INSERT INTO NewTable (part_number, ...)
> SELECT partnumber+' '+manufacturercode, ...
> FROM OtherTable
> --
> David Portas
> SQL Server MVP
> --
>

Combining two columns as third column

Maybe a dumb question or me being burnt out.
The people that wrote the DB I am working on were not the brightest in the
world.
They created an inventory item with the manufacturer post pended to the
number.
Example.
81335C12 AMP
Where 81553C12 is the part number and AMP is the abbreviation for the
Manufacturer.
Please don't ask me why.
But I am pushing data to the DB and I need to combine the part number from
the new DB which is kept in a column by itself and then concatenate the
Manufacturer code which is kept in a column by itself in to one column on an
append query.
It is possible or do I need to do an intermedate table?
It is partnumber space manufacturercode. That is there primary key.
Suggestions appreciated
GeorgeAssuming this is just an INSERT and assuming you don't have any NULLs to
worry about, could this be what you're looking for:
INSERT INTO NewTable (part_number, ...)
SELECT partnumber+' '+manufacturercode, ...
FROM OtherTable
David Portas
SQL Server MVP
--|||Thanks, more than you can know, brain burnt out today.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:fLOdnQ8cJ5zoOQfcRVn-sw@.giganews.com...
> Assuming this is just an INSERT and assuming you don't have any NULLs to
> worry about, could this be what you're looking for:
> INSERT INTO NewTable (part_number, ...)
> SELECT partnumber+' '+manufacturercode, ...
> FROM OtherTable
> --
> David Portas
> SQL Server MVP
> --
>

Combining two columns as third column

Maybe a dumb question or me being burnt out.
The people that wrote the DB I am working on were not the brightest in the
world.
They created an inventory item with the manufacturer post pended to the
number.
Example.
81335C12 AMP
Where 81553C12 is the part number and AMP is the abbreviation for the
Manufacturer.
Please don't ask me why.
But I am pushing data to the DB and I need to combine the part number from
the new DB which is kept in a column by itself and then concatenate the
Manufacturer code which is kept in a column by itself in to one column on an
append query.
It is possible or do I need to do an intermedate table?
It is partnumber space manufacturercode. That is there primary key.
Suggestions appreciated
GeorgeAssuming this is just an INSERT and assuming you don't have any NULLs to
worry about, could this be what you're looking for:
INSERT INTO NewTable (part_number, ...)
SELECT partnumber+' '+manufacturercode, ...
FROM OtherTable
--
David Portas
SQL Server MVP
--|||Thanks, more than you can know, brain burnt out today.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:fLOdnQ8cJ5zoOQfcRVn-sw@.giganews.com...
> Assuming this is just an INSERT and assuming you don't have any NULLs to
> worry about, could this be what you're looking for:
> INSERT INTO NewTable (part_number, ...)
> SELECT partnumber+' '+manufacturercode, ...
> FROM OtherTable
> --
> David Portas
> SQL Server MVP
> --
>

combining Sat-Sun and also Fri-Sun

One of the dimensions in my cube is Weekday, with 1=Monday through 7=Sunday.

I have created named sets {1,2,3,4,5} for Mon-Fri and {6,7} for Sat and Sun. However many people also want to include Friday as the weekend, they prefer {1,2,3,4} and {5,6,7}, so I created named sets for those too.

However, I can't find any way to use these at the client level (my client is Excel Pivot tables, but I evaluated many other clients). I find named sets are only usable within SSAS's own cube browser (as a filter). Further, I can't find any way to get the cube to pre-calculate the groups. I know named sets don't get calculated, I am open to another way to get this done. I use VBA within Excel to select multiple weekdays in the PivotTable, but this causes lengthy OLAP queries each time.

If I had only one way of breaking weekday vs weekend, I would create a new table with 7 records:

1 Weekday

2 Weekday

...

6 Weekend

7 Weekend

and then create a hierarchy, the weekday/weekend level would then get pre-calculated. But since I have two possibilities, I can't do this.

I've tried all kinds of creative possibilities, does anyone have a suggestion on how to get all 11 "members" of this dimension (7 individual weekdays, plus the 4 named sets) to be pre-calculated? Thanks in advance!

Hello! The simple solution is that you use a client that will present SSAS2005 named sets, like ProClarity Professional 6.3 or Excel 2007.

Older versions of Excel do not support named sets.

If this is not possible I suggest that you add two new attributes for the two different weekday/weekend classifications.

HTH

Thomas Ivarsson

Combining Queries/ Results

I have created a search interface for a large table and I allow users to search on keywords. The users can enter multiple keywords and I build a SQL based on their input to search a full-text indexed table.
However the users want to be able to search like an old system they had, where they enter single words and then combine their searches to drill-down into the results.
What would be the best method to combine searches?
At the moment I can create a merged query from 2 queries if they have searched using single words, but I know down the line it will get far more complicated if they keep combining and merging even with multiple word entries.
Each time they search I store the 'where' section of each query, then if they choose to combine I have a function to build a new query through arrays (to eliminate duplicates and sort etc)
Is there a better way in SQL to combine queries as sometimes the logic of the combined query means no results are returned (because of OR/ AND conditions in the wrong places etc)
e.g.
1. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"'))
2. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"level"') OR CONTAINS(subject,'"level"'))
Combined using my function creates:
Select count(ID) as myCount FROM myTable where (contains(title,'"level"') AND contains(title,'"run"')) OR (contains(subject,'"level"') AND contains(subject,'"run"'))

When I combine I'm drilling down, so if the first query returns a count of 400 (where thetitleORsubjectcontains 'run') and then the second query returns 600 records (where thetitleORsubjectcontains 'level') I need to combine so that I'm looking for records where thetitlecontains both keywords 'run' AND 'level' OR else thesubjectcontains both 'run' AND 'level' and I end up with say 50 records where the title has both keywords OR the subject holds both words.
I think the main trouble lies if they try combine a previously combines search with a new search. here my logic gets totally thrown and I'm not sure how to handle soemthing like this. Has anyone got any ideas or experience with this kind of functionality? In SQL or even in vb.net is there a method to combine searches easily?

You don't need to build it like you are. Just keep adding ANDswith the appropriate conditions. SQL will figure it out.
Example:
S1: WHERE (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"')
S2: WHERE (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"')
AND (CONTAINS(title,'"level"') OR CONTAINS(subject,'"level"'))
S3: WHERE (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"')
AND (CONTAINS(title,'"level"') OR CONTAINS(subject,'"level"'))
AND (CONTAINS(title,'"blah"') OR CONTAINS(subject,'"blah"'))
...

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 2 select with count and datediff into 1 select. need help.

I have created two select clauses for counting weekdays. Is there a way to combine the two select together? I would like 1 table with two columns:

Jobs Complete Jobs completed within 5 days

10 5

-

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed'
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

select each one as a sub select - this will only work if each one only returns 1 column and 1 row

select

(

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

)

(

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed'
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

)

|||thanks for the response.. However, this will not work. I'm using Visual Web Developer 2005 express to test your script and it returns 0. I believe its because you it select (....) <-nothing.|||

I missed the comma between the two... I really should check my syntax better!

select

(

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2)
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

) AS 'Jobs Completed within 5 days',

(

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2)
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

)AS 'Total Jobs Completed'

|||

thanks for the update. It runs and returned:

Total Jobs Completed Jobs Completed within 5 days

0 0

It should return 6 and 3. So, the subset is not returning the right values.

|||

I have also ran the query just this:

SELECT (SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS Expr1
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)) AS 'Jobs Completed within 5 days'

-

it return:

Jobs Completed within 5 days

0

|||

with some messing around.. I found that this code below works properly.

-

SELECT (SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS Expr1
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request '))
AS 'Total Jobs Completed',
(SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS Expr1
FROM dbo.Project AS Project_1
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)) AS 'Jobs Completed within 5 days'

-

Many thanks for you help.

|||

Hi,

I am thinking through this same process, and I could be wrong, but I don't think that this code is going to be accurate for determining weekdays. The reason is that if the first day you're counting is a Sunday (in this case, your DateintoSD), then you will have one more weekday than SQL is going to count. It will count the first week interval 6 days later, between Saturday and Sunday. You're multiplying your week DateDiff by 2, so you'll get that saturday and sunday subtracted from your total days of the month, but that first Sunday never gets subtracted. Am I wrong?

There is a solution I found elsewhere that involves building a calendar table in SQL, and if you Google that it will come up in your results. That's a bit more involved, however.

Andy

|||

A "calander" table should be added to any database as standard practice, loaded with weekday/end flags, public holidays and various date formats (these can be very useful when dealing with system interfaces)

combining 2 select with count and datediff into 1 select. need help.

I have created two select clauses for counting weekdays. Is there a way to combine the two select together? I would like 1 table with two columns:

Jobs Complete Jobs completed within 5 days

10 5

-

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed'
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

select each one as a sub select - this will only work if each one only returns 1 column and 1 row

select

(

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

)

(

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed'
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

)

|||thanks for the response.. However, this will not work. I'm using Visual Web Developer 2005 express to test your script and it returns 0. I believe its because you it select (....) <-nothing.|||

I missed the comma between the two... I really should check my syntax better!

select

(

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2)
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

) AS 'Jobs Completed within 5 days',

(

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2)
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

)AS 'Total Jobs Completed'

|||

thanks for the update. It runs and returned:

Total Jobs Completed Jobs Completed within 5 days

0 0

It should return 6 and 3. So, the subset is not returning the right values.

|||

I have also ran the query just this:

SELECT (SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS Expr1
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)) AS 'Jobs Completed within 5 days'

-

it return:

Jobs Completed within 5 days

0

|||

with some messing around.. I found that this code below works properly.

-

SELECT (SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS Expr1
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request '))
AS 'Total Jobs Completed',
(SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS Expr1
FROM dbo.Project AS Project_1
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)) AS 'Jobs Completed within 5 days'

-

Many thanks for you help.

Combine two int columns into one bigint column

Hello
I have created a database that uses an int for the table primary keys.
This database is being deployed at several sites. Each site has a
unique site ID which is less than 100.
There is another database that acts as a master viewer. This database
holds a copy of all the site databases to do reports across all the
sites. Basically each site database gets 'merged' with the master
database once a month.
The master viewer database uses exactly the same structure as the site
database, but needs to store the site ID with every record. I have
currently achieved this by changing the table primary key to be a
bigint and store the site ID in 30 bits and the actual site record
primary in the remaining 34 bits (ok I could have used 32/32). The
reason for not adding another primary key column is because I use the
exact same sql queries in the master database and site databases, and
adding another column would mean creating two seperate queries when
joining tables (one to work on the master and one to work on the sites
- more work and difficult to maintain).
It is relatively simple to extract the site ID and record primary key
from the bigint using bitwise operations and bit shifting.
Unfortunately sql does not support bit shifting and I use division for
the same affect. The only downside I see is the performance issue when
extracting the site primary key in a sql query. If I want to test the
site primary key I use "WHERE ((MaintenanceTransaction_PRK &
-1073741824) / 1073741824) = 1" for example (I use "WHERE (1073741823 &
MaintenanceTransaction_PRK) = 1" to test the site ID) where 2^30 =
1073741824. If the table has tens of thousands/millions of records I
can see this taking a while.
Does anyone have any other suggestions that I could use?
Many thanks
PaulChange your database in the master and client sites to include site ID as
part of the key in all of them. Then you can write one SQL that will run
against all of your databases equally. This will server you better in the
long run.
Or you can simply multiply the PK by 10000 and add the site ID to it to get
the new ID. This will give you a new combined ID without all that screwing
around with bits. Much simpler and you will have room for 10000 customers
before you run out of site IDs. This really is a kludge, however, and not
the best way to solve the problem.
<kerplunkwhoops@.yahoo.co.uk> wrote in message
news:1149083975.233210.304140@.i40g2000cwc.googlegroups.com...
> Hello
> I have created a database that uses an int for the table primary keys.
> This database is being deployed at several sites. Each site has a
> unique site ID which is less than 100.
> There is another database that acts as a master viewer. This database
> holds a copy of all the site databases to do reports across all the
> sites. Basically each site database gets 'merged' with the master
> database once a month.
> The master viewer database uses exactly the same structure as the site
> database, but needs to store the site ID with every record. I have
> currently achieved this by changing the table primary key to be a
> bigint and store the site ID in 30 bits and the actual site record
> primary in the remaining 34 bits (ok I could have used 32/32). The
> reason for not adding another primary key column is because I use the
> exact same sql queries in the master database and site databases, and
> adding another column would mean creating two seperate queries when
> joining tables (one to work on the master and one to work on the sites
> - more work and difficult to maintain).
> It is relatively simple to extract the site ID and record primary key
> from the bigint using bitwise operations and bit shifting.
> Unfortunately sql does not support bit shifting and I use division for
> the same affect. The only downside I see is the performance issue when
> extracting the site primary key in a sql query. If I want to test the
> site primary key I use "WHERE ((MaintenanceTransaction_PRK &
> -1073741824) / 1073741824) = 1" for example (I use "WHERE (1073741823 &
> MaintenanceTransaction_PRK) = 1" to test the site ID) where 2^30 =
> 1073741824. If the table has tens of thousands/millions of records I
> can see this taking a while.
> Does anyone have any other suggestions that I could use?
> Many thanks
> Paul
>|||Hello
I did consider changing the whole of the project to include the site ID
as a seperate column in the site database, but that would be a huge
amount of work and the master viewer is a special case for one
customer. I was trying to make it as simple as possible. It all works
correctly now, but I am just concerned about the performance.
Your solution about mutliplying by 10000 is the same concept to what I
do currently. It still has the problem of division to extract the
primary key.
Thanks
Paul
Jim Underwood wrote:
> Change your database in the master and client sites to include site ID as
> part of the key in all of them. Then you can write one SQL that will run
> against all of your databases equally. This will server you better in the
> long run.
> Or you can simply multiply the PK by 10000 and add the site ID to it to ge
t
> the new ID. This will give you a new combined ID without all that screwin
g
> around with bits. Much simpler and you will have room for 10000 customers
> before you run out of site IDs. This really is a kludge, however, and not
> the best way to solve the problem.
> <kerplunkwhoops@.yahoo.co.uk> wrote in message
> news:1149083975.233210.304140@.i40g2000cwc.googlegroups.com...|||I am not sure if you will be able to get the Site Id by using multiplication
and division.
Consider the following scenario
Site Id Other Id
10 1000 = 10X1000 = 10000
20 500 = 20X500 = 10000
You will not be able to find out the site Id using division as both the
multiplication
results in the same value.
You could try changing the datatype of Master database's Id col to VARCHAR
and have Id values as
10 concatenated with 00001000 as 1000001000
20 concatenated with 00000500 as 1000000500.
I hope doing this will not affect your Queries as there are not new column
and only a DataTypeChange. You could also extract the site id by using
substring (first 2 chrs) functions.
- Sha Anand
"kerplunkwhoops@.yahoo.co.uk" wrote:

> Hello
> I did consider changing the whole of the project to include the site ID
> as a seperate column in the site database, but that would be a huge
> amount of work and the master viewer is a special case for one
> customer. I was trying to make it as simple as possible. It all works
> correctly now, but I am just concerned about the performance.
> Your solution about mutliplying by 10000 is the same concept to what I
> do currently. It still has the problem of division to extract the
> primary key.
> Thanks
> Paul
>
> Jim Underwood wrote:
>

combine 2 select count datediff

I have created two select clauses for counting weekdays. Is there a way to combine the two select together? I would like 1 table with two columns:

Jobs Complete Jobs completed within 5 days

10 5

-

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed'
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

2 ways of the top of my head:

1

select Z1.[Jobs Completed within 5 days], Z2.[Total Jobs Completed]

from

(

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

) Z1,

(

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed'
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

) Z2

2

select

(

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

),

(

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed'
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

)

|||

Did this solve your problem? Please mark answer if so or reply back.

Derek

combine 2 select count datediff

I have created two select clauses for counting weekdays. Is there a way to combine the two select together? I would like 1 table with two columns:

Jobs Complete Jobs completed within 5 days

10 5

-

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed'
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

2 ways of the top of my head:

1

select Z1.[Jobs Completed within 5 days], Z2.[Total Jobs Completed]

from

(

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

) Z1,

(

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed'
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

) Z2

2

select

(

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

),

(

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed'
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

)

|||

Did this solve your problem? Please mark answer if so or reply back.

Derek

Combination charts issues

I have created a chart to chart a combinational chart including a
stacked column and the continous line charts.
But the problem is this combinational chart nowhere looks like the
Excel combinational chart for the same data as in the reporting
services report. Not sure if this is a problem in the RS charting
engine.
How do I create a stacked column and a continous like chart.
Thanks
KarenHi Karen
So far, I didn't find anything where you can mix charttypes. The only thing
is, you can define a Series to be drawn as a line. You can set that in the
properties of a value on the second tab there's a checkbox for that.
HTH
Luzia
<karenmiddleol@.yahoo.com> schrieb im Newsbeitrag
news:1131399473.731448.325340@.g49g2000cwa.googlegroups.com...
>I have created a chart to chart a combinational chart including a
> stacked column and the continous line charts.
> But the problem is this combinational chart nowhere looks like the
> Excel combinational chart for the same data as in the reporting
> services report. Not sure if this is a problem in the RS charting
> engine.
> How do I create a stacked column and a continous like chart.
> Thanks
> Karen
>|||Hi Luzia
I even dropped setting a combination chart of line and stacked column
instead I set multiple series all line graphs. The charts generated are
nowhere comparable to what I get in Excel not sure what is wrong.
I am frustrated.
Thanks
Karen

Columns on Summary Page of SSMS

Hello all,
The Summary Page In SSMS looking at the tables of a DB in Details view,
there are 3 columns: Name, Schema, & Created.
Is there any way to select other columns to view?
I created a "Notes" property in the Extended Properties of a Table's
Properties with some info on why that table is there and what the data is.
I would like to display this as a column in the Summary Page.
Is this possible?
Thanks for any help anyone can provide,
Conan Kelly
Have a look at this blog. There is a lot of good info related to SSMS and
specifically custom reports in SP2.
http://blogs.msdn.com/sqlrem/default.aspx
Andrew J. Kelly SQL MVP
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:7H7Wh.62184$VU4.46621@.bgtnsc05-news.ops.worldnet.att.net...
> Hello all,
> The Summary Page In SSMS looking at the tables of a DB in Details view,
> there are 3 columns: Name, Schema, & Created.
> Is there any way to select other columns to view?
> I created a "Notes" property in the Extended Properties of a Table's
> Properties with some info on why that table is there and what the data is.
> I would like to display this as a column in the Summary Page.
> Is this possible?
> Thanks for any help anyone can provide,
> Conan Kelly
>

Columns on Summary Page of SSMS

Hello all,
The Summary Page In SSMS looking at the tables of a DB in Details view,
there are 3 columns: Name, Schema, & Created.
Is there any way to select other columns to view?
I created a "Notes" property in the Extended Properties of a Table's
Properties with some info on why that table is there and what the data is.
I would like to display this as a column in the Summary Page.
Is this possible?
Thanks for any help anyone can provide,
Conan KellyHave a look at this blog. There is a lot of good info related to SSMS and
specifically custom reports in SP2.
http://blogs.msdn.com/sqlrem/default.aspx
Andrew J. Kelly SQL MVP
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:7H7Wh.62184$VU4.46621@.bgtnsc05-news.ops.worldnet.att.net...
> Hello all,
> The Summary Page In SSMS looking at the tables of a DB in Details view,
> there are 3 columns: Name, Schema, & Created.
> Is there any way to select other columns to view?
> I created a "Notes" property in the Extended Properties of a Table's
> Properties with some info on why that table is there and what the data is.
> I would like to display this as a column in the Summary Page.
> Is this possible?
> Thanks for any help anyone can provide,
> Conan Kelly
>

Columns on Summary Page of SSMS

Hello all,
The Summary Page In SSMS looking at the tables of a DB in Details view,
there are 3 columns: Name, Schema, & Created.
Is there any way to select other columns to view?
I created a "Notes" property in the Extended Properties of a Table's
Properties with some info on why that table is there and what the data is.
I would like to display this as a column in the Summary Page.
Is this possible?
Thanks for any help anyone can provide,
Conan KellyHave a look at this blog. There is a lot of good info related to SSMS and
specifically custom reports in SP2.
http://blogs.msdn.com/sqlrem/default.aspx
Andrew J. Kelly SQL MVP
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:7H7Wh.62184$VU4.46621@.bgtnsc05-news.ops.worldnet.att.net...
> Hello all,
> The Summary Page In SSMS looking at the tables of a DB in Details view,
> there are 3 columns: Name, Schema, & Created.
> Is there any way to select other columns to view?
> I created a "Notes" property in the Extended Properties of a Table's
> Properties with some info on why that table is there and what the data is.
> I would like to display this as a column in the Summary Page.
> Is this possible?
> Thanks for any help anyone can provide,
> Conan Kelly
>

Columns IDENTITY property

Hi all.
I have inherited some table full of data. It has a primary key [int]
clustered on one column. But this column was not created with IDENTITY. Now
I need to add IDENTITY to this column (ALTER COLUMN, I guess) without
loosing the data but can't figure out the syntax of proper script :( I know
that EM allows you to do just that but I need a script).
Could anyone help me with that, please? Is it possible at all? Books Online
doc is not clear about this situation.
Thanks!!Here is the script that EM generates to add Identity to a column:
As you can see, you must create a new table,
copy the data from the old table to the new,
drop the old table and rename the new one.
This is a very simple example.
For a table with constraints, triggers, foreign keys etc., the script gets
longer.
On another table I have, the same operation generates about 300 lines of
code.
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_numbers
(
number int NOT NULL,
test int NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_numbers ON
GO
IF EXISTS(SELECT * FROM dbo.numbers)
EXEC('INSERT INTO dbo.Tmp_numbers (number, test)
SELECT number, test FROM dbo.numbers TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_numbers OFF
GO
DROP TABLE dbo.numbers
GO
EXECUTE sp_rename N'dbo.Tmp_numbers', N'numbers', 'OBJECT'
GO
COMMIT
"Kikoz" <kikoz@.hotmail.com> wrote in message
news:ubdqHSICFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hi all.
> I have inherited some table full of data. It has a primary key [int]
> clustered on one column. But this column was not created with IDENTITY.
Now
> I need to add IDENTITY to this column (ALTER COLUMN, I guess) without
> loosing the data but can't figure out the syntax of proper script :( I
know
> that EM allows you to do just that but I need a script).
> Could anyone help me with that, please? Is it possible at all? Books
Online
> doc is not clear about this situation.
> Thanks!!
>
>|||You can not do it using ALTER TABLE. Set the identity property in EM and
click "Save change script" button (third from left to rigth).
AMB
"Kikoz" wrote:

> Hi all.
> I have inherited some table full of data. It has a primary key [int]
> clustered on one column. But this column was not created with IDENTITY. No
w
> I need to add IDENTITY to this column (ALTER COLUMN, I guess) without
> loosing the data but can't figure out the syntax of proper script :( I kno
w
> that EM allows you to do just that but I need a script).
> Could anyone help me with that, please? Is it possible at all? Books Onlin
e
> doc is not clear about this situation.
> Thanks!!
>
>|||That's exactly what I was looking for. Thanx, man!!
"raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
news:up6OjnICFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Here is the script that EM generates to add Identity to a column:
> As you can see, you must create a new table,
> copy the data from the old table to the new,
> drop the old table and rename the new one.
> This is a very simple example.
> For a table with constraints, triggers, foreign keys etc., the script gets
> longer.
> On another table I have, the same operation generates about 300 lines of
> code.
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_numbers
> (
> number int NOT NULL,
> test int NOT NULL IDENTITY (1, 1)
> ) ON [PRIMARY]
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers ON
> GO
> IF EXISTS(SELECT * FROM dbo.numbers)
> EXEC('INSERT INTO dbo.Tmp_numbers (number, test)
> SELECT number, test FROM dbo.numbers TABLOCKX')
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers OFF
> GO
> DROP TABLE dbo.numbers
> GO
> EXECUTE sp_rename N'dbo.Tmp_numbers', N'numbers', 'OBJECT'
> GO
> COMMIT
> "Kikoz" <kikoz@.hotmail.com> wrote in message
> news:ubdqHSICFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Now
> know
> Online
>|||That's exactly what I was looking for. Thanx, man!!
"raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
news:up6OjnICFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Here is the script that EM generates to add Identity to a column:
> As you can see, you must create a new table,
> copy the data from the old table to the new,
> drop the old table and rename the new one.
> This is a very simple example.
> For a table with constraints, triggers, foreign keys etc., the script gets
> longer.
> On another table I have, the same operation generates about 300 lines of
> code.
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_numbers
> (
> number int NOT NULL,
> test int NOT NULL IDENTITY (1, 1)
> ) ON [PRIMARY]
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers ON
> GO
> IF EXISTS(SELECT * FROM dbo.numbers)
> EXEC('INSERT INTO dbo.Tmp_numbers (number, test)
> SELECT number, test FROM dbo.numbers TABLOCKX')
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers OFF
> GO
> DROP TABLE dbo.numbers
> GO
> EXECUTE sp_rename N'dbo.Tmp_numbers', N'numbers', 'OBJECT'
> GO
> COMMIT
> "Kikoz" <kikoz@.hotmail.com> wrote in message
> news:ubdqHSICFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Now
> know
> Online
>

Column space utilization

Hello

I created a table with column name "description" as varchar(8000). My doubt is if I am not storing 8000 characters in this column, will SQL Server use memory space needed for 8000 characters ? or will it use only the space that needs for my text. ?

Thanking You
NavaneethIt will only use the space for your text.|||Thank you marcel,

I am planning to implement search functionality for my website. So choosing which datatype for search keywords will be optimum for me ? I need to store large number of data in this filed. I planned for text type. Is it a good choice ? Will text type utilize more space than any other datatype ? How much big my SQL database can grow ?|||You might was to look into using Full Text Search. Search in Book On Line for this topic.

Thanks,|||

Hello!

Moving forward you should NOT USE TEXT data types. TEXT has been obsoleted. VARCHAR(MAX) is the replacement.

Charles Hyman

Senior Consultant

MCTS SQL Server 2005

MCTS Biztalk Server

MCTS Vista Config

MCITP Database Administration

MCITP Database Developer

TALLAN Inc.

www.tallan.com

|||Thank you,

But upto my knowledge I think varchar can store only 8000 characters ? In new versions is it increased ?|||varchar(max) can store as much information as text. (so upto 2GB)

Thanks,

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
>