T Hoecherl
asked on
Add '#' to end of file
I have a stored procedure which is called by a vb.net program and the end result of the process is that a text file is generated. This is the T-SQL code for the stored procedure:
ALTER PROCEDURE [dbo].[udp_INTERCEPT_FILE] @Batch_Num VARCHAR(20)
AS
select col1
from (
Select -2 AS row_num, '<V1.0>,"' + substring(@Batch_Num, 11, 1 ) + substring(@Batch_Num, 14, 2) + 'I"' AS col1
UNION ALL
Select -1 AS row_num, '*,"' + substring(@Batch_Num, 7, 5) + substring(@Batch_Num, 14, 2) + 'i",,"PONUM",,,"' +
+ substring(@Batch_Num, 7, 5) + substring(@Batch_Num, 14, 2) + 'i.dat"'
UNION ALL
Select ROW_NUMBER() Over ( order by order_number, line_number,winpart,winpos ) AS row_num,
Cast( (ROW_NUMBER() Over ( order by order_number, line_number,winpart,winpos )) as varchar( 100 )) + ','
+ Cast(Quantity as varchar(25)) + ',0,"IG",'
+ CASE winpart
when 'FIXED' then Cast(xwpFixedUnitWidth as varchar(25))
else Cast(xwpVentUnitWidth as varchar(25))
end + ','
+ CASE winpart
when 'FIXED' then Cast(xwpFixedUnitHeight as varchar(25))
else Cast(xwpVentUnitHeight as varchar(25))
end
+ ',,"'
+ Isnull ( ( select top 1 item_number from pct_order_bom where pct_order_bom.Order_Number = pct_glass_labels.ORDER_NUM BER
AND pct_order_bom.LINE_Number = pct_glass_labels.LINE_Numb er
AND STOCKED = 'SPACER' ), 'Not Found') + '","GAS",,,,,,,,"' + substring(Order_Number,7,6 ) + '-' + Cast(line_number as varchar(5)) + '"' AS COL1_AFTER_ROW_NUM
from pct_glass_labels
where batch_Number = @Batch_Num
) as derived
order by row_num
It works perfectly except for one thing: I need to have a '#' at the end of the file on a row by itself. If I add this code:
UNION ALL
SELECT '#'
just before the ") as derived", I get this error:
"All queries combined using UNION, INTERSECT OR EXCEPT must have an equal number of expressions in their target list."
How can I get this '#' added to the end of my file?
ALTER PROCEDURE [dbo].[udp_INTERCEPT_FILE]
AS
select col1
from (
Select -2 AS row_num, '<V1.0>,"' + substring(@Batch_Num, 11, 1 ) + substring(@Batch_Num, 14, 2) + 'I"' AS col1
UNION ALL
Select -1 AS row_num, '*,"' + substring(@Batch_Num, 7, 5) + substring(@Batch_Num, 14, 2) + 'i",,"PONUM",,,"' +
+ substring(@Batch_Num, 7, 5) + substring(@Batch_Num, 14, 2) + 'i.dat"'
UNION ALL
Select ROW_NUMBER() Over ( order by order_number, line_number,winpart,winpos
Cast( (ROW_NUMBER() Over ( order by order_number, line_number,winpart,winpos
+ Cast(Quantity as varchar(25)) + ',0,"IG",'
+ CASE winpart
when 'FIXED' then Cast(xwpFixedUnitWidth as varchar(25))
else Cast(xwpVentUnitWidth as varchar(25))
end + ','
+ CASE winpart
when 'FIXED' then Cast(xwpFixedUnitHeight as varchar(25))
else Cast(xwpVentUnitHeight as varchar(25))
end
+ ',,"'
+ Isnull ( ( select top 1 item_number from pct_order_bom where pct_order_bom.Order_Number
AND pct_order_bom.LINE_Number = pct_glass_labels.LINE_Numb
AND STOCKED = 'SPACER' ), 'Not Found') + '","GAS",,,,,,,,"' + substring(Order_Number,7,6
from pct_glass_labels
where batch_Number = @Batch_Num
) as derived
order by row_num
It works perfectly except for one thing: I need to have a '#' at the end of the file on a row by itself. If I add this code:
UNION ALL
SELECT '#'
just before the ") as derived", I get this error:
"All queries combined using UNION, INTERSECT OR EXCEPT must have an equal number of expressions in their target list."
How can I get this '#' added to the end of my file?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER