Customer issue
The fashion industry requires a strict management of product and stock in order to obtain maximum information and high quality data. The latter makes it possible to help the actors of this industry in decision-making. For their customer’s needs, Fit Retail develops interfaces that allow the PLM to communicate with other systems.
We provide PLM data exports from PLM to Cegid’s ERP. Among these data, we find, the sizes selected on the finished products. In the PLM, these sizes are stored in a list and separated by pipes « | », while the Orli ERP awaits for the sizes to be transmetted in distinct columns.
The difficulty lies in the datas transformation and restitution to the ERP in the expected format in order to facilitate the reading of all this information, keeping in mind that the sizes grids exist in different formats :
– Shoes sizes : 6.5, 7, 8, 8.5, etc. – Trousers sizes : 26, 28, 30… – Shirts sizes : S, M, L, XL…
To solve this problem, we can implement two T-SQL technics : the CTE (Common Table Expression) and the PIVOT.
– The CTE (Common Table Expression) : set of temporary results defined in the scope of execution of a single SELECT, INSERT, UPDATE, DELETE or CREATE VIEW instruction.
– The PIVOT : allows to simplify a complex series of instructions.
Let’s take a simple example of an article’s table :
These three rows use different size grids – one from XS to XL, one from 36 to 44 (EU sizes) – and one that has no XS and XL sizes on the second article.
The code for the solution is as follows :
Here is the result of this SQL script :
X being the position of the size within the size grid (this one being transmitted in another export).
If a size is selected, we return « X » in a column named GRIL_VENT_Tx. The size position is important so that Orli can link the selected size with its position in the size grid.
The selected size is also returned into a column GRIL_FAB_Tx. On the contrary, if a size is not selected, we return « . » in the concerned GRIL_VENT_Tx and GRIL_FAB_Tx columns.
GRIL_VENT_Tx and GRIL_FAB_Tx columns are numbered from 1 to 20. This means we authorize size grids with a maximum of 20 sizes.
Focus on the technical solution
First, we use a recursive CTE. It is not stored as an object and lasts only for the duration of the query. It provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is an expression in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained.
Our recursive CTE enables to parse the selected sizes list. Each size is returned as a row with its position « Ordre_Taille » relative to the size grid.
The result stored in the « tmp » table is as below :
One can notice the way that the « TaillesSelectionnees » lists are parsed.
By reading this table, we can make two observations :
– The list requires to be declared as nvarchar(max). If necessary, use this script beforehand :
– By default, a recursive CTE is limited to 100 iterations. To bypass this limit, the OPTION MAXRECURSION clause can be used. Make sure before to set your loop end condition correctly.
Eventually, in order to return each size (ie. each row returned by the CTE) into distinct columns, we use the PIVOT function discussed above.
A practical and effective response to our customers
The smart combination of two T-SQL functionnalities, recursive CTE and PIVOT, enables us to solve the issue of communication between PLM and Orli’s ERP.
All the provided information provided is therefore much more readable and understandable and the maintenance of these types of complex queries is highly improved. It is also optimized by using recursivity instead of a WHILE or CURSOR to loop on the list.
This process provides our clients clearer information that allows them to optimize the management of their products management their life cycle. This solution is a time and money saver for companies who also see this tool as a competitive advantage in a market with a very large number of players.
* Source : Microsoft online documentation.
