Mark duplicate records in a Select statement ?

I have a requirement to mark duplicate records when I pull them from the database.

However, I only want to mark the 2nd, 3rd, 4th etc record - not the first one.

The code I have below creates a column called Dupes but marks all the duplicates - including the first one.

Is there a way to only mark the 2nd, 3rd, 4th etc record ?

SELECT *, cs.CallStatusDescription as CSRStatusDesc, cs2.CallStatusDescription as CustomerStatusDesc, (Select MAX(CallAttemptNumber)From CallResults cr Where cl.Id = cr.CallLogId) as CallAttemptNumber,

Dupes = (select count(id)
from CallLogs
where (CustomerHomePhone != '' AND cl.CustomerHomePhone = CustomerHomePhone)
OR (CustomerBusinessPhone != '' AND cl.CustomerBusinessPhone = CustomerBusinessPhone)
AND DealerId= 'hdsh'
AND CSRStatus IS NULL
and datediff(d, logdate, getdate()) <= 21),

FROM CallLogs cl
left Join CallStatus cs on cs.Id = cl.CSRstatus
left Join CallStatus cs2 on cs2.Id = cl.Customerstatus
Where SaleStage IN ('1', '2', '3', '4', '5', '6') And (LogProcessFlag = 1 Or LogProcessFlag = 0)
And DealerId='hdsh'
And Logdate Between '08/01/2007' And '08/31/2007'

Can't you just decrement the 'count' statement by 1 before assigning it to Dupes?

1Dupes = (SELECTcount(id)
2FROM CallLogs
3WHERE(CustomerHomePhone !=''AND cl.CustomerHomePhone = CustomerHomePhone)
4 OR (CustomerBusinessPhone !=''AND cl.CustomerBusinessPhone = CustomerBusinessPhone)
5AND DealerId='hdsh'
6 AND CSRStatusISNULL
7 ANDdatediff(d, logdate,getdate()) <= 21))- 1

That way it just accounts for itself by subtracting 1 from the total 

|||

The problem is that both records get the same Dupe number.

I need the 2nd "dupe" to be marked as 2, the 3rd "dupe" to be marked as 3 etc

Mappings question in OLE DB Destination

Hi,

I have a situation where I want to map a column from a flat file to TWO columns in a table.

However, in the mappings tab, you can only select the "Input Column" once. Once a column has been used, it no longer appears in the drop down list.

I am wondering if there's a way to override this behavior, and if not, what is the best way to handle this type of situation?

I have added an EXECUTE SQL task to update the second column with the inserted column values, but I would like to know if the default mapping behavior can be changed, as it seems so limited.

Thanks

Add a derived column right before the destination and select the column that you want to use more than once and drag it to the expression box. Adjust the name of the new column accordingly.

Then in the OLE DB Destination you can select the column you just added.

Feel free to suggest new features over at http://connect.microsoft.com/sqlserver/feedback|||

Great, thanks

sql

Mapping image pointers to page numbers

Is there a way to convert an image pointer to a page ID that could be
used in DBCC page

i.e.

select TEXTPTR(document)FROM testdocs where id = 1
resturns
0xFEFF3601000000000800000003000000

select convert(int,TEXTPTR(document)) FROM testdocs where id =1
returns
50331648

dbcc page (9,3,8,1)
dumps the first page of the image

I am trying to map 0xFEFF3601000000000800000003000000 - > page
number 8

thanks"ScottYoder" <scott.yoder@.ngc.com> wrote in message
news:f03d8889.0410011322.6ab77061@.posting.google.c om...
> Is there a way to convert an image pointer to a page ID that could be
> used in DBCC page
> i.e.
> select TEXTPTR(document)FROM testdocs where id = 1
> resturns
> 0xFEFF3601000000000800000003000000
> select convert(int,TEXTPTR(document)) FROM testdocs where id =1
> returns
> 50331648
> dbcc page (9,3,8,1)
> dumps the first page of the image
>
> I am trying to map 0xFEFF3601000000000800000003000000 - > page
> number 8
> thanks

I don't think so - according to BOL, TEXTPTR returns a pointer to the root
of an internal pointer tree. So just having the root pointer may not be
enough to find all the pages in the image, if the internal pointer tree is
not accessible in any way.

"Inside SQL Server 2000" pp 260-266 discusses text/image storage - it might
be useful if you haven't read it already. You seem to be doing something
quite unusual, so if you can give some more details about what your real
goal is, someone might have a better answer.

Simon

Mapping a string field to Boolean output in SELECT clause

Hello,
I am facing a problem in a SELECT clause which i cannot solve.
In my SQL table ("myTable") i have a few columns ("Column1", "Column2", "TypeColumn"). When I select different columns of the table, instead of getting the value of TypeColumn, i would like to get a boolean indicating whether its value is a certain string or not.
For example, the TypeColumn accepts only a number of selected strings: "AAA", "BBB", "CCC".
when i do a select query on the table, instead of asking for TypeColumn i would like to ask a boolean value of 1 if TypeColumn is "AAA" and 0 if TypeColumn is "BBB" or "CCC". Also, i would like to make this query while I am also fetching the other columns. And i would like to use one query to get all that. I thought something like thsi would work:

SELECT Column1 AS Col1, Column2 AS Col2, IF(TypeColumn = "AAA", 1, 0) AS Col3
FROM myTable

but this doesn't work in SQL 2005!
Is it possible to do something similar in SQL 2005 using one query only? i am trying to avoid multiple queries for this.

thanks a lot for your help!

Hi,

try this here:

SELECT Column1 AS Col1, Column2 AS Col2, CASE WHEN TypeColumn = "AAA" THEN 1 ELSE 0 END AS Col3
FROM myTable

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||It works!
Thank you, thank you, thank you!!!!!!!sql

Mapped Drive

Can I make my "default location" for new databases be a mapped drive? When
I go into the properties and select the database tab and select the ...
box to list my drives, the shared drive does not show up. If I go ahead
and enter the path of where I want the files to be located on the "mapped"
drive, it accepts it, but does not use it when I create a new database.
My situation is I have 2 computers in a single room and I want then to be
pointed at the same file location for all databases... Both machines have
MSDE version of sql server loaded...Can I somehow point one machine to the
second machines SQL server such that we are working on the same database?
Any ideas/suggestions'> MSDE version of sql server loaded...Can I somehow point one machine to
the
> second machines SQL server such that we are working on the same database?
I don't think you can share databases between engines (even between
instances on the same machine).
Why not just point one machine's client tools to the other machine's SQL
Server? Then one machine will be working locally, and the other will be
working "remotely" so to speak. But they will be working on the same copy
of exactly one database.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||No.
First, SQL does not support mapped drives for database or transaction log
files.
Second, when SQL does open a database file, it is completely exclusive for
that server instance. Even a second instance on the same host nod could not
access the same underlying database files.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jim Heavey" <JHeavey@.nospam.com> wrote in message
news:Xns9495971974DBBJHeaveyBDUP@.207.46.248.16...
> Can I make my "default location" for new databases be a mapped drive?
When
> I go into the properties and select the database tab and select the ...
> box to list my drives, the shared drive does not show up. If I go ahead
> and enter the path of where I want the files to be located on the "mapped"
> drive, it accepts it, but does not use it when I create a new database.
> My situation is I have 2 computers in a single room and I want then to be
> pointed at the same file location for all databases... Both machines
have
> MSDE version of sql server loaded...Can I somehow point one machine to
the
> second machines SQL server such that we are working on the same database?
> Any ideas/suggestions'

Mapped Drive

Can I make my "default location" for new databases be a mapped drive? When
I go into the properties and select the database tab and select the ...
box to list my drives, the shared drive does not show up. If I go ahead
and enter the path of where I want the files to be located on the "mapped"
drive, it accepts it, but does not use it when I create a new database.
My situation is I have 2 computers in a single room and I want then to be
pointed at the same file location for all databases... Both machines have
MSDE version of sql server loaded...Can I somehow point one machine to the
second machines SQL server such that we are working on the same database?
Any ideas/suggestions'> MSDE version of sql server loaded...Can I somehow point one machine to
the
> second machines SQL server such that we are working on the same database?
I don't think you can share databases between engines (even between
instances on the same machine).
Why not just point one machine's client tools to the other machine's SQL
Server? Then one machine will be working locally, and the other will be
working "remotely" so to speak. But they will be working on the same copy
of exactly one database.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||No.
First, SQL does not support mapped drives for database or transaction log
files.
Second, when SQL does open a database file, it is completely exclusive for
that server instance. Even a second instance on the same host nod could not
access the same underlying database files.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jim Heavey" <JHeavey@.nospam.com> wrote in message
news:Xns9495971974DBBJHeaveyBDUP@.207.46.248.16...
> Can I make my "default location" for new databases be a mapped drive?
When
> I go into the properties and select the database tab and select the ...
> box to list my drives, the shared drive does not show up. If I go ahead
> and enter the path of where I want the files to be located on the "mapped"
> drive, it accepts it, but does not use it when I create a new database.
> My situation is I have 2 computers in a single room and I want then to be
> pointed at the same file location for all databases... Both machines
have
> MSDE version of sql server loaded...Can I somehow point one machine to
the
> second machines SQL server such that we are working on the same database?
> Any ideas/suggestions'

