Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 119
  • Last Modified:

how to keep negative sign in sql statement

I have a script that takes data from a table and sends it to a script.  however if the amount field is negative its converting it to positive.   how can I keep the - sign when writing to file

thanks
0
mgmhicks
Asked:
mgmhicks
  • 3
1 Solution
 
mgmhicksAuthor Commented:
USE [TAG_eSite]
GO
/****** Object:  StoredProcedure [dbo].[Gas_ExportToTextFile]    Script Date: 10/15/2014 16:20:16 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[Gas_ExportToTextFile]  @CPropertyID char(10)
AS

-- Just Gas Billings
DECLARE @object int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
declare @ObjTextStream int

-- Create the FSO
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @object OUT
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object
    RETURN
END


declare @FileName varchar(100)
declare @Type varchar(1)
declare @PropertyID varchar(10)
declare @BldgID varchar(5)
declare @UnitID varchar(8)
declare @ResiID int
declare @RName varchar(52)
declare @TransDate varchar(10)
declare @Account varchar(10)
declare @Amount money
declare @PayType varchar(1)
declare @Ref varchar(20)
declare @Descr varchar(30)
declare @CurrPropID varchar(10)


-- Billing Sample
--B, PropertyID, BldgID, UnitID, ResiID, ResidentName, transdate, IncomeCode, Amount, Ref#, Description
-- Payment Sample
--P, PropertyID, BldgID, UnitID, ResiID, transdate, Amount, C , Check#, Description


declare pranav cursor for 
Select 
Type, RTrim(PropertyID), RTrim(BldgID), RTrim(UnitID), ResiID, IsNULL(RName,'') as ResidentName, Convert(varchar(10),BillDate,101), RTrim(IsNULL(Account,'')) as IncCode, abs(Amount), IsNULL(PayType,'') as PayType, RTrim(IsNULL(Ref,'')) as Reference,
Replace(Replace(Replace(RTrim(IsNULL(Descr,'')),'  ','_'),' ','_'),',','') as [Desc] FROM gas_tempASCIITableGas where PropertyID = @CPropertyID
order by PropertyID, BldgID, UnitID, ResiID, inccode, Type

open pranav
fetch pranav into @Type, @PropertyID, @BldgID, @UnitID, @ResiID, @RName, @TransDate, @Account, @Amount, @PayType, @Ref, @Descr

Set @FileName = 'C:\Program Files\GasBills\ASCII_ImportGas_' + @PropertyID + '_Billings.txt'
-- Get the TextStream object
EXEC @hr = sp_OAMethod @object, 'CreateTextFile', @ObjTextStream  out, @FileName
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object
    RETURN
END
Set @CurrPropID = @PropertyID

While @@Fetch_Status <> -1
BEGIN
	declare @Param sysname --(nvarchar(128))
	if @Type = 'B'
	BEGIN
		set @Param = @Type + ',' + @PropertyID + ',' + @BldgID + ',' + @UnitID + ',' + convert(varchar(3),@ResiID) + ',' +  @RName + ',' +  convert(varchar(10),@TransDate) + ',' + @Account + ',' + convert(varchar(10),@Amount) + ',' +  @Ref + ',' +  @Descr
		EXEC @hr = sp_OAMethod @ObjTextStream, 'WriteLine', null, @Param
		IF @hr <> 0
		BEGIN
		   EXEC sp_OAGetErrorInfo @ObjTextStream
		    RETURN
		END
	END
	fetch pranav into  @Type, @PropertyID, @BldgID, @UnitID, @ResiID, @RName, @TransDate, @Account, @Amount, @PayType, @Ref, @Descr

	if @CurrPropID <> @PropertyID
	BEGIN
		-- Destroy the TextStream Object
		EXEC @hr = sp_OADestroy @ObjTextStream
		IF @hr <> 0
		BEGIN
		   EXEC sp_OAGetErrorInfo @ObjTextStream
		    RETURN
		END
		Set @FileName = 'C:\TAG_ASCIIFiles\ASCII_ImportGas_' + @PropertyID + '_Billings.txt'
		-- Get the TextStream object
		EXEC @hr = sp_OAMethod @object, 'CreateTextFile', @ObjTextStream  out, @FileName
		IF @hr <> 0
		BEGIN
		   EXEC sp_OAGetErrorInfo @object
		    RETURN
		END
		Set  @CurrPropID = @PropertyID
	END
END

close pranav
deallocate pranav
-- Destroy the FileSystem Object
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object
    RETURN
END

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Ok, I'll bite.  Which column/value is converting to positive and should show negative?
Looking at the above code it's not real intuitive.
0
 
mgmhicksAuthor Commented:
set @Param = @Type + ',' + @PropertyID + ',' + @BldgID + ',' + @UnitID + ',' + convert(varchar(3),@ResiID) + ',' +  @RName + ',' +  convert(varchar(10),@TransDate) + ',' + @Account + ',' + convert(varchar(10),@Amount) + ',' +  @Ref + ',' +  @Descr

i believe its being set here.  its the @amount variable.   in db is negative amount, this seems to convert to positive number
0
 
Sinisa VukCommented:
you declare pranav cursor with: abs(Amount) ini it. This is a problem. Remove Abs - and put Abs later
if you need it: Abs(@Account)
Exists Sign function which will return -1 or +1 if you want.
0
 
mgmhicksAuthor Commented:
thank you
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now