Solved

how to keep negative sign in sql statement

Posted on 2014-10-16
5
83 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 25

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 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

14 Experts available now in Live!

Get 1:1 Help Now