Another way than using a Cursor

Hi all,
I have the following example Table & Data...
Create Table Test
(
Cust_Code varchar(10),
Acc_Deal_Type Varchar(3),
Account_No Varchar(30),
AccountTypes Varchar(50)
)
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123456','MMC','12345678')
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123456','MMC','12345679')
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123456','CCL','12345680')
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123457','MMC','12345681')
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123457','MMC','12345682')
I need to concatanate the distinct values of Acc_Deal_Types Column for
each Cust_Code and set the AccountTypes column with the Result.
So, in this example, the grouped results would be two rows with the
Cust_Code & AccountTypes Column selected:
1st Row
Cust_Code = 123456
AccountType = MMC,CCL
2nd Row
Cust_Code = 123457
AccountType = MMC
How can I create the AccountTypes column without using a cursor? The
Table in question has around 7000 rows.
Thanks
Barrytry
select distinct Acc_Deal_Type + Cust_Code as NewType into Test# from Test
update Test
set Acc_Deal_Type = NewType
-- select Cust_Code, Acc_Deal_Type, Account_No, AccountTypes , NewType
from Test a, Test# b
where NewType = Acc_Deal_Type + Cust_Code
increase the zize of the Acc_Deal_Type though|||Hi, Barry
See: http://www.aspfaq.com/show.asp?id=2529
Razvan

Another Slow Execution Plan with sp_prepare

