SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Join with Multiple IDs to multiple descriptions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cstokes91
Yak Posting Veteran

USA
58 Posts

Posted - 01/21/2013 :  12:05:19  Show Profile  Reply with Quote
Hello,

I am having an issue with showing data when there are multiple IDs that I am joining. When there are multiple IDs SQL Server has an issue putting them in a field. Here is my code that is erroring out...


SELECT  [CFSID]
      ,[RID]
      ,[ActionTakenIDs]
      ,cd.Description
      
  FROM ComplaintReport cr
  LEFT JOIN CombinedDesc cd ON cd.ID = cr.ActionTakenIDs


Here is what it looks like if I don't add the description. On CFSID 192173 where there is multiple ID fields it fails. When it is like CFSID 73290 with only one it works like a charm.


CFSID	RID	ActionTakenIDs
73290	NULL	1604
152320	128	1586
192173	NULL	1256,1582,1260


Here is what it looks like if there is only one actionTakenIDs

CFSID	RID	ActionTakenIDs	Description
73290	NULL	1604	        Traffic 


Here is what I want it to look like:

CFSID	RID	ActionTakenIDs	Description
73290	NULL	1604	        Traffic
192173	NULL	1256,1582,1260  Park, Beach, Coconut

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 01/21/2013 :  15:27:59  Show Profile  Reply with Quote
Is the CombinedDesc table normalized? Can you post the sample data in that table as well?

This would be almost trivial if the ComplaintReport (and CombinedDesc) were in 1NF. Because ComplaintReport is not, even assuming CombinedDesc is normalized, one would have to parse the comma-separated string, join to the CombinedDesc table and then concatenate the results.

In any case, please post the sample data in CombinedDesc table.
Go to Top of Page

cstokes91
Yak Posting Veteran

USA
58 Posts

Posted - 01/21/2013 :  15:35:59  Show Profile  Reply with Quote
Here is the sample data from CombinedDesc


ID	Description
1256	Park
1260	Coconut
1582	Beach


Yeah, this is a clients former database design. We changed their database design we are just trying to get the data out now and then I ran into this crap.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 01/21/2013 :  15:56:32  Show Profile  Reply with Quote
Since you have not posted readily consumable data, it is hard to test, but here is my attempt at doing this. You need a function that can split a comma-separated string into individual pieces. My example below uses the function that Jeff Moden has posted in this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153458 You can use others as well, just change the function name and the parameter names as appropriate.

;with cte1 as
(
	SELECT
		a.cfsid,
		a.rid,
		a.ActionTakenIDs,
		b.ItemNumber,
		b.Item
	FROM
		ComplaintReport  a
		CROSS APPLY  dbo.DelimitedSplit8K(ActionTakenIDs,',') b
)
SELECT
	c1.cfsid,
	c1.rid,
	c1.ActionTakenIDs,
	STUFF(b.Descriptions,1,1,'')
FROM
	cte1 c1
	CROSS APPLY
	(
		SELECT b.Description
		FROM CombinedDesc b
		WHERE b.ActionTakenId = c1.Item
		FOR XML PATH('')
	)b(Descriptions);
Not sure if this will preserve the ordering of the comma-separated tokens when reconstituted.
Go to Top of Page

cstokes91
Yak Posting Veteran

USA
58 Posts

Posted - 01/22/2013 :  10:30:23  Show Profile  Reply with Quote
For some reason after I have ran the function then I run the script you posted it returns this error:

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '.'.

Any ideas? There aren't any random periods or anything either.


;with cte1 as
(
	SELECT
		 a.cfsid
		,a.ReportOfficerID
		,a.ActionTakenIDs
		,b.ItemNumber
		,b.Item
	FROM [NewClient].[dbo].[ComplaintReport] a
		CROSS APPLY dbo.DelimitedSplit8K(a.ActionTakenIDs,',') b
)
SELECT
	c1.cfsid,
	c1.ReportOfficerID,
	c1.ActionTakenIDs,
	STUFF(b.Descriptions,1,1,'')
