Solved

how to keep negative sign in sql statement

Posted on 2014-10-16
5
104 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 27

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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Results to Excel File 18 77
monitoring configuration for SQL server DB 32 50
SQL Server maintenance plan 8 54
Sql case statement to calculate totals 5 37
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

697 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