I have a problem that I can not solve, my final goal is to make a trigger that runs every time it is inserted in the 'factura' table and then fill an intermediate table that only has one field (varchar max), because ?, because I have a component in Java that reads this field and generates a file.txt that he later used, but I have a problem that I can not solve, I was seeing what could be with a cursor or better still with a while loop but I am entangled, the BD manager that I use is SQL Server 2008, the select query that I use to generate the data that will be filled in the intermediate table in the trigger, consists of two parts, the header and the details, which I seek to do is that it only shows one row per header, but since there are more than 1 detail in some cases it shows repeated rows.
This is my query:
select
--HEADER(CB),
'CB' + '|' + CONVERT(varchar(10),(CONVERT(DATE, f.FECHA))) + '|' +
'20601140897' + '|' + '03' + '|' +
SUBSTRING(f.SERIE, 1, 2) + '0' + SUBSTRING(f.SERIE, 3, 2) + '-' +
REPLACE(STR(f.NUMERO, 8), SPACE(1), '0') + '|' +
CASE
WHEN f.CODIGO = '' THEN '99999999'
ELSE f.CODIGO
END + '|' + '0' + '|' +
CASE
WHEN f.NOMBRE ='' THEN 'Clientes varios'
ELSE f.NOMBRE
END + '|PEN|' +
CONVERT(varchar(13), (CAST(f.SUBTOTAL AS decimal(10, 2)))) +
'|0.00|0.00|' +
CONVERT(varchar(13), (CAST(f.IMPUESTO AS decimal(10, 2)))) +
'|0.00|0.00|0.00|0.00|' +
CONVERT(varchar(13), (cast(f.TOTAL as decimal(10, 2)))) + '|0.00|0.00|' +
'MONTO TOTAL' + '|||||||1000||||0.00|0.00|0.00||' + CHAR(13) + CHAR(10) +
--DETAIL(DF)
'DB' + '|' + 'NUMBER OF ROW' + '|' + d.PRODUCTO + '|' + 'NIU' + '|' + '1' + '|' +
d.DESCRIPCIO + '|' + CONVERT(varchar(13),(ROUND((d.PRECIO/1.18), 2))) + '|' +
CONVERT(varchar(13), (cast(d.PRECIO as decimal(10, 2)))) + '|' +
CONVERT(varchar(13), (ROUND(((d.PRECIO/1.18) * 0.18), 2))) + '|10|0.00||' +
CONVERT(varchar(13), (cast(d.TOTAL as decimal(10, 2)))) + '|0.00||0.00||'
FROM
factura f
FULL JOIN
detalle d ON f.NUMERO = d.NUMERO
FULL JOIN
clientes c ON f.CODIGO = c.codigo
And this is what it shows:
CB|2017-10-08|20601140897|03|B001-00002224|000700323|0|Clientes varios|PEN|25.42|0.00|0.00|4.58|0.00|0.00|0.00|0.00|30.00|0.00|0.00|MONTO TOTAL|||||||1000||||0.00|0.00|0.00|| DB|NUMBER OF ROW|220|NIU|1|TODO EL DIA|8.47|10.00|1.53|10|0.00||10.00|0.00||0.00||
CB|2017-10-08|20601140897|03|B001-00002224|000700323|0|Clientes varios|PEN|25.42|0.00|0.00|4.58|0.00|0.00|0.00|0.00|30.00|0.00|0.00|MONTO TOTAL|||||||1000||||0.00|0.00|0.00|| DB|NUMBER OF ROW|230|NIU|1|10 MIN FIN DE SEMANA|16.94|20.00|3.06|10|0.00||20.00|0.00||0.00||
What I look for:
CB|2017-10-08|20601140897|03|B001-00002224|000700323|0|Clientes varios|PEN|25.42|0.00|0.00|4.58|0.00|0.00|0.00|0.00|30.00|0.00|0.00|MONTO TOTAL|||||||1000||||0.00|0.00|0.00|| DB|NUMBER OF ROW|220|NIU|1|TODO EL DIA|8.47|10.00|1.53|10|0.00||10.00|0.00||0.00|| DB|NUMBER OF ROW|230|NIU|1|10 MIN FIN DE SEMANA|16.94|0.00|3.06|10|0.00||20.00|0.00||0.00||
Somebody could help me? or give me a structure to be able to achieve it, please, I'd be really grateful.
Here is the query, which will append the detail data when there is an identical header data. Query is slow as it involves JOIN on string and large column, You can replace CTE with temp table and by adding index may give a better performance.
;WITH CTE AS (
select
--HEADER(CB),
'CB' + '|' + CONVERT(varchar(10),(CONVERT(DATE, f.FECHA))) + '|' +
'20601140897' + '|' + '03' + '|' +
SUBSTRING(f.SERIE, 1, 2) + '0' + SUBSTRING(f.SERIE, 3, 2) + '-' +
REPLACE(STR(f.NUMERO, 8), SPACE(1), '0') + '|' +
CASE
WHEN f.CODIGO = '' THEN '99999999'
ELSE f.CODIGO
END + '|' + '0' + '|' +
CASE
WHEN f.NOMBRE ='' THEN 'Clientes varios'
ELSE f.NOMBRE
END + '|PEN|' +
CONVERT(varchar(13), (CAST(f.SUBTOTAL AS decimal(10, 2)))) +
'|0.00|0.00|' +
CONVERT(varchar(13), (CAST(f.IMPUESTO AS decimal(10, 2)))) +
'|0.00|0.00|0.00|0.00|' +
CONVERT(varchar(13), (cast(f.TOTAL as decimal(10, 2)))) + '|0.00|0.00|' +
'MONTO TOTAL' + '|||||||1000||||0.00|0.00|0.00||' + CHAR(13) + CHAR(10) HeaderData,
--DETAIL(DF)
'DB' + '|' + 'NUMBER OF ROW' + '|' + d.PRODUCTO + '|' + 'NIU' + '|' + '1' + '|' +
d.DESCRIPCIO + '|' + CONVERT(varchar(13),(ROUND((d.PRECIO/1.18), 2))) + '|' +
CONVERT(varchar(13), (cast(d.PRECIO as decimal(10, 2)))) + '|' +
CONVERT(varchar(13), (ROUND(((d.PRECIO/1.18) * 0.18), 2))) + '|10|0.00||' +
CONVERT(varchar(13), (cast(d.TOTAL as decimal(10, 2)))) + '|0.00||0.00||' DetailData
FROM factura f FULL JOIN detalle d ON f.NUMERO = d.NUMERO
FULL JOIN clientes c ON f.CODIGO = c.codigo
)
select HeaderData+STUFF((SELECT '|'+ DetailData
FROM CTE C
WHERE C.HeaderData=T.HeaderData
FOR XML PATH('')),1,1,'')
FROM CTE T
GROUP BY HeaderData