FROM
	cte1 c1
	CROSS APPLY
	(
		SELECT b.Description
		FROM newclient.dbo.CombinedDesc b
		WHERE b.ID = c1.Item
		FOR XML PATH('')
	)b(Descriptions);
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 01/22/2013 :  10:54:48  Show Profile  Reply with Quote
Not sure why. I assume you are on SQL 2008. If you are on a version earlier than SQL 2005, CROSS APPLY wouldn't work. Also, can you verify that the function has been installed using this query in your database? It should return one row if it is installed
SELECT * FROM sys.objects WHERE OBJECT_NAME(OBJECT_ID) = 'DelimitedSplit8K'
Go to Top of Page

cstokes91
Yak Posting Veteran

USA
58 Posts

Posted - 01/22/2013 :  10:58:06  Show Profile  Reply with Quote
Yes, I am on 2008 r2... Yes, it returned one row.


name	object_id	principal_id	schema_id	parent_object_id	type	type_desc	create_date	modify_date	is_ms_shipped	is_published	is_schema_published
DelimitedSplit8K	1956970098	NULL	1	0	IF	SQL_INLINE_TABLE_VALUED_FUNCTION	2013-01-22 09:05:51.160	2013-01-22 09:05:51.160	0	0	0
Go to Top of Page

cstokes91
Yak Posting Veteran

USA
58 Posts

Posted - 01/22/2013 :  11:14:47  Show Profile  Reply with Quote
Also, I ran both of these queries to make sure there weren't any random punctuation in the data and neither returned any rows...

SELECT *
FROM [NewClient].[dbo].[ComplaintReport]
where ActionTakenIDs = '%.%'

SELECT *
FROM [NewClient].[dbo].[CombinedDesc]
where Description = '%.%'

Edited by - cstokes91 on 01/22/2013 11:15:34
Go to Top of Page

cstokes91
Yak Posting Veteran

USA
58 Posts

Posted - 01/22/2013 :  11:31:45  Show Profile  Reply with Quote
Sorry this has been such a pain in the arse James and sticking out this far into it... Definitely appreciate the help, mate.

It is somewhere in this code. I played around with commenting a bit and it has something to do with the cross apply part of it.

Runs:

SELECT
a.cfsid
,a.ReportOfficerID
,a.ActionTakenIDs
--,b.ItemNumber
--,b.Item
FROM [NewClient].[dbo].[ComplaintReport] a
--CROSS APPLY dbo.DelimitedSplit8K(a.ActionTakenIDs,',') b


Fails:

SELECT
 a.cfsid
,a.ReportOfficerID
,a.ActionTakenIDs
,b.ItemNumber
,b.Item
FROM [NewClient].[dbo].[ComplaintReport] a
CROSS APPLY dbo.DelimitedSplit8K(a.ActionTakenIDs,',') b

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 01/22/2013 :  11:41:22  Show Profile  Reply with Quote
I created some test data and made changes to the code. (If you post readily consumable test data like I have in the code below, that makes it easier) You can copy and paste this code to run it
-- Consumable test data
CREATE TABLE #ComplaintReport
(CFSID INT, RID INT, ActionTakenIDs VARCHAR(32));
CREATE TABLE #CombinedDesc (id INT, [Description] VARCHAR(32));

INSERT INTO #ComplaintReport VALUES
(73290, NULL, '1604'),
(152320, 128, '1586'),
(192173	,NULL,'1256,1582,1260')

INSERT INTO #CombinedDesc VALUES
(1256,'Park'),
(1260,'Coconut'),
(1582,'Beach'),
(1604,'Traffic'),
(1586,'Weather');

-- Query
;with cte1 as
(
	SELECT
		a.cfsid,
		a.rid,
		a.ActionTakenIDs,
		b.ItemNumber,
		b.Item,
		d.[Description]
	FROM
		#ComplaintReport  a
		CROSS APPLY  master.dbo.DelimitedSplit8K(ActionTakenIDs,',') b
		INNER JOIN #CombinedDesc d ON d.id = b.Item
)
SELECT
	c1.cfsid,
	c1.rid,
	c1.ActionTakenIDs,
	STUFF(b.Descriptions,1,1,'')
FROM
	(SELECT DISTINCT cfsid, rid, ActionTakenIds FROM cte1) AS c1 
	CROSS APPLY
	(
		SELECT ','+b.[Description]
		FROM cte1 b
		WHERE b.cfsid = c1.cfsid
		FOR XML PATH('')
	)b(Descriptions);
	

DROP TABLE #ComplaintReport;
DROP TABLE #CombinedDesc;
Go to Top of Page

cstokes91
Yak Posting Veteran

USA
58 Posts

Posted - 01/22/2013 :  12:02:20  Show Profile  Reply with Quote
James,

Can't thank you enough. I figured it out... I was getting an error saying that it didn't recognize ActionTakenIDs on this line

CROSS APPLY master.dbo.DelimitedSplit8K(ActionTakenIDs,',') b

So I changed it to this:
CROSS APPLY master.dbo.DelimitedSplit8K(a.ActionTakenIDs,',') b

That was the period that threw it off. I removed the a. and get this error again...

'"ActionTakenIDs" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.'

Did a google search a ran this code:
ALTER DATABASE NewClient
SET COMPATIBILITY_LEVEL = 100

Now it works like a champ... Thanks again for all your help and patience!
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 01/22/2013 :  12:57:22  Show Profile  Reply with Quote
You are very welcome - glad to be of help.

When you change the compatibility level, you need to be careful, because some of the features that work in a lower compatibility level may have been discontinued. Go to this page to see if you were relying on any such features http://technet.microsoft.com/en-us/library/cc707782(v=sql.100).aspx
Go to Top of Page

cstokes91
Yak Posting Veteran

USA
58 Posts

Posted - 01/22/2013 :  14:43:35  Show Profile  Reply with Quote
Gotcha, it shouldn't be too much of a problem because once the data is out, the database will be deleted anyway...

I know I am asking too much again but I am fairly new/ never used with clauses before so I am getting some errors when putting it into my insert/select script.

How would I insert something like that into this where it will insert the data from the portion that was converted into the string? I tried putting it all in parenthesis and then in the select portion take out the select c1.cfsid, c1.ReportOfficerID,c1.ActionTakenIDs, and just leve the STUFF(b.Descriptions,1,1,'') but SQL is getting mad at the with statement.

Here is a similar conversion script to what I will be using:


INSERT INTO [CAD].[dbo].[CADNOTE] 
(
INCIDENTNO
,NOTE
) 

SELECT 
cConv.INCIDENTNO AS INCIDENTNO 
,--How would I get that code that you wrote into this with it just bring over that specific data?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 01/22/2013 :  15:09:51  Show Profile  Reply with Quote
The insert into clause should come just before the final select - something like this:
;with cte1 as
(
	SELECT
		a.cfsid,
		a.rid,
		a.ActionTakenIDs,
		b.ItemNumber,
		b.Item,
		d.[Description]
	FROM
		#ComplaintReport  a
		CROSS APPLY  master.dbo.DelimitedSplit8K(ActionTakenIDs,',') b
		INNER JOIN #CombinedDesc d ON d.id = b.Item
)
INSERT INTO [CAD].[dbo].[CADNOTE] 
(
	INCIDENTNO
	,NOTE
) 
SELECT
	c1.cfsid,
	--c1.rid,
	--c1.ActionTakenIDs,
	STUFF(b.Descriptions,1,1,'')
FROM
	(SELECT DISTINCT cfsid, rid, ActionTakenIds FROM cte1) AS c1 
	CROSS APPLY
	(
		SELECT ','+b.[Description]
		FROM cte1 b
		WHERE b.cfsid = c1.cfsid
		FOR XML PATH('')
	)b(Descriptions);
Go to Top of Page

cstokes91
Yak Posting Veteran

USA
58 Posts

Posted - 01/22/2013 :  16:54:42  Show Profile  Reply with Quote
Thanks again for everything man. It works great and I am finally able to get the last pieces for this data transfer. If you ever need someone to look over any of your scripts need a hand give me a shout. I am still learning a lot but once I learn something once I won't have to ask again. Haha.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 01/22/2013 :  17:52:21  Show Profile  Reply with Quote
You are quite welcome - glad to be of help.

And, thank you for the offer; I will remember it and will surely take you up on it when the day comes!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000