Solved

how to keep negative sign in sql statement

Posted on 2014-10-16
5
89 Views
Last Modified: 2014-10-17
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
Comment
Question by:mgmhicks
  • 3
5 Comments
 

Author Comment

by:mgmhicks
ID: 40384158
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40384268
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
 

Author Comment

by:mgmhicks
ID: 40384334
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
 
LVL 26

Accepted Solution

by:
Sinisa Vuk earned 500 total points
ID: 40386122
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
 

Author Closing Comment

by:mgmhicks
ID: 40386702
thank you
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now