Solved

how to keep negative sign in sql statement

Posted on 2014-10-16
5
108 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 66

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

728 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