**

Good Friday to all!

I am in need of some assistance, can someone help a guy out?

I am needing to import several records into the database. They are in several csv files and linked via an ID number. The users wish to utilize the ID number as the Remedy request Id. I have built the schema to utilize this with no issue.

The Issue: Once the records are imported, they use the ID number for Request ID but is does not utilize the assigned 12 digit length. It took the exact value as it appeared in the csv file: record ID 99 came in as record ID 99 where is should have been 000000000099

I was thinking that I could right a SQL script to update the tables with the correct value...

I am not sure what the correct syntax is to do this. Any ideas?

I need to update the single digit numbers with 11 '0'
I need to update the double digit numbers with 10 '0'
I need to update the triple digit numbers with 9 '0'
as so worth...

I am running SQL 2000.




Eric W. Dunn-Star Quality Certified-2003-2005
Programmer Analysist II
McLeodUSA, Inc.
8665 New Trials Dr.
The Woodlands, TX 77381
(281)465-1265
(281)465-1951 fax





NOTICE: This electronic mail transmission may contain confidential information and is intended only for the person(s) named. Any use, copying or disclosure by any other person is strictly prohibited. If you have received this transmission in error, please notify the sender via e-mail.



This posting was submitted via the Web interface

**
try:

right(('000000000000' + recordID ),12)





From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Dunn, Eric
Sent: Friday, April 15, 2005 12:50 PM
To: arslist@ARSLIST.ORG
Subject: SQL Question - Appending a value


**

Good Friday to all!

I am in need of some assistance, can someone help a guy out?

I am needing to import several records into the database. They are in several csv files and linked via an ID number. The users wish to utilize the ID number as the Remedy request Id. I have built the schema to utilize this with no issue.

The Issue: Once the records are imported, they use the ID number for Request ID but is does not utilize the assigned 12 digit length. It took the exact value as it appeared in the csv file: record ID 99 came in as record ID 99 where is should have been 000000000099

I was thinking that I could right a SQL script to update the tables with the correct value...

I am not sure what the correct syntax is to do this. Any ideas?

I need to update the single digit numbers with 11 '0'
I need to update the double digit numbers with 10 '0'
I need to update the triple digit numbers with 9 '0'
as so worth...

I am running SQL 2000.




Eric W. Dunn-Star Quality Certified-2003-2005
Programmer Analysist II
McLeodUSA, Inc.
8665 New Trials Dr.
The Woodlands, TX 77381
(281)465-1265
(281)465-1951 fax





NOTICE: This electronic mail transmission may contain confidential information and is intended only for the person(s) named. Any use, copying or disclosure by any other person is strictly prohibited. If you have received this transmission in error, please notify the sender via e-mail.



This posting was submitted via the Web interface
This posting was submitted via the Web interface

**
Eric,

I am sure someone is here for ya. It might be me.

A. did you use the remedy import tool or just use the database import tool?
1. the remedy tool should have filled in the field for you

B. I am not a MSSQL man, but in oracle the key is lpad. The format is something along the lines of lpad(,,)


If you need more, let me know before I take off. Give you about 15 to 30 minutes. ;-)

Brian

"Dunn, Eric" wrote:

**

Good Friday to all!

I am in need of some assistance, can someone help a guy out?

I am needing to import several records into the database. They are in several csv files and linked via an ID number. The users wish to utilize the ID number as the Remedy request Id. I have built the schema to utilize this with no issue.

The Issue: Once the records are imported, they use the ID number for Request ID but is does not utilize the assigned 12 digit length. It took the exact value as it appeared in the csv file: record ID 99 came in as record ID 99 where is should have been 000000000099

I was thinking that I could right a SQL script to update the tables with the correct value...

I am not sure what the correct syntax is to do this. Any ideas?

I need to update the single digit numbers with 11 '0'
I need to update the double digit numbers with 10 '0'
I need to update the triple digit numbers with 9 '0'
as so worth...

I am running SQL 2000.




Eric W. Dunn-Star Quality Certified-2003-2005
Programmer Analysist II
McLeodUSA, Inc.
8665 New Trials Dr.
The Woodlands, TX 77381
(281)465-1265
(281)465-1951 fax





NOTICE: This electronic mail transmission may contain confidential information and is intended only for the person(s) named. Any use, copying or disclosure by any other person is strictly prohibited. If you have received this transmission in error, please notify the sender via e-mail.



This posting was submitted via the Web interface



