Help with data transforation problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with data transforation problem

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-03 : 14:32:21

Hi all,
I have a little dillema, and I need some help. I have a column DOC_NO
that is varchar 15. I need to trim of the left 10 characters and then remove any leading zeros on the left. I need to do this as an update on the Table that the data is in(AR_OPN_ITEM). Is there asimple way I can do this as an UPDATE query, or does this need to be some long drawn out fiasco?

There's never enough time to type code right,
but always enough time for a hotfix...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-03 : 15:02:22

what does this give ?
select cast(right(DOC_NO,5) as int), DOC_NO from tbl
-- assumes isnumeric(DOC_NO) = 1

rockmoose

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-03 : 15:18:54

Hey Rockmoose,
If I run that query as 'int' it blows up(did I mention that there are alpha characters and <-> in this mess?). If I run it as type 'varchar' it parses out with the leading 0's in place. What I need to do is get all of the bad data that came over during DTS off of this table. What fun!
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-03 : 15:52:44

not numeric, how sad. things got messier.
Try this:

declare @s varchar(15)
set @s = 'xx0xabc'

select substring(right(@s,5),patindex('%[^0]%',right(@s,5)),5)

rockmoose

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-03 : 16:23:30

Ok,
that returns 'xabc' once for each row of data...

There's never enough time to type code right,
but always enough time for a hotfix...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-03 : 16:27:47

Well, you have to adjust to your situation...

select substring(right(DOC_NO,5),patindex('%[^0]%',right(DOC_NO,5)),5) --,DOC_NO
from tbl

rockmoose

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-03 : 16:36:26

OH DUH!!!
I knew that. Ok, that returns a good set. Now all I need to do is run the update to fix this mess...
Many Thanks! I knew there had to be a simple way to parse this out...

There's never enough time to type code right,
but always enough time for a hotfix...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-03 : 16:42:14

You're ok on how to do the update I assume.
Good Luck.

rockmoose

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-03 : 17:43:24

Hey Rock!
So close, yet so far...
I can't seem to get the syntax right for this update (long day; thinking like a spaz)
here's what I've got:
UPDATE AR_OPN_ITEM
set DOC_NO=select substring(right(DOC_NO,5),patindex('%[^0]%',right(DOC_NO,5)),5)
from AR_OPN_ITEM

But....no go. what am I missing?

There's never enough time to type code right,
but always enough time for a hotfix...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-03 : 18:35:08

quote:
Originally posted by steamngn

Hey Rock!
So close, yet so far...
I can't seem to get the syntax right for this update (long day; thinking like a spaz)
here's what I've got:
UPDATE AR_OPN_ITEM
set DOC_NO=~~select~~ substring(right(DOC_NO,5),patindex('%[^0]%',right(DOC_NO,5)),5)
-- from AR_OPN_ITEM -- not necessary

But....no go. what am I missing?

There's never enough time to type code right,
but always enough time for a hotfix...

Ok ?

rockmoose

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-03 : 18:41:33

OK!
Actually, I beat you to that part (I am having a BAAAAAD day)
But now I have a new issue. This column is part of the primary key,
anf when we strip the data, some of the finance charge doc_no become duplicates! is there a way to test for that, or can this be built into this update?

There's never enough time to type code right,
but always enough time for a hotfix...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-04 : 06:25:48

You have to think about what you are doing.
Are there duplicates in the table that have to be removed ( clean the data )
Or isn't the design compliant with the business requirements ( the new 5 pos key is not good enough! )

this will let you view the "duplicates" in your table:

select dups.new_key, AR_OPN_ITEM.*
from AR_OPN_ITEM
join
( 	select substring(right(DOC_NO,5),patindex('%[^0]%',right(DOC_NO,5)),5) as new_key
	from AR_OPN_ITEM
	group by substring(right(DOC_NO,5),patindex('%[^0]%',right(DOC_NO,5)),5)
	having count(*) > 1 ) as dups
on AR_OPN_ITEM.substring(right(DOC_NO,5),patindex('%[^0]%',right(DOC_NO,5)),5) = dups.new_key
order by dups.new_key

