March 7, 2013 at 1:55 pm
Hello all. I’ve got an issue with flattening a 1 to many relationship. For argument’s sake, let’s say I have a database that keeps track of pencils manufactured. Pencils are manufactured in lots, so two lots of pencils will not have the same lot Id.
Once a lot comes off the line the pencils are stored for later distribution. The pencils may not all be stored in one location, however. It’s possible that out of a lot of 500 pencils 200 may be stored in cabinet A, shelf 2; and another 300 may be stored in cabinet F, shelf 1.
But don’t order yet! Because the legacy system didn’t record how many individual pencils from this lot are stored in each location - only the fact that some pencils in this lot are stored across many possible locations – the new system also has this horrible constraint. (And before you tell me this is poor design I agree wholeheartedly, but the client refuses to re-inventory 70,000 pencils to record their exact location, so this is not an option.)
So now saleswoman Brenda wants to pull pencils from lot Id ABC123. She searches the system and sees that lot ABC123 has 750 pencils left in stock. She places an internal order for 400 of those pencils. The system says “can do!” and places her order. It then reports the fact that of the 400 pencils she ordered of this lot, she will have to pull from the following locations – sorry, but we can’t be any more specific Brenda!
Pencils of lot ABC123 can be found in:
Cabinet A, shelf 1
Cabinet D, shelf 1
Cabinet D, shelf 2
Cabinet K, shelf 2
When it comes time to print out Brenda’s order I had been using a user-defined function to concatenate together a list of all potential storage locations so that we kept a one-line per lot Id and qty relationship. Here order would look a bit like this:
Lot Id ||QTY||LOCATIONS
------------------------------------------------------------------
ABC123||400||Cabinet A, shelf 1; Cabinet D, shelf 1; Cabinet D, shelf 2; Cabinet K, shelf 2
A workaround, yes – but it did work. But wait, there’s more! Now it has been decided that some of the pencils are of such a sophisticated design that foreign powers would want to take them from the factory. The solution is to encrypt all of the storage location data so no one can actually know where the pencils are stored until the very end of their order. Big problem though – you cannot open or close a symmetric key inside a UDF so now the ability to concatenate all possible storage locations into one line of the order is lost.
But if you actually JOIN to the storage locations table you wind up duplicating order lines; one line for each storage location. And this in turn then shows false quantities ordered which in turn then sum incorrectly at the footer of the order. Hooray!
So, does anyone have a suggested workaround that returns one line of multiple storage locations for each lot ordered?
Thanks. Kurt
March 7, 2013 at 2:22 pm
From what you are describing it sounds like what you have is over complicated for the results. It is however pretty much impossible to offer any kind of direct help because there are not enough details to work with. If you want specific coding help please take a few minutes to read the first link in my signature for best practices when posting questions.
You might also take a look at this article http://www.sqlservercentral.com/articles/comma+separated+list/71700/[/url]. It explains how to generate a comma separated list of rows which I think is basically what you are trying to do here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2013 at 3:09 pm
Hello Sean. Apologies, I will try to clarify.
I have a pencils table that describes the pencils.
pkPencilId||PencilDescription||LENGTH
1||Yellow, w/o erase||5”
2||Yellow, w/eraser||5”
3||Green, w/o eraser||6”
Then there is a PencilLot table.
pkLotId||flPencilId ||Created||QTY
100||2||11/15/2007||1000
200||1||12/21/2008||800
300||3||4/22/2011||2000
400||1||5/12/2011||900
Then we have a locations table that keeps track of what lots are spread over what locations.
pkStorageId||fkLotId||LOCATION
5||200||Cabinet A, shelf 1
6||200||Cabinet B, shelf 2
7||200||Cabinet K, shelf 2
8||100||Cabinet B, shelf 3
9||300||Cabinet D, shelf 2
10||300||Cabinet D, shelf 3
So if my saleswoman Brenda wants to pull 750 pencils made in lot #300, her order might look like this:
Sales Person: Brenda
PencilType||LotId||Location|| Qty Requested
3||300||Cabinet D, shelf 2||750
3||300||Cabinet D, shelf 3 ||750
Total for order:1,500
But of course she did not order TWO sets of 750 pencils. These line items are split into two because of the one --> many relationship between lotId and storage locations. The order SHOULD read:
Sales Person: Brenda
PencilType||LotId||Location|| Qty Requested
3||300||Cabinet D, shelf 2, Cabinet D, shelf 3||750
Total for order750
Further compounding the issue is that she can place more than 1 lotId on a single order. I think the example you steered me towards may be it, but I need to see if that example will work for multiple different lotIds on one order. Hope this explains a bit better.
Thanks.
March 7, 2013 at 3:27 pm
kpwimberger (3/7/2013)
Hello Sean. Apologies, I will try to clarify.
It would be better if you had read the article. The problem is it is really hard to visualize your tables and it is even harder to create queries when there are no tables. If you want actual coding assistance you need to provide ddl, sample data and desired output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2013 at 3:30 pm
I have been reading the article and am trying to apply that fix now. I thought the additional data I sent would have helped. I may be able to simplify actual structures and post query code, but that will take some time as the example I gace was obviously not what Iam actually working on.
I will see if I can whip something up if my attempts to apply the article fix fail.
Thanks.
Kurt
March 7, 2013 at 4:39 pm
kpwimberger (3/7/2013)
I have been reading the article and am trying to apply that fix now. I thought the additional data I sent would have helped. I may be able to simplify actual structures and post query code, but that will take some time as the example I gace was obviously not what Iam actually working on.I will see if I can whip something up if my attempts to apply the article fix fail.
Thanks.
Kurt
It doesn't have to be the actual data. Just post the data you previously posted in a readily consumable format like in the article.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 8, 2013 at 8:24 am
Thanks Jeff and Sean. The fix Sean steered me to to (using XML PATH and STUFF in a corelated query) worked! Now I just need to pull it all apart a few times to see if I can understand all the parts. I hate applying a fix I don't fully understand.
Thanks for the help.
Kurt
March 8, 2013 at 8:52 am
kpwimberger (3/8/2013)
Thanks Jeff and Sean. The fix Sean steered me to to (using XML PATH and STUFF in a corelated query) worked! Now I just need to pull it all apart a few times to see if I can understand all the parts. I hate applying a fix I don't fully understand.Thanks for the help.
Kurt
You are welcome. Glad you were able to figure it out.
+10000 to wanting to understand the code and not liking using code you don't understand.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2013 at 9:30 am
kpwimberger (3/8/2013)
Now I just need to pull it all apart a few times to see if I can understand all the parts. I hate applying a fix I don't fully understand.
That's refreshing!
Happy you found a fix!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2013 at 9:34 am
Thanks Sean and OPC:
I figure it just bites you on the.. uh, ankle... later on if you don't follow what was given. I had no idea that FOR XML could concatenate columns and customize output. I actually love learning new things so it all works out in the end.
Have a great day.
Kurt
March 8, 2013 at 12:05 pm
Okay all, just got out of a meeting and went back to it. I think I have it all sorted except for one, tiny portion:
, 'StorageLocation'
= STUFF
(
(
SELECT ','
+ COALESCE('Rm: ' + lbr2.Room, '')
+ COALESCE(', Cab: ' + ll2.fkCabinet, '')
+ COALESCE(', Bin: ' + CAST(DecryptByKey(ll2.Bin) AS varchar(20)), '')
+ COALESCE(', Box: ' + CAST(DecryptByKey(ll2.Box) AS varchar(20)), '')
+ COALESCE(', ' + CAST(DecryptByKey(ll2.StartColumn) AS varchar(2))
+ CAST(DecryptByKey(ll2.StartRow) AS varchar(2)), '')
+ COALESCE(' --> ' + CAST(DecryptByKey(ll2.EndColumn) AS varchar(2))
+ CAST(DecryptByKey(ll2.EndRow) AS varchar(2)), '')
FROM
dbo.tblLotLocation AS ll2
INNER JOIN
dbo.tblLocationBldgRoom AS lbr2 ON lbr2.pkLocationBldgRoomId = ll2.fkLocationBldgRoomId
WHERE
ll2.fkItemLotId = od.fkItemLotId
ORDER BY
pkLotLocationId
FOR XML PATH(''),
TYPE
).value('.','varchar(max)')
,1
,1
,''
)
This is the section I am a bit stuck on:
).value('.','varchar(max)')
I have read that .value() is an XQury function that accepts an XML data type and converts it to SQL Server data type. Makes sense.
From MSDN: value (XQuery, SQLType), where "XQuery - is the XQuery expression, a string literal, that retrieves data inside the XML instance" and "SQLType - Is the preferred SQL type."
But what is the XQuery '.' literal actually doing? Can I *assume* that since an empty string is sent to FOR XML PATH('') no root node name is created, and that the '.' argument in the XQuery is just looking at the unnamed root for its data?
Thanks.
Kurt
March 8, 2013 at 1:33 pm
But what is the XQuery '.' literal actually doing? Can I *assume* that since an empty string is sent to
FOR XML PATH('')no root node name is created, and that the '.' argument in the XQuery is just looking at the unnamed root for its data?
As a test, change your code to look like this and see if it makes more sense to see that the FOR XML piece of the query is actually returning an XML document.
The .value is actually just picking up the contents of the root level node of that document, i.e. the '.' node, and casting it to a VARCHAR(MAX).
, 'StorageLocation'
=
(
SELECT ','
+ COALESCE('Rm: ' + lbr2.Room, '')
+ COALESCE(', Cab: ' + ll2.fkCabinet, '')
+ COALESCE(', Bin: ' + CAST(DecryptByKey(ll2.Bin) AS varchar(20)), '')
+ COALESCE(', Box: ' + CAST(DecryptByKey(ll2.Box) AS varchar(20)), '')
+ COALESCE(', ' + CAST(DecryptByKey(ll2.StartColumn) AS varchar(2))
+ CAST(DecryptByKey(ll2.StartRow) AS varchar(2)), '')
+ COALESCE(' --> ' + CAST(DecryptByKey(ll2.EndColumn) AS varchar(2))
+ CAST(DecryptByKey(ll2.EndRow) AS varchar(2)), '')
FROM
dbo.tblLotLocation AS ll2
INNER JOIN
dbo.tblLocationBldgRoom AS lbr2 ON lbr2.pkLocationBldgRoomId = ll2.fkLocationBldgRoomId
WHERE
ll2.fkItemLotId = od.fkItemLotId
ORDER BY
pkLotLocationId
FOR XML PATH(''),
TYPE
)
edit: add quote
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2013 at 1:54 pm
Excellent! That's what I thought. Thanks.
Kurt
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply