Unusual Recursive CTE with non-unique codes

Question

Unusual Recursive CTE with non-unique codes

david.edwards 76768

Ten Centuries

Points: 1246
More actions
April 16, 2015 at 6:34 am

#299905

Hi all
I think this is an unusual circumstance because I cannot find any info to help with this and was wondering if anyone here can see what I am missing please.
I am fairly new to using Recursive CTEs to create hierarchies and was chuffed when I got this working, until I found the anomaly.
I have a table of code lookups used in the front end software for drop-downs, descriptions etc.
CREATE TABLE [dbo].[dbCodeLookup](
[cdID] [INT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[cdType] [dbo].[uCodeLookup] NOT NULL,
[cdCode] [dbo].[uCodeLookup] NOT NULL,
[cdUICultureInfo] [dbo].[uUICultureInfo] NOT NULL,
[cdDesc] [NVARCHAR](1000) NULL,
[cdSystem] [BIT] NOT NULL,
[cdDeletable] [BIT] NOT NULL,
[cdAddLink] [dbo].[uCodeLookup] NULL,
[cdHelp] [NVARCHAR](500) NULL,
[cdNotes] [NVARCHAR](500) NULL,
[cdGroup] [BIT] NOT NULL,
[rowguid] [UNIQUEIDENTIFIER] ROWGUIDCOL NOT NULL,
CONSTRAINT [PK_dbCodeLookup] PRIMARY KEY CLUSTERED
(
[cdID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
CONSTRAINT [IX_dbCodeLookup] UNIQUE NONCLUSTERED
(
[cdType] ASC,
[cdCode] ASC,
[cdUICultureInfo] ASC,
[cdAddLink] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [IndexGroup]
)

The Codes are organised into groups, represented as "virtual folders" in the front end:
cdType is the parent "folder"
cdGroup=1 is a "folder"
cdGroup=0 is the lowest level child item - the Code Lookup.
We have a need to produce a hierarchy report on some of our custom groups, which I thought I had cracked with:
-- Produce Hierarchy of Code Lookups using Recursive CTE
WITHFolders
AS (
SELECT
cdType AS Root
,cdCode
, cdDesc
,CASE cdGroup WHEN 1 THEN 'Folder' ELSE 'Code Lookup' END AS CodeType
,CONVERT(NVARCHAR(100), 'Code Lookup/OMS/' + cdCode + '/') AS FullPath
,1 AS RecursionCounter
FROM
dbCodeLookup
WHERE --Entire where clause creates a unique anchor at the first level required for reporting
cdType = 'OMS' -- Top Level.
AND cdCode = 'udNHT' -- Will become parameter after testing
AND cdGroup = 1
UNION ALL
SELECT
dcl.cdType
,dcl.cdCode
,DCL.cdDesc
,CASE dcl.cdGroup WHEN 1 THEN 'Folder' ELSE 'Code Lookup' END
,CONVERT(NVARCHAR(100), f.FullPath + dcl.cdCode + '/')
,RecursionCounter + 1
FROM
dbCodeLookup AS dcl
INNER JOIN Folders AS f
ON dcl.cdType = f.cdCode
)
SELECT
*
FROM
Folders
ORDER BY
FullPath
So far so good, I get this output (certain data hidden for confidentiality)
Root cdCode cdDesc CodeType FullPath RecursionCounter
--------------- --------------- ------------------------------------- ----------- --------------------------------------------- ----------------
OMS UDNHT N** H**** Team Code Lookups Folder Code Lookup/OMS/UDNHT/ 1
UDNHT DEVELOPERS List of Developers Folder Code Lookup/OMS/UDNHT/DEVELOPERS/ 2
DEVELOPERS /******/ /******/ Code Lookup Code Lookup/OMS/UDNHT/DEVELOPERS/******/ 3
...
...
UDNHT GARTEN Garage Tenure Folder Code Lookup/OMS/UDNHT/GARTEN/ 2
GARTEN F Freehold Code Lookup Code Lookup/OMS/UDNHT/GARTEN/F/ 3
GARTEN L Leasehold Code Lookup Code Lookup/OMS/UDNHT/GARTEN/L/ 3
UDNHT INCENTMATCH Incentives Match Folder Code Lookup/OMS/UDNHT/INCENTMATCH/ 2
INCENTMATCH FALSE No Code Lookup Code Lookup/OMS/UDNHT/INCENTMATCH/FALSE/ 3
INCENTMATCH PART Partly Code Lookup Code Lookup/OMS/UDNHT/INCENTMATCH/PART/ 3
INCENTMATCH TRUE Yes Code Lookup Code Lookup/OMS/UDNHT/INCENTMATCH/TRUE/ 3
(69 row(s) affected)
My problem is when I look at certain other Groups I ended up with a right pig's ear (pun half intended) because they contain cdCodes that are duplicated - they could be a code for a group or a codelookup. Obviously codes cannot be duplicated within a group.
Below are example results for a different top level group of CONTACTINFO, I have removed most of the good data for space.
Root cdCode cdDesc CodeType FullPath RecursionCounter
--------------- --------------- -------------------------------------- ----------- ------------------------------------------------------- ----------------
OMS CONTACTINFO Contact Information Folder Code Lookup/OMS/CONTACTINFO/ 1
CONTACTINFO CONTLINK Contact Links Folder Code Lookup/OMS/CONTACTINFO/CONTLINK/ 2
CONTLINK BENEF Beneficiary Code Lookup Code Lookup/OMS/CONTACTINFO/CONTLINK/BENEF/ 3
...
...
CONTACTINFO ETHNIC Ethnic Origin Types Folder Code Lookup/OMS/CONTACTINFO/ETHNIC/ 2
ETHNIC 0 Other Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/0/ 3
ETHNIC 1 White Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/1/ 3
ETHNIC 10 Mixed white and black Caribbean Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/10/ 3
ETHNIC 11 Mixed white and black African Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/11/ 3
ETHNIC 12 Mixed white and Asian Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/12/ 3
ETHNIC 13 Mixed other Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/13/ 3
13 ACT ACT Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/13/ACT/ 4
13 NSW NSW Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/13/NSW/ 4
13 NT NT Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/13/NT/ 4
13 QLD QLD Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/13/QLD/ 4
13 SA SA Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/13/SA/ 4
13 STATESINFULL State Names in Full Folder Code Lookup/OMS/CONTACTINFO/ETHNIC/13/STATESINFULL/ 4
13 TAS TAS Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/13/TAS/ 4
13 VIC VIC Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/13/VIC/ 4
13 WA WA Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/13/WA/ 4
ETHNIC 14 White other Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/14/ 3
ETHNIC 15 Asian or Asian British other Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/15/ 3
ETHNIC 2 Irish Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/2/ 3
ETHNIC 3 Black (African) Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/3/ 3
ETHNIC 4 Black (Caribbean) Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/4/ 3
ETHNIC 5 Black (Other) Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/5/ 3
ETHNIC 6 Indian Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/6/ 3
ETHNIC 7 Pakistani Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/7/ 3
ETHNIC 8 Bangladeshi Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/8/ 3
ETHNIC 9 Chinese Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/9/ 3
ETHNIC 99 Unknown Code Lookup Code Lookup/OMS/CONTACTINFO/ETHNIC/99/ 3
...
...
(115 row(s) affected)
cdType "13" is the name of a subgroup containing Australian States, inside the group "States".
Would anybody with the time or inclination be able to offer any advice as to how I can handle this, or tell me why it's not possible!
Many Thanks
David
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

Good job with the DDL but to get a better answer it would be helpful if you could include some sample values so that we can better understand what you are working with. Note the article in my signature line about how to get better answers to questions.

In the meantime, take a look at this article:

Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets[/url]

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

david.edwards 76768 Ten Centuries Points: 1246 More actions · Answer 2

Celko: thanks for your reply. I think I probably didn't explain things as well as I could have.

First, this has not been developed in house, it is part of an extremely popular piece of legal case management software and this is a standard table referenced throughout, so "starting over" is not an option.

Second, it would be madness to split up this table. It is normalised properly as it is purely for matching descriptions to the codes that are stored all over in appropriate tables with their human readable descriptions, the entity is "Codes" if you like. The front end presents these in captions, drop downs etc using a simple codelookup function. Fairly standard I think, the system this is replacing uses a similar concept. To use your Automobiles, Squids etc as a very rough, basic analogy you would put this info in one table wouldn't you?

If they were products with a group and a name you wouldn't create a new table for each to show just their category, code and description.

Type Code Desc

Animal SQ Squid

Animal AEP African Elephant

Auto BCR Blue Car

Auto RCR Red Car

That is effectively what this table is for.

Without explaining how the entire product works, there is an included SDK with this product, based on a stripped down Visual Studio which allows the product to be developed and extended. The "virtual folders" I referred to are only a way to visualise how the codes are logically organised in the SDK. The users of the SDK can create their own groups of lookups in the SDK that are organised logically for them. When supplied there were already lots of codes used throughout and no guidelines on naming provided when handed over. In normal, intended use duplicate codes are, and never will be, a problem. The unique IX_dbCodeLookup prevents Type/Code duplications which would be.

Anyway, it is what it is, it works efficiently and effectively when doing what it is designed to do. I am trying to get info out in a non-standard way and could not think of a more appropriate way than a recursive CTE, as the "path" depth could theoretically be any length.

I'm not sure why they insist on GUIDS in every table, at least they have not created clustered indexes on them like another product we have!!

Alan B.:

Thank you, I read that article in your sig a little while ago and had it in the back of my head while writing the original post. I rarely ask for help, I prefer to research and work things out myself, but this one had me stumped - I had hoped the the act of writing out the problem in a post would clarify it my own head!

Sorry, I had intended to include a sample of the table data. Here it is, it should be fairly representative of the good and bad examples in my original post.

All the data marked ** Confidential ** would be at the bottom of the hierarchy and these work at the moment anyway. I have only included relevant columns.

cdType cdCode cdDesc cdGroup

--------------- ------------------ -------------------------------------------------------------------------------------------- -------

STATES 13 Australia 1

ETHNIC 13 Mixed other 0

MSSTAGE 13 MS Stage 13 0

IMEMAILHEADER 13 http://schemas.microsoft.com/mapi/id/{00020328-0000-0000-C000-000000000046}/8ff00003 0

DATEWIZGROUPS 13 Commercial Property 0

13 NSW NSW 0

13 VIC VIC 0

13 SA SA 0

13 WA WA 0

13 ACT ACT 0

13 TAS TAS 0

13 QLD QLD 0

13 NT NT 0

13 STATESINFULL State Names in Full 1

OMS UDNHT N** H**** Team Code Lookups 1

UDNHT INCENTMATCH Incentives Match 1

INCENTMATCH TRUE Yes 0

INCENTMATCH FALSE No 0

INCENTMATCH PART Partly 0

UDNHT GARTEN Garage Tenure 1

GARTEN F Freehold 0

GARTEN L Leasehold 0

UDNHT DEVELOPERS List of Developers (NHT Purchase) 1

DEVELOPERS ** Confidential ** ** Confidential ** 0

(83 row(s) affected)

Thanks for taking the time to look.

"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?

Ken McKelvey SSCoach Points: 18766 More actions · Answer 3

Your test data should be consumable. (ie CREATE TABLE... INSERT ...)

I suspect your specific problem can be resolved by checking the parent is a folder:

FROM

dbCodeLookup AS dcl

INNER JOIN Folders AS f

ON dcl.cdType = f.cdCode

AND f.CodeType LIKE 'Folder%'

david.edwards 76768 Ten Centuries Points: 1246 More actions · Answer 4

Thank you Ken. Spot on.

I used

INNER JOIN Folders AS f

ON dcl.cdType = f.cdCode

AND f.CodeType = 'Folder'

rather than a LIKE though.

I had got so close to that before posting, if only I'd known.

Thanks again, sanity now restored 🙂

"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?

Ken McKelvey SSCoach Points: 18766 More actions · Answer 5

Glad your problem is sorted.

As Joe mentioned, general lookup tables are a bad, if seemingly common, idea although I have never been unfortunate enough to come across a recursive general lookup table before!

I suspect you will need to be careful never to have two folders with the same code otherwise you will really be in trouble.

david.edwards 76768 Ten Centuries Points: 1246 More actions · Answer 6

It's definitely given me food for thought.

Not that I can change it in this case, but I am doing some reading on the arguments over lookup tables.

"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?