Thanks to Erland, my issue with sp_prepare of NVarchar and Varchar has been
resolved by changing a parameter in JDBC to force it to use Varchar instead
of NVarchar. JDBC's default setting uses NVarchar. Look under Subject "Slo
w
Query with sp_prepare and sp_execute" to see the thread.
However, I now have a problem with the INT datatype in my prepare
statements. I have tried using various numeric data types with sp_prepare.
But none gives me a response time as fast as running a single query.
For example, I have the following table:
CREATE TABLE Segment (
SegmentId int NOT NULL,
SegmentSetId int NOT NULL,
Sequence varchar (255) NOT NULL,
CONSTRAINT PK_Segment PRIMARY KEY CLUSTERED (SegmentId)
With the below index:
CREATE INDEX IX_Segment ON Segment(SegmentSetId)
The table has 6 million rows. I am using prepare statments in a JDBC
environment. When the below prepare statement is used, the execution plan
uses index scan which generates a slow response time:
Declare @.P1 int
Exec sp_prepare @.P1 output,
N'@.P1 int',
N'select * from Segment where SegmentSetId=@.P1'
Select @.P1
Exec sp_execute @.P1,649
Execution Tree
--
Parallelism(Gather Streams)
|--Clustered Index Scan(OBJECT:([Dev].[dbo].[Segment].[PK_Segment]),
WHERE:([Segment].[SegmentSetId]=[@.P1]))
Now, if I just run the query with all the parameters already provided, the
execution plan
uses index s which generates a fast response time:
select * from Segment where SegmentSetId=649
Execution Tree
--
Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Dev].[dbo].[Segment]))
|--Index S(OBJECT:([Dev].[dbo].[Segment].[IX_Segment]),
SEEK:([Segment].[SegmentSetId]=Convert([@.1])) ORDERED FORWARD)
Can anyone give me a hint how to resolve this one? What can I use in my
prepared statement to force the optimizer to use an index s instead of an
index scan?
Again, any help will be greatly appreciated!Hi
If you add to this statement (AND 1=1) do you see an index s?
N'select * from Segment where SegmentSetId=@.P1 and 1=1'
"lorinda" <lorinda@.community.nospam> wrote in message
news:121EF92B-554B-4D96-AFCA-C6E6F0A5D757@.microsoft.com...
> Thanks to Erland, my issue with sp_prepare of NVarchar and Varchar has
> been
> resolved by changing a parameter in JDBC to force it to use Varchar
> instead
> of NVarchar. JDBC's default setting uses NVarchar. Look under Subject
> "Slow
> Query with sp_prepare and sp_execute" to see the thread.
> However, I now have a problem with the INT datatype in my prepare
> statements. I have tried using various numeric data types with
> sp_prepare.
> But none gives me a response time as fast as running a single query.
> For example, I have the following table:
> CREATE TABLE Segment (
> SegmentId int NOT NULL,
> SegmentSetId int NOT NULL,
> Sequence varchar (255) NOT NULL,
> CONSTRAINT PK_Segment PRIMARY KEY CLUSTERED (SegmentId)
> With the below index:
> CREATE INDEX IX_Segment ON Segment(SegmentSetId)
> The table has 6 million rows. I am using prepare statments in a JDBC
> environment. When the below prepare statement is used, the execution plan
> uses index scan which generates a slow response time:
> Declare @.P1 int
> Exec sp_prepare @.P1 output,
> N'@.P1 int',
> N'select * from Segment where SegmentSetId=@.P1'
> Select @.P1
> Exec sp_execute @.P1,649
> Execution Tree
> --
> Parallelism(Gather Streams)
> |--Clustered Index Scan(OBJECT:([Dev].[dbo].[Segment].[PK_Segment]),
> WHERE:([Segment].[SegmentSetId]=[@.P1]))
> Now, if I just run the query with all the parameters already provided, the
> execution plan
> uses index s which generates a fast response time:
> select * from Segment where SegmentSetId=649
> Execution Tree
> --
> Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Dev].[dbo].[Segment]))
> |--Index S(OBJECT:([Dev].[dbo].[Segment].[IX_Segment]),
> SEEK:([Segment].[SegmentSetId]=Convert([@.1])) ORDERED FORWARD)
> Can anyone give me a hint how to resolve this one? What can I use in my
> prepared statement to force the optimizer to use an index s instead of
> an
> index scan?
> Again, any help will be greatly appreciated!
>|||lorinda (lorinda@.community.nospam) writes:
> However, I now have a problem with the INT datatype in my prepare
> statements. I have tried using various numeric data types with
> sp_prepare. But none gives me a response time as fast as running a
> single query.
> For example, I have the following table:
> CREATE TABLE Segment (
> SegmentId int NOT NULL,
> SegmentSetId int NOT NULL,
> Sequence varchar (255) NOT NULL,
> CONSTRAINT PK_Segment PRIMARY KEY CLUSTERED (SegmentId)
> With the below index:
> CREATE INDEX IX_Segment ON Segment(SegmentSetId)
> The table has 6 million rows. I am using prepare statments in a JDBC
> environment. When the below prepare statement is used, the execution plan
> uses index scan which generates a slow response time:
>...
> Now, if I just run the query with all the parameters already provided, the
> execution plan
> uses index s which generates a fast response time:
> select * from Segment where SegmentSetId=649
So this is a completely different issue from the varchar thing.
This is the classical choice between using a non-clustered index or
scanning the table. If there are two rows with SegmentSetID = 649 the
index is alright. But if there is a million, it's faster to find them
by scanning the table, because using the index would require many
page accesses to the same page.
SQL Server has statistics from which it makes estimates. At least for
stored procedure it sniffs the input parameter value when it builds
the plan and puts in cache. I would assume that this is the case
with sp_prepare as well. So I would expect it to use the index. Then
again, if you first attempt was with SegmentSetID = 0, and there are
a million rows with id 0, then what ended up in the cache was the
clustered index scan.
You can force the index with an index hint:
SELECT * FROM Segment WITH (INDEX = IX_Segment) WHERE ...
but this can come back bite when you are asking for that SegmentSetID
with lots of rows.
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 makes sense. Looks like I will have to find a work around. Thanks
again for your help!

Another Question SP

Hi,
I have this I need to be able to update the information based on custid.
DECLARE @.sql1 Varchar(8000)
DECLARE @.custid varchar (10)
DECLARE @.custname varchar (80)
DECLARE @.doc varchar(8000)
SET @.custid='000900'
SET @.sql1 = @.sql1 + 'Select c.custname From dbo.tblArCust c where c.custid='
+ @.custid Is this wrong?
SET @.custname = @.sql1
SET @.doc ='
<STORE>
<ADDRESS>
<CUST_ATTN>Mrssuchikanuganti</CUST_ATTN>
<CUST_NAME>' + @.custname + '</CUST_NAME> I am trying to insert a variable
here based on the custid,
<ADDR_1>@.address1</ADDR_1>
<ADDR_2>@.address2</ADDR_2>
<ADDR_CITY>@.city</ADDR_CITY>
<ADDR_STATE>@.region</ADDR_STATE>
<ADDR_POSTAL>@.zipcode</ADDR_POSTAL>
</ADDRESS>
When I run this I am getting NULL what is wrong with this?
Thanks
DibTry,
Select @.custname = c.custname From dbo.tblArCust c where c.custid = @.custid
...
AMB
"Dib" wrote:

> Hi,
> I have this I need to be able to update the information based on custid.
> DECLARE @.sql1 Varchar(8000)
> DECLARE @.custid varchar (10)
> DECLARE @.custname varchar (80)
> DECLARE @.doc varchar(8000)
> SET @.custid='000900'
> SET @.sql1 = @.sql1 + 'Select c.custname From dbo.tblArCust c where c.custid
='
> + @.custid Is this wrong?
> SET @.custname = @.sql1
> SET @.doc ='
> <STORE>
> <ADDRESS>
> <CUST_ATTN>Mrssuchikanuganti</CUST_ATTN>
> <CUST_NAME>' + @.custname + '</CUST_NAME> I am trying to insert a variab
le
> here based on the custid,
> <ADDR_1>@.address1</ADDR_1>
> <ADDR_2>@.address2</ADDR_2>
> <ADDR_CITY>@.city</ADDR_CITY>
> <ADDR_STATE>@.region</ADDR_STATE>
> <ADDR_POSTAL>@.zipcode</ADDR_POSTAL>
> </ADDRESS>
> When I run this I am getting NULL what is wrong with this?
> Thanks
> Dib
>
>|||Thanks, it worked. How can I handle NULL value here
Dib
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:CF2BF93E-091C-494D-A625-19D36E041E9C@.microsoft.com...
> Try,
>
> Select @.custname = c.custname From dbo.tblArCust c where c.custid =
@.custid
> ...
>
> AMB
> "Dib" wrote:
>
c.custid='
variable|||Doesn't look like you have initialised @.sql1. Set it to a blank space and
try again.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dib" <dNOSPAMshahene@.conNOSPAMsoftware.com> wrote in message
news:uZZHW4VjFHA.1196@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have this I need to be able to update the information based on custid.
> DECLARE @.sql1 Varchar(8000)
> DECLARE @.custid varchar (10)
> DECLARE @.custname varchar (80)
> DECLARE @.doc varchar(8000)
> SET @.custid='000900'
> SET @.sql1 = @.sql1 + 'Select c.custname From dbo.tblArCust c where
> c.custid='
> + @.custid Is this wrong?
> SET @.custname = @.sql1
> SET @.doc ='
> <STORE>
> <ADDRESS>
> <CUST_ATTN>Mrssuchikanuganti</CUST_ATTN>
> <CUST_NAME>' + @.custname + '</CUST_NAME> I am trying to insert a
> variable
> here based on the custid,
> <ADDR_1>@.address1</ADDR_1>
> <ADDR_2>@.address2</ADDR_2>
> <ADDR_CITY>@.city</ADDR_CITY>
> <ADDR_STATE>@.region</ADDR_STATE>
> <ADDR_POSTAL>@.zipcode</ADDR_POSTAL>
> </ADDRESS>
> When I run this I am getting NULL what is wrong with this?
> Thanks
> Dib
>|||This is not working
This is what I did
SET @.custid = ''
SET @.custname = ''
SET @.address1 = ''
SET @.address2 = ''
SET @.city = ''
SET @.region = ''
SET @.zipcode = ''
SET @.doc =''
SET @.custid='000900'
SET @.custname = (Select case WHEN tblArCust.custname = NULL Then ''End From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.address1 = (Select case WHEN tblArCust.Addr1 = NULL Then '' End From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.address2 = (Select case WHEN tblArCust.Addr2 = NULL Then ''END From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.city = (Select case WHEN tblArCust.City = NULL Then '' End From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.region = (Select case WHEN tblArCust.Region = NULL Then '' End From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.zipcode = (Select case WHEN tblArCust.PostalCode = NULL Then'' End From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.doc ='
It worked fine at first when I changed the Custid to 00500 it returned NULL
now nothing works.
Thanks
Dib
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uLcdeFWjFHA.708@.TK2MSFTNGP09.phx.gbl...
> Doesn't look like you have initialised @.sql1. Set it to a blank space and
> try again.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dib" <dNOSPAMshahene@.conNOSPAMsoftware.com> wrote in message
> news:uZZHW4VjFHA.1196@.TK2MSFTNGP10.phx.gbl...
>|||On Wed, 20 Jul 2005 15:51:26 -0400, Dib wrote:

>This is not working
>This is what I did
>SET @.custid = ''
>SET @.custname = ''
>SET @.address1 = ''
>SET @.address2 = ''
>SET @.city = ''
>SET @.region = ''
>SET @.zipcode = ''
>SET @.doc =''
>
>SET @.custid='000900'
>SET @.custname = (Select case WHEN tblArCust.custname = NULL Then ''End From
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.address1 = (Select case WHEN tblArCust.Addr1 = NULL Then '' End From
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.address2 = (Select case WHEN tblArCust.Addr2 = NULL Then ''END From
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.city = (Select case WHEN tblArCust.City = NULL Then '' End From
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.region = (Select case WHEN tblArCust.Region = NULL Then '' End From
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.zipcode = (Select case WHEN tblArCust.PostalCode = NULL Then'' End Fro
m
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.doc ='
>
>It worked fine at first when I changed the Custid to 00500 it returned NULL
>now nothing works.
>Thanks
>Dib
Hi Dib,
There are several problems with your code.
1. comparing to NULL with the = operator will allways fail - no value is
equal to NULL, not even NULL itself. (This is by design - NULL is
regarded as unknown values, so comparing NULL to NULL is like comparing
two unknown values; you can never say if two unknown values are equal or
unequal). Use IS NULL instead of = NULL for correct results.
2. The CASE statement lacks an ELSE part. If the WHEN part is not true
(which it never is, due to the reason above), and there is no ELSE part,
the result will default to NULL.
3. It is highly inefficient to execute the same query six consecutive
times. Instead, use the Transact-SQL extension to the SELECT statement
to assign values to all variables at once.
Here's some code to replace the snippet above. I also replaced the CASE
expressions with COALESCE expressions, that are (in this case)
equivalent. Oh, and since I use SELECT instead of SET, there's no need
to initialize the variables first - they'll always be assigned a value
in this statement!
SET @.custid = '000900'
SELECT @.custname = COALESCE(custname, ''),
@.address1 = COALESEC(Addr1, ''),
@.address2 = COALESCE(Addr2, ''),
@.city = COALESCE(City, ''),
@.region = COALESCE(Region, ''),
@.zipcode = COALESCE(PostalCode, '')
FROM dbo.tblArCust
WHERE custid = @.custid
(untested, since you didn't provide CREATE TABLE and INSERT statements)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

another question

Thanks for the note Vishal,
What if each had a date. So that
create table #cartype(manufacturer varchar(500), itemnumber int, datemade date)
insert into #cartype values('Toyota',1, 4/6/2004)
insert into #cartype values('Toyota',1, 4/6/2004)
insert into #cartype values('Honda',2, 4/6/2004)
insert into #cartype values('Honda',2, 4/6/2004)
insert into #cartype values('Toyota',1, 4/7/2004)
insert into #cartype values('Honda',3, 4/7/2004)
insert into #cartype values('GE',3, 4/7/2004)
insert into #cartype values('GE',3, 4/7/2004)
So that
insert into #cartype values('Toyota',1, 4/6/2004)
insert into #cartype values('Honda',2, 4/6/2004)
Would get deleted because there the exact same records (same number) of records are duplicated for that date.
But the records:
insert into #cartype values('Toyota',1, 4/7/2004)
insert into #cartype values('Honda',3, 4/7/2004)
insert into #cartype values('GE',3, 4/7/2004)
insert into #cartype values('GE',3, 4/7/2004)
The GE records would stay because all of the records are not duplicated, just the GE records are so I want to keep all the records.
Thanks for any ideas!
Try query as follows:
delete a
from #cartype a join
(select manufacturer, datemade, count(*) cnt
from #cartype
group by manufacturer, datemade
having count(*) > 1) b on a.manufacturer = b.manufacturer and
a.datemade = b.datemade and b.cnt <>
(select count(*)
from #cartype x
group by manufacturer
having x.manufacturer = b.manufacturer)
Vishal Parkar
vgparkar@.yahoo.co.in
|||Here is what I have done to make it fit my query. Here are my records:
store, deliverydate, itemnumber, qty
006SS,04/15/2004,070100,018
006SS,04/15/2004,090096,018
006SS,04/15/2004,070100,018
006SS,04/15/2004,090096,018
(this should get deleted, exact same as 2 lines above)
007SS,04/15/2004,030498,020
007SS,04/15/2004,030498,020
007SS,04/15/2004,030498,020
007SS,04/15/2004,090495,020
007SS,04/15/2004,090495,020
(all lines should stay because it is not exact same.)
selext a.*, cnt
from tblItemOrder a join
(select itemnumber, quantity, store, deliverydate, count(*) cnt
from tblItemOrder
group by itemnumber, quantity, store, deliverydate
having count(*) > 1) b on a.itemnumber = b.itemnumber and
a.quantity = b.quantity and a.store = b.store and a.deliverydate = b.deliverydate and b.cnt <>
(select count(*)
from tblItemOrder x
group by store, deliverydate
having x.store = b.store and x.deliverydate = b.deliverydate)
I get all records that have duplicate lines, not just the ones with same count of duplicate records (storeno, deliverydate).
Any ideas? I have thought about it many different ways and have not come up with a solution yet. Thanks again,
|||hi ashley,
Remember SELECT and DELETE are different statements. DELETE will delete
the data from the table while with the help of SELECT statement you can
filterout the rows from the table.
See following example:
create table tt
(store varchar(50),
deliverydate datetime,
itemnumber varchar(50),
qty int)
--insert some data
insert into tt
select '006SS','04/15/2004','070100','018' union all
select '006SS','04/15/2004','090096','018' union all
select '006SS','04/15/2004','070100','018' union all
select '006SS','04/15/2004','090096','018' union all
select '007SS','04/15/2004','030498','020' union all
select '007SS','04/15/2004','030498','020' union all
select '007SS','04/15/2004','030498','020' union all
select '007SS','04/15/2004','090495','020' union all
select '007SS','04/15/2004','090495','020'
--Try this query:
select a.*
from tt a join
(select store, deliverydate, itemnumber,count(*) cnt
from tt
group by store, deliverydate, itemnumber
having count(*) > 1) b on a.itemnumber = b.itemnumber and
a.store = b.store and a.deliverydate = b.deliverydate and 1 not in
(select 1
from tt x
group by store, deliverydate, itemnumber
having x.store = b.store and x.deliverydate = b.deliverydate and
x.itemnumber <> b.itemnumber and count(*) = b.cnt)
Vishal Parkar
vgparkar@.yahoo.co.in

Another query help question

Similar to my other question, but the counting is different. Thank you
again for your help.
CREATE TABLE [dbo].[Tracking] (
[key_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[value_] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )
insert into tracking VALUES ('EVENT','verbal aggression')
insert into tracking VALUES ('EVENT','peer')
insert into tracking VALUES ('EVENT','bad behavior')
insert into tracking VALUES ('EVENT','other')
insert into tracking VALUES ('PRELIM','Loud noise')
insert into tracking VALUES ('PRELIM','agitation')
insert into tracking VALUES ('PRELIM','schedule change')
insert into tracking VALUES ('PRELIM','meal time')
CREATE TABLE [dbo].[Tracking_DATA] (
[ID_] [int] IDENTITY (1, 1) NOT NULL ,
[bts_ID_] [int] NULL ,
[key_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[value_] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
insert into tracking_DATA VALUES (1, 'EVENT', 'other')
insert into tracking_DATA VALUES (1, 'EVENT', 'bad behavior')
insert into tracking_DATA VALUES (1, 'PRELIM', 'Loud noise')
insert into tracking_DATA VALUES (1, 'PRELIM', 'agitation')
insert into tracking_DATA VALUES (2, 'EVENT', 'other')
insert into tracking_DATA VALUES (2, 'EVENT', 'verbal aggression')
insert into tracking_DATA VALUES (2, 'PRELIM', 'Loud noise')
event prelim count
other loud noise 2
other agitation 1
bad behavior loud noise 1
bad behavior agitation 1
verbal aggression loud noise 1
peer <BLANK> 0Hi
Can you say how the information is grouped to get these values?
John
"Jack" wrote:

> Similar to my other question, but the counting is different. Thank you
> again for your help.
> CREATE TABLE [dbo].[Tracking] (
> [key_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [value_] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )
> insert into tracking VALUES ('EVENT','verbal aggression')
> insert into tracking VALUES ('EVENT','peer')
> insert into tracking VALUES ('EVENT','bad behavior')
> insert into tracking VALUES ('EVENT','other')
> insert into tracking VALUES ('PRELIM','Loud noise')
> insert into tracking VALUES ('PRELIM','agitation')
> insert into tracking VALUES ('PRELIM','schedule change')
> insert into tracking VALUES ('PRELIM','meal time')
>
> CREATE TABLE [dbo].[Tracking_DATA] (
> [ID_] [int] IDENTITY (1, 1) NOT NULL ,
> [bts_ID_] [int] NULL ,
> [key_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [value_] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> insert into tracking_DATA VALUES (1, 'EVENT', 'other')
> insert into tracking_DATA VALUES (1, 'EVENT', 'bad behavior')
> insert into tracking_DATA VALUES (1, 'PRELIM', 'Loud noise')
> insert into tracking_DATA VALUES (1, 'PRELIM', 'agitation')
> insert into tracking_DATA VALUES (2, 'EVENT', 'other')
> insert into tracking_DATA VALUES (2, 'EVENT', 'verbal aggression')
> insert into tracking_DATA VALUES (2, 'PRELIM', 'Loud noise')
>
> event prelim count
> other loud noise 2
> other agitation 1
> bad behavior loud noise 1
> bad behavior agitation 1
> verbal aggression loud noise 1
> peer <BLANK> 0
>
>

Another Newbie

Trying to insert into a field that is varchar(20) but I want to pad the
entry with "0" on the beginning of it... so if the item to be written is 10
chars long I want to write 10 "0" on the front of the item.. how to
accomplish?
Thanks
--
Austin Henderson <><
Network Administratorprint replicate('0',20 -len('microsoft')) + 'microsoft'
--
gani
"Austin Henderson" <kahenderson@.firstfleetinc.NOSPAM.com> wrote in message
news:u06WsobcDHA.652@.tk2msftngp13.phx.gbl...
> Trying to insert into a field that is varchar(20) but I want to pad the
> entry with "0" on the beginning of it... so if the item to be written is
10
> chars long I want to write 10 "0" on the front of the item.. how to
> accomplish?
> Thanks
>
> --
> Austin Henderson <><
> Network Administrator
>|||Got it THANKS!
--
Austin Henderson <><
Network Administrator
"gani" <a@.a.com> wrote in message
news:Ow8oA5bcDHA.1540@.tk2msftngp13.phx.gbl...
> print replicate('0',20 -len('microsoft')) + 'microsoft'
> --
> gani
>
>
> "Austin Henderson" <kahenderson@.firstfleetinc.NOSPAM.com> wrote in message
> news:u06WsobcDHA.652@.tk2msftngp13.phx.gbl...
> > Trying to insert into a field that is varchar(20) but I want to pad the
> > entry with "0" on the beginning of it... so if the item to be written is
> 10
> > chars long I want to write 10 "0" on the front of the item.. how to
> > accomplish?
> >
> > Thanks
> >
> >
> > --
> > Austin Henderson <><
> > Network Administrator
> >
> >
>