Hi there,
I'm a bit embarrassed about this question, because I'm sure that a lot of you would find it trivial, but I'm really not the best at T-SQL and especially not string handling.
I'm trying to generate a 'parent' value, by replacing characters in the 'child' field with ''. It's a classic Chart of Accounts sort of problem, for feeding into a Parent/Child dimension in AS. It's a lot easier to understand if you look at this:
This is the set I have:
GLCode GLDescription
0.-.-.- Balance S
0.0.-.- Balance S.Balance Sheet
0.0.0.- Balance S.Balance Sheet.Balance Sheet
0.0.0.5000 Balance S.Balance Sheet.Balance Sheet.Assets - Area1
0.0.0.5001 Balance S.Balance Sheet.Balance Sheet.Assets - Area2
0.0.0.5002 Balance S.Balance Sheet.Balance Sheet.Assets - Area3
This is the set I want
Ch GLCode Par GLCode GLDescription
0.-.-.- -.-.-.- Balance S
0.0.-.- 0.-.-.- Balance S.Balance Sheet
0.0.0.- 0.0.-.- Balance S.Balance Sheet.Balance Sheet
0.0.0.5000 0.0.0.- Balance S.Balance Sheet.Balance Sheet.Assets - Area1
0.0.0.5001 0.0.0.- Balance S.Balance Sheet.Balance Sheet.Assets - Area2
0.0.0.5002 0.0.0.- Balance S.Balance Sheet.Balance Sheet.Assets - Area3
That is to say,
0.0.0.- is the parent of 0.0.0.5001
0.0.-.- is the parent of 0.0.0.-
0.-.-.- is the parent of 0.0.-.-
So, what I really need to do is replace the last non '-' string with '-'. I've tried various combos of PATINDEX, CHARINDEX, REPLACE etc, but I'm really struggling. To make in more complicated, and of the strings between the . can be any length.
Any ideas?
Maybe something like this would work for you:
SET NOCOUNT ON
DECLARE @.MyTable table
( RowID int IDENTITY,
GLCode varchar(20),
[Description] varchar(100)
)
INSERT INTO @.MyTable VALUES ( '0.-.-.-', 'Balance S' )
INSERT INTO @.MyTable VALUES ( '0.0.-.-', 'Balance S.Balance Sheet' )
INSERT INTO @.MyTable VALUES ( '0.0.0.-', 'Balance S.Balance Sheet.Balance Sheet' )
INSERT INTO @.MyTable VALUES ( '0.0.0.5000', 'Balance S.Balance Sheet.Balance Sheet.Assets - Area1' )
INSERT INTO @.MyTable VALUES ( '0.0.0.5001', 'Balance S.Balance Sheet.Balance Sheet.Assets - Area2' )
INSERT INTO @.MyTable VALUES ( '0.0.0.5002', 'Balance S.Balance Sheet.Balance Sheet.Assets - Area3' )
SELECT
[Ch GLCode] = GLCode,
[Par GLCode] = CASE
WHEN isnumeric( parsename( GLCode, 1 )) = 1 THEN '0.0.0.-'
WHEN parsename( GLCode, 2 ) <> '-' THEN '0.0.-.-'
WHEN parsename( GLCode, 3 ) <> '-' THEN '0.-.-.-'
WHEN parsename( GLCode, 4 ) <> '-' THEN '-.-.-.-'
END,
[Description]
FROM @.MyTable
ORDER BY GLCode
Ch GLCode Par GLCode Description
-- - -
0.-.-.- -.-.-.- Balance S
0.0.-.- 0.-.-.- Balance S.Balance Sheet
0.0.0.- 0.0.-.- Balance S.Balance Sheet.Balance Sheet
0.0.0.5000 0.0.0.- Balance S.Balance Sheet.Balance Sheet.Assets - Area1
0.0.0.5001 0.0.0.- Balance S.Balance Sheet.Balance Sheet.Assets - Area2
0.0.0.5002 0.0.0.- Balance S.Balance Sheet.Balance Sheet.Assets - Area3
Thanks for that Arnie,
I did something a bit stupid in my example - I implied that the source dataset is a lot simpler than it acually is. It's a 13,000 row Chart of Accounts, and I just gave you the top 6 rows. If we look further down, and resample the data, we can see members like this:
In this case, 3.S.A.0001 , 3.S.A.0003 , 3.S.A.0005 , 3.S.A.0102 , 3.S.A.0110 are the children of parent 3.S.A.-
3.S.A.- is the child of parent 3.S.-.-
3.S.-.- is the child of parent 3.-.-.-
(and we can therefore infer that the top member, 3.R.W.4501 is the child of 3.R.W.-
so I don't think your approach of hardcoding the parent into the CASE would work. But your approach was perfectly reasonable, given the lame example you had to work with!
You used ISNUMERIC, which I didn't think of, and PARSENAME, which I haven't used before, so I'll see if I can use those in a more dynamic solution.
As you've probably guessed, I'm a long way from being a T-SQL ninja, so if anyone has any clever ideas on how I should approach this, I'd be interested to know...
|||
The approach I supplied previously seems to work just fine on the additional values you supplied.
(And should as long as the forth part is always a number.)
|||Maybe I'm missing something, but I don't quite understand how hardcoding '0's into the output is going to help where the code is something like '2.H.A.003'. But you've given me some ideas, so thanks for your help.
SELECT
[Ch GLCode] = GLCode,
[Par GLCode] = CASE
WHEN isnumeric( parsename( GLCode, 1 )) = 1 THEN '0.0.0.-'
WHEN parsename( GLCode, 2 ) <> '-' THEN '0.0.-.-'
WHEN parsename( GLCode, 3 ) <> '-' THEN '0.-.-.-'
WHEN parsename( GLCode, 4 ) <> '-' THEN '-.-.-.-'
END,
[GLDescription]
FROM dbo.WRK_CoA
ORDER BY GLCode
Ch GLCode Par GLCode GLDescription
1.S.U.0380 0.0.0.- Academy of F&H Education.Planning
1.S.U.0600 0.0.0.- Academy of F&H Education.Planning and Develop
2.-.-.- -.-.-.- Adult Academy
2.B.-.- 0.-.-.- Adult Academy.Creative & Cultural Industries
2.B.S.- 0.0.-.- Adult Academy.Creative & Cultural Industri
2.B.S.0130 0.0.0.- Adult Academy.Creative & Cultural Indus
2.H.-.- 0.-.-.- Adult Academy.Employment Services
2.H.A.- 0.0.-.- Adult Academy.Employment Services.Bu
2.H.A.0001 0.0.0.- Adult Academy.Employment Services.Busin
2.H.A.0003 0.0.0.- Adult Academy.Employment Services.Busines
2.H.A.0005 0.0.0.- Adult Academy.Employment Services.Busine
2.H.A.0102 0.0.0.- Adult Academy.Employment Services.Business
2.H.A.0110 0.0.0.- Adult Academy.Employment Services.Business
Sam,
Sorry, I should have gone into more detail. You can use PARSENAME() to deconstruct the values, AND you can use
PARSENAME() to re-construct the values.
Hopefully this will give you the guidance you need.
DECLARE @.MyTable table
( RowID int IDENTITY,
GLCode varchar(20)
)
INSERT INTO @.MyTable VALUES ( '0.-.-.-' )
INSERT INTO @.MyTable VALUES ( '0.0.-.-' )
INSERT INTO @.MyTable VALUES ( '0.0.0.-' )
INSERT INTO @.MyTable VALUES ( '0.0.0.5000' )
INSERT INTO @.MyTable VALUES ( '0.0.0.5001' )
INSERT INTO @.MyTable VALUES ( '0.0.0.5002' )
INSERT INTO @.MyTable VALUES ( '3.R.W.4501' )
INSERT INTO @.MyTable VALUES ( '3.S.-.-' )
INSERT INTO @.MyTable VALUES ( '3.S.A.-' )
INSERT INTO @.MyTable VALUES ( '3.S.A.0001' )
INSERT INTO @.MyTable VALUES ( '3.S.A.0003' )
INSERT INTO @.MyTable VALUES ( '3.S.A.0005' )
INSERT INTO @.MyTable VALUES ( '3.S.A.0102' )
INSERT INTO @.MyTable VALUES ( '3.S.A.0110' )
SELECT
[Ch GLCode] = GLCode,
[Par GLCode] = CASE
WHEN isnumeric( parsename( GLCode, 1 )) = 1
THEN parsename( GLCode, 4 ) + '.' + parsename( GLCode, 3 ) + '.' + parsename( GLCode, 2 ) + '.-'
WHEN parsename( GLCode, 2 ) <> '-'
THEN parsename( GLCode, 4 ) + '.' + parsename( GLCode, 3 ) + '.-.-'
WHEN parsename( GLCode, 3 ) <> '-'
THEN parsename( GLCode, 4 ) + '.-.-.-'
WHEN parsename( GLCode, 4 ) <> '-' THEN '-.-.-.-'
END
FROM @.MyTable
ORDER BY GLCode
Ch GLCode Par GLCode
-- -
0.-.-.- -.-.-.-
0.0.-.- 0.-.-.-
0.0.0.- 0.0.-.-
0.0.0.5000 0.0.0.-
0.0.0.5001 0.0.0.-
0.0.0.5002 0.0.0.-
3.R.W.4501 3.R.W.-
3.S.-.- 3.-.-.-
3.S.A.- 3.S.-.-
3.S.A.0001 3.S.A.-
3.S.A.0003 3.S.A.-
3.S.A.0005 3.S.A.-
3.S.A.0102 3.S.A.-
3.S.A.0110 3.S.A.-
i dunno, here's my attempt:
Code Snippet
SET NOCOUNT ON
DECLARE @.MyTable table
( RowID int IDENTITY,
GLCode varchar(20),
[Description] varchar(100)
)
INSERT INTO @.MyTable VALUES ( '3.-.-.-', 'Balance S' )
INSERT INTO @.MyTable VALUES ( '3.S.-.-', 'Balance S.Balance Sheet' )
INSERT INTO @.MyTable VALUES ( '3.S.A.-', 'Balance S.Balance Sheet.Balance Sheet' )
INSERT INTO @.MyTable VALUES ( '3.S.A.0001', 'Balance S.Balance Sheet.Balance Sheet.Assets - Area1' )
INSERT INTO @.MyTable VALUES ( '3.S.A.5001', 'Balance S.Balance Sheet.Balance Sheet.Assets - Area2' )
INSERT INTO @.MyTable VALUES ( '3.S.A.5002', 'Balance S.Balance Sheet.Balance Sheet.Assets - Area3' )
SELECT
CASE
WHEN CHARINDEX('-', GLCode) = 0 THEN LTRIM(RTRIM(LEFT(GLCode, LEN(GLCode) - CHARINDEX('.', REVERSE(GLCode)))))
WHEN CHARINDEX('-', GLCODE) = 3 THEN '-.-.-.-'
ELSE LTRIM(RTRIM(LEFT(GLCode, CHARINDEX('.-', GLCode))))
END
FROM @.MyTable
edit: the above isn't correct, but perhaps it'll offer some help...|||Thanks guys - I can see that I'll be able to pick apart what you've done and come up with a solution.
min,