Many to one Select

I have 2 tables related as:

T1.KEY, T1.FIELD1, T1.FIELD2

T2.KEY, T2.FIELDA, T2.FIELDB
T2.KEY, T2.FIELDA, T2.FIELDB

T1.KEY = T2.KEY

I want to return a SELECT as:

T1.FIELD1, T1.FIELD2, T2.FIELDA, T2.FIELDB, T2.FIELDA, T2.FIELDA

The second table, in some cases but not all, has multiple rows for each
row in T1. I want to return a single row with all values for T2.FEILDA
and B.

--
jeffvh
----------------------
jeffvh's Profile: http://www.dbtalk.net/m47
View this thread: http://www.dbtalk.net/t293766jeffvh (jeffvh.251ikz@.no-mx.forums.yourdomain.com.au) writes:
> I have 2 tables related as:
> T1.KEY, T1.FIELD1, T1.FIELD2
> T2.KEY, T2.FIELDA, T2.FIELDB
> T2.KEY, T2.FIELDA, T2.FIELDB
> T1.KEY = T2.KEY
> I want to return a SELECT as:
> T1.FIELD1, T1.FIELD2, T2.FIELDA, T2.FIELDB, T2.FIELDA, T2.FIELDA
> The second table, in some cases but not all, has multiple rows for each
> row in T1. I want to return a single row with all values for T2.FEILDA
> and B.

So for T1.Key = 8 there are six rows in T2, there should be 14 columns,
two for T1 and seven for T2?

I'm afraid that is not easily doable.

The result of a query is alwys a table, and a table has a fixed number
of columns; it cannot be jagged.

It still possible to define a query that has maximum of columns needed,
but that number must be known in advance. You cannot write a query
which produces 16 columns on one execution, and 20 columns next time.

Furthermore, we need rules to say which row goes into which column.

So in the general case, this is very messy, and may be easier to sort
this out client-side.

However, if there are further conditions that you know, but didn't tell us,
it might be easier. The general recommendation is that you post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.
o A short narrative of the busines problem.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||this is called a "cross tab" report. very hard to do in sql.
do some research, and you can find some examples, but they all require
custom sql.

Many fields update from a Group by Clause

Hi All,

In Oracle, I can easily make this query :

UPDATE t1 SET (f1,f2)=(SELECT AVG(f3),SUM(f4)
FROM t2
WHERE t2.f5=t1.f6)
WHERE f5='Something'

I cannot seem to be able to do the same thing with MS-SQL. There are
only 2 ways I've figured out, and I fear performance cost in both cases,
which are these :
1)
UPDATE t1 SET f1=(SELECT AVG(f3)
FROM t2
WHERE t2.f5=t1.f6)
WHERE f5='Something'

and then the same statement but with f2, and

2)
UPDATE t1 SET f1=(SELECT AVG(f3)
FROM t2
WHERE t2.f5=t1.f6),
f2=(SELECT SUM(f4)
FROM t2
WHERE t2.f5=t1.f6)
WHERE f5='Something'

Is there a way with MS-SQL to do the Oracle equivalent in this case ?

Thanks,

MichelHi

You could try something like

UPDATE t
SET f1 = dt.avgcol, f2 = dt.sumcol
FROM t1 JOIN ( SELECT f5, AVG(f3) AS avgcol, SUM(f4) AS SumCol
FROM t2
WHERE f5 = 'Something'
GROUP BY f5 ) dt ON dt.f5=t1.f6

John
"Michel" <Michel@.askme.com> wrote in message
news:mGfeb.1763$r.377054@.news20.bellglobal.com...
> Hi All,
> In Oracle, I can easily make this query :
> UPDATE t1 SET (f1,f2)=(SELECT AVG(f3),SUM(f4)
> FROM t2
> WHERE t2.f5=t1.f6)
> WHERE f5='Something'
> I cannot seem to be able to do the same thing with MS-SQL. There are
> only 2 ways I've figured out, and I fear performance cost in both cases,
> which are these :
> 1)
> UPDATE t1 SET f1=(SELECT AVG(f3)
> FROM t2
> WHERE t2.f5=t1.f6)
> WHERE f5='Something'
> and then the same statement but with f2, and
> 2)
> UPDATE t1 SET f1=(SELECT AVG(f3)
> FROM t2
> WHERE t2.f5=t1.f6),
> f2=(SELECT SUM(f4)
> FROM t2
> WHERE t2.f5=t1.f6)
> WHERE f5='Something'
> Is there a way with MS-SQL to do the Oracle equivalent in this case ?
> Thanks,
> Michel|||It works fine, thank you very much. I did try something similar just before,
but I made the links in the where clause instead of the joins, and for some
reason it wasn't working.

Well, thanks, now it works fine and I'm saving 50% time on the queries!

Michel

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f798cee$0$8767$ed9e5944@.reading.news.pipex.n et...
> Hi
> You could try something like
> UPDATE t
> SET f1 = dt.avgcol, f2 = dt.sumcol
> FROM t1 JOIN ( SELECT f5, AVG(f3) AS avgcol, SUM(f4) AS SumCol
> FROM t2
> WHERE f5 = 'Something'
> GROUP BY f5 ) dt ON
dt.f5=t1.f6
> John
> "Michel" <Michel@.askme.com> wrote in message
> news:mGfeb.1763$r.377054@.news20.bellglobal.com...
> > Hi All,
> > In Oracle, I can easily make this query :
> > UPDATE t1 SET (f1,f2)=(SELECT AVG(f3),SUM(f4)
> > FROM t2
> > WHERE t2.f5=t1.f6)
> > WHERE f5='Something'
> > I cannot seem to be able to do the same thing with MS-SQL. There are
> > only 2 ways I've figured out, and I fear performance cost in both cases,
> > which are these :
> > 1)
> > UPDATE t1 SET f1=(SELECT AVG(f3)
> > FROM t2
> > WHERE t2.f5=t1.f6)
> > WHERE f5='Something'
> > and then the same statement but with f2, and
> > 2)
> > UPDATE t1 SET f1=(SELECT AVG(f3)
> > FROM t2
> > WHERE t2.f5=t1.f6),
> > f2=(SELECT SUM(f4)
> > FROM t2
> > WHERE t2.f5=t1.f6)
> > WHERE f5='Something'
> > Is there a way with MS-SQL to do the Oracle equivalent in this case ?
> > Thanks,
> > Michel|||Is that 50% over Oracle :)

"Michel" <Michel@.askme.com> wrote in message
news:0Bgeb.1931$r.386539@.news20.bellglobal.com...
> It works fine, thank you very much. I did try something similar just
before,
> but I made the links in the where clause instead of the joins, and for
some
> reason it wasn't working.
> Well, thanks, now it works fine and I'm saving 50% time on the queries!
> Michel
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3f798cee$0$8767$ed9e5944@.reading.news.pipex.n et...
> > Hi
> > You could try something like
> > UPDATE t
> > SET f1 = dt.avgcol, f2 = dt.sumcol
> > FROM t1 JOIN ( SELECT f5, AVG(f3) AS avgcol, SUM(f4) AS SumCol
> > FROM t2
> > WHERE f5 = 'Something'
> > GROUP BY f5 ) dt ON
> dt.f5=t1.f6
> > John
> > "Michel" <Michel@.askme.com> wrote in message
> > news:mGfeb.1763$r.377054@.news20.bellglobal.com...
> > > Hi All,
> > > > In Oracle, I can easily make this query :
> > > > UPDATE t1 SET (f1,f2)=(SELECT AVG(f3),SUM(f4)
> > > FROM t2
> > > WHERE t2.f5=t1.f6)
> > > WHERE f5='Something'
> > > > I cannot seem to be able to do the same thing with MS-SQL. There
are
> > > only 2 ways I've figured out, and I fear performance cost in both
cases,
> > > which are these :
> > > 1)
> > > UPDATE t1 SET f1=(SELECT AVG(f3)
> > > FROM t2
> > > WHERE t2.f5=t1.f6)
> > > WHERE f5='Something'
> > > > and then the same statement but with f2, and
> > > > 2)
> > > UPDATE t1 SET f1=(SELECT AVG(f3)
> > > FROM t2
> > > WHERE t2.f5=t1.f6),
> > > f2=(SELECT SUM(f4)
> > > FROM t2
> > > WHERE t2.f5=t1.f6)
> > > WHERE f5='Something'
> > > > Is there a way with MS-SQL to do the Oracle equivalent in this case ?
> > > > Thanks,
> > > > Michel
> >

manually update a table

i have 2 tables in an sql db. table1 has 2400 records and table2 has 1400 records. if i open the table and select all rows in table2 and try to manually edit the contents of a field i receive the following error:

communication link failure.

if i open the table and select top 1000 i can manually edit any value with out any problem.

if i open table1 and select all the rows i can edit any value i want with out any issues.

can it be that the size of the table prevents me from editing when i select all the rows?

extra info:
table1 has more records and more fields but the fields in table2 are larger (navchar 50 compared to navchar20)

Thank You,
ThomasDo you have a primary or unique key on your tables?|||Try to use the OLE DB provider for SQL Server instead an ODBC driver, and see whether you problem persists.|||Are you doing this in EM? If so, it is highly reccomended you do not edit data this way as it creates a big overhead with locks. Writing appropriate update statements would be better. (again, if you are doing it from EM)

HTH

manipulate field value from select statement

Hi all,

any assistance will be much appreciated on this one .... a bit clueless at the mo!

I've been trying to execute the code below in which part of my select statement is a calculated value i.e. Right([ED],2) & "/" & SUBSTRING([ED],5,2) & "/" & Left([ED],4) AS ENDDATE

code:

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) & "/" & SUBSTRING([SD],5,2) & "/" & Left([SD],4) AS STARTDATE,
Right([ED],2) & "/" & SUBSTRING([ED],5,2) & "/" & Left([ED],4) AS ENDDATE, vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), ENDDATE) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]

however this error message keeps coming up at runtime:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ENDDATE'.

My guess is it's happening when I try to get the date difference (DATEDIFF)....

help!!

Try this..

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) + '/' + SUBSTRING([SD],5,2) + '/' + Left([SD],4) AS STARTDATE,
Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4) AS ENDDATE,
vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), ENDDATE) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]

|||

Sh... should have seen that one.

Cheers mate .. however I'm still having an error from that code:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'vw_contract_spend'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'vw_contract_dates'.

Is there some sort of restriction on selecting from a view in sql server?

|||

Bolugbe wrote:

My guess is it's happening when I try to get the date difference (DATEDIFF)....

You're right, the problem is in the DATEDIFF statement.
You cannot use just assigned aliases in calculations, so you should either copy/paste the formula for getting ENDDATE into DATEDIFF function or use nested select statements|||

Try this one..

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) + '/' + SUBSTRING([SD],5,2) + '/' + Left([SD],4) AS STARTDATE,
Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4) AS ENDDATE,
vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), Convert(datetime,Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4))) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]

Manipulate Data

How can I manipulate the data in a column to get only the
numbers and leave the rest (Either on the select or remove
the text and leave the numbers in the column)?
Column with the data like:
346876 Error
432422 Warning
233556 Error
445332 Error
564445 Error
124345 Warning
995445 Info
Thanks for the help.
select * from bla where column1 like
'[0-9][0-9][0-9][0-9][0-9][0-9]%'
"Donna" <anonymous@.discussions.microsoft.com> wrote in message
news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
> How can I manipulate the data in a column to get only the
> numbers and leave the rest (Either on the select or remove
> the text and leave the numbers in the column)?
> Column with the data like:
> 346876 Error
> 432422 Warning
> 233556 Error
> 445332 Error
> 564445 Error
> 124345 Warning
> 995445 Info
> Thanks for the help.
|||If the data is always in this format, you can do:
SELECT SUBSTRING(column, 1, CHARINDEX(' ', column)) FROM table
You might consider storing the two data elements separate since, apparently,
they are idependently relevant...
http://www.aspfaq.com/
(Reverse address to reply.)
"Donna" <anonymous@.discussions.microsoft.com> wrote in message
news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
> How can I manipulate the data in a column to get only the
> numbers and leave the rest (Either on the select or remove
> the text and leave the numbers in the column)?
> Column with the data like:
> 346876 Error
> 432422 Warning
> 233556 Error
> 445332 Error
> 564445 Error
> 124345 Warning
> 995445 Info
> Thanks for the help.
|||Thanks Chris.........but the number of digits can vary
(4,5,6,7,8,9,10)

>--Original Message--
>select * from bla where column1 like
>'[0-9][0-9][0-9][0-9][0-9][0-9]%'
>
>"Donna" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
the[vbcol=seagreen]
remove
>
>.
>
|||Thanks Aaron......That is what I wanted...

>--Original Message--
>If the data is always in this format, you can do:
>SELECT SUBSTRING(column, 1, CHARINDEX(' ', column)) FROM
table
>You might consider storing the two data elements separate
since, apparently,
>they are idependently relevant...
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Donna" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
the[vbcol=seagreen]
remove
>
>.
>

Manipulate Data

How can I manipulate the data in a column to get only the
numbers and leave the rest (Either on the select or remove
the text and leave the numbers in the column)?
Column with the data like:
346876 Error
432422 Warning
233556 Error
445332 Error
564445 Error
124345 Warning
995445 Info
Thanks for the help.select * from bla where column1 like
'[0-9][0-9][0-9][0-9][0-9][0-9]%'
"Donna" <anonymous@.discussions.microsoft.com> wrote in message
news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
> How can I manipulate the data in a column to get only the
> numbers and leave the rest (Either on the select or remove
> the text and leave the numbers in the column)?
> Column with the data like:
> 346876 Error
> 432422 Warning
> 233556 Error
> 445332 Error
> 564445 Error
> 124345 Warning
> 995445 Info
> Thanks for the help.|||If the data is always in this format, you can do:
SELECT SUBSTRING(column, 1, CHARINDEX(' ', column)) FROM table
You might consider storing the two data elements separate since, apparently,
they are idependently relevant...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Donna" <anonymous@.discussions.microsoft.com> wrote in message
news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
> How can I manipulate the data in a column to get only the
> numbers and leave the rest (Either on the select or remove
> the text and leave the numbers in the column)?
> Column with the data like:
> 346876 Error
> 432422 Warning
> 233556 Error
> 445332 Error
> 564445 Error
> 124345 Warning
> 995445 Info
> Thanks for the help.|||Thanks Chris.........but the number of digits can vary
(4,5,6,7,8,9,10)
>--Original Message--
>select * from bla where column1 like
>'[0-9][0-9][0-9][0-9][0-9][0-9]%'
>
>"Donna" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
>> How can I manipulate the data in a column to get only
the
>> numbers and leave the rest (Either on the select or
remove
>> the text and leave the numbers in the column)?
>> Column with the data like:
>> 346876 Error
>> 432422 Warning
>> 233556 Error
>> 445332 Error
>> 564445 Error
>> 124345 Warning
>> 995445 Info
>> Thanks for the help.
>
>.
>|||Thanks Aaron......That is what I wanted...
>--Original Message--
>If the data is always in this format, you can do:
>SELECT SUBSTRING(column, 1, CHARINDEX(' ', column)) FROM
table
>You might consider storing the two data elements separate
since, apparently,
>they are idependently relevant...
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Donna" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0f2801c4e3a3$94936ac0$a601280a@.phx.gbl...
>> How can I manipulate the data in a column to get only
the
>> numbers and leave the rest (Either on the select or
remove
>> the text and leave the numbers in the column)?
>> Column with the data like:
>> 346876 Error
>> 432422 Warning
>> 233556 Error
>> 445332 Error
>> 564445 Error
>> 124345 Warning
>> 995445 Info
>> Thanks for the help.
>
>.
>

Management Studio: "select *",column names automatically filled in?

I am new to SQL 2005 and can't figgure this out. If I open a table, the
results are displayed. If I then modify the query statement (leaving
select *) and then click on execute, all of the column names are filled
in. Quite annoing. Is there any way to turn this "feature" off and just
leave the "*"?
It would also be nice to have the color coded query designer with an
editable result set, is there any way to do this?
Thanks!Most people don't want the particular feature you request as such
unqualified column fetches do not offer the best performance.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"emde" <emdeusenet@.yahoo.com> wrote in message
news:1160774919.157908.248050@.h48g2000cwc.googlegroups.com...
>I am new to SQL 2005 and can't figgure this out. If I open a table, the
> results are displayed. If I then modify the query statement (leaving
> select *) and then click on execute, all of the column names are filled
> in. Quite annoing. Is there any way to turn this "feature" off and just
> leave the "*"?
> It would also be nice to have the color coded query designer with an
> editable result set, is there any way to do this?
> Thanks!
>|||Since you are new to SQL, please accept our encouragement to NOT use 'SELECT
*'.
It becomes a crutch because it seems so 'easy', yet over time, it can cause
problems. Too much unnecessary data retrieved (and transmitted), potential
for broken applications when there is a business need to add additional
columns to the table and the application doesn't expect them, etc.
It is a 'Best Practice' to only SELECT the specific columns needed for a
particular purpose.
Welcome to SQL Server, and good luck.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"emde" <emdeusenet@.yahoo.com> wrote in message
news:1160774919.157908.248050@.h48g2000cwc.googlegroups.com...
>I am new to SQL 2005 and can't figgure this out. If I open a table, the
> results are displayed. If I then modify the query statement (leaving
> select *) and then click on execute, all of the column names are filled
> in. Quite annoing. Is there any way to turn this "feature" off and just
> leave the "*"?
> It would also be nice to have the color coded query designer with an
> editable result set, is there any way to do this?
> Thanks!
>|||emde wrote:
> I am new to SQL 2005 and can't figgure this out. If I open a table, the
> results are displayed. If I then modify the query statement (leaving
> select *) and then click on execute, all of the column names are filled
> in. Quite annoing. Is there any way to turn this "feature" off and just
> leave the "*"?
> It would also be nice to have the color coded query designer with an
> editable result set, is there any way to do this?
> Thanks!
For reasons of performance and maintainability you should always avoid
using SELECT * in any production-quality code. If you need to do this
for ad-hoc / non-production use you can just save the script as a
query. Click New Query and then save the file.
The fastest way to learn is to ignore the "designer" and "open table"
features and just type your queries directly. The query designer has an
annoying habit of rewriting your queries for you. It also has a lot of
limitations.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks everyone. I am actually a long time SQL 2000 dba who is finally
making the switch to 2005. I only use select * when trying to debug
apps and make changes to data, etc. Thanks for the insight. I am sure I
will have many questions down the road and it looks like this is a
great group to hang out in.
Take care.

Management Studio: "select *",column names automatically filled in?

I am new to SQL 2005 and can't figgure this out. If I open a table, the
results are displayed. If I then modify the query statement (leaving
select *) and then click on execute, all of the column names are filled
in. Quite annoing. Is there any way to turn this "feature" off and just
leave the "*"?
It would also be nice to have the color coded query designer with an
editable result set, is there any way to do this?
Thanks!Most people don't want the particular feature you request as such
unqualified column fetches do not offer the best performance.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"emde" <emdeusenet@.yahoo.com> wrote in message
news:1160774919.157908.248050@.h48g2000cwc.googlegroups.com...
>I am new to SQL 2005 and can't figgure this out. If I open a table, the
> results are displayed. If I then modify the query statement (leaving
> select *) and then click on execute, all of the column names are filled
> in. Quite annoing. Is there any way to turn this "feature" off and just
> leave the "*"?
> It would also be nice to have the color coded query designer with an
> editable result set, is there any way to do this?
> Thanks!
>|||Since you are new to SQL, please accept our encouragement to NOT use 'SELECT
*'.
It becomes a crutch because it seems so 'easy', yet over time, it can cause
problems. Too much unnecessary data retrieved (and transmitted), potential
for broken applications when there is a business need to add additional
columns to the table and the application doesn't expect them, etc.
It is a 'Best Practice' to only SELECT the specific columns needed for a
particular purpose.
Welcome to SQL Server, and good luck.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"emde" <emdeusenet@.yahoo.com> wrote in message
news:1160774919.157908.248050@.h48g2000cwc.googlegroups.com...
>I am new to SQL 2005 and can't figgure this out. If I open a table, the
> results are displayed. If I then modify the query statement (leaving
> select *) and then click on execute, all of the column names are filled
> in. Quite annoing. Is there any way to turn this "feature" off and just
> leave the "*"?
> It would also be nice to have the color coded query designer with an
> editable result set, is there any way to do this?
> Thanks!
>|||emde wrote:
> I am new to SQL 2005 and can't figgure this out. If I open a table, the
> results are displayed. If I then modify the query statement (leaving
> select *) and then click on execute, all of the column names are filled
> in. Quite annoing. Is there any way to turn this "feature" off and just
> leave the "*"?
> It would also be nice to have the color coded query designer with an
> editable result set, is there any way to do this?
> Thanks!
For reasons of performance and maintainability you should always avoid
using SELECT * in any production-quality code. If you need to do this
for ad-hoc / non-production use you can just save the script as a
query. Click New Query and then save the file.
The fastest way to learn is to ignore the "designer" and "open table"
features and just type your queries directly. The query designer has an
annoying habit of rewriting your queries for you. It also has a lot of
limitations.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks everyone. I am actually a long time SQL 2000 dba who is finally
making the switch to 2005. I only use select * when trying to debug
apps and make changes to data, etc. Thanks for the insight. I am sure I
will have many questions down the road and it looks like this is a
great group to hang out in.
Take care.

Management Studio will not allow me to create a view