rockmoose

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-05 : 17:23:17

I'm baack-
I got it straightened out. Apparently the A/R person was entering
'1CHECK', '1CHECK', etc. for each time a customer made a payment on a single statement. parsing it out returned 'CHECK' more than once for the same document number, which invalidates the primary key. I ran an update to change the the data to a sequencial format and then it all parsed out ok. I thank you ever so much for the conversion string! Can you explain it out for me? I understand the RIGHT commands, but I'm a little confused about the %[^0]% thing...

There's never enough time to type code right,
but always enough time for a hotfix...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-06 : 04:38:27

Ok, good, you're welcome.

patindex( '%[^0]%', '....' )
This will give the index of the first occurence of the first not zero character.

patindex( '%[^0]%', 'xxx' ) = 1
patindex( '%[^0]%', '0xxx' ) = 2
patindex( '%[^0]%', '00xxx' ) = 3

See pattern matching in BOL.

rockmoose

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-07 : 13:45:25

I get it! Thanks for the help. Ihave one more little problem that just landed on my desk, I'm a bit confused...
we have the following trigger on an order line table. when an item is ordered, and the commnt_1 line matches a ticket number, it updates the status of that item on the ticket. problem is, when the commnt_1 line is null or has something like 'stock' in it instead of a ticket number, it blows up. What am I missing?

CREATE trigger tr_UpdateTicketsorderstat
on dbo.PO_PREQ_LIN
for insert, update, delete
as

update tl
set tl.ITEM_ORDER_STATUS = 'P'
from inserted i
inner join PO_PREQ_LIN rl 
on i.ITEM_NO = rl.ITEM_NO
and i.SEQ_NO=rl.SEQ_NO
and i.PREQ_NO = rl.PREQ_NO
inner join ps_tkt_lin tl
on rl.COMMNT_1 = tl.tkt_no
and rl.ITEM_NO=tl.ITEM_NO

There's never enough time to type code right,
but always enough time for a hotfix...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-07 : 15:32:22

Does the error message give You any clues ?

rockmoose

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-07 : 16:14:42

The app returns an error that says
'Row cannot be located for updating.Some values may have changed since it was last read'. If the trigger is not on the table or if there are only lines on the PO that have a valid ticket number in the comment field, then it works ok.

There's never enough time to type code right,
but always enough time for a hotfix...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-08 : 03:13:03

Hi!,

Have you tried this link ;)

Basically You have to use "set nocount on" in you trigger.

rockmoose

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-11 : 10:08:53

I'm baack...
Out sick the past couple days... Thanks for the kick in the pants about NoCount! I know this stuff, I'm just not thinking right... Guess that's what happens with too much cough syrup!
Thanks again for the help, I do appreciate it. Hey, here's one I don't know: Is there an easy way to set ORDER in a query so that it returns data in the order entered? In other words, if I do something like this:

SELECT TKT_NO
FROM PS_TKT_HDR
where TKT_NO='6152' or TKT_NO='5193' or TKT_NO='7142'

can we get it so the set returned is 6152,5193,7142 which is exactly the order entered, instead of normal ascending or decending?
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-11 : 10:36:30

Any reason why you HAVE to order that way?

The only solution I can think of is to insert the values into a temp table in the order you want, add an identity column, then order by that identity:

CREATE TABLE #tix (tkt_no varchar(4) not null, ID int not null identity(1,1))
insert #tix(tkt_no) values('6152')
insert #tix(tkt_no) values('5193')
insert #tix(tkt_no) values('7142')

SELECT H.TKT_NO FROM PS_TKT_HDR H inner join #tix t on h.tkt_no=t.tkt_no ORDER BY t.ID

DROP TABLE #tix

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-11 : 11:04:50

Or you can code the ordering:

SELECT TKT_NO
FROM PS_TKT_HDR
where TKT_NO='6152' or TKT_NO='5193' or TKT_NO='7142'
order by case TKT_NO when '6152' then 1 when '5193' then 2 when '7142' then 3 else 999 end

rockmoose

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources