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.
Author |
Topic |
cstokes91
Yak Posting Veteran
72 Posts |
Posted - 2013-01-21 : 12:05:19
|
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 ActionTakenIDs73290 NULL 1604152320 128 1586192173 NULL 1256,1582,1260 Here is what it looks like if there is only one actionTakenIDsCFSID RID ActionTakenIDs Description73290 NULL 1604 Traffic Here is what I want it to look like:CFSID RID ActionTakenIDs Description73290 NULL 1604 Traffic192173 NULL 1256,1582,1260 Park, Beach, Coconut |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-21 : 15:27:59
|
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. |
|
|
cstokes91
Yak Posting Veteran
72 Posts |
Posted - 2013-01-21 : 15:35:59
|
Here is the sample data from CombinedDescID Description1256 Park1260 Coconut1582 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. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-21 : 15:56:32
|
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. |
|
|
cstokes91
Yak Posting Veteran
72 Posts |
Posted - 2013-01-22 : 10:30:23
|
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 10Incorrect 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); |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-22 : 10:54:48
|
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 installedSELECT * FROM sys.objects WHERE OBJECT_NAME(OBJECT_ID) = 'DelimitedSplit8K' |
|
|
cstokes91
Yak Posting Veteran
72 Posts |
Posted - 2013-01-22 : 10:58:06
|
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_publishedDelimitedSplit8K 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 |
|
|
cstokes91
Yak Posting Veteran
72 Posts |
Posted - 2013-01-22 : 11:14:47
|
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 = '%.%' |
|
|
cstokes91
Yak Posting Veteran
72 Posts |
Posted - 2013-01-22 : 11:31:45
|
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:SELECTa.cfsid,a.ReportOfficerID,a.ActionTakenIDs--,b.ItemNumber--,b.ItemFROM [NewClient].[dbo].[ComplaintReport] a--CROSS APPLY dbo.DelimitedSplit8K(a.ActionTakenIDs,',') b Fails:SELECT a.cfsid,a.ReportOfficerID,a.ActionTakenIDs,b.ItemNumber,b.ItemFROM [NewClient].[dbo].[ComplaintReport] aCROSS APPLY dbo.DelimitedSplit8K(a.ActionTakenIDs,',') b |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-22 : 11:41:22
|
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 dataCREATE 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; |
|
|
cstokes91
Yak Posting Veteran
72 Posts |
Posted - 2013-01-22 : 12:02:20
|
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,',') bSo I changed it to this:CROSS APPLY master.dbo.DelimitedSplit8K(a.ActionTakenIDs,',') bThat 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 NewClientSET COMPATIBILITY_LEVEL = 100Now it works like a champ... Thanks again for all your help and patience! |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-22 : 12:57:22
|
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 |
|
|
cstokes91
Yak Posting Veteran
72 Posts |
Posted - 2013-01-22 : 14:43:35
|
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? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-22 : 15:09:51
|
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); |
|
|
cstokes91
Yak Posting Veteran
72 Posts |
Posted - 2013-01-22 : 16:54:42
|
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. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-22 : 17:52:21
|
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! |
|
|
|
|
|
|
|