Hi there,
When I run the following query I get the correct result.
select * from Inventory As I Full Outer Join Publisher As P on
I.ID=P.InventoryID
However, when I try to create a view with the same select statement I get
the following error:
Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
Column names in each view or function must be unique. Column name 'ID' in
view or function 'InventoyPublisherView' is specified more than once.
The CREATE VIEW statement I'm using is:
CREATE VIEW InventoyPublisherView AS
(SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
I.ID=P.InventoryID)
Many thanks in advance for the help. Very much appreciatedName the columns in the SELECT statement. Both tables has a column named ID,
and the view cannot
have two columns with the same name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> Hi there,
> When I run the following query I get the correct result.
> select * from Inventory As I Full Outer Join Publisher As P on
> I.ID=P.InventoryID
> However, when I try to create a view with the same select statement I get
> the following error:
> Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
> Column names in each view or function must be unique. Column name 'ID' in
> view or function 'InventoyPublisherView' is specified more than once.
> The CREATE VIEW statement I'm using is:
> CREATE VIEW InventoyPublisherView AS
> (SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
> I.ID=P.InventoryID)
> Many thanks in advance for the help. Very much appreciated
>|||"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> Hi there,
> When I run the following query I get the correct result.
> select * from Inventory As I Full Outer Join Publisher As P on
> I.ID=P.InventoryID
> However, when I try to create a view with the same select statement I get
> the following error:
> Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
> Column names in each view or function must be unique. Column name 'ID' in
> view or function 'InventoyPublisherView' is specified more than once.
> The CREATE VIEW statement I'm using is:
> CREATE VIEW InventoyPublisherView AS
> (SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
> I.ID=P.InventoryID)
> Many thanks in advance for the help. Very much appreciated
It's because you have a column named ID in both tables.
If you didn't use Select * (and you should not) you wouldn't have the
problem. Name the columns.
Besides, why would you select I.ID and P.InventoryID in the query since they
have the same value.|||chris,
use column names in the select list instead of the asterisk:
select i.id, i.col2, i.col3, p.col1, p.col2, etc..
from Inventory As I Full Outer Join Publisher As P on I.ID=P.InventoryID
dean
"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> Hi there,
> When I run the following query I get the correct result.
> select * from Inventory As I Full Outer Join Publisher As P on
> I.ID=P.InventoryID
> However, when I try to create a view with the same select statement I get
> the following error:
> Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
> Column names in each view or function must be unique. Column name 'ID' in
> view or function 'InventoyPublisherView' is specified more than once.
> The CREATE VIEW statement I'm using is:
> CREATE VIEW InventoyPublisherView AS
> (SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
> I.ID=P.InventoryID)
> Many thanks in advance for the help. Very much appreciated
>|||Thank you very much, Tibor. I am following exercises from a Wrox book. I
think I will have to shoot the author for writing incorrect code in his
examples.
"Tibor Karaszi" wrote:

> Name the columns in the SELECT statement. Both tables has a column named I
D, and the view cannot
> have two columns with the same name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
> news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
>|||Thank you very much Raymond. The speed of all of your replies (from all of
you guys) is very reassuring for a total beginner like myself. Fantastic job
.
thanks
"Raymond D'Anjou" wrote:

> "Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
> news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> It's because you have a column named ID in both tables.
> If you didn't use Select * (and you should not) you wouldn't have the
> problem. Name the columns.
> Besides, why would you select I.ID and P.InventoryID in the query since th
ey
> have the same value.
>
>|||Thanks Dean. Much appreciated. Like I mentioned in the post above the author
of the book I′m following put incorrect code in his examples. Luckily, ther
e
are great people out there to come to the resuce. Cheers
"Dean" wrote:

> chris,
> use column names in the select list instead of the asterisk:
> select i.id, i.col2, i.col3, p.col1, p.col2, etc..
> from Inventory As I Full Outer Join Publisher As P on I.ID=P.InventoryID
> dean
> "Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
> news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
>
>|||"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:00CEBECA-7183-436E-BB8A-CA36F3388B23@.microsoft.com...
> Thank you very much, Tibor. I am following exercises from a Wrox book. I
> think I will have to shoot the author for writing incorrect code in his
> examples.
This must be why WROX went bankrupt.
All their authors were shot. :-)

Management Studio strange behaviour?

Under Management Studio, when I right-click a stored proc, select Modify, change the stored proc, then select Execute, the stored proc is updated on the server. But then I noticed that the sql tab holding the changed stored proc (in the right pane of Management Studio) still diplays and asterisk (*). When I right-clicked the tab it offeres the Save option but that is to save the sql file (with the * in its tab) to a file. This is confusing behaviour.

Is there any way to change this behaviour so running Execute causes the (*) to disapeear?

TIA,

Barkingdog

The asterisk means the script file you are modifying is not saved to disk. Executing the script/file does not save the file and that is why the asterisk does not disappear.

You have to consider the actual procedure in the database as not being the script/file in management studio that creates/updates it.

Execute does not save the file as it only communicates with the connected server. Save does not update the connected server as it only saves the script to disk.