Brian Goralczyk
Mobile 574-249-0018
Email: bdg0584@yahoo.com
Website: www.acegolfclubs.com

"If we let the opinions of others keep us from doing what we know is right,
than we have betrayed both ourselves as well as those we interact with"
This posting was submitted via the Web interface

**
Why not open the CSV in Excel, select the ID column and pad the cells in the column with leading zeros and reimport?

Norm



From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Dunn, Eric
Sent: 04/15/2005 2:50 PM
To: arslist@ARSLIST.ORG
Subject: SQL Question - Appending a value


**

Good Friday to all!

I am in need of some assistance, can someone help a guy out?

I am needing to import several records into the database. They are in several csv files and linked via an ID number. The users wish to utilize the ID number as the Remedy request Id. I have built the schema to utilize this with no issue.

The Issue: Once the records are imported, they use the ID number for Request ID but is does not utilize the assigned 12 digit length. It took the exact value as it appeared in the csv file: record ID 99 came in as record ID 99 where is should have been 000000000099

I was thinking that I could right a SQL script to update the tables with the correct value...

I am not sure what the correct syntax is to do this. Any ideas?

I need to update the single digit numbers with 11 '0'
I need to update the double digit numbers with 10 '0'
I need to update the triple digit numbers with 9 '0'
as so worth...

I am running SQL 2000.




Eric W. Dunn-Star Quality Certified-2003-2005
Programmer Analysist II
McLeodUSA, Inc.
8665 New Trials Dr.
The Woodlands, TX 77381
(281)465-1265
(281)465-1951 fax





NOTICE: This electronic mail transmission may contain confidential information and is intended only for the person(s) named. Any use, copying or disclosure by any other person is strictly prohibited. If you have received this transmission in error, please notify the sender via e-mail.



This posting was submitted via the Web interface
This posting was submitted via the Web interface

**
We have a winner! That worked.

I was close, just not close enough. I was trying C1 = (000000000000 + C1).

Thanks for the help.

Eric

-----Original Message-----
From: Sroujian, Siham [mailto:siham.sroujian@PEGS.COM]
Sent: Friday, April 15, 2005 3:13 PM
To: arslist@ARSLIST.ORG
Subject: Re: SQL Question - Appending a value


**
try:

right(('000000000000' + recordID ),12)





From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Dunn, Eric
Sent: Friday, April 15, 2005 12:50 PM
To: arslist@ARSLIST.ORG
Subject: SQL Question - Appending a value


**

Good Friday to all!

I am in need of some assistance, can someone help a guy out?

I am needing to import several records into the database. They are in several csv files and linked via an ID number. The users wish to utilize the ID number as the Remedy request Id. I have built the schema to utilize this with no issue.

The Issue: Once the records are imported, they use the ID number for Request ID but is does not utilize the assigned 12 digit length. It took the exact value as it appeared in the csv file: record ID 99 came in as record ID 99 where is should have been 000000000099

I was thinking that I could right a SQL script to update the tables with the correct value...

I am not sure what the correct syntax is to do this. Any ideas?

I need to update the single digit numbers with 11 '0'
I need to update the double digit numbers with 10 '0'
I need to update the triple digit numbers with 9 '0'
as so worth...

I am running SQL 2000.




Eric W. Dunn-Star Quality Certified-2003-2005
Programmer Analysist II
McLeodUSA, Inc.
8665 New Trials Dr.
The Woodlands, TX 77381
(281)465-1265
(281)465-1951 fax





NOTICE: This electronic mail transmission may contain confidential information and is intended only for the person(s) named. Any use, copying or disclosure by any other person is strictly prohibited. If you have received this transmission in error, please notify the sender via e-mail.



This posting was submitted via the Web interface This posting was submitted via the Web interface




NOTICE: This electronic mail transmission may contain confidential information and is intended only for the person(s) named. Any use, copying or disclosure by any other person is strictly prohibited. If you have received this transmission in error, please notify the sender via e-mail.




This posting was submitted via the Web interface

**
I used the Remedy Import Tool for ARSystem 5.12 and It does not.

Eric

-----Original Message-----
From: Brian Goralczyk [mailto:bdg0584@YAHOO.COM]
Sent: Friday, April 15, 2005 3:14 PM
To: arslist@ARSLIST.ORG
Subject: Re: SQL Question - Appending a value


**
Eric,

I am sure someone is here for ya. It might be me.

A. did you use the remedy import tool or just use the database import tool?
1. the remedy tool should have filled in the field for you

B. I am not a MSSQL man, but in oracle the key is lpad. The format is something along the lines of lpad(,,)


If you need more, let me know before I take off. Give you about 15 to 30 minutes. ;-)

Brian

"Dunn, Eric" wrote:

**

Good Friday to all!

I am in need of some assistance, can someone help a guy out?

I am needing to import several records into the database. They are in several csv files and linked via an ID number. The users wish to utilize the ID number as the Remedy request Id. I have built the schema to utilize this with no issue.

The Issue: Once the records are imported, they use the ID number for Request ID but is does not utilize the assigned 12 digit length. It took the exact value as it appeared in the csv file: record ID 99 came in as record ID 99 where is should have been 000000000099

I was thinking that I could right a SQL script to update the tables with the correct value...

I am not sure what the correct syntax is to do this. Any ideas?

I need to update the single digit numbers with 11 '0'
I need to update the double digit numbers with 10 '0'
I need to update the triple digit numbers with 9 '0'
as so worth...

I am running SQL 2000.




Eric W. Dunn-Star Quality Certified-2003-2005
Programmer Analysist II
McLeodUSA, Inc.
8665 New Trials Dr.
The Woodlands, TX 77381
(281)465-1265
(281)465-1951 fax





NOTICE: This electronic mail transmission may contain confidential information and is intended only for the person(s) named. Any use, copying or disclosure by any other person is strictly prohibited. If you have received this transmission in error, please notify the sender via e-mail.



This posting was submitted via the Web interface



Brian Goralczyk
Mobile 574-249-0018
Email: bdg0584@yahoo.com
Website: www.acegolfclubs.com

"If we let the opinions of others keep us from doing what we know is right,
than we have betrayed both ourselves as well as those we interact with"
This posting was submitted via the Web interface




NOTICE: This electronic mail transmission may contain confidential information and is intended only for the person(s) named. Any use, copying or disclosure by any other person is strictly prohibited. If you have received this transmission in error, please notify the sender via e-mail.




This posting was submitted via the Web interface

Eric,

This is the nature of "Merge" events. (Merge assumes that the user
knows what they are doing. So the application server just takes the
data it is given and runs Merge filters too. When you use the "replace
existing" or "update existing" options.:) It does not even mess with
'Last modified by' values. :)


I would actually suggest you fix the CSV files and drop the existing
data and reload it.

At the SQL level you have to update several tables and the exact
number of them depends on your form structure. It can be done, but it
is really easier to fix the CSV files and then import the data again.
(Even if you are talking 1000's of records. But maybe not if your
talking 1,000,000 or records. :)

There are some cool things you can do with Excel to fix things like
this, or Perl, or awk/sed, or Emac, or (if you really want to) by
hand.

Also do not for get any form prefix's to the 'Request ID' value too. (
If your form has one. )

--
Carey Matthew Black
Remedy Skilled Professional (RSP)
ARS = Action Request System(Remedy)

Solution = People + Process + Tools
Fast, Accurate, Cheap.... Pick two.
Never ascribe to malice, that which can be explained by incompetence.

http://www.fellowshipchurch.com




On 4/15/05, Dunn, Eric wrote:
> **
>
> Good Friday to all!
>
> I am in need of some assistance, can someone help a guy out?
>
> I am needing to import several records into the database. They are in
> several csv files and linked via an ID number. The users wish to utilize the
> ID number as the Remedy request Id. I have built the schema to utilize this
> with no issue.
>
> The Issue: Once the records are imported, they use the ID number for Request
> ID but is does not utilize the assigned 12 digit length. It took the exact
> value as it appeared in the csv file: record ID 99 came in as record ID 99
> where is should have been 000000000099
>
> I was thinking that I could right a SQL script to update the tables with the
> correct value...
>
> I am not sure what the correct syntax is to do this. Any ideas?
>
> I need to update the single digit numbers with 11 '0'
> I need to update the double digit numbers with 10 '0'
> I need to update the triple digit numbers with 9 '0'
> as so worth...
>
> I am running SQL 2000.
>
>
>
>
> Eric W. Dunn-Star Quality Certified-2003-2005
> Programmer Analysist II
> McLeodUSA, Inc.
> 8665 New Trials Dr.
> The Woodlands, TX 77381
> (281)465-1265
> (281)465-1951 fax